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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
