Karl Reitschuster's Tech Space

With Oracle 10g Oracle introduced a new key column spread over the dynamic performance views - the SQL_ID; The SQL_ID identified a SQL statement. The SQL_ID was needed to persist all performance metrics of a specific SQL statement in a repository.

With Oracle 11g the granularity even gets deeper. With the new columns SQL_EXEC_ID and SQL_EXEX_START performance data could be collected of a specific execution of a specific SQL statement! What a quantum leap in monitoring!

Here is was the documentation writes about the new Columns:

SQL_EXEC_ID : Execution identifier, Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.

A simple lookup where the new columns are used :

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as oasis
 
SQL> set echo on;
SQL>
SELECT c.Owner,
       c.Table_Name,
       c.Column_Name,
       c.Data_Type
  FROM Dba_Tab_Columns c
 WHERE c.Column_Name LIKE 'SQL\_EXEC\_%' ESCAPE '\'
 ORDER BY c.Table_Name,
          c.Column_Name
;
 
OWNER       TABLE_NAME                     COLUMN_NAME      DATA_TYPE
----------- ------------------------------ ---------------- ---------
SYS         DBA_HIST_ACTIVE_SESS_HISTORY   SQL_EXEC_ID      NUMBER
SYS         DBA_HIST_ACTIVE_SESS_HISTORY   SQL_EXEC_START   DATE
SYS         GV_$ACTIVE_SESSION_HISTORY     SQL_EXEC_ID      NUMBER
SYS         GV_$ACTIVE_SESSION_HISTORY     SQL_EXEC_START   DATE
SYS         GV_$OPEN_CURSOR                SQL_EXEC_ID      NUMBER
SYS         GV_$SESSION                    SQL_EXEC_ID      NUMBER
SYS         GV_$SESSION                    SQL_EXEC_START   DATE
SYS         GV_$SESSION_LONGOPS            SQL_EXEC_ID      NUMBER
SYS         GV_$SESSION_LONGOPS            SQL_EXEC_START   DATE
SYS         GV_$SQL_MONITOR                SQL_EXEC_ID      NUMBER
SYS         GV_$SQL_MONITOR                SQL_EXEC_START   DATE
SYS         GV_$SQL_PLAN_MONITOR           SQL_EXEC_ID      NUMBER
SYS         GV_$SQL_PLAN_MONITOR           SQL_EXEC_START   DATE
SYS         GV_$SQL_WORKAREA_ACTIVE        SQL_EXEC_ID      NUMBER
SYS         GV_$SQL_WORKAREA_ACTIVE        SQL_EXEC_START   DATE
SYS         V_$ACTIVE_SESSION_HISTORY      SQL_EXEC_ID      NUMBER
SYS         V_$ACTIVE_SESSION_HISTORY      SQL_EXEC_START   DATE
SYS         V_$OPEN_CURSOR                 SQL_EXEC_ID      NUMBER
SYS         V_$SESSION                     SQL_EXEC_ID      NUMBER
SYS         V_$SESSION                     SQL_EXEC_START   DATE
 
OWNER       TABLE_NAME                     COLUMN_NAME      DATA_TYPE
----------- ------------------------------ ---------------- ---------
SYS         V_$SESSION_LONGOPS             SQL_EXEC_ID      NUMBER
SYS         V_$SESSION_LONGOPS             SQL_EXEC_START   DATE
SYS         V_$SQL_MONITOR                 SQL_EXEC_ID      NUMBER
SYS         V_$SQL_MONITOR                 SQL_EXEC_START   DATE
SYS         V_$SQL_PLAN_MONITOR            SQL_EXEC_ID      NUMBER
SYS         V_$SQL_PLAN_MONITOR            SQL_EXEC_START   DATE
SYS         V_$SQL_WORKAREA_ACTIVE         SQL_EXEC_ID      NUMBER
SYS         V_$SQL_WORKAREA_ACTIVE         SQL_EXEC_START   DATE
SYS         WRH$_ACTIVE_SESSION_HISTORY    SQL_EXEC_ID      NUMBER
SYS         WRH$_ACTIVE_SESSION_HISTORY    SQL_EXEC_START   DATE
SYS         WRH$_ACTIVE_SESSION_HISTORY_BL SQL_EXEC_ID      NUMBER
SYS         WRH$_ACTIVE_SESSION_HISTORY_BL SQL_EXEC_START   DATE
 
32 rows selected
 
SQL>

Oracle 11g is now able to capture performance data of a specific executed SQL even during runtime. This allows sophisticated ad hoc analysis of slow running SQL; You need not to wait till the SQL ends but you are able to monitor every execution step in the execution plan currently executed. The feature is accessible both by Oracle Grid Control or by using directly the DBMS_SQLTUNE package.

Getting Performance Report of a currently slow running SQL with all Detail in HTML format :

SELECT Dbms_Sqltune.Report_Sql_Monitor(Event_Detail => 'YES',
                                       Report_Level => 'ALL',
                                       TYPE         => 'HTML')
  FROM Dual;

This results for example in following HTML Outout :

Output of Dbms_Sqltune.Report_Sql_Monitor

Conclusion

Oracle improved consequently it's real time monitoring capabilities. For the road map of Oracle 11 there seemed to be a goal to improve the granularity of SQL monitoring. And keep in mind that all this metrics are stored in the Repository for later detailed analysis. it's a pleasure to explore the new features like the SQL Real-time Monitoring.

Karl Reitschuster