Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Clusters and CBO
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.
1.
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
------- ------ -------- ---------- ---------- ---------- ---------- ------
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
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
2.
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)
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