Hi,

 

Having a bit of a problem with the CBO on 9.2.0.6, and I wonder if anyone can offer any advice?

 

I have a query that looks like this:

SELECT FPGI.FPGI_ID L_FPGI_ID,

       FPGI.FPGI_UMI_PAGE_NUMBER DSP_FPGI_UMI_PAGE_NUMBER,

       FPGI.PC_ID DSP_PC_ID

  FROM FULL_PAGE_IMAGES FPGI

 WHERE ( ( :1='ENTER-QUERY')

    OR ( ( NOT :2='ENTER-QUERY')

   AND ( /* CG$MDTU_VWC_START DOC.FPGI */ (FPGI.PC_ID = :3) /* CG$MDTU_VWC_END DOC.FPGI */ ) ) );

 

As some of you may recognize, this query is generated by Oracle Forms, as part of an LOV population.  (That’s where all those parentheses and the ‘ENTER-QUERY’ business comes in.)  So, as this is generated, I have no control over the code, and can’t change it.

 

It’s doing a FULL TABLE SCAN on the table, rather than an INDEX RANGE SCAN on the index on the PC_ID column.  So, after playing with it a bit, it seems that the thing that’s screwing it up is those extra predicates that Forms is adding, regarding the query mode.  If I modify the query and remove those extraneous predicates, the query does the right thing (INDEX RANGE SCAN).  But, I can’t touch the query, cause it’s generated.

 

So, says I, I’ll use stored outlines, and no problem, be on my way…..

 

Well, after struggling to get stored outlines to work (what an awkward interface!  I can’t wait till 10g and SQL Profiles!), I see that I’ve in fact, created a larger problem.  The “fixed” plan is in place, via stored outline, and my performance is WORSE than the FTS!  After scratching my head for a while, I realized that, even with an INDEX hint correctly specified and correctly choosing the index, the reason that the performance is worse, is that the optimizer is choosing to do an INDEX FULL SCAN, followed by a TABLE ACCESS BY ROWID on every row in the  table, which is FAR worse than the FTS I started with!  The problem is that an INDEX hint specifies (correctly) which index to use, but it does NOT specify what type of scan operation (unique, range, or full) Oracle will do on said index!  It seems that in the presence of those aforementioned, extraneous predicates, forces the CBO into doing a full scan rather than range scan!  I did a 10053 trace, to try to get some insight, and found that there are no differences in the trace, up to the SINGLE TABLE ACCESS PATH.  At that point, they diverge significantly.  Here’s what I have in each trace file:

Problem query:

***************************************

SINGLE TABLE ACCESS PATH

Column:      PC_ID  Col#: 2      Table: FULL_PAGE_IMAGES   Alias: FPGI

    NDV: 2794406   NULLS: 0         DENS: 3.5786e-07 LO:  1  HI: 34817851

    NO HISTOGRAM: #BKT: 1 #VAL: 2

  TABLE: FULL_PAGE_IMAGES     ORIG CDN: 95463327  ROUNDED CDN: 954635  CMPTD CDN: 954635

  Access path: index (no sta/stp keys)

      Index: FPGI_INDX_FK01

  TABLE: FULL_PAGE_IMAGES

      RSC_CPU: 0   RSC_IO: 6964505

  IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00

  BEST_CST: 696451.00  PATH: 4  Degree:  1

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***********************

Join order[0]:  FULL_PAGE_IMAGES[FPGI]#0

Best so far: TABLE#: 0  CST:     696451  CDN:     954635  BYTES:   16228795

prefetching is on for FPGI_INDX_FK01

Final - All Rows Plan:

  JOIN ORDER: 0

  CST: 696451  CDN: 954635  RSC: 696450  RSP: 696450  BYTES: 16228795

  IO-RSC: 696450  IO-RSP: 696450  CPU-RSC: 0  CPU-RSP: 0

 

 

Good query (without extraneous predicates):

***************************************

SINGLE TABLE ACCESS PATH

Column:      PC_ID  Col#: 2      Table: FULL_PAGE_IMAGES   Alias: FPGI

    NDV: 2794406   NULLS: 0         DENS: 3.5786e-07 LO:  1  HI: 34817851

    NO HISTOGRAM: #BKT: 1 #VAL: 2

  TABLE: FULL_PAGE_IMAGES     ORIG CDN: 95463327  ROUNDED CDN: 34  CMPTD CDN: 34

  Access path: index (equal)

      Index: FPGI_INDX_FK01

  TABLE: FULL_PAGE_IMAGES

      RSC_CPU: 0   RSC_IO: 7

  IX_SEL:  0.0000e+00  TB_SEL:  3.5786e-07

  BEST_CST: 1.00  PATH: 4  Degree:  1

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS

***********************

Join order[0]:  FULL_PAGE_IMAGES[FPGI]#0

Best so far: TABLE#: 0  CST:          1  CDN:         34  BYTES:        578

Final - All Rows Plan:

  JOIN ORDER: 0

  CST: 1  CDN: 34  RSC: 1  RSP: 1  BYTES: 578

  IO-RSC: 1  IO-RSP: 1  CPU-RSC: 0  CPU-RSP: 0

 

 

Note the difference in access paths, “index(no sta/stp)”, which is the full scan, vs. “index (equal)” which is the range scan.

So, at this point, I’m stuck….anyone else faced something similar?  Without the ability to modify the SQL, what are my options?  Any ideas, anyone?

 

AdvThanksance,

 

-Mark

 

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
734.997.4059  or 800.521.0600 x 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.