I have a rather large query that returns in less than 10 seconds on the first run but if you immediately run the query again it takes an unknown number of minutes to return. I can't figure it out. Has anybody ran into this situation before. I haven't been able to get a sql trace when the query is running slow because I haven't let the query finish. I stop it because it consumes a lot of CPU on the server.
Database Version = 11.2.0.1
Here is the sql statement and explain plan
SELECT A1.DBASSOC "Associate",
NVL(A1.DBLNAME, ' ') "Last Name",
NVL(A1.DBFNAME, ' ') "First Name",
NVL(A1.DBCTR, 0) "New Center",
NVL(AH.DBCTR, 0) "Old Center",
NVL(JD2.DBRIS_JOB, ' ') "Old Job",
NVL(JD1.DBRIS_JOB, ' ') "New Job",
J1.DBEFF_DATE "New Job Date~S",
JH2.DBEFF_DATE "Old Job Date~S"
FROM HR_USER.ASSOC A1,
HR_USER.JOB_HISTORY J1,
HR_USER.JOB_HISTORY JH,
HR_USER.JOB_HISTORY JH2,
HR_USER.STATUS S1,
HR_USER.JOB_TYPE JD1,
HR_USER.JOB_TYPE JD2,
HR_USER.ASSOC AH
WHERE A1.DBDATE = (SELECT MAX(DBDATE)
FROM HR_USER.ASSOC
WHERE DBASSOC = A1.DBASSOC)
AND S1.DBASSOC = A1.DBASSOC
AND S1.DBSTATUS IN ('A', 'L')
AND S1.DBID = (SELECT MAX(SX.DBID)
FROM (SELECT *
FROM HR_USER.STATUS S2
WHERE S2.DBEFF_DATE =
(SELECT MAX(S3.DBEFF_DATE)
FROM HR_USER.STATUS S3
WHERE S3.DBASSOC = S2.DBASSOC)) SX
WHERE SX.DBASSOC = S1.DBASSOC)
AND J1.DBASSOC = A1.DBASSOC
AND J1.DBEFF_DATE = (SELECT MAX(DBEFF_DATE)
FROM HR_USER.JOB_HISTORY
WHERE DBASSOC = J1.DBASSOC)
AND J1.DBDATE_SEQ = 1
AND JD1.DBJOB_TYPE = J1.DBJOB_TYPE
AND (JD1.DBJOB_LEVEL > 400 OR
JD1.DBJOB_LEVEL < 200)
AND JH.DBASSOC = A1.DBASSOC
AND JH.DBEFF_DATE =
(SELECT MAX(DBEFF_DATE)
FROM HR_USER.JOB_HISTORY J3,
HR_USER.JOB_TYPE JT
WHERE J3.DBASSOC = JH.DBASSOC
AND JT.DBJOB_TYPE = J3.DBJOB_TYPE
AND JT.DBJOB_LEVEL >= 200
AND JT.DBJOB_LEVEL <= 400
AND J3.DBEFF_DATE <= to_date('20110417','YYYYMMDD')
AND J3.DBDATE_SEQ = 1
AND (J3.DBEFF_DATE >= to_date('20101227','YYYYMMDD') OR
J3.DBEFF_DATE =
(SELECT MAX(DBEFF_DATE)
FROM HR_USER.JOB_HISTORY
WHERE DBASSOC = J3.DBASSOC
AND DBEFF_DATE < to_date('20101227','YYYYMMDD'))))
AND JH.DBDATE_SEQ = 1
AND JD2.DBJOB_TYPE = JH.DBJOB_TYPE
AND JH2.DBASSOC = A1.DBASSOC
AND JH2.DBEFF_DATE =
(SELECT MIN(J6.DBEFF_DATE)
FROM HR_USER.JOB_HISTORY J6
WHERE J6.DBASSOC = JH2.DBASSOC
AND J6.DBJOB_TYPE = JH.DBJOB_TYPE
AND J6.DBEFF_DATE <= JH.DBEFF_DATE
AND J6.DBEFF_DATE >
(SELECT NVL(MAX(DBEFF_DATE), TO_DATE('19481022', 'yyyymmdd'))
FROM HR_USER.JOB_HISTORY
WHERE DBASSOC = J6.DBASSOC
AND DBJOB_TYPE <> JH.DBJOB_TYPE
AND DBEFF_DATE < JH.DBEFF_DATE))
AND JH2.DBDATE_SEQ = 1
AND AH.DBASSOC = A1.DBASSOC
AND AH.DBDATE = (SELECT MAX(A3.DBDATE)
FROM HR_USER.ASSOC A3
WHERE A3.DBASSOC = AH.DBASSOC
AND A3.DBDATE < J1.DBEFF_DATE)
AND AH.DBCTR < 495
AND NOT EXISTS (SELECT DBSTATUS
FROM HR_USER.STATUS SH
WHERE SH.DBASSOC = A1.DBASSOC
AND SH.DBSTATUS = 'T'
AND SH.DBEFF_DATE > JH2.DBEFF_DATE)
ORDER BY A1.DBASSOC ASC;
system@inodata> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT |
| 1 | 151 | 2861K (1)|
| 1 | FILTER |
| | | |
| 2 | NESTED LOOPS |
| | | |
| 3 | NESTED LOOPS |
| 1 | 151 | 643 (1)|
| 4 | NESTED LOOPS |
| 1 | 139 | 640 (1)|
| 5 | NESTED LOOPS ANTI |
| 1 | 128 | 639 (1)|
| 6 | NESTED LOOPS |
| 1 | 113 | 638 (1)|
| 7 | NESTED LOOPS |
| 1 | 106 | 637 (1)|
| 8 | NESTED LOOPS |
| 1 | 86 | 635 (1)|
| 9 | NESTED LOOPS |
| 1 | 69 | 633 (1)|
| 10 | NESTED LOOPS |
| 1 | 53 | 631 (1)|
| 11 | TABLE ACCESS BY INDEX ROWID | JOB_HISTORY
| 163K| 3196K| 587 (1)|
| 12 | INDEX FULL SCAN | IDX_JOB_HIS_DBASSOC_DBDATE
| 22 | | 582 (1)|
| 13 | SORT AGGREGATE |
| 1 | 13 | |
| 14 | FIRST ROW |
| 1 | 13 | 2 (0)|
| 15 | INDEX RANGE SCAN (MIN/MAX)| IDX_JOB_HIS_DBASSOC_DBDATE
| 1 | 13 | 2 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | ASSOC
| 1 | 33 | 2 (0)|
| 17 | INDEX RANGE SCAN | SYS_C0012952
| 1 | | 1 (0)|
| 18 | SORT AGGREGATE |
| 1 | 13 | |
| 19 | FIRST ROW |
| 1 | 13 | 2 (0)|
| 20 | INDEX RANGE SCAN (MIN/MAX)| SYS_C0012952
| 1 | 13 | 2 (0)|
| 21 | INDEX RANGE SCAN | IDX_JOB_HISTORY01
| 2 | 32 | 2 (0)|
| 22 | TABLE ACCESS BY INDEX ROWID | ASSOC
| 1 | 17 | 2 (0)|
| 23 | INDEX RANGE SCAN | SYS_C0012952
| 1 | | 1 (0)|
| 24 | SORT AGGREGATE |
| 1 | 13 | |
| 25 | FIRST ROW |
| 1 | 13 | 2 (0)|
| 26 | INDEX RANGE SCAN (MIN/MAX) | SYS_C0012952
| 1 | 13 | 2 (0)|
| 27 | INDEX RANGE SCAN | IDX_JOB_HISTORY01
| 1 | 20 | 2 (0)|
| 28 | SORT AGGREGATE |
| 1 | 28 | |
| 29 | FILTER |
| | | |
| 30 | NESTED LOOPS |
| | | |
| 31 | NESTED LOOPS |
| 1 | 28 | 5 (0)|
| 32 | INDEX RANGE SCAN | IDX_JOB_HISTORY01
| 2 | 40 | 3 (0)|
| 33 | INDEX UNIQUE SCAN | PK_JOB_TYPE
| 1 | | 0 (0)|
| 34 | TABLE ACCESS BY INDEX ROWID | JOB_TYPE
| 1 | 8 | 1 (0)|
| 35 | SORT AGGREGATE |
| 1 | 13 | |
| 36 | FIRST ROW |
| 1 | 13 | 2 (0)|
| 37 | INDEX RANGE SCAN (MIN/MAX) | IDX_JOB_HIS_DBASSOC_DBDATE
| 1 | 13 | 2 (0)|
| 38 | SORT AGGREGATE |
| 1 | 17 | |
| 39 | FILTER |
| | | |
| 40 | TABLE ACCESS BY INDEX ROWID | JOB_HISTORY
| 1 | 17 | 2 (0)|
| 41 | INDEX RANGE SCAN | IDX_JOB_HIST_DBASOC_DBJOB_TYP
E | 1 | | 1 (0)|
| 42 | SORT AGGREGATE |
| 1 | 17 | |
| 43 | TABLE ACCESS BY INDEX ROWID | JOB_HISTORY
| 1 | 17 | 3 (0)|
| 44 | INDEX RANGE SCAN | IDX_JOB_HIS_DBASSOC_DBDATE
| 1 | | 2 (0)|
| 45 | TABLE ACCESS BY INDEX ROWID | JOB_TYPE
| 1 | 7 | 1 (0)|
| 46 | INDEX UNIQUE SCAN | PK_JOB_TYPE
| 1 | | 0 (0)|
| 47 | INDEX RANGE SCAN | UK_STATUS
| 46018 | 674K| 1 (0)|
| 48 | TABLE ACCESS BY INDEX ROWID | JOB_TYPE
| 1 | 11 | 1 (0)|
| 49 | INDEX UNIQUE SCAN | PK_JOB_TYPE
| 1 | | 0 (0)|
| 50 | INLIST ITERATOR |
| | | |
| 51 | INDEX RANGE SCAN | UK_STATUS
| 1 | | 2 (0)|
| 52 | TABLE ACCESS BY INDEX ROWID | STATUS
| 1 | 12 | 3 (0)|
| 53 | SORT AGGREGATE |
| 1 | 40 | |
| 54 | NESTED LOOPS |
| | | |
| 55 | NESTED LOOPS |
| 1 | 40 | 4 (0)|
| 56 | VIEW | VW_SQ_1
| 1 | 22 | 2 (0)|
| 57 | SORT GROUP BY |
| 1 | 13 | 2 (0)|
| 58 | INDEX RANGE SCAN | UK_STATUS
| 2 | 26 | 2 (0)|
| 59 | INDEX RANGE SCAN | UK_STATUS
| 1 | | 1 (0)|
| 60 | TABLE ACCESS BY INDEX ROWID | STATUS
| 1 | 18 | 2 (0)|
--------------------------------------------------------------------------------
--------------------------------
Note
-----
- 'PLAN_TABLE' is old version
70 rows selected.
Edited by: jeff81 on May 4, 2011 9:06 PM