以下教大家怎样手工算出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

/

转载请注明本文地址