I am using Oracle 11.2.0.3. . My first sql that queries v$active_session_history is completing in 2ms. When I put same sql as subquery as shown in 2nd SQL it takes 1minute 56 seconds with a-rows increasing from 489 to 4044K.
Could anyone please help me understand this?
Also for 2nd query, why is A-time of Select (1:29.39) less than A-time of view (1:56.82)
select /*+ gather_plan_statistics */ distinct sql_id from v$active_session_history where sample_time between to_timestamp ('21-may-2015 11:10:05 AM') and to_timestamp ('21-may-2015 11:26:07 AM') and sql_opname <>'SELECT'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 9 |00:00:00.02 | | | |
| 1 | HASH UNIQUE | | 1 | 1 | 5 | 5 (100)| 00:00:01 | 9 |00:00:00.02 | 956K| 956K| 928K (0)|
| 2 | VIEW | GV$ACTIVE_SESSION_HISTORY | 1 | 1 | 5 | 4 (100)| 00:00:01 | 37 |00:00:00.01 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 53 | 4 (100)| 00:00:01 | 37 |00:00:00.01 | | | |
|* 4 | FIXED TABLE FULL | X$KEWASH | 1 | 1 | 21 | 4 (100)| 00:00:01 | 489 |00:00:00.01 | | | |
|* 5 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 489 | 1 | 32 | 0 (0)| | 37 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ gather_plan_statistics */ * from v$sql where sql_id in (select /*+ gather_plan_statistics */ distinct sql_id from v$active_session_history where sample_time between to_timestamp ('21-may-2015 11:10:05 AM') and to_timestamp ('21-may-2015 11:26:07 AM') and sql_opname <>'SELECT' )
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 11 |00:01:21.39 |
|* 1 | FILTER | | 1 | | | | | 11 |00:01:21.39 |
|* 2 | FIXED TABLE FULL | X$KGLCURSOR_CHILD | 1 | 1 | 5718 | 0 (0)| | 14481 |00:00:00.73 |
| 3 | VIEW | GV$ACTIVE_SESSION_HISTORY | 8276 | 1 | 8 | 4 (100)| 00:00:01 | 8 |00:01:56.82 |
| 4 | NESTED LOOPS | | 8276 | 1 | 53 | 4 (100)| 00:00:01 | 8 |00:01:56.81 |
|* 5 | FIXED TABLE FULL | X$KEWASH | 8276 | 1 | 21 | 4 (100)| 00:00:01 | 4044K|00:00:11.26 |
|* 6 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 4044K| 1 | 32 | 0 (0)| | 8 |00:00:17.13 |
----------------------------------------------------------------------------------------------------------------------------------------