根据Richard Foote的文章,做了如下验证：
Ref：Clustering Factor Calculation Improvement and provide some control over DBMS_STATS index clustering factor computation
[oracle@db-42 ~]$ unzip p13262857_112030_Generic.zip [oracle@db-42 ~]$ cd 13262857/ [oracle@db-42 13262857]$ ora si SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 10:44:32 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@db-42 13262857]$ /data/app1/oracle/product/11.2.0/db_1/OPatch/opatch apply Invoking OPatch 11.2.0.1.7 Oracle Interim Patch Installer version 11.2.0.1.7 Copyright (c) 2011, Oracle Corporation. All rights reserved. Oracle Home : /data/app1/oracle/product/11.2.0/db_1 Central Inventory : /data/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.7 OUI version : 11.2.0.3.0 Log file location : /data/app1/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_10-46-13AM.log Applying interim patch '13262857' to OH '/data/app1/oracle/product/11.2.0/db_1' Verifying environment and performing prerequisite checks... Do you want to proceed? [y|n] yes User Responded with: Y All checks passed. Backing up files... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Patch 13262857 successfully applied Log file location: /data/app1/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-05-14_10-46-13AM.log OPatch succeeded. [oracle@db-42 13262857]$ ora si SQL*Plus: Release 11.2.0.3.0 Production on Tue May 14 10:46:25 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 8017100800 bytes Fixed Size 2244192 bytes Variable Size 1828716960 bytes Database Buffers 6157238272 bytes Redo Buffers 28901376 bytes Database mounted. Database opened. SQL> SQL> @?/rdbms/admin/dbmsstat.sql Package created. No errors. Synonym created. Grant succeeded. Role created. Grant succeeded. Grant succeeded. Library created. SQL> SQL> @?/rdbms/admin/prvtstat.plb Package body created. No errors. SQL> SQL> @?/rdbms/admin/execstat.sql PL/SQL procedure successfully completed. SQL> SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 8017100800 bytes Fixed Size 2244192 bytes Variable Size 1828716960 bytes Database Buffers 6157238272 bytes Redo Buffers 28901376 bytes Database mounted. Database opened. SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE',pname=>'TABLE_CACHED_BLOCKS', pvalue=>42); PL/SQL procedure successfully completed. SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null); 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_ID_I'; 2 TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------------------------ ---------- ---------- ----------------- BOWIE BOWIE_ID_I 1126 300000 1035 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_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null); 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_ID_I'; 2 TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------------------------ ---------- ---------- ----------------- BOWIE BOWIE_ID_I 1126 300000 1035 SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE',pname=>'TABLE_CACHED_BLOCKS', pvalue=>1); PL/SQL procedure successfully completed. SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null); 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_ID_I'; 2 TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------------------------ ---------- ---------- ----------------- BOWIE BOWIE_ID_I 1126 300000 193228
eg: 重复一遍这个过程
SQL> truncate table bowie;
Table truncated.
SQL> exec bowie_proc
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_prefs(ownname=>user, tabname=>'BOWIE',pname=>'TABLE_CACHED_BLOCKS', pvalue=>1);
PL/SQL procedure successfully completed.
SQL> EXEC dbms_stats.gather_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null);
PL/SQL procedure successfully completed.
SQL> set autot off;
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_ID_I'; 2
TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
BOWIE BOWIE_ID_I 1126 300000 357685
SQL> set autot trace ;
SQL> select * from bowie where id between 42 and 429;
388 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 389 | 7780 | 310 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| BOWIE | 389 | 7780 | 310 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=429 AND "ID">=42)
Statistics
----------------------------------------------------------
9 recursive calls
1 db block gets
1532 consistent gets
0 physical reads
0 redo size
8659 bytes sent via SQL*Net to client
799 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
388 rows processed
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_index_stats(ownname=>user, indname=>'BOWIE_ID_I', estimate_percent=> null);
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_ID_I'; 2
TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
BOWIE BOWIE_ID_I 1126 300000 1396
SQL> set autot trace ;
SQL> select * from bowie where id between 42 and 429;
Execution Plan
----------------------------------------------------------
Plan hash value: 3472402785
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 389 | 7780 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BOWIE | 389 | 7780 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 519 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
14532 bytes sent via SQL*Net to client
799 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
388 rows processed