ASSM带来的性能问题

ASSM通过增加数据的分布的随机性来减少争用。在ASSM下，并发的进程基本都选择不同的块来插入数据行。因此，在我们使用序列或者以
日期作为插入顺序的表并在其上面建立索引的话，可能将会导致索引的聚簇因子比较大。

SQL> SELECT TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('ASSM','MSSM');

TABLESPACE_NAME      SEGMENT_SPAC
-------------------- ------------
ASSM                 AUTO
MSSM                 MANUAL

SQL> create sequence assm_s;

Sequence created.

SQL> create sequence mssm_s;

Sequence created.

SQL> create table assm_test(id int,flag char(1)) tablespace assm;

Table created.

SQL> create table mssm_test(id int,flag char(1)) tablespace mssm;

Table created.

首先创建2序列和2个表，一个创建在ASSM管理的表空间下，另一个创建在手工段空间管理的表空间下。

SQL>  create or replace procedure load_assm_data(v_flag char)
  2  as
  3  begin
  4    for i in 1..1000 loop
  5      insert into assm_test values(assm_s.nextval,v_flag);
  6    end loop;
  7    commit;
  8* end;

Procedure created.

SQL> create or replace procedure load_mssm_data(v_flag char)
  2  as
  3  begin
  4    for i in 1..1000 loop
  5      insert into mssm_test values(mssm_s.nextval,v_flag);
  6    end loop;
  7    commit;
  8  end;
  9  /

Procedure created.

分别创建2个过程用来为表ASSM_TEST和MSSM_TEST加载数据。

SQL> declare
  2   jobname varchar2(30);
  3  BEGIN
  4    FOR I IN 0..4 LOOP
  5      JOBNAME:=DBMS_SCHEDULER.GENERATE_JOB_NAME;
  6      DBMS_SCHEDULER.CREATE_JOB(
  7      job_name=>JOBNAME,
  8      job_type=>'STORED_PROCEDURE',
  9      job_action=>'LOAD_ASSM_DATA',
 10      auto_drop=>TRUE,
 11      enabled=>FALSE,
 12      number_of_arguments=>1);
 13      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOBNAME,1,CHR(65+I));
 14      DBMS_SCHEDULER.ENABLE(JOBNAME);
 15    END LOOP;
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> declare
  2   jobname varchar2(30);
  3  BEGIN
  4    FOR I IN 0..4 LOOP
  5      JOBNAME:=DBMS_SCHEDULER.GENERATE_JOB_NAME;
  6      DBMS_SCHEDULER.CREATE_JOB(
  7      job_name=>JOBNAME,
  8      job_type=>'STORED_PROCEDURE',
  9      job_action=>'LOAD_MSSM_DATA',
 10      auto_drop=>TRUE,
 11      enabled=>FALSE,
 12      number_of_arguments=>1);
 13      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOBNAME,1,CHR(65+I));
 14      DBMS_SCHEDULER.ENABLE(JOBNAME);
 15    END LOOP;
 16  END;
 17  /

PL/SQL procedure successfully completed.

使用JOB的方式，同时启动5个并发进程，向表assm_test和mssm_test插入数据。

SQL> CREATE INDEX ASSM_TEST_IDX ON ASSM_TEST(ID);

Index created.

SQL> CREATE INDEX MSSM_TEST_IDX ON MSSM_TEST(ID);

Index created.

分别在2个表的ID列上建立索引。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ASSM_TEST',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'MSSM_TEST',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> COL TABLE_NAME FORMAT A20

SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME IN ('ASSM_TEST','MSSM_TEST');

TABLE_NAME               BLOCKS   NUM_ROWS
-------------------- ---------- ----------
ASSM_TEST                    28       5000
MSSM_TEST                    10       5000

SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM USER_INDEXES WHERE TABLE_NAME IN ('ASSM_TEST','MSSM_TEST');

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
MSSM_TEST_IDX                 1          11                 8
ASSM_TEST_IDX                 1          11              2441

很明显，ASSM下的空间管理方式下的聚簇因子远远大于手工段空间管理模式下的。

 

SQL> SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)BLOCK#
  2  FROM YSP.ASSM_TEST;

    BLOCK#
----------
       437
       440
       436
       739
       438
      1442
       740
      1459
       439

9 rows selected.

可以看到，表ASSM_TEST只有28个BLOCKS，实际表的数据只占用了9个BLOCK，但是聚簇因子却达到了2441。 这就是ASSM在避免了表争用问题
带来的一个副作用。

SQL> SELECT BLOCK#,COUNT(*)
  2  FROM (
  3  SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)BLOCK# ,FLAG
  4  FROM YSP.ASSM_TEST)
  5  GROUP BY BLOCK#;

    BLOCK#   COUNT(*)
---------- ----------
       437          1
       440          3
       436          3
       739          1
       438          2
      1442          1
      1459          1
       740          1
       439          1

9 rows selected.

可以看到大部分块，被一个进程使用，减少了争用。

而在MSSM下，一个块基本被2个进程争用。

SQL> SELECT BLOCK#,COUNT(*)
  2  FROM (
  3  SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)BLOCK# ,FLAG
  4  FROM YSP.MSSM_TEST)
  5  GROUP BY BLOCK#;

    BLOCK#   COUNT(*)
---------- ----------
       397          3
       400          3
       395          2
       401          2
       399          3
       396          2
       394          1
       398          3

8 rows selected.

下面看看，ASSM对执行计划的影响：

SQL> SELECT * FROM  MSSM_TEST WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1865800780

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    50 |   400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MSSM_TEST     |    50 |   400 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MSSM_TEST_IDX |    50 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"<=50)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

我们从5000行的数据里选取了50行，选择率大约为1/100。此时索引扫描相对较好。

SQL> SELECT * FROM  ASSM_TEST  WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 957178735

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    50 |   400 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ASSM_TEST |    50 |   400 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("ID"<=50 AND "ID">=1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

但是在ASSM下，ORACLE却选择了成本较高的全表扫描。
这就是ASSM的特性带来的对执行计划的影响。

如果使用索引，ORACLE计算出来的成本会比全表扫描高。
但实际走索引的效率会更好，从逻辑读取也可以看得出来。
SQL> SELECT /*+INDEX(ASSM_TEST)*/  * FROM  ASSM_TEST  WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2637445088

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    50 |   400 |    27   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ASSM_TEST     |    50 |   400 |    27   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ASSM_TEST_IDX |    50 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"<=50)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

我们可以通过如下方式校正这种不正确的执行计划：

1、重新组织表

SQL> CREATE TABLE ASSM_TEST_2 TABLESPACE ASSM AS SELECT * FROM ASSM_TEST ORDER BY ID;

Table created.

SQL> CREATE INDEX ASSM_TEST_2_IDX ON ASSM_TEST_2(ID);

Index created.

我们复制了一个与ASSM_TEST一模一样的表。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ASSM_TEST_2',CASCADE=>TRUE);;

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME,BLOCKS,NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME LIKE '%SSM%';

TABLE_NAME               BLOCKS   NUM_ROWS
-------------------- ---------- ----------
MSSM_TEST                    10       5000
ASSM_TEST_2                  12       5000
ASSM_TEST                    28       5000

SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM USER_INDEXES WHERE TABLE_NAME LIKE '%SSM%';

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
ASSM_TEST_2_IDX               1          11                 8
MSSM_TEST_IDX                 1          11                 8
ASSM_TEST_IDX                 1          11              2441

SQL> SET AUTOT TRACEONLY
SQL> SELECT * FROM  ASSM_TEST_2 WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3232560642

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    50 |   400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ASSM_TEST_2     |    50 |   400 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ASSM_TEST_2_IDX |    50 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"<=50)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

可以看到，重新组织表后，使ORACLE从全表扫描走了索引扫描。

2、使用SYS_OP_COUNTCHG()来校正CLUSTERING_FACTOR

SQL> ALTER SESSION SET SQL_TRACE TRUE;

Session altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ASSM_TEST',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET SQL_TRACE FALSE;

Session altered.

启用SQL跟踪，格式化跟踪文件，并在跟踪文件中找到如下SQL语句。
select /*+ no_parallel_index(t,"ASSM_TEST_IDX") dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_expand index(t,"ASSM_TEST_IDX") */ count(*) as nrw,count(distinct
  sys_op_lbid(60257,'L',t.rowid)) as nlb,count(distinct "ID") as ndk,
  sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "YSP"."ASSM_TEST" t where "ID" is not null

修改 sys_op_countchg(substrb(t.rowid,1,15),1) as clf  第二个参数为5(并发的进程数),并在SQLPLUS执行，
将得到的CLF值写回到数据字典中。。

select /*+ no_parallel_index(t,"ASSM_TEST_IDX") dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_expand index(t,"ASSM_TEST_IDX") */ count(*) as nrw,count(distinct
  sys_op_lbid(60257,'L',t.rowid)) as nlb,count(distinct "ID") as ndk,
  sys_op_countchg(substrb(t.rowid,1,15),5) as clf
from
 "YSP"."ASSM_TEST" t where "ID" is not null

其中,NRW=USER_INDEXES.NUM_ROWS,NLB=USER_INDEXES.LEAF_BLOCKS,NDK=USER_INDEXES.NUM_DISTINCT,CLF=USER_INDEXES.CLUSTERING_FACTOR。

 

SQL> select /*+ no_parallel_index(t,"ASSM_TEST_IDX") dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_expand index(t,"ASSM_TEST_IDX") */ count(*) as nrw,count(distinct
  4    sys_op_lbid(60257,'L',t.rowid)) as nlb,count(distinct "ID") as ndk,
  5    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  6  from
  7   "YSP"."ASSM_TEST" t where "ID" is not null;

       NRW        NLB        NDK        CLF
---------- ---------- ---------- ----------
      5000         11       5000          9

SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM USER_INDEXES WHERE TABLE_NAME LIKE '%SSM%';

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
ASSM_TEST_2_IDX               1          11                 8
MSSM_TEST_IDX                 1          11                 8
ASSM_TEST_IDX                 1          11              2441

SQL> exec dbms_stats.set_index_stats(user,'ASSM_TEST_IDX',CLSTFCT=>9);

PL/SQL procedure successfully completed.

SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM USER_INDEXES WHERE TABLE_NAME LIKE '%SSM%';

INDEX_NAME               BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ----------- -----------------
ASSM_TEST_2_IDX               1          11                 8
MSSM_TEST_IDX                 1          11                 8
ASSM_TEST_IDX                 1          11                 9

SQL> SET AUTOT TRACEONLY
SQL> SELECT * FROM  ASSM_TEST  WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2637445088

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    50 |   400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ASSM_TEST     |    50 |   400 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ASSM_TEST_IDX |    50 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"<=50)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

此时，ORACLE也是从全表扫描转为索引扫描。

此处有点小疑问，使用索引ASSM_TEST_IDX的逻辑读是35。如果正常的话，应该和前面使用索引得到的一致，应该是11才对。
但是在此，不知道为什么ORACLE得出的是35。
如果加个提示，逻辑读就是11了。
SQL> SELECT /*+INDEX(ASSM_TEST)*/ * FROM ASSM_TEST  WHERE ID BETWEEN 1 AND 50;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2637445088

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    50 |   400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ASSM_TEST     |    50 |   400 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ASSM_TEST_IDX |    50 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("ID">=1 AND "ID"<=50)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1561  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

明显都是使用的同一个索引。不知道ORACLE怎么计算出一个是35，一个是11。