Capture Resource intensive SQLs

[rpardip]

I was wondering if there is a way to capture "resource intensive" SQL statements in the database without turning
the trace on.

I want avoid turning tracing on in the production database
and also avoid generating lot fo trace files.

Are there any useful SQL statements to capture "resource intensive" SQL statements?


TIA

[WROOS]

Here are two:

------------------------------------------------------------------------
-- Purpose:to find expensive sql that may need tuning
------------------------------------------------------------------------
column load format a6 justify right
column executes format 9999999
break on load on executes

select substr(to_char(s.pct,'99.00'),2)||'%' load,
s.executions executes, p.sql_text
from
(select address, disk_reads, executions, pct,
rank() over (order by disk_reads desc) ranking
from
(select address, disk_reads, executions,
100 * ratio_to_report(disk_reads) over () pct
from v$sql
where command_type != 47)
where disk_reads > 50 * executions) s,
sys.v_$sqltext p
where s.ranking <= 5
and p.address = s.address
order by 1, s.address, p.piece
/
undefine Percent

AND

------------------------------------------------------------------------
-- Purpose: to find suspicious sql that may need tuning
------------------------------------------------------------------------column load format a6 justify right
column executes format 9999999
break on load on executes
select substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes, p.sql_text
from (select address, buffer_gets, executions, pct,
rank() over (order by buffer_gets desc) ranking
from (select address, buffer_gets, executions,
100 * ratio_to_report(buffer_gets) over () pct
from sys.v$sql
where command_type != 47)
where buffer_gets > 50 * executions) s, v$sqltext p
where s.ranking <= 5
and p.address = s.address
order by 1, s.address, p.piece
/
undefine Percent