首页 » ORACLE 9i-23ai » Clustering_Factor(索引的集群因子)对执行计划影响

Clustering_Factor(索引的集群因子)对执行计划影响

今天 在一个oracle高级调优的例子，提到Cluster_Factor的概念，觉的很有意思，在这分享一下

在大型数据库生产系统的运维中可能会遇到这样一个问题，一条查询语句，操作的是相同的表和数据，为什么在生产数据库上执行起来就很慢，而在备份数据库反而会很快。这其中一个重要原因就在于索引CLUSTER_FACTOR的不同。

下面看我的一个案例

anbob@ORCL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

anbob@ORCL> l
1   CREATE TABLE testord
2  (
3  ID NUMBER(32),
4  NAME VARCHAR2(80)
5  )
6  pctfree 0
7   storage
8   (
9   initial 1M
10   next 1M
11*  )

Table created.

anbob@ORCL> alter session set sql_trace=true;

Session altered.

anbob@ORCL> begin
2  for i in 1..70000 loop
3  insert into testord(id,name)
4  values (i,dbms_random.string('a',80));
5  end loop;
6  commit;
7  end;
8  /

PL/SQL procedure successfully completed.

anbob@ORCL> alter session set sql_trace=false;

Session altered.

格式化后trace file content  发现自动绑定变量的
********************************************************************************

begin
for i in 1..70000 loop
insert into testord(id,name)
values (i,dbms_random.string('a',80));
end loop;
commit;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      2.77       3.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.79       3.02          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 98
********************************************************************************

INSERT INTO TESTORD(ID,NAME)
VALUES
(:B1 ,DBMS_RANDOM.STRING('a',80))

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  70000     17.09      16.46          0        813      77429       70000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    70001     17.09      16.46          0        813      77429       70000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS

######################################end##############################

继续建第二个表
sql>CREATE TABLE testrdm
(
ID NUMBER(32),
NAME VARCHAR2(80)
)
pctfree 0
storage
(
initial 1M
next 1M
)

anbob@ORCL> set timing on

anbob@ORCL> insert into testrdm nologging
2  select * from testord order by dbms_random.random;

70000 rows created.

Elapsed: 00:00:00.85

anbob@ORCL> alter table testord add constraint pk_tord primary key(id);

Table altered.

Elapsed: 00:00:00.30
anbob@ORCL> alter table testrdm add constraint pk_trdm primary key(id);

Table altered.

Elapsed: 00:00:00.19

anbob@ORCL> run
1  select segment_name,segment_type,blocks,bytes/1024/1024 si
2  from user_segments
3  where segment_name in('TESTRDM',
4  'TESTORD',
5  'PK_TRDM',
6* 'PK_TORD')

SEGMENT_NAME         SEGMENT_TYPE             BLOCKS         SI
-------------------- -------------------- ---------- ----------
TESTORD              TABLE                       896          7
TESTRDM              TABLE                       896          7
PK_TORD              INDEX                       256          2
PK_TRDM              INDEX                       256          2

Elapsed: 00:00:00.09

anbob@ORCL> select * from testord where rownum<3;

ID NAME
---------- --------------------------------------------------------------------------------
358 KFQGzMArPRwNeqQZpmdCTfgPebYgBpyibFteghJenfTpxHbrjQlbrfFohpnBBzarXpsLYZDcRxrQnHGN
359 sJItUDWIuggihjVKUMAVgeTBOdsfmLCazvvWCxzRWYtFIZmmJsmUmZOHmAYHOYitcbEjgUcqluapEFnX

Elapsed: 00:00:00.01
anbob@ORCL> select * from testrdm where rownum<3;

ID NAME
---------- --------------------------------------------------------------------------------
62559 RVyUykDQqmFcDNMexXOKusBNpNEIMxWkcDKEUPgRdAmguAMSCxKVOaUGiDdKiaBTpQhXdtwbYBqpsOgr
21125 dphRLkNhqhkZEPwFChCQDxGLJOClttbbXQRaWTugWXVVWryBlsrOBtanDkCFdpINxBAairlYEXlaoFNv

Elapsed: 00:00:00.01

anbob@ORCL> exec dbms_stats.gather_TABLE_stats(ownname => user,tabname=>'TESTORD',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.45
anbob@ORCL> exec dbms_stats.gather_TABLE_stats(ownname => user,tabname=>'TESTRDM',cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30

anbob@ORCL> SELECT INDEX_NAME,clustering_factor from user_indexes where index_name like 'PK%';

INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
PK_TORD                                      795
PK_TRDM                                    69904

anbob@ORCL> conn system/oracle
Connected.
system@ORCL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
system@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> set autot trace
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
anbob@ORCL> conn system/oracle
Connected.
system@ORCL> grant plustrace to anbob;

Grant succeeded.

Elapsed: 00:00:00.09
system@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> set autot trace
anbob@ORCL> select * from testord where id>2000 and id<7000;

4999 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 1319006981

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |  5001 |   415K|    69   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTORD |  5001 |   415K|    69   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PK_TORD |  5001 |       |    12   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">2000 AND "ID"<7000)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
733  consistent gets
0  physical reads
0  redo size
493640  bytes sent via SQL*Net to client
4048  bytes received via SQL*Net from client
335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
4999  rows processed

anbob@ORCL> select * from testrdm where id>2000 and id<7000;

4999 rows selected.

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 4168707143

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  5010 |   415K|   182   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TESTRDM |  5010 |   415K|   182   (2)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"<7000 AND "ID">2000)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
1146  consistent gets
811  physical reads
0  redo size
473701  bytes sent via SQL*Net to client
4048  bytes received via SQL*Net from client
335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
4999  rows processed

anbob@ORCL>

ps:

CLUSTER_FACTOR对Oracle执行计划会产生重要影响。这个值越高，说明索引的使用效率将会越差。这个值会存在于一个区间内，区间的最小值为表占用的数据块数，最大值为表拥有的数据行数。上面的案例也可以看出cost、consistent gets，physical reads 要高出很多

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Cedrick Mccusker | #1
    2011-12-21 at 03:02

    Good morning! I was surfing arround on Bing and found your blog. I like the articles! I also have a website . Please feel free to leave a comment there.

上一篇：

下一篇：