Long Running Queries
Ever had a scenario where a Query ran for days? I had a similar situation where I had to merge a table with precisely 369886655 records to an other table. During this time all I ever heard was a continuous narccisitic question: "when is this going to be over?" I am not the DB server; There is no progress bar, I have no idea!
Consider this use-case: The following is a screen-shot of a query I executed a few days ago... If you took a closer look, You'll notice that the query has been executing for a good 14021 Minutes and 27 Seconds.
So how will I know the completion time? Step 1: Check if the query executed is Active.
This query will check all the active SQL statements...
/*This one shows SQL that is currently "ACTIVE"*/ SELECT S.SID, S.USERNAME NAME, S.OSUSER, T.SQL_ID, SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES T, V$SESSION S WHERE T.ADDRESS = S.SQL_ADDRESS AND T.HASH_VALUE = S.SQL_HASH_VALUE AND S.STATUS = 'ACTIVE' AND S.USERNAME <> 'SYSTEM' ORDER BY S.SID, T.PIECE;
SL SID NAME OSUSER SQL_ID SQL_TEXT 1 293 IBBS mithun a28gmk7vv5dt4 "/*This one shows SQL that is currently ""ACTIVE""*/SELECT S.SID, " 2 293 IBBS mithun a28gmk7vv5dt4 "S.USERNAME, S.OSUSER, T.SQL_ID, SQL_TEXT FROM V$SQLTEXT_WITH_N" 3 293 IBBS mithun a28gmk7vv5dt4 "EWLINES T, V$SESSION S WHERE T.ADDRESS = S.SQL_ADDRESS AND T" 4 293 IBBS mithun a28gmk7vv5dt4 ".HASH_VALUE = S.SQL_HASH_VALUE AND S.STATUS = 'ACTIVE' AND" 5 293 IBBS mithun a28gmk7vv5dt4 " S.USERNAME <> 'SYSTEM' ORDER BY S.SID, T.PIECE" 6 301 SYSMAN 2b064ybzkwf1y BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 7 321 IBBS mithun fnwzpbb6rzwdg "/****IMP****//****PASS_SHEET****/INSERT INTO PASS_SHEET (SEL" 8 321 IBBS mithun fnwzpbb6rzwdg "ECT B.DPCD, B.FACILITY, B.ACID, substr(B.VALUE_DATE, 1, 8), " 9 321 IBBS mithun fnwzpbb6rzwdg "ROW_NUMBER() OVER(PARTITION BY B.ACID ORDER BY B.VALUE_DATE) " 10 321 IBBS mithun fnwzpbb6rzwdg "AS GROUPSEQUENCE1, B.TRAN_PARTICULAR, CASE WHEN B.PART_TRAN_TYPE " 11 321 IBBS mithun fnwzpbb6rzwdg "= 'D' THEN TO_NUMBER(B.TRAN_AMT) END AS DEBIT, CASE WHEN " 12 321 IBBS mithun fnwzpbb6rzwdg "B.PART_TRAN_TYPE = 'C' THEN TO_NUMBER(B.TRAN_AMT) END AS CREDIT, " 13 321 IBBS mithun fnwzpbb6rzwdg "SUM(CASE WHEN B.PART_TRAN_TYPE = 'D' THEN TO_NUMBER(B.TRAN_AMT) WHEN " 14 321 IBBS mithun fnwzpbb6rzwdg "B.PART_TRAN_TYPE = 'C' THEN -TO_NUMBER(B.TRAN_AMT) ELSE 0 END) OVER " 15 321 IBBS mithun fnwzpbb6rzwdg "(PARTITION BY B.ACID ORDER BY B.VALUE_DATE) AS BALANCE FROM " 16 321 IBBS mithun fnwzpbb6rzwdg "B2K_DAILY_TRAN B --ORDER BY 3,4 "
Step 2: Yes, The query is Active. Check for Locks!
Some times the query is executing indefenitely because it may be locked...
/*This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock*/ SELECT SESSION_ID, OBJECT_NAME, OBJECT_TYPE, TYPE, -- TYPE OR SYSTEM/USER LOCK LMODE, -- LOCK MODE IN WHICH SESSION HOLDS LOCK REQUEST, BLOCK, CTIME -- TIME SINCE CURRENT MODE WAS GRANTED FROM V$LOCKED_OBJECT, ALL_OBJECTS, V$LOCK WHERE V$LOCKED_OBJECT.OBJECT_ID = ALL_OBJECTS.OBJECT_ID AND V$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID AND V$LOCK.SID = V$LOCKED_OBJECT.SESSION_ID ORDER BY SESSION_ID, CTIME DESC, OBJECT_NAME;
SESSION_ID OBJECT_NAME OBJECT_TYPE TYPE LMODE REQUEST BLOCK CTIME 321 PASS_SHEET TABLE TM 3 0 0 846312
The LOCK_TYPE is TM which means that it is a Table-level lock and is primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation. Read the following for more details on Locks... Locks, During DML Operations The LMODE is the Lock mode in which the session holds the lock: 3 means row-X (SX) Also, Check out the following... v$lock
Step 3: No, The query is not Locked. Check for Total Work/Blocks Pending/Completed.
You can find the total Work Completed from V$SESSION_LONGOPS
SELECT SID, TO_CHAR(START_TIME, 'HH24:MI:SS') STIME, MESSAGE, (SOFAR / TOTALWORK) * 100 PERCENT FROM V$SESSION_LONGOPS WHERE SOFAR / TOTALWORK < 1;
SID STIME MESSAGE PERCENT 321 18:03:53 Sort Output: 958495 out of 1003172 Blocks done 95.5464267343985
Step 4: Time left for the operation to complete.
You could find out the time left for the operation to complete from V$SESSION_LONGOPS
SELECT SID,TARGET, SUM(TOTALWORK) TOTWORK, SUM(SOFAR) DONE, SUM(TOTALWORK - SOFAR) "TO DO", SUM(TIME_REMAINING) "TIME LEFT" FROM V$SESSION_LONGOPS WHERE TOTALWORK <> SOFAR AND SID = 321 GROUP BY SID,TARGET ORDER BY TARGET;
SID TARGET TOTWORK DONE TO DO TIME LEFT 321 1003172 958506 44666 39308
To answer the Narcistic Question: It will roughly take about 39308 Seconds (655.1333333333333 Minutes or 10.91888888888889 Hours) as there are 44666 more blocks in queue...
Further Reading & Credits...
OracleRecipies
stackoverflow1
stackoverflow2