Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: question on EXPLAIN_PLAN

Re: question on EXPLAIN_PLAN

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Thu, 07 Feb 2002 14:38:27 -0800
Message-ID: <F001.00409678.20020207140820@fatcity.com>

Kevin,
"select count(*) from table_name"

This was the key on the basis of that I suggested this solution...Your Test definately don't have that high water mark... As regard still some slowness, check for number of extents and it is sizing...If more extents 'select counts' goes for full table scan and takes longer...If extents are rightly sized and minimum in numbers performance shall be better for select count....

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Thu, 07 Feb 2002 13:06:49 -0800

   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

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




MOHAMMAD RAFIQ


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

-- 
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).
Received on Thu Feb 07 2002 - 16:38:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US