Cost = (#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed ) / sreadtim
#SRDs is the number of single block reads
#MRDs is the number of multi block reads
#CPUCycles is the number of CPU Cycles *)
sreadtim is the single block read time
mreadtim is the multi block read time
cpuspeed is the CPU cycles per second Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.
| Selectivity | A number between 0 and 1 which corresponds to the (assumed) ratio of selected to probed records in filtering predicates (0: no records, 1: all records are returned). The selectivity is not visible in an execution plan. |
| Cardinality | The number of rows returned by a plan operation («row source»). The cardinality is stored in the column cardinality in plan tables. |
| CPU speed | |
| I/O throughput | |
| Available Memory |
select
count(*) cnt,
ratio
from (
select
pln.operation,
pln.options,
pln.object_name,
pln.object_owner,
pln.cost,
pln.cpu_cost,
pln.io_cost,
round( (pln.cost -pln.io_cost) / nullif(pln.cpu_cost, 0), 15) ratio,
pln.time,
pln.cardinality,
pln.partition_start,
pln.partition_stop,
pln.*
from
v$sql_plan_statistics_all pln
where
pln.operation in ('TABLE ACCESS', 'INDEX ACCESS')
-- pln.cost is not null or
-- pln.cpu_cost is not null or
-- pln.io_cost is not null
order by
pln.cost desc nulls last
)
group by
ratio
order by
count(*) desc
;
select pln.cost, pln.time, to_char(pln.cost / pln.time, '999990.0') ratio, pln.operation, pln.options, pln.object_owner, pln.object_name from v$sql_plan pln order by pln.time desc nulls last;