18 de jun. de 2008

AWR - SQL Costs

Segue abaixo o sql para extrair do Awr as instruções mais impactantes:

spool INSTRUCOES_IMPACTANTES.LOG
col c1 heading ‘SQLID’ format a13

col c2 heading ‘Cost’ format 9,999,999
col c3 heading ‘SQL Text’ format a200

select p.sql_id c1,
p.cost c2,
DBMS_LOB.SUBSTR(s.sql_text,4000,1) c3
from dba_hist_sql_plan p,
dba_hist_sqltext s
where p.id = 0
and p.sql_id = s.sql_id
and p.cost is not null
order by p.cost desc;

spool off

Nenhum comentário: