ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 系统统计对成本的改变

系统统计对成本的改变

原创 Linux操作系统 作者:westzq1984 时间:2009-04-09 17:36:19 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    966357