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.
>
OK makes perfect sense. Here is some more info....
The example below shows that we had a HARD parse (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. That suggests to me that this query was not finding an inefficient plan in the library cache, but rather it was selecting an inefficient plan itself. I am suspecting a problem with bind variable peeking because I enabled a 10053 trace and I can see occurences of "Bind#0 No oacdef for this bind".
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=0time=48 us)(object id 69663591)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited