Re: Clusters and CBO

From: Jonathan Bliss <bliss_jonathan_at_nossspam.hotmail.com>
Date: Wed, 31 Jul 2002 16:58:05 +0100
Message-ID: <EJT19.2484$sH3.77614@newsfep1-win.server.ntli.net>

Vladimir

Yes I had considered outlines and I may have to go that way, but if the CBO truly is better then all I would be doing is finding a way to perpetuate the bad execution plans with the good ones. My gut feeling is that I should be able to get benefit from the CBO if I can identify and solve the main problem areas.

Consider the following trace fragments generated with full stats computed on a tiny demo database running in 8.1.7 Personal, the code is a cursor and the trace represents a case where it is called from within another cursor that fetches 40 rows (+1) and calls this once for each fetch. The second trace demonstrates that this cursor really should return one row per MAIN_DATA row by implementing it as a subquery. Incidentally can anyone explain the ORA-00904 and lack of a full plan from tkprof. Plan table exists in the table owner's schema, all stuff run by table owner.

SELECT ORG.CODE
FROM
ORG_RELS ORR,ORGANISATIONS ORG,DEP_LIST DPL,ORG_ALIASES OAL WHERE ORR.ORG_ID_TO = OAL.ORG_ID AND ORR.ORG_ID_FROM = ORG.ID AND ORG.CODE = DPL.ORG_CODE AND OAL.CODE = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.00 0.00 0 0 0 0
Execute 40000 4.34 4.24 0 0 0 0
Fetch 40000 241.40 242.12 136 2412000 160000 38000
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 80001 245.74 246.36 136 2412000 160000 38000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (HPS) (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------

  38000 NESTED LOOPS
  76000 NESTED LOOPS
  76000 NESTED LOOPS

  42000     TABLE ACCESS BY INDEX ROWID ORG_ALIASES
  42000      INDEX UNIQUE SCAN (object id 3619)
  76000     INDEX FAST FULL SCAN (object id 3621)

110000 TABLE ACCESS CLUSTER ORGANISATIONS 38000 INDEX UNIQUE SCAN (object id 5540)

ORA-00904: invalid column name

parse error offset: 104

UPDATE MAIN_DATA I10
SET USER_2 = (SELECT org.code

        FROM org_rels orr,
             organisations org,
             dep_list dpl,
             org_aliases oal
       WHERE orr.org_id_to = oal.org_id
         AND orr.org_id_from = org.id
         AND org.code = dpl.org_code
         AND oal.code = I10.SUB_DEPT)

WHERE NVL(SUBSTR(SUB_DEPT,1,3),'V81') <> 'V81' AND END_DATE BETWEEN '01-APR-91' AND '30-JUL-02' call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.78 0.80 0 2824 0 0
Execute 1 0.64 2.80 2375 4336 90 40
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 1.42 3.60 2375 7160 90 40

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (HPS)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  UPDATE CDS_INPATIENTS
     41   TABLE ACCESS FULL CDS_INPATIENTS

ORA-00904: invalid column name

parse error offset: 104

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:ai8th1$1uv$1_at_babylon.agtel.net...
> Answer to 3) is definite yes - in 10i (speculative name) RBO will be no
more.
> As for 1) - that depends. But if RBO gives you a better performing plan,
> capture the plan and create a stored outline from it and enable stored
outlines -
> then CBO will work just the same way. 2) - no idea.
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "Jonathan Bliss" <bliss_jonathan_at_nossspam.hotmail.com> wrote in message
> news:vcS19.2148$sH3.63606_at_newsfep1-win.server.ntli.net...
> > Hi
> >
> > I am working with an application originally written in Oracle 6. A
major
> > set of reference tables are built as a cluster. These reference tables
are
> > highly normalized and self referencing. i.e. an item in the main table
can
> > be related to other items in the same table that can be in turn related
to
> > others. The application appears to run more slowly with the CBO than
with
> > the rule based optimizer thus the live environment uses the RBO. The
> > culprit appears to be when multiple aliases of the reference tables are
> > used. The entire application is written in PL\SQL. I didn't write it.
> >
> > 1. Should the CBO *always* outperform the RBO.
> > 2. Are clusters one instance this may not be the case.
> > 3. Is Oracle really going to take away the RBO
> >
> > Tests suggest that rebuilding the tables without the cluster, computing
> > stats and using CBO may not degrade performance but it takes courage to
bite
> > the bullet with the live system.
> >
> > Any thoughts appreciated.
> >
> > Jonathan
> >
> >
>
Received on Wed Jul 31 2002 - 10:58:05 CDT