系統統計對成本的改變

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/，如需轉載，請註明出處，否則將追究法律責任。

系統統計對成本的改變

相關文章