Hi I am using Release 11.2.0.3.0 of oracle.
We have a query executed last night for around ~21hrs and then got completed. I was verifying the sql execution history in OEM from monitored sql section and and i saw the DB time is around ~27 Sec only whereas the elapsed time is ~21hrs, not able to understand the reason. Also whe i went through the sql it seems simple one and Here the master table contains ~600000(6 lacs) records and Refrence table contains 4 records. Also when i verified the gv$active_session_history i didnt get much wait event log for this sql query, there were minimal occurrence of wait events like 'SQL*Net more data to client' , 'direct path read temp '. So just wondering if the DB time is in seconds so why the elapsed time is such high and what exact thing the sql was doing for that amount of time.
Even i think the stats for master tables looks stale, but then also the opration should not last for these amount of duration(~21 hours). Please help me understand the scenario,and the action we should take.
Below is the execution plan.
SELECT master.createtimestmp
FROM master master, Refrence
WHERE master.Refrence_id = Refrence.Refrence_id
AND ( (Refrence.Refrence_cd = 'A' AND master.createtimestmp <= (:1))
OR (Refrence.Refrence_cd = 'B' AND master.createtimestmp <= :2)
OR ( ( Refrence.Refrence_cd = 'D'
AND master.createtimestmp <= :4
AND master.master_dltd_in = 0)
OR (TRUNC (master.createtimestmp) = TRUNC(:5)
AND master.master_dltd_in = 1)
))
ORDER BY master.createtimestmp
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2017K| | (1)| 00:04:06 |
| 1 | SORT ORDER BY | | 844K | 2017K| 302M | 50482 (1)| 00:04:06 |
|* 2 | HASH JOIN | | 844K | 2017K| | 13678 (1)| 00:03:59 |
| 3 | TABLE ACCESS FULL| Refrence | 4 | 24 | | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| Master | 2M | 1966K| | 13668 (1)| 00:03:59 |
---------------------------------------------------------------------------------------------