[20130809]12c Clustering Factor(3).txt

[20130809]12c Clustering Factor(3).txt

参考链接，重复测试:
http://richardfoote.wordpress.com/2013/06/04/clustering-factor-calculation-improvement-part-iii-too-much-rope/

SQL> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SQL> create table bowie (id number, code number, text varchar2(30));

Table created.

SQL> insert into bowie select rownum,  trunc(dbms_random.value(0, 100)), 'DAVID BOWIE' from dual connect by level 

70000 rows created.

SQL> commit;

Commit complete.

SQL> EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', estimate_percent=> null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> create index bowie_code_i on bowie(code);

Index created.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor FROM user_tables t, user_indexes i WHERE

t.table_name = i.table_name AND i.index_name='BOWIE_CODE_I';

TABLE_NAME INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR

---------- ------------------------------ ---------- ---------- -----------------

BOWIE      BOWIE_CODE_I                          244      70000             22698

SQL> set autot traceonly ;

SQL> select * from bowie where code between 42 and 44;

2112 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1845943507

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |  2814 | 56280 |    68   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| BOWIE |  2814 | 56280 |    68   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("CODE"=42)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        259  consistent gets

          0  physical reads

          0  redo size

      27674  bytes sent via SQL*Net to client

        654  bytes received via SQL*Net from client

         12  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       2112  rows processed

--CF很大，而且取值code between 42 and 44;占3%，执行计划选择全表扫描。

SQL> set autot off

SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE', pname=>'TABLE_CACHED_BLOCKS', pvalue=>255);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', estimate_percent=> null, cascade=> true,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor FROM user_tables t, user_indexes i WHERE

t.table_name = i.table_name AND i.index_name='BOWIE_CODE_I';

TABLE_NAME INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR

---------- ------------------------------ ---------- ---------- -----------------

BOWIE      BOWIE_CODE_I                          244      70000               240

SQL> set autot traceonly;

SQL> alter system flush shared_pool;

System altered.

SQL> select * from bowie where code between 42 and 44;

2112 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 853003755

----------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |              |  2814 | 56280 |    17   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |  2814 | 56280 |    17   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |  2814 |       |     7   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CODE">=42 AND "CODE"

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        702  consistent gets

          0  physical reads

          0  redo size

      55129  bytes sent via SQL*Net to client

        654  bytes received via SQL*Net from client

         12  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       2112  rows processed

--为了消除recursive calls，可以执行多次。
--可以发现CF很小，执行计划选择使用索引，但是从逻辑读上看702，明显比259大，执行计划选择全表扫描更加合理。

-- 总之使用这个参数TABLE_CACHED_BLOCKS调整执行计划，还是有许多需要注意的地方。