RE: question on EXPLAIN_PLAN

Hello Kevin

you wrote:
 And the table and index is analyzed at same way. What way?
If you use estimate you can get wrong statistics. Try to do full analysis.

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: kevin wang [SMTP:kwang_at_vivonet.com]
> Sent: Wed, February 06, 2002 11:46 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: question on EXPLAIN_PLAN
>
> Hi, Bill
>
> You are right, there is really something to do with the index.
> I drop&re-created that PK index, it became much faster, but after 5
> minutes, it became slow again, and I am sure no records
> insert/delete/update happened. I drop&re-created that indexes again,
> nothing happened this time, it is still slow!
> It is really weird!
>
> And I found on the bad performance database, a simplest query: select
> count(*) from table_name will take 4 seconds!
> the explain_plan said it used cost-optimizer, using fast_full_index_scan
> on that PK index,
> what should I do with the index? I already tried to re-created the PK
> index.
>
> The bad performance database is on a super-box, 4 CPUs and more memory.
> the good performance one is on a normal box.
> And the table and index is analyzed at same way.
>
> thanks a lot for your reply.
>
>
> Kevin Wang
>
>
>
>
> ----- Original Message -----
> From: Bill Zakrzewski <mailto:bill_at_intactus.com>
> To: kevin wang <mailto:kwang_at_vivonet.com>
> Sent: Wednesday, February 06, 2002 12:38 PM
> Subject: Re: question on EXPLAIN_PLAN
>
> Kevin,
>
> Have these indexes been rebuilt recently? If not, you may want
> to consider rebuilding the indexes. Not sure if this is your issue, but
> if you have inserted/deleted records from your tables over time, the index
> levels may have grown as well.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Bill Zakrzewski
> Senior Consultant
> Intactus Technology, Inc.
>
>
> ----- Original Message -----
> From: kevin wang <mailto:kwang_at_vivonet.com>
> To: LazyDBA.com Discussion <mailto:oracledba_at_lazydba.com>
> Sent: Wednesday, February 06, 2002 2:43 PM
> Subject: question on EXPLAIN_PLAN
>
> 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.
>
>
>
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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).