Oracle高级SQL调优之：cluster_factor案例研究

萧田国 EMAIL/MSN：xiaotg#gmail.com 转载请注明出处

摘要：

Cluster_factor是Oracle索引机制中的一个很重要的参数，往往对Oracle执行计划的路径选择产生重大影响，现在网上的关于这方面的文章较少，有限的几篇也语焉不详，甚至多有纰漏。本文以案例的形式对此进行深入浅出的剖析，为关键大型数据库生产系统提供调优依据。

大家在大型数据库生产系统的运维中可能会遇到这样一个问题，一条查询语句，操作的是相同的表和数据，为什么在生产数据库上执行起来就很慢，而在备份数据库反而会很快。这其中一个重要原因就在于索引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表的数据，随机杂乱存储。

	TESTCF	TESTCF2
数据存储方式	按ID值从小到大依次存储	随机存储
PK索引名称（建立于ID列）	PK_TESTCF	PK_TESTCF2
PK索引的cluster_factor	908	79913
PK索引与数据行的混乱度	低	高
按id > 2000 and id < 8000查询时的执行路径	INDEX RANGE SCAN	TABLE ACCESS FULL

3. 案例实验过程

3.1 系统配置：

Oracle 10.2.0.1，初始化参数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表的所有数据灌入TESTCF2以获得相应信息。

重要：灌入时，让数据随机的进入TESTCF2，由dbms_random控制随机性

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

3.4 查看表和各自索引的数据块数量

表都为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.5 查看数据样例

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.6分别产生统计数据，然后查看两个表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.7 测试cluster_factor对执行计划的影响

执行相同的查询语句，获得一段连续id内的数据行，以检查cluster_factor对执行计划的影响

从实验可以看出，cluster_factor对执行计划产生了巨大影响，这使得

a) TESTCF表的执行计划，走的是INDEX RANGE SCAN，而test2表走的是TABLE ACCESS FULL

同事这导致TESTCF表的总cost为83，TESTCF2表的总cost为210，是后者的四倍

b) TESTCF表的物理读和一致性读远小于

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>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/426772/viewspace-1009583/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/426772/viewspace-1009583/