Re: slow sql question (Ram Raman)

From: David Mann <dmann99@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Thu, 5 May 2011 11:04:15 -0400

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
--
//www.freelists.org/webpage/oracle-l

Re: slow sql question (Ram Raman)

Other related posts: