系統統計對成本的改變

SQL> select pname,pval1 from  sys.aux_stats$ where sname= 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                      1171.5678
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

SQL> show parameter db_file_mul

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_file_multiblock_read_count        integer                          16

MBRC = db_file_multiblock_read_count = 16
SREADTIM = ioseektim + db_block_size / iotrfrspeed = 10 + 8192/4096 = 12
MREADTIM = ioseektim + db_file_multiblock_read_count * db_block_size / iotrfrspeed = 10 + (8192*16)/4096 =42

SQL> CREATE TABLE test (ID NUMBER,rn NUMBER,flag CHAR(2000));

Table created.

SQL> INSERT INTO TEST
  2     SELECT ROWNUM, MOD(ROWNUM, 100), 'aaaa'
  3             FROM (SELECT 1 FROM DBA_TABLES A, DBA_TABLES B WHERE ROWNUM <= 100000);

SQL> CREATE INDEX idx_test ON test(rn)
  2  ;
 
SQL> BEGIN
  2  dbms_stats.gather_table_stats(NULL,'TEST',method_opt => 'for all columns size 1',cascade=>TRUE);
  3  END;
  4  /

SQL>  SELECT a.num_rows,a.blocks FROM User_Tables A WHERE table_name='TEST';

  NUM_ROWS     BLOCKS
---------- ----------
    100000      33557

SQL> SELECT column_name,density,num_nulls,num_distinct FROM user_tab_columns WHERE table_name = 'TEST';

COLUMN_NAME                       DENSITY  NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
ID                                 .00001          0       100000
RN                              .00990099          0          101
FLAG                                    1          0            1

SQL> SELECT blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows FROM user_indexes WHERE index_name = 'IDX_TEST';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
---------- ----------- ------------- ----------------- ----------
         1         196           100            100000     100000

SQL> SELECT MAX(rn),MIN(rn) FROM TEST;

   MAX(RN)    MIN(RN)
---------- ----------
        99          0

SQL> SELECT * FROM test WHERE rn BETWEEN 10 AND 20;

11000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12081 |    23M|  7361   (1)| 00:01:29 |
|*  1 |  TABLE ACCESS FULL| TEST | 12081 |    23M|  7361   (1)| 00:01:29 |
--------------------------------------------------------------------------

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

   1 - filter("RN"<=20 AND "RN">=10)

COST = #SRds + #MRds * mreadtim / sreadtim + #CPUCycles /( cpuspeed * sreadtim)
     = 0 + 33557/16 * (42/12) + ??
     = 7341 + ??

大概和成本接近，下面嘗試下放大 mreadtim / sreadtim 的值，看看對成本的影響

SQL> BEGIN
  2    dbms_stats.set_system_stats('SREADTIM',5.0);
  3    dbms_stats.set_system_stats('MREADTIM',30.0);
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> SELECT * FROM test WHERE rn BETWEEN 10 AND 20;

11000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12081 |    23M|  7357   (1)| 00:01:29 |
|*  1 |  TABLE ACCESS FULL| TEST | 12081 |    23M|  7357   (1)| 00:01:29 |
--------------------------------------------------------------------------

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

   1 - filter("RN"<=20 AND "RN">=10)
  
--變化的很少，而且，理論上應該升高的COST卻降低了
  
SQL> BEGIN
  2  dbms_stats.set_system_stats('CPUSPEED',500);
  3  dbms_stats.set_system_stats('SREADTIM',5.0);
  4  dbms_stats.set_system_stats('MREADTIM',30.0);
  5  dbms_stats.set_system_stats('MBRC',16);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.
 
SQL> SELECT * FROM test WHERE rn BETWEEN 10 AND 20;

11000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 12081 |    23M| 12143   (1)| 00:01:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     | 12081 |    23M| 12143   (1)| 00:01:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST | 12081 |       |    26   (4)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("RN">=10 AND "RN"<=20)

--執行計劃變了
SQL> SELECT /*+full(test)*/* FROM test WHERE rn BETWEEN 10 AND 20;

11000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12081 |    23M| 12691   (1)| 00:01:04 |
|*  1 |  TABLE ACCESS FULL| TEST | 12081 |    23M| 12691   (1)| 00:01:04 |
--------------------------------------------------------------------------

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

   1 - filter("RN"<=20 AND "RN">=10)

COST = #SRds + #MRds * mreadtim / sreadtim + #CPUCycles /( cpuspeed * sreadtim)
     = 0 + 33557/16 * (30/5) + ??
     = 12584 + ??
    
可以看到全表掃描的成本已經被放大，而SQL選用了索引，貌似相關的系統統計必須要齊全，才能正確使用這些統計值來計算成本

在來複習下索引的成本計算：
COST(IO) = blevel + selectivity index * leaf + selectivity table * clustering_factor 
         = 1 + 196 * 0.12  + 100000 * 0.12
         = 12125 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-586912/，如需轉載，請註明出處，否則將追究法律責任。