craig Site Admin
Joined: 19 Feb 2006 Posts: 40
|
Posted: Wed Nov 17, 2010 3:06 pm Post subject: SQL to get num_execs / rows read percentage |
|
|
The following sql will return a percentage of rows read per execution. High rate may indicate indexing opportunities. It could also be from group by's or sums or such.
select cast(100-(cast((cast(num_executions as double) / cast(rows_read as double)) as double) *100) as decimal(5,2))
,rows_read
,num_executions
,total_exec_time,stmt_sorts
,cast(stmt_text as clob) as stmt_text
from table(snapshot_dyn_sql('',-2)) as s
where rows_read > 0
and rows_read > num_executions
and cast(100-(cast((cast(num_executions as double) / cast(rows_read as double)) as double) *100) as decimal(5,2)) > 20
order by 1 desc,num_executions desc |
|