分析如何影响CBO决策一例

SQL> conn evan/evanConnected.SQL> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionSQL> create table t as select object_id,object_name from dba_objects where 1=2;Table created.SQL> create index ind_t on t(object_id);Index created.SQL> insert into t select object_id,object_name from dba_objects;72486 rows created.SQL> commit;Commit complete.SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T'; NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZE---------- ----------- ---------- ------------SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T'; BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE---------- ----------- ------------- ------------ 0 0 0 15-APR-11 以上可知，t表和索引没有被分析。

 

使用dynamic_sampling hint来禁止动态采样，可以看出cbo采用了index range scan,生成了错误的执行计划,cbo估算满足条件的记录为4（Rows）。

SQL> set autotrace traceonlySQL> select /*+ dynamic_sampling(t 0) */ * from t where object_id>30;72457 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 4013845416-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 |316 | 0 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 4 |316 | 0 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND_T | 1 | | 0 (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID">30)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 10254 consistent gets 0 physical reads 0 redo size 3383807 bytes sent via SQL*Net to client 53650 bytes received via SQL*Net from client 4832 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72457 rows processed 使用dbms_stats.gather_table_stats对表和索引做分析.

可以看出cbo估算出的记录数为72459(Rows),和实际记录数很相近，从而采用table access full.

SQL> set autotrace offSQL> exec dbms_stats.gather_table_stats(user,'T');PL/SQL procedure successfully completed.SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T'; NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZE---------- ----------- ---------- ------------ 72486 29 370 15-APR-11SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T'; BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE---------- ----------- ------------- ------------ 1 261 72486 15-APR-11SQL> set autotrace traceonlySQL> select /*+ dynamic_sampling(t 0) */ * from t where object_id>30;72457 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 72459 | 2052K| 103 (1)| 00:00:02 ||* 1 | TABLE ACCESS FULL| T | 72459 | 2052K| 103 (1)| 00:00:02 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID">30)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5179 consistent gets 0 physical reads 0 redo size 3094035 bytes sent via SQL*Net to client 53650 bytes received via SQL*Net from client 4832 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72457 rows processedSQL> select * from t where object_id>30;72457 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 72459 | 2052K| 103 (1)| 00:00:02 ||* 1 | TABLE ACCESS FULL| T | 72459 | 2052K| 103 (1)| 00:00:02 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID">30)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5179 consistent gets 0 physical reads 0 redo size 3094035 bytes sent via SQL*Net to client 53650 bytes received via SQL*Net from client 4832 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72457 rows processed