Oracle SQL Monitoring

All of us has some point in time used Oracle SQL monitoring reports which are pretty useful to identify critical statistics for an currently running query which can be find through various database queries as well.
With SQL monitoring reports it makes pretty easy for DBA's to identify real time statistics about qery execution state.

There are multiple ways where sql monitoring reports can be extracts


  1. Real Time - Fetch the reports when query is running.
  2. Historical Reports - Fetch the reports when queries are already completed in some point time in history.
Real Time Reports can be extracted through

  1. OEM (Oracle Enterprise Manager)
  2. SQLPLUS commands - We are going to discuss them within this blog.
Very often everyone of us might of noticed sql monitoring reports are not generated for all queries, which is due to some important factor:

_sqlmon_threshold with default value of 5 means any query which has combined CPU and I/O execution more than 5 seconds is entitled to have sql monitoring reports 

_sqlmon_max_planlines with default value of 300 means any query which has execution plan more than 300 lines will not be monitored through sql mon reports.


_sqlmon_max_plan limits the number of sql being  monitoered by sqlmon these are by default calculated as 20 * CPU_COUNT.

I don't advise you to set any of above parameters on system level, but it can be used on session level for testing purpose.

Historically SQL Monitoring reports were only kept in memory buffers but with later version of oracle these reports are being persisted on oracle disk with the retention based on your AWR.
Historical reports can be viewed through DBA_HIST_REPORTS.

Extract the report id from dba_hist_reports and generate the reports using package dba_auto_report.report_repository_detail and key1 would the sql id of your query.

select report_id from dba_hist_reports
where key1='yoursqlid';

 Use the report_id from above query to extract the report:

 
SET echo ON
SET trimspool ON
SET TRIM ON
SET pages 0
SET linesize 4000
SET LONG 10000000
SET longchunksize 1000000
spool sqlmon_sql_id.html
 
SELECT dbms_auto_report.Report_repository_detail(rid=>report_id from query, TYPE=>'text')
FROM dual;
 
spool OFF
There are many other blogs which will give you information about generating read time sql monitoring reports through OEM and SQLPLUS, hence not covering them within this blog. Feel free to leave your comments!!!

Comments

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s...
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose. ...
Read more

Error Processing Request. - Oracle APEX Post upgrade (Invalid WWV_FLOW_SESSION_RAS)

There would be occasions where you would upgrade your oracle database which is hosting your APEX and post upgrade you get error while opening APEX URL " Error Processing Request Contact your application administrator " This looks very strange as you have just upgraded only database. Database version and APEX version are compatible. For Argument lets say both are on 19c.  Now since UI doesn't give detailed information about the error you would need to get to backend oracle database and query view apex_debug_messages.     select * from apex_debug_messages; Looking at the results of this view you would get the clue of the error in this case it was:      ORA-04063 package body APEX_190100.WWV_FLOW_SESSION_RAS has error This means this package body has problem and looking at the status of this body it's found to be invalid and when you try to compile it get following error      alter package APEX_050100.WWV_FLOW_SESSION_RAS compile body;  ...
Read more