Re: question on EXPLAIN

From: kevin wang <kwang_at_vivonet.com>
Date: Wed, 06 Feb 2002 13:52:24 -0800
Message-ID: <F001.0040806C.20020206134543@fatcity.com>

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

<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Bill Zakrzewski

  To: <A title=kwang_at_vivonet.com
  href="mailto:kwang_at_vivonet.com">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 ZakrzewskiSenior

  ConsultantIntactus Technology, Inc.
  <BLOCKQUOTE dir=ltr
  style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: kevin wang

    To: <A title=oracledba_at_lazydba.com
    href="mailto:oracledba_at_lazydba.com">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. <FONT face=Arial
size=2> Received on Wed Feb 06 2002 - 15:52:24 CST

Re: question on EXPLAIN_PLAN