Re: slow sql question (Ram Raman)
Date: Thu, 5 May 2011 11:04:15 -0400
Message-ID: <BANLkTikSR1JG=ZSQUAve3VFpr2FvjGqRmQ_at_mail.gmail.com>
You can use the API to generate AWR reports... see dbms_workload_repository.awr_report_text() and dbms_workload_repository.awr_report_html()... but do you really want to deal with hundreds or thousands of separate reports?
If you have specific questions about performance and you have the AWR data you might want to query right against the views.
DBA_HIST_SNAPSHOT - main info for each snapshot including begin/end time DBA_HIST_SQL_STAT - SQL execution statistics, you will probably be interested in filtering on SQL_ID... but you can get interesting stuff like Plan_Hash_Value, # of executions captured during the snapshot, total elapsed time during the snapshot, total buffer gets and total disk reads during the executions.
I don't have a query handy but not sure of what values you are looking for either, But those two tables can get you pretty far in determining what resources and time were required to execute the SQLs over time. Paste into Excel and voila... purty graphs for you managers.
-Dave
--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 05 2011 - 10:04:15 CDT