Re: Oracle Performance Problem W/ Very Large Database

When you are running Oracle7, stay away from computing statistics. We've had a call to Oracle about the optimizer not using indexes rendering a very slow performance. We were told that

    the optimizer in O7 has problems
    to stay clear of compute statistics
    to upgrade to Oracle 8

The latter recommendation was made because they re-implemented the optimizer in O8.

Hope this helps,

--
Gerrit-Jan Linker

web:     http://www.oraxcel.com
email:  gjlinker_at_oraxcel.com

Oraxcel: Linking Oracle to Excel
OraCodes: Explaining ORA- error codes
OraSQL: Explaining Oracle SQL syntax
OraDebug: PL/SQL debugger
OraWeb: Oracle access from IE


Mark Himeda <mhimeda_at_halcyon.com> wrote in message
news:kovH4.1128$F%5.45507_at_news.uswest.net...


> Help!  We have a simple, but very large database for which we cannot

> understand why the optimizer is producing the following explain plans.

> Specifically, for Explain Plan1 below, why is a table scan being performed

> on the ParentTbl when the primary key index should be used?  For Explain

> Plan2 below, why is an full index scan being performed instead of a range

> scan?  Even optimizer hints don't seem to work!

>

> We have a simple schema similar to the following:

>

> ParentTbl (SeqNo integer, Col1 varchar(50), Col2 varchar(50)...)

> ChildTbl(DateTimeStamp datetime, SeqNo integer, Col1 varchar(50)...)

>

> ParentTbl has 100,000,000 rows, each row approx. 800 k max.

> ChildTbl has 700,000,000 rows, each row approx. 100 k max.

>

> Primary keys: ParentTbl (SeqNo), ChildTbl (TimeStamp)

> Additional Idx: ChildTbl (SeqNo, DateTimeStamp)

>

> Query 1: Produces approx. 50,000 rows.

> Select ChildTbl.Col1, count(ChildTbl.*)

> From ChildTbl, ParentTbl

> WHERE ChildTbl.DateTimeStamp >= to_date({some date}...)

>       AND ChildTbl.DateTimeStamp < to_date({some larger date}...)

>       AND ParentTbl.SeqNo = ChildTbl.SeqNo

> GROUP BY ChildTbl.Col1;

>

>

>

> Explain Plan 1:

> 1) Table scan on ParentTbl

> 2) ChildTbl PK Index used

>

> Query 2:  1 row (aggregate)

> Select count(ChildTbl.*)

> From ChildTbl, ParentTbl

> WHERE ChildTbl.DateTimeStamp >= to_date({some date}...)

>       AND ChildTbl.DateTimeStamp < to_date({some larger date}...)

>       AND ParentTbl.SeqNo = ChildTbl.SeqNo;

>

> Explain Plan 2:

> 1) Index scan on ChildTbl PK

> 2) ParentTbl PK Index used

>

>

>

>

>

>