Oracle高级SQL调优：CLUSTER_FACTOR案例研究

大家在大型数据库生产系统的运维中可能会遇到这样一个问题，一条查询语句，操作的是相同的表和数据，为什么在生产数据库上执行起来就很慢，而在备份数据库反而会很快。这其中一个重要原因就在于索引CLUSTER_FACTOR的不同。
　　Oracle数据库下，索引在做完统计分析后，会获得很多重要信息，其中之一就是CLUSTER_FACTOR，CLUSTER_FACTOR表示索引数据顺序和表数据顺序的一致性，关于CLUSTER_FACTOR的理论和机制分析见随后作者的文章，Oracle高级SQL调优之：CLUSTER_FACTOR机制研究。
　　CLUSTER_FACTOR的精彩之处就在于，能借此区分看来貌似完全相同的情况：表结构、表数据和索引完全相同，但就是表数据行的存储顺序不同。下面以案例的形式加以分析。
　　1. 研究结论
　　CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差。这个值会存在于一个区间内，区间的最小值为表占用的数据块数，最大值为表拥有的数据行数。
　　2. 研究对象
　　研究对象为两个数据表TESTCF和TESTCF2，两者的数据结构相同，都只有两列：ID列，整数型，name列，字符型，80个字符，数据相同，都8万行，占用1024个数据块。不同的在于两个表的数据行的存储顺序不同，TESTCF表的数据，按照ID值从小到大的顺序依次存储，而TESTCF2表的数据，随机杂乱存储。
　　3. 案例实验过程
　　3.1 系统配置：
　　Oracle 11.1.0.6，初始化参数optimizer_index_cost_adj为默认值100
　　SQL> SELECT * FROM v$version;
　　BANNER
　　--------------------------------------------------------------------------------
　　Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
　　PL/SQL Release 11.1.0.6.0 - Production
　　CORE 11.1.0.6.0 Production
　　TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
　　NLSRTL Version 11.1.0.6.0 – Production
　　SQL> SELECT name, type, value FROM v$parameter p WHERE p.name = ’optimizer_index_cost_adj’;
　　NAME TYPE VALUE
　　------------------------------ ---------- --------------------
　　optimizer_index_cost_adj 3 100 　3.2 创建表TESTCF和TESTCF2
　　设置表TESCF控制其行长度和行数使得总共占用约1024个数据块
　　表定义：每行至少80个字节，共8万行，PCTFREE = 0，初始盘区和NEXT盘区都为1M
　　3.2.1 创建表TESTCF，并产生数据
　　数据的两列，分别由类序列值和随机函数产生，随机函数直接产生80位长的字符
　　SQL> CREATE TABLE TESTCF
　　2 (
　　3 ID NUMBER(32),
　　4 NAME VARCHAR2(80)
　　5 )
　　6 TABLEspace USERS
　　7 pctfree 0
　　8 initrans 1
　　9 maxtrans 255
　　10 storage
　　11 (
　　12 initial 1M
　　13 next 1M
　　14 minextents 1
　　15 maxextents unlimited
　　16 );
　　表已创建。
　　已用时间: 00: 00: 00.06
　　SQL> begin
　　2 for i in 1..80000 loop
　　3 insert into TESTCF(id, name)
　　4 values(i,dbms_random.string(’a’,80));
　　5 end loop;
　　6 commit;
　　7 end;
　　8 /
　　PL/SQL 过程已成功完成。
　　已用时间: 00: 00: 18.53 　3.2.2 创建表TESTCF2
　　该表和表TESTCF结构相同，且数据相同。将TESTCF表的所有数据灌入TESTCF2以获得相应信息。
　　重要：灌入时，让数据随机的进入TESTCF2，由dbms_random控制员Ｖぷ愎坏乃婊浴?BR>　　SQL> CREATE TABLE TESTCF2
　　2 (
　　3 ID NUMBER(32),
　　4 NAME VARCHAR2(80)
　　5 )
　　6 TABLEspace USERS
　　7 pctfree 0
　　8 initrans 1
　　9 maxtrans 255
　　10 storage
　　11 (
　　12 initial 1M
　　13 next 1M
　　14 minextents 1
　　15 maxextents unlimited
　　16 );
　　表已创建。
　　已用时间: 00: 00: 00.04
　　SQL>
　　SQL> insert into TESTCF2 nologging
　　2 SELECT * FROM TESTCF order by dbms_random.random;
　　已创建80000行。
　　已用时间: 00: 00: 01.28
　　SQL>
　　SQL> commit;
　　提交完成。
　　已用时间: 00: 00: 00.00 　3.3 给两个表都创建PK
　　SQL> alter TABLE TESTCF add constraint pk_TESTCF primary key(id);
　　表已更改。
　　已用时间: 00: 00: 00.71
　　SQL>
　　SQL> alter TABLE TESTCF2 add constraint pk_TESTCF2 primary key(id);
　　表已更改。
　　已用时间: 00: 00: 00.37
　　表都为1024个数据块，索引都为256个数据块。
　　SQL> SELECT t.SEGMENT_NAME, t.SEGMENT_TYPE, t.BLOCKS FROM user_segments t WHERE t.SEGMENT_NAME like ’%TESTCF%’;
　　SEGMENT_NAME SEGMENT_TYPE BLOCKS
　　-------------------- ------------------------------------ ----------
　　TESTCF2 TABLE 1024
　　PK_TESTCF INDEX 256
　　TESTCF TABLE 1024
　　PK_TESTCF2 INDEX 256
　　已用时间: 00: 00: 00.10
　　3.4 查看数据样例
　　SQL> SELECT * FROM TESTCF WHERE rownum < 3;
　　ID NAME
　　---------- --------------------------------------------------------------------------------
　　1 XMaMnroMsEWEamYPDopXkESqZkNbQrxlOeXsaHIGZIRrAnrTzPRtoOawwooEimyGjtwBuhWcxHPlsKKY
　　2 AeVFFXiLTLwJtGNJCOtvUOvwWgfhZkVxTJJoKgRDFtKonklzVIgNZFUXLAnfHDImVGxDnfMHHEjIzhvs
　　已用时间: 00: 00: 00.00
　　SQL> SELECT * FROM TESTCF2 WHERE rownum < 3;
　　ID NAME
　　---------- --------------------------------------------------------------------------------
　　39312 uWNqugvticIHolgfCcbNIVHOUTESzhVPhwLJeEydkUKfuywcCkiKyRkPqSIuNRJQYURSqeJnwmsDTEqW
　　41453 cOSqPzChzGBYkHnJbhIGbwUYBKquCBRcTNHbyHyVjdNItSxpxDKWXzSdYIkSBUJSUIziOleLLWPVOSNy
　　已用时间: 00: 00: 00.01 　3.5分别产生统计数据，然后查看两个表PK索引的CLUSTER_FACTOR
　　可以看出两表PK索引的CLUSTER_FACTOR值相差甚远。
　　TESTCF表的值为908，接近于表的数据块数(1024)，
　　TESTCF2表的值为79913，接近于表的数据行数(80000)，
　　这说明，当数据行的存储顺序和索引顺序越接近，CLUSTER_FACTOR越小，越有利于使用索引。
　　SQL> begin
　　2 dbms_stats.gather_TABLE_stats(ownname => user,tabname => ’TESTCF’,cascade => true);
　　3 end;
　　4 /
　　PL/SQL 过程已成功完成。
　　已用时间: 00: 00: 00.93
　　SQL> begin
　　2 dbms_stats.gather_TABLE_stats(ownname => user,tabname => ’TESTCF2’,cascade => true);
　　3 end;
　　4 /
　　PL/SQL 过程已成功完成。
　　已用时间: 00: 00: 00.45
　　SQL> SELECT i.index_name, i.CLUSTERING_FACTOR FROM dba_indexes i WHERE i.index_name like ’PK_TESTCF%’;
　　INDEX_NAME CLUSTERING_FACTOR
　　------------------------------ -----------------
　　PK_TESTCF 908
　　PK_TESTCF2 79913
　　3.6 测试CLUSTER_FACTOR对执行计划的影响
　　执行相同的查询语句，获得一段连续id内的数据行，以检查CLUSTER_FACTOR对执行计划的影响
　　从实验可以看出，CLUSTER_FACTOR对执行计划产生了巨大影响，这使得
　　a) TESTCF表的执行计划，走的是INDEX RANGE SCAN，而test2表走的是TABLE ACCESS FULL
　　同事这导致TESTCF表的总cost为83，TESTCF2表的总cost为210，是后者的四倍。
　　b) TESTCF表的物理读和一致性读远小少于TESTCF2。
　　SQL> alter system flush buffer_cache;
　　系统已更改。
　　已用时间: 00: 00: 00.06
　　SQL> set autotrace traceonly;
　　SQL> SELECT * FROM TESTCF WHERE id > 2000 and id < 8000;
　　已选择5999行。
　　已用时间: 00: 00: 00.65 　执行计划
　　----------------------------------------------------------
　　Plan hash value: 2216396729
　　--------------------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
　　--------------------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 6001 | 498K| 83 (0)| 00:00:0
　　| 1 | TABLE ACCESS BY INDEX ROWID| TESTCF | 6001 | 498K| 83 (0)| 00:00:0
　　|* 2 | INDEX RANGE SCAN | PK_TESTCF | 6001 | | 14 (0)| 00:00:0
　　--------------------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - access("ID">2000 AND "ID"<8000)
　　统计信息
　　----------------------------------------------------------
　　320 recursive calls
　　0 db block gets
　　929 consistent gets
　　121 physical reads
　　0 redo size
　　592212 bytes sent via SQL*Net to client
　　4774 bytes received via SQL*Net FROM client
　　401 SQL*Net roundtrips to/FROM client
　　5 sorts (memory)
　　0 sorts (disk)
　　5999 rows processed
　　SQL> alter system flush buffer_cache;
　　系统已更改。
　　已用时间: 00: 00: 00.03
　　SQL> set autotrace traceonly;
　　SQL> SELECT * FROM TESTCF2 WHERE id > 2000 and id < 8000;
　　已选择5999行。
　　已用时间: 00: 00: 00.59 　执行计划
　　----------------------------------------------------------
　　Plan hash value: 4178501150
　　-----------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　-----------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | 6001 | 498K| 210 (2)| 00:00:03 |
　　|* 1 | TABLE ACCESS FULL| TESTCF2 | 6001 | 498K| 210 (2)| 00:00:03 |
　　-----------------------------------------------------------------------------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　1 - filter("ID"<8000 AND "ID">2000)
　　统计信息
　　----------------------------------------------------------
　　312 recursive calls
　　0 db block gets
　　1383 consistent gets
　　944 physical reads
　　0 redo size
　　568273 bytes sent via SQL*Net to client
　　4774 bytes received via SQL*Net FROM client
　　401 SQL*Net roundtrips to/FROM client
　　5 sorts (memory)
　　0 sorts (disk)
　　5999 rows processed
　　SQL>