Hi Guru,
I have response time problem on below sql
sql statement taking more time to give the results.
SQL Statement:-
WITH
ARCH
AS
(SELECT THREAD# as Thread , SEQUENCE# as Sequence
FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_TIME) IN ( SELECT THREAD#, MAX (FIRST_TIME)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#)),
APPL
AS
(SELECT THREAD# as Thread, SEQUENCE# as sequence
FROM V$LOG_HISTORY
WHERE (THREAD#, FIRST_TIME) IN ( SELECT THREAD#, MAX (FIRST_TIME)
FROM V$LOG_HISTORY
GROUP BY THREAD#))
SELECT
ARCH.THREAD "Thread",
ARCH.SEQUENCE "Last Sequence Received",
APPL.SEQUENCE "Last Sequence Applied",
(ARCH.SEQUENCE - APPL.SEQUENCE) "Difference"
FROM ARCH, APPL
WHERE ARCH.Thread = APPL.Thread;
Plan:-
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 111 | 3 (100)| 00:00:01 |
|* 3 | HASH JOIN | | 94 | 8366 | 2 (100)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KCCAL | 94 | 4794 | 1 (100)| 00:00:01 |
| 5 | FIXED TABLE FULL | X$KCCLH | 2336 | 88768 | 0 (0)| |
| 6 | VIEW | VW_NSO_1 | 2336 | 51392 | 1 (100)| 00:00:01 |
| 7 | HASH GROUP BY | | 2336 | 58400 | 1 (100)| 00:00:01 |
| 8 | FIXED TABLE FULL| X$KCCLH | 2336 | 58400 | 0 (0)| |
|* 9 | FILTER | | | | | |
| 10 | HASH GROUP BY | | 94 | 3572 | 3 (100)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KCCAL | 94 | 3572 | 2 (100)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("LHTHP"="THREAD#" AND "MAX(FIRST_TIME)"=TO_DATE("LHLOT",'MM
/DD/RR HH24:MI:SS','nls_calendar=''GREGORIAN'''))
3 - access("ALTHP"="LHTHP")
4 - filter("INST_ID"=USERENV('INSTANCE'))
9 - filter(("ALTHP"=:B1 AND TO_DATE(:B2,'MM/DD/RR
HH24:MI:SS','nls_calendar=''GREGORIAN''')=MAX(TO_DATE("ALLOT",'MM/DD/RR
HH24:MI:SS','nls_calendar=''GREGORIAN'''))))
11 - filter("INST_ID"=USERENV('INSTANCE'))