Computing cost -- full table Scan

The following describes how to manually calculate the cost value in the Oracle execution plan.

The cost is calculated as follows:
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 Number of CPU clock cycles


Sreadtim-single block read time (unit: milliseconds, 1000 milliseconds equals 1 second)
Mreadtim-multi block read time (unit: milliseconds, 1000 milliseconds equals 1 second)
Cpuspeed-CPU cycles per second CPU frequency (unit: MHz) Unit: seconds


Mreadtim = ioseektim + db_file_multiblock_count * db_block_size/iotftspeed
Sreadtim = ioseektim + db_block_size/iotfrspeed

@ The script will be provided later

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
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
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 -- the time of one multi-block read
Mreadtim
----------
42
1 row selected.
SQL> @ getsreadtime -- the time when a single block is read
Sreadtim
----------
12
1 row selected.
SQL> @ getcputime -- CPU consumed time
Cputim
----------
. 928809822
1 row selected.
SQL> @ getmreadnum -- Scott. AAA full table scan is required for multiple reads
Enter value for owner: Scott
Enter value for table_name: AAA
Mreadnum
----------
8.8125
1 row selected.
SQL> @ gettablecost -- calculate cost
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 -- check implicit Parameters
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
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 |
-------------------------------------------------------------------

-- The following is the @ script

[Email protected]
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;

[Email protected]
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;

[Email protected]
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;

[Email protected]
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;

[Email protected] _ 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;
 

[Email protected]
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
/

Reprinted please specify the address of this Article