Automating problem query identification in Oracle 11g

Question

In our test bed, a number of test suites will be run in a row (unattended), producing reports for later consumption. I want to include in those reports queries which are candidates for further investigation, along with the data that justifies their inclusion in that list. We should be able to associate any query identified this way with the test suite that exposed it as a concern.

When we use SQL Server, this is relatively straight forward - a call to DBCC FREEPROCCACHE clears all of the counters before a suite begins, then at test end we run a query against sys.dm_exec_query_stats, which gives us access to the execution counts and min/max/total time(s) of each cached query plan, with hooks available to retrieve the parameterized SQL statement (we use FORCED parametrization in our mssql instances) and the query plan.

Ref: http://msdn.microsoft.com/en-us/library/ms189741%28SQL.90%29.aspx

My question: how do I implement an approximation for this when my target app has been connected to Oracle 11g? My reading thus far suggests that everything I'm after is available via the AWR, and that it should be possible to access the supporting views directly, but I haven't been able to close the circle on my own.

Answer 1

Why do you need to access the supporting views directly? It would seem to me that the simplest solution would be

• Each test suite starts and ends by explicitly generating an AWR snapshot so it knows the starting and ending snapshot ID and so that you can generate AWR reports for each suite individually.
• You run AWR reports for each test suite
• You review the AWR reports looking in particular at 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 so, that's obviously easier.

Just for sanity, I should point out that I am assuming you are licensed to use AWR. Technically, even querying the AWR views requires that you have licensed the Performance and Tuning Pack. If you want to hit the views directly rather than generating full AWR reports because of licensing concerns, you're not saving yourself any license headaches by hitting the views.

Answer 2

The Oracle equivalent of DBCC FREEPROCCACHE is

SQL> alter system flush shared_pool;

Answer 3

The closest to the SQL Server counters are V$SYSSTAT and V$SYSTEM_EVENT. However Oracle actually tracks these 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. And you don't need the Diagnostic/Tuning pack licenses to access them.

They don't go down to the SQL level. That is available in V$SQL, though would not be specific to that session. You can use session level tracing to track down individual SQLs that may be causing problems

Answer 4

Justin's answer had the correct outline, but I needed more details about the implementation.

• Each test suite starts and ends by explicitly generating an AWR snapshot so it knows the starting and ending snapshot ID and so that you can generate AWR reports for each suite individually.
• You run AWR reports for each test suite
• You review the AWR reports looking in particular at the various Top SQL sections

• I explicitly generate the snapshots by calling dbms_workload_repository.create_snapshot, the result gets saved off for later.

  select dbms_workload_repository.create_snapshot() as snap_id from dual

• In order to get the report, I need the database id and the instance number. Those are 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). The arguments are the same in both cases, including a options flag which will include information from the Automatic Diagnostic Database Monitor (ADDM). It wasn't immediately obvious to me that I could leverage the ADDM results, so I turn that off. The return value is a column of varchar, so the function call gets wrapped

  select output from table(dbms_workload_repository.awr_report_html(1043611354,1,5539,5544,0))

• This result is easily written to a file, which is assembled with the other artifacts of the test.

Documentation of these methods is available online