ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle高级SQL调优:CLUSTER_FACTOR案例研究

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

原创 Linux操作系统 作者:kiddwyl 时间:2019-04-08 10:15:06 0 删除 编辑
大家在大型数据库生产系统的运维中可能会遇到这样一个问题,一条查询语句,操作的是相同的表和数据,为什么在生产数据库上执行起来就很慢,而在备份数据库反而会很快。这其中一个重要原因就在于索引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控制员Vぷ愎坏乃婊?浴?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>

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

上一篇: CBO学习笔记
请登录后发表评论 登录
全部评论

注册时间:2004-08-27

  • 博文量
    59
  • 访问量
    42265