hi I am using version- 11.2.0.4.0 of oracle. We have one query which run for ~30 minutes but when i verified the stats from dba_hist_sqlstat(ELAPSED_TIME_delta), i found it took ~30 second. also then i verified the SQL_MONITOR plan it does show that the query was running(from DURATION) for 30 minutes+, but the elapsed_time shows 35 seconds. i am not seeing any idle wait event(SQL* NET...) also. also when i ran this query from SQLPLUS prompt it completes within ~30 seconds. Below is the plan. What must be the scenario? How to debug this?
1)Is it that even if the database has completed fetching all the results and sent it to the client, but still that client will not release the DB connection and so the DURATION for the query in SQL_MONITOR will keep on increasing till ~30 minutes, even the elapsed time is just ~30 seconds?
2)or is it that in case of, the query processing row by row and some other operation is happening with each row outside database, so the elapsed time is just showing the part of the query which has been spent on DB only, but sql monitor will show the full time? should not it be that , the sql monitor shopuld also show the duration as ~30 seconds?
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
Session : (203:32471)
SQL ID : 8rtttytyqd8
SQL Execution ID : 33554432
Execution Started : 08/03/2016 04:25:06
First Refresh Time : 08/03/2016 04:25:10
Last Refresh Time : 08/03/2016 04:58:05
Duration : 1979s
Module/Action :
Service :
Program :
Fetch Calls : 5106
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 35 | 11 | 5.87 | 0.04 | 19 | 5106 | 1M | 6373 | 50MB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=22357602222)
================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1948 | +32 | 1 | 960K | | | | | |
| 1 | NESTED LOOPS | | 23973 | 77574 | 1948 | +32 | 1 | 960K | | | | | |
| 2 | NESTED LOOPS | | 23973 | 77574 | 1948 | +32 | 1 | 960K | | | | | |
| 3 | HASH JOIN | | 23973 | 29598 | 1976 | +4 | 1 | 960K | | | 2M | | |
| 4 | TABLE ACCESS STORAGE FULL | SSC | 766 | 54 | 1 | +4 | 1 | 747 | | | | | |
| 5 | HASH JOIN | | 23978 | 29544 | 1976 | +4 | 1 | 960K | | | 159M | 3.13 | Cpu (1) |
| 6 | HASH JOIN | | 23978 | 9664 | 29 | +4 | 1 | 960K | | | 1M | | |
| 7 | TABLE ACCESS STORAGE FULL | STC | 13 | 54 | 1 | +4 | 1 | 15 | | | | | |
| 8 | NESTED LOOPS | | 28516 | 9610 | 29 | +4 | 1 | 1M | | | | | |
| 9 | NESTED LOOPS | | 67562 | 9610 | 29 | +4 | 1 | 2M | | | | | |
| 10 | PARTITION LIST SINGLE | | 74 | 54 | 1 | +4 | 1 | 72 | | | | | |
| 11 | TABLE ACCESS STORAGE FULL | SPROF | 74 | 54 | 1 | +4 | 1 | 72 | | | | | |
| 12 | INDEX RANGE SCAN | SFE_IX3 | 913 | 5 | 29 | +4 | 2217 | 2M | 100 | 800KB | | 21.88 | gc buffer busy acquire (3) |
| | | | | | | | | | | | | | gc cr block busy (4) |
| 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 384 | 129 | 32 | +1 | 2M | 1M | 5232 | 41MB | | 71.88 | gc buffer busy acquire (6) |
| | | | | | | | | | | | | | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | gc cr multi block request (1) |
| | | | | | | | | | | | | | gc current block 2-way (2) |
| | | | | | | | | | | | | | gc current grant busy (1) |
| | | | | | | | | | | | | | Cpu (6) |
| | | | | | | | | | | | | | cell single block physical read (2) |
| | | | | | | | | | | | | | read by other session (4) |
| 14 | TABLE ACCESS STORAGE FULL | STD | 1M | 17225 | 1948 | +32 | 1 | 1M | | | | | |
| 15 | INDEX UNIQUE SCAN | SFHP | 1 | 1 | 1948 | +32 | 960K | 960K | | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | SFH | 1 | 2 | 1948 | +32 | 960K | 960K | | | | 3.13 | Cpu (1) |
================================================================================================================================================================================================================