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

Try it with autotracing enabled (Set autotrace traceonly;) and check the execution strategy. It may be that with the small table added Oracle is scanning the large table to get the key to access the smaller one by index, rather than the other way around.

What optimizer are you using (RULE or COST) ? If cost, when was the last time the statistics were updated ?

Rob Duke
Database Administrator
Motherwell Information Systems

NNOOR wrote:

> 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 Tue Jun 15 1999 - 09:16:39 CDT