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.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more