下面教大家如何手工算出oracle执行计划中的cost值。

成本的计算方式如下：
Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ) / sreadtime


#SRds - number of single block reads 单块读个数     
#MRds - number of multi block reads  多块读个数     
#CPUCyles - number of CPU cycles     CPU时钟周期数  


sreadtim - single block read time    单块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒
mreadtim - multi block read time     多块读耗时(单位milliseconds 毫秒，1000毫秒等于1秒)
cpuspeed - CPU cycles per second     CPU频率(单位MHZ)   单位是秒


mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed 
sreadtim=ioseektim+db_block_size/iotfrspeed  

@脚本将在后面给出

SQL>create table aaa as select * from dba_objects where rownum<=10000;

SQL> conn scott/tiger 
Connected.
SQL> alter system set db_file_multiblock_read_count=16;
System altered.
SQL> explain plan for select count(*) from aaa;
Explained.

SQL> @getplan
‘general,outline,starts‘
Enter value for plan type:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 977873394
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    33   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AAA  | 10000 |    33   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> @getmreadtime    --一次多块读的时间
  mreadtim
----------
        42
1 row selected.
SQL> @getsreadtime   --一次单块读的时间
  sreadtim
----------
        12
1 row selected.
SQL> @getcputime   --消耗的cpu的时间
    cputim
----------
.928809822
1 row selected.
SQL> @getmreadnum             --scott.aaa全表扫描是多块读需要的次数
Enter value for owner: scott
Enter value for table_name: aaa
  MREADNUM
----------
    8.8125
1 row selected.
SQL> @gettablecost           --计算出成本
Enter value for mreadtime: 42
Enter value for mreadnum:  8.8125
Enter value for cputime: 0.928809822
Enter value for sreadtime: 12

(42*8.8125+0.928809822)/12
--------------------------
                30.9211508
1 row selected.
conn /as sysdba
@getparam_imp                   --查隐含参数
Enter value for parameter_name:_table_scan_cost_plus_one
_table_scan_cost_plus_one                          TRUE

SQL> conn scott/tiger 
Connected.
SQL> alter session set "_table_scan_cost_plus_one"=false;
Session altered.
SQL> explain plan for select count(*) from aaa;
Explained.
SQL> @getplan
‘general,outline,starts‘
Enter value for plan type:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 977873394


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    32   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AAA  | 10000 |    32   (0)| 00:00:01 |
-------------------------------------------------------------------

--以下是@脚本

--@getmreadtime
select (select pval1 from sys.aux_stats$ where pname = ‘IOSEEKTIM‘) +
       (select value from v$parameter where name = ‘db_file_multiblock_read_count‘) * 
       (select value from v$parameter where name = ‘db_block_size‘) / 
       (select pval1 from sys.aux_stats$ where pname = ‘IOTFRSPEED‘) "mreadtim"
   from dual;

--@getsreadtime
select (select pval1 from sys.aux_stats$ where pname = ‘IOSEEKTIM‘) +
       (select value from v$parameter where name = ‘db_block_size‘) /
       (select pval1 from sys.aux_stats$ where pname = ‘IOTFRSPEED‘) "sreadtim"
  from dual;  

--@getcputime
select (select distinct cpu_cost from plan_table where cpu_cost is not null)/
       (select pval1 from sys.aux_stats$ where sname=‘SYSSTATS_MAIN‘ and pname=‘CPUSPEEDNW‘)/
       1000 "cputim"
  from dual;

--@getmreadnum
select (select BLOCKS from dba_tables where owner=upper(‘&owner‘) and table_name=upper(‘&table_name‘))/
       (select value from v$parameter where name = ‘db_file_multiblock_read_count‘) "mreadnum"
  from dual;

@gettablecost
select (&mreadtime*&mreadnum+&cputime)/&sreadtime from dual;

--@getparam_imp  
SELECT nam.ksppinm NAME, val.ksppstvl VALUE  
  FROM sys.x$ksppi nam, sys.x$ksppsv val  
 WHERE nam.indx = val.indx  
   AND nam.ksppinm LIKE ‘%&&parameter_name%‘  
 ORDER BY 1;  
 

--@getplan
set feedback off
pro ‘general,outline,starts‘
pro
acc type prompt ‘Enter value for plan type:‘ default ‘general‘
select * from table(dbms_xplan.display) where ‘&&type‘=‘general‘;
select * from table(dbms_xplan.display(null, null,‘advanced -projection‘)) where ‘&&type‘=‘outline‘;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,‘ALLSTATS LAST‘)) where ‘&&type‘=‘starts‘;
set feedback on
undef type
/

转载请注明本文地址