系统统计对成本的改变
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博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-586912/ ，如需转载，请注明出处，否则将追究法律责任。
