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.
Identify_sql_1_2

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’;

Identify_sql_2_2

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.

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.

Posted by Nitin Pai
Comments (0)
November 5th, 2007

Comments (0)