As a DBA you might be receiving a lot of calls from users asking some of the questions listed below.
- What their process is currently executing?
- How long will it take?
- Should I cancel my process?
In this post, I will cover some steps that will help you quickly respond to the above questions.
I have executed the below SQR Report in my environment and want to know what is happening.
What their process is currently executing?
1.Use the below SQL to correctly identify the Oracle session
SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE ‘NPAI%’ AND STATUS =‘ACTIVE’;
2.The above results identify the session executing the report; in this case it is SID 32.
3.Use this SID to identify the SQL being executed.
SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 32) ORDER BY PIECE;
SQL_TEXT
—————————————————————-
SELECT A.RECNAME, A.FIELDNAME FROM PSRECFIELD A WHERE A.SUBRECO
RD <> ‘Y’ AND NOT EXISTS (SELECT ‘X’ FROM PSDBFIELD B WHERE B.FI
ELDNAME = A.FIELDNAME)
4.The above results will provide you the SQL that is currently being executed. Equipped with this information you SHOULD be able to dig deeper if the session continues to execute the same SQL for more than expected time. Some of the scenarios could be:
a.Locked rows
b.Bad SQL
c.Statistics not updated
d.Bad DB configuration – SQL waiting on some event
e.Missing index, etc
Note 1
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.
Note 2
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.
Read More About what is being processed?
