Home

About Me Contact Me Oracle12cSIG(IOUG)
 
Home  :: Quick-Script

Troubleshooting a long running SQL

0 Comments

You can use below  sql to see detailed operation of SQL

 

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

  SET LINESIZE 1000

 SET PAGESIZE 0

 SET TRIM ON

 SET TRIMSPOOL ON

 SET ECHO OFF

 SET FEEDBACK OFF

 SELECT DBMS_SQLTUNE.report_sql_monitor(

 sql_id       => 'XxXxXxXxxxx',  Replace your SQL_ID here

type         => 'TEXT',

report_level => 'ALL') AS report

FROM dual;

 

It will give you very nice report of Global information about SQL.

 

 

Information of Global stats of SQL

 

 

 

 

 

 

 

 

 

 

 

 

 

Information on all Bind values

 

 

 

Detailed Realtime execution with Rows (Estimated) vs Rows (Actual)***

 

 

Rows (Actual)*** is cumulative number.

 

Other useful monitoring methods of DBMS_SQLTUNE

 

BEGIN_OPERATION Function Starts a composite database operation in the current session

END_OPERATION Procedure Ends the monitoring operation in the current session

REPORT_SQL_MONITOR_LIST Function Builds a report for all or a subset of database operations that have been monitored by Oracle

 

More reading on database 20c  

 

https://docs.oracle.com/en/database/oracle/oracle-database/20/arpls/DBMS_SQLTUNE.html#GUID-FE47DFDF-C584-44D8-9082-68E62C533E88

 

 

 

Posted on : 4/6/2020 by Anuj
 
Comments
No comment found.
 


Add Your Comments
 
Name :
Email :
Comments :

 


 
 
  Archives
 
  Recent Posts

Database 19c New Features
Posted on :   5/28/2020

Request for free Oracle Cloud certification exams
Posted on :   5/7/2020

Oracle Cloud freeonline training and certification exams (until May 15, 2020)
Posted on :   4/22/2020

  Recent Comments
 

Home  |  About Me  |  Contact  |  Oracle12cSIG(IOUG)

All Rights Reserved 2019 Oracle-12c