3

Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just per-statement.

Operation level information is available in DBA_HIST_ACTIVE_SESS_HISTORY. Many AWR reports use that table, but do any of them display information at the operation level? Currently I am getting this data by joining DBA_HIST_ACTIVE_SESS_HISTORY and DBMS_XPLAN.DISPLAY_AWR, but it seems like there should be a built-in feature to do this for me. Before I build my own tool I want to ensure someone else hasn't already built it.

This missing feature may sound like a minor inconvenience but using the actual execution statistics, or even a sample of them, makes tuning so much easier. For example, below is a real SQL Monitoring Report for a recent problem, modified to fit here. With operation-level information there was no need to guess which step was slow, or even examine the waits. The problem was quickly found - the indexes were not disabled before a large INSERT.

SQL Monitoring Report


SQL Text
------------------------------
<SQL statement...>


Global Information
------------------------------
<metadata like run status, session, sql_id, duration, etc...>

Parallel Execution Details (DOP=64 , Servers Allocated=128)
...

SQL Plan Monitoring Details (Plan Hash Value=2062479242)

=================================================================================
| Id |     Operation          | Activity |          Activity Detail             |
|    |                        |   (%)    |            (# samples)               |
=================================================================================
...
|  1 |   PX COORDINATOR       |     0.19 | enq: KO - fast object checkpoint (21)|
|  2 |    PX SEND QC (RANDOM) |          |                                      |
|  3 |     INDEX MAINTENANCE  |    84.70 | buffer busy waits (2801)             |
|    |                        |          | enq: HW - contention (29)            |
|    |                        |          | enq: TX - allocate ITL entry (25)    |
|    |                        |          | free buffer waits (61734)            |
|    |                        |          | write complete waits (10365)         |
|    |                        |          | Cpu (341)                            |
|    |                        |          | enq: FB - contention (1632)          |
|    |                        |          | enq: TX - contention (53)            |
|    |                        |          | db file sequential read (3717)       |
|    |                        |          | read by other session (641)          |
|  4 |      PX RECEIVE        |     0.02 | Cpu (18)                             |
|    |                        |          | resmgr:cpu quantum (1)               |
...
=================================================================================

There are some other methods that produce similar information, such as tracing, /*+ gather_plan_statistics */, or alter session set statistics_level=all;. But those methods are only useful in a development environment. I don't have the luxury of being able to re-run statements with different settings.

  • I am not aware of any tools that 'go back in time' since Oracle DO NOT store all details necessary in AWR. Going forward, you may be able to create these sql monitoring reports and save them into a user table on a periodic basis. – Raj Mar 18 '14 at 13:01
  • @Raj Yes, it's not exactly the same information, but the difference between sampling and monitoring is very small. Adding up the number of samples from DBA_HIST_ACTIVE_SESS_HISTORY gets the same report as the one based on the _MONITOR_ tables. All the AWR reports go back in time, but for some reason they only seem to aggregate at the statement level or higher. – Jon Heller Mar 18 '14 at 13:23
  • I imagine this doesn't exist because the amount of data generated would be massive, and relatively useless before statistical aggregation. – Andrew Brennan Mar 19 '14 at 17:40
  • @AndrewBrennan The data is massive but AWR already stores the raw, non-aggregated information in DBA_HIST_ACTIVE_SESS_HISTORY. As far as I can tell the AWR reports only aggregate at a very high level. In a data warehouse I don't need a report to tell me the database is slow, or that a SQL statement is slow. I need a report to tell me which part of a long-running query is running slow. – Jon Heller Mar 19 '14 at 17:50
0

Oracle 12c has a Performance Hub which includes Monitored SQL in historical mode. Although I haven't tested it, and based on some open bugs I doubt it works 100% of the time.


For 11g, or when the Performance Hub is not available or not working, I have created the open source program Historical SQL Monitoring (hist_sql_mon):

Here's an example of how to use it:

select hist_sql_mon.hist_sql_mon(
    p_sql_id            => '2ssrz4j1m39wx',
    p_start_time_filter => date '2014-09-25',
    p_end_time_filter   => sysdate - interval '1' day)
from dual;

The primary output is a CLOB containing an execution plan with a count and distinct count of events.

-------------------------------------------------------------
|Id  | Operation              | Name   | Rows  | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
|  0 | SELECT STATEMENT       |        |       | 83031 (100)|
|  1 |  SORT AGGREGATE        |        |     1 |            |
|  2 |   HASH JOIN RIGHT OUTER|        |  8116K| 83031   (4)| Cpu (25|25)
|  3 |    INDEX FULL SCAN     | I_USER2|   155 |     1   (0)|
|  4 |    NESTED LOOPS OUTER  |        |  8116K| 82993   (4)| Cpu (1|1)
|  5 |     NESTED LOOPS OUTER |        |  8116K| 10702  (24)| Cpu (2|2)
...

The functions also print the SQL statement to DBMS_OUTPUT. The bind variables are replaced with hard-coded values so the query can run anywhere. This can help with debugging or creating your own queries.

----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
    --Add execution metadata.
    case
        when plan_table_output like 'Plan hash value: %' then
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.