Re: Please help me solve this Oracle mystery...

What most likely happened, is that the statement plan changed by dropping and recreating the table.
I can imagine the following scenario
optimizer _goal is choose in init.ora
the schema is analyzed, so the cost based optimizer is used one table is dropped and recreated, this table is not analyzed after recreation
Oracle now uses the rule based optimizer because there are no statistics on one of the tables involved in the query. Without looking at trace output (either issue alter session set sql_trace = true, or better still set autotrace on in sqlplus 3.3.4 and higher), you will never know what really happened, but it must be something like this. The software just does what you ask of it...

Hth,

Sybrand Bakker, Oracle DBA

NNOOR wrote in message <7jdve1$epg_at_chronicle.concentric.net>...
>
>Hi,
>
>We are running Oracle 8.0.5. Machine is a dual Pentium Pro 200 with
>512MB RAM and RAID level-3 hard drives.
>
>In a query, a table with only 45 records was part of a join. Other
>two tables had about 100,000 record each. The query ran very slow.
>When the smaller table (only 45 records, indexed, two fields--one
>varchar(10), the other varchar(35), indexed and joined on the smaller
>field with fields of same kind) was dropped from the join, the
>query was lightning fast. Nothing else changed in the query! When
>drop and recreate the smaller table and make it part of the join
>again, query is now very fast. So dropping the table and recreating
>it fixed the problem. Same thing happened on another query with
>another table. Now, since we were "experienced", we dropped and
>recreated that table and the problem fixed itself.
>
>The Question: What do you think was causing such a behaviour? Can
>the data in Oracle go corrupt? Fragmented? To a degree that a table
>of only 45 records could bring a query to it's knees? Are there any
>tools that we can use to check the integrity of the rest of the
>data? Where would you start?
>
>Thanks very much for your help.
>
>Regards,
>Nasir Noor (nnoor_at_cris.com)
Received on Sun Jun 06 1999 - 10:38:21 CDT