基础知识之11g新特性用extended statistics 解决列相关性

最新推荐文章于 2024-12-09 15:13:09 发布

原创最新推荐文章于 2024-12-09 15:13:09 发布 · 902 阅读

CC 4.0 BY-SA版权

本文探讨了SQL查询性能优化的关键步骤，包括使用动态采样、表统计信息更新及多谓词查询优化，通过实例展示了如何提升查询效率并解决统计信息准确性问题。

SQL> set wrap off
--创建测试表，reap列有比较高的重复率
SQL> create table t as select object_id,floor(object_id/10000) reap from dba_obj
ects;

表已创建。

SQL> set autotrace traceonly explain
--2级动态采样
SQL> select * from t where object_id<10000;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  8494 |   215K|    41   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  8494 |   215K|    41   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"<10000)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> show parameter dynamic
SQL> show parameter dyna
--未动态采样
SQL> select /*+dynamic_sampling(t 0)*/* from t where object_id<10000;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   576 | 14976 |    40   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   576 | 14976 |    40   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"<10000)
--表级分析
SQL> exec dbms_stats.gather_table_stats('YWBZ','T');

PL/SQL 过程已成功完成。

SQL> select * from t where object_id<10000;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  8771 | 70168 |    41   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  8771 | 70168 |    41   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"<10000)
--多谓词导致执行计划信息不准确
SQL> select * from t where object_id<10000 and reap=1;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   975 |  7800 |    41   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   975 |  7800 |    41   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REAP"=1 AND "OBJECT_ID"<10000)

SQL> set autotrace off
--实际记录数0
SQL> select count(*) from t where object_id<10000 and reap=1;

  COUNT(*)
----------
         0
-- extended statistics
SQL> exec dbms_stats.gather_table_stats('YWBZ','T',METHOD_OPT=>'FOR ALL COLUMNS
SIZE SKEWONLY FOR COLUMNS(OBJECT_ID,REAP) SIZE SKEWONLY');

PL/SQL 过程已成功完成。
--验证列相关性问题解决，统计信息较准确
SQL> select * from t where object_id<10000 and reap=1;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  195 |  960 |    21   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  195 |  960 |    21   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"<10000 AND "REAP"=1)