Svenson's Bulletin Board

[craig]

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

[superman7]

,total_exec_time,stmt_sorts
and rows_read > num_executions
_________________
Smile PleazZZZzzzZZZzzzz……