RE: question on EXPLAIN_PLAN

Kevin,

Have you tried it with autotrace on to see the number of reads etc?

Iain

-----Original Message-----
Sent: Wednesday, February 06, 2002 10:09 PM To: Multiple recipients of list ORACLE-L

Yes, Mike, I analyzed the table and PK index on the two databases at the same way,
it seems that there is something wrong with the PK index, the" select count(*) from table_name " query took 4 seconds, and only 335199 rows atcually.
it use fast_full_index scan of the PK index, and I re-created the PK index, same thing. that's very bad.

it is Oracle 8.1.6 on win2000.

thanks for reply.

Kevin Wang

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, February 06, 2002 1:55 PM

> Kevin,
>
> Have you analyzed the tables on both databases? Card is the CBO's estimate
> of the number of rows it will process.
>
> Mike
>
>
>
> >From: "kevin wang" <kwang_at_vivonet.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: question on EXPLAIN_PLAN
> >Date: Wed, 06 Feb 2002 11:43:38 -0800
> >
> > Hi, guys
> >
> > The problem belows is really make me confused and gave me big trouble,
> >is there someone can give me some hlep?
> >
> > I have two databses, same version(oracle 8.1.6),same O/S(win2000),
same
> >schema structure, different data(but small difference of size).
> > and even exactly same explain_plan of my sql query.
> > But on one database, the cardinality of one PK index access upon one
> >table is 27(cost=2,card=27,bytes=756) (table rows 263758)
> > and the other is 11706!!!! (cost=3,card=11706,bytes=199002)( table
rows
> >351173).
> > so, on one DB the sql query took 300ms, one the other, it took 5
> >seconds!
> >
> > Any advise is highly appreciated.
> >
> > thanks,
> >
> > Kevin Wang
> > Database Administrator
> > Vivonet Canada Inc.
> >
> >
> >
>
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Killough
> INET: mwkillough_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kevin wang
  INET: kwang_at_vivonet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).