How To Get Historical SQL Monitor Report For SQL Statements (Doc ID 2555350.1)

 In this Document

Goal
Solution
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

GOAL

Goal of this document is to explain how to generate or extract historical SQL monitoring report for SQL statements using command line or EM (Enterprise Manager).

One of the major limitations with SQL Monitoring framework is that the information are quickly aged out of memory and not stored in AWR.
From 12c onwards, there are ways to extract the historical SQL monitor report using APIs like DBMS_PERF or DBMS_AUTO_REPORT and also
from Performance Hub page through Oracle Enterprise Manager though not directly through DBMS_SQL_MONITOR which is only for real-time.
Refer the below document for SQL monitoring:

Document 1380492.1 Monitoring SQL Statements with Real-Time SQL Monitoring

 

SOLUTION

1. Historical SQL monitoring report can be extracted using Performance hub report either through EM or using command-line. Under "Monitored SQL" section of the historical performance hub report shows the historical SQL monitoring information.

Document 2436566.1 Monitoring Database Performance Using Performance Hub Report

Example: To generate Performance Hub in historical mode through command line.

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
SQL> spool perfhub_history.html
SQL> select dbms_perf.report_perfhub(is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
SQL> spool off

2. Historical SQL monitoring report can be extracted for particular SQL statement using SQL Details report.

Example: To generate SQL details report to get the SQL monitoring information for particular SQL statement in historical mode.

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
SQL> spool sql_details_history.html
SQL> select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
SQL> spool off

Note in 19c or higher it is better to add "outer_start_time" and  "outer_end_time" to get the desired results:

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
SQL> spool sql_details_history.html
SQL> select dbms_perf.report_perfhub( is_realtime=>0, outer_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), outer_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss'), selected_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), selected_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss')) from dual;
SQL> spool off

3. Using DBMS_AUTO_REPORT from 12c to view SQL Monitoring and Real-time ADDM data that has been captured into AWR.

(i) First identify the report_id containing the monitored SQL during the historical time period from dba_hist_reports view.
Example:

SQL> SELECT report_id FROM dba_hist_reports WHERE dbid = 1954845848 AND component_name = 'sqlmonitor' AND period_start_time BETWEEN To_date('10/09/2018 04:00:00','DD/MM/YYYY HH:MI:SS') AND To_date('10/09/2018 05:00:00','DD/MM/YYYY HH:MI:SS') AND key1 = '9vkyyg1xj6fgc';

REPORT_ID
=========
1042

(ii) Using the report_id, execute the DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL function to get the historical SQL monitoring for SQL statement(s).
Example:

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
SQL> spool sql_mon_hist.html
SQL> SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 1042, TYPE => 'html') FROM dual;
SQL> spool off

Please note in the above command that TYPE =>'html' can also be TYPE =>'active', active does provide more detail than html.

REMEMBER: By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored.
However, It is also possible to force monitoring to occur for any SQL statement by simply adding the MONITOR hint to the statement.
Ex:
SELECT /*+ MONITOR */ col1, col2, col3 from table1 where col1=10;
If however, the SQL statement cannot be added with /*+ MONITOR */ hint since it is coming from a third part application etc. you can still force monitoring to occur by setting the event "sql_monitor" with the SQL_ID for the statement you want to monitor at the system level.

SQL> ALTER SYSTEM SET EVENTS 'sql_monitor [sql:5hc07qvt8v737] force=true';

To disable:
SQL> ALTER SYSTEM SET EVENTS 'sql_monitor [sql:5hc07qvt8v737] off';
Parallel queries(SELECT), Parallel DML and Parallel DDL statements are automatically monitored as soon as execution begins. It does not wait for 5 seconds limit for PARALLEL SQL statements. Normal DDL or DML statements are not monitored apparently.
Example:
SQL> insert into t1 select /*+ parallel(4) */ * from emp;
SQL> create table t2 as select /*+ parallel(4) */ * from emp;

Once again not all parallel DDLs are monitored.
Example:
SQL> alter table t1 move parallel 4;
The ALTER command though executed in parallel is not monitored.

 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more