| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer Instability on 10.2.0.2
> So in your case,
> You had a 'slow cursor' in existence at 13:00,
> so you re-used it.
>
> The cursor got invalidated - or maybe the plan
> simply got flushed (you can also check the LOADS
> column on v$sql) - before 13:30.
>
> At 13:30 you reloaded and got a new LAST_load_time,
> but the first_load_time was still 6:00.
Makes perfect sense, but.... the example below shows that we are HARD parsing (misses in the library cache = 1) but we are still Full Table Scanning the large table even though we are only retrieving 12 rows from PS_AUD_JOB. This suggests to me that we didnt find an inefficient plan in the library cache but rather the optimizer chose the FTS at parse time for this statement itself, even though the cardinality of PS_AUD_JOB is only 12 rows.
Also I have to use a date which is 3 weeks old to flip the plan from the index lookup to the FTS (and 3 weeks of data is over 10,000 rows). But the maximum number of rows that we have processed in PS_AUD_JOB in the last 6 months is 700. This suggests to me that bind variable peeking is intermittently failing or we are hitting some other spurious bug. Anything else I am missing..?
SELECT DISTINCT I01.EMPLID, I01.EMPL_RCD, I02.PER_ORG,
TO_CHAR(I02.EFFDT,
'YYYY-MM-DD'), I02.EFFSEQ
from
PS_AUD_JOB_JR I01, PS_JOB I02 where I01.AUDIT_STAMP > :1 and
I01.EMPLID = I02.EMPLID and I01.EMPL_RCD = I02.EMPL_RCD
and
I02.JOB_INDICATOR = 'P' and (I02.PER_ORG = 'EMP' or :2 =
'TMMFCHRO')
and I02.COMPANY IN (SELECT MST.COMPANY FROM PS_HRMSSI_COMPANY
MST WHERE
MST.HRMSSI_ID = :2) and ( I02.EFFDT = (SELECT MAX(A_ED1.EFFDT)
FROM
PS_JOB A_ED1 WHERE I02.EMPLID = A_ED1.EMPLID AND I02.EMPL_RCD =
A_ED1.EMPL_RCD) AND I02.EFFSEQ = (SELECT MAX(A_ES1.EFFSEQ) FROM
PS_JOB
A_ES1 WHERE I02.EMPLID = A_ES1.EMPLID AND I02.EMPL_RCD =
A_ES1.EMPL_RCD AND
I02.EFFDT = A_ES1.EFFDT) ) ORDER BY I01.EMPLID
call count cpu elapsed disk query current rows
Parse 1 0.14 0.13 0 0 0 0 Execute 1 0.12 0.12 0 0 0 0 Fetch 2 1.00 20.33 19790 27743 0 7
total 4 1.26 20.59 19790 27743 0 7
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 21
Rows Row Source Operation
------- ---------------------------------------------------
7 SORT UNIQUE (cr=27743 pr=19790 pw=0 time=20331452 us)
7 FILTER (cr=27743 pr=19790 pw=0 time=20331406 us)
7 SORT GROUP BY (cr=27743 pr=19790 pw=0 time=20331396 us)
7 FILTER (cr=27743 pr=19790 pw=0 time=20328375 us)
7 NESTED LOOPS (cr=27727 pr=19790 pw=0 time=20328194 us)
7 NESTED LOOPS (cr=27711 pr=19790 pw=0 time=20328117 us)
33 HASH JOIN (cr=27709 pr=19790 pw=0 time=55723555 us)
12 TABLE ACCESS BY INDEX ROWID PS_AUD_JOB_JR (cr=11 pr=4
pw=0 time=26661 us)
12 INDEX RANGE SCAN PSAAUD_JOB_JR (cr=7 pr=3 pw=0
time=10910 us)(object id 69663009)
340687 TABLE ACCESS FULL PS_JOB (cr=27698 pr=19786 pw=0
time=13978748 us)
7 INDEX UNIQUE SCAN PS_HRMSSI_COMPANY (cr=2 pr=0 pw=0
time=177 us)(object id 52907198)
7 INDEX RANGE SCAN PSAJOB (cr=16 pr=0 pw=0 time=50 us)
(object id 69663591)
7 SORT AGGREGATE (cr=16 pr=0 pw=0 time=96 us)
7 FIRST ROW (cr=16 pr=0 pw=0 time=56 us)
7 INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=16 pr=0 pw=0
time=48 us)(object id 69663591)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
 |
 |