Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer Instability on 10.2.0.2

Re: Optimizer Instability on 10.2.0.2

From: <mccmx_at_hotmail.com>
Date: 24 Apr 2007 08:23:57 -0700
Message-ID: <1177428237.404718.288760@s33g2000prh.googlegroups.com>


> 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=0
time=48 us)(object id 69663591)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total
Waited Received on Tue Apr 24 2007 - 10:23:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US