Craig,
Could you please send the explain plans for each statement, the
value of db_file_multiblock_read_count and the following columns related
to tables, columns and indexes (user_tables.blocks,
user_tables.num_rows, user_indexes.blevel, user_indexes.leaf_blocks,
user_indexes.distinct_keys, user_indexes.clustering_factor,
user_tab_columns.density for each column in the index).
--
Daniel W. Fink
http://www.optimaldba.com
Craig Healey wrote:
>OK, I'm confused. Maybe it's Monday morning and my brain's not working.
>We have a production schema and a test schema on the same Oracle 8.1.7
>instance, running on Windows. They both have a customer table, with 3
>million and 2 million records respectively. They both have the same
>indexes, and both have been analyzed today. Production used an index and
>took 40ms. Test didn't and took 20s. I played around, analyzing,
>dropping and creating indexes etc. Now neither of them use the index,
>both taking around 20s.
>I can add a hint, which works, but I want to know what changed.
>
>TIA
>
>Craig Healey
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel W. Fink
INET: optimaldba_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Jun 02 2003 - 11:40:06 CDT
