Automating the identification of problematic queries in Oracle 11g
On our test bench, a number of test suites will be launched in succession (unattended), generating reports for subsequent consumption. I want to include in these reports requests that are candidates for further investigation, as well as data that justifies their inclusion in this list. We need to be able to associate any request identified in this way with the test suite that identified it as a problem.
When we are using SQL Server, it is relatively straight forward - the DBCC FREEPROCCACHE call clears all counters before the start of the set, and then on test completion we run the query with sys.dm_exec_query_stats which gives us access to execute the counts and min / max / total time of each cached plan queries with available hooks for a parameterized SQL statement (we are using FORCED parameterization in our mssql instances) and a query plan.
Link: http://msdn.microsoft.com/en-us/library/ms189741%28SQL.90%29.aspx
My question is, how do I implement an approximation for this when my target application was connected to Oracle 11g? My reading so far shows that everything I need is available through AWR and that it should be possible for it to directly access the supporting views, but I was unable to close the circle myself.
source to share
Why would you need to access the supporting views directly? It seemed to me that the simplest solution would be
- Each test suite is started and ended by explicitly creating an AWR snapshot so that it knows the start and end ID of the snapshot and so you can generate AWR reports for each individual package.
- You run AWR reports for each test suite
- You are browsing AWR reports by looking in particular the various Top SQL sections
It's absolutely possible to get all the information from the underlying views directly, but if you don't need to do this, it's obviously easier.
Just for common sense, I should point out that I am assuming you have a license to use AWR. Technically, even AWR requests require you to license the Performance and Tuning Pack. If you want to hit the views directly, rather than generate full AWR reports due to licensing issues, you are not saving yourself any license headaches by clicking on the views.
source to share
The closest SQL Server counters are V $ SYSSTAT and V $ SYSTEM_EVENT . However Oracle actually keeps track of them at the session level too in v $ SESSION_WAIT, V $ SESSION_WAIT_CLASS and V $ SESSION_EVENT, so you don't need to reset them at the system level. You do not need Diagnostic / Tuning pack licenses to access them.
They don't go down to the SQL level. This is available in V $ SQL, although it will not be specific to this session. You can use session level tracing to track individual SQL queries that might be causing problems.
source to share
Justin's answer had the right plan, but I needed implementation details.
- Each test suite is started and ended by explicitly creating an AWR snapshot so that it knows the start and end ID of the snapshot and so you can generate AWR reports for each individual package.
- You run AWR reports for each test suite
- You are browsing AWR reports by looking in particular the various Top SQL sections
-
I am explicitly generating snapshots by calling dbms_workload_repository.create_snapshot, the result will be saved later.
select dbms_workload_repository.create_snapshot () as snap_id from double
-
To get the report, I need the database ID and the instance number. This can be easily obtained from v $ database and v $ instance.
select d.DBID, i.instance_number as inst_num from v $ database d, v $ instance i
-
The report is available as text (dbms_workload_repository.awr_report_text) or html (dbms_workload_repository.awr_report_html). In both cases, the arguments are the same, including an options flag that will include information from the Automatic Data Diagnostics Monitor (ADDM). It didn't immediately become obvious to me that I could use the ADDM results, so I'll disable that. The return value is a varchar column, so the function call is wrapped
select output from table (dbms_workload_repository.awr_report_html (1043611354,1,5539,5544,0))
-
This result is easily written to a file that is collected along with other test artifacts.
source to share