Re: question on EXPLAIN_PLAN

[kevin wang]

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.