Re: question on EXPLAIN_PLAN

Re the HWM causing a slowdown:

A needle in a haystack is much easier to find when the haystack has been removed.

Jared

On Thursday 07 February 2002 13:06, kevin wang wrote:
> Hi, Rafiq
> Thank you for your advise, and thanks everyone, the handsome guys, reply
> me.
> You are right, it seems like a High Water Mark problem, after lots of
> other try,
> finally I drop that table and re-create that table, copy data, build PK
> index,
> analyzed in same way as before, and test the performance,
> the SQL statement is as fast as we expected.
> The "select count(*) from table_name" is still slower than the "good
> guy", but much faster than before.
> As a problem, it is fixed now.
>
> But I still don't understand how can the stupid HWM make such a slow-down
> on performance?
> I did lots of tests, I am sure there is nothing to do with
> analyze,SGA,session_wait,resource.
> On both two databases, that table and its PK index use one extent on
> different tablespace on different datafile.
> Actually, the table in the good performance database has the same HWM as
> the bad performance buy.
> I mean, re-create table can fix the problem, but, the problem is not only
> with the HWM.
> I will do some further investigation.
>
> thanks a lot,
>
> Kevin Wang
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, February 06, 2002 2:18 PM
>
> > Try to reduce High Water Mark on your table by
> > creating as select a backup table
> > truncate original table
> > insert into original table from backup table...
> >
> > This is based on your info for full table scan...Please also rebuild
>
> indexes
>
> > on that table and analyze table if you are on COST BASED..
> >
> > HTH,
> > Regards
> > Rafiq
> >
> >
> >
> >
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Date: Wed, 06 Feb 2002 13:45:44 -0800
> >
> > 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
> > To: kevin wang
> > 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
> > To: LazyDBA.com Discussion
> > 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.
> >
> >
> >
> >
> >
> >
> >
> > MOHAMMAD RAFIQ
> >
> >
> > _________________________________________________________________
> > Chat with friends online, try MSN Messenger: http://messenger.msn.com
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Mohammad Rafiq
> > INET: rafiq9857_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: Jared Still
  INET: jkstill_at_cybcon.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).