Re: Oracle 8i and poor query performance in some queries

Niall Litchfield wrote:
>
 > ...
 >
> Hopefully what this demonstrates is that by looking at the problem queries,
> tracing them, thinking about what they are doing it will almost always be
> possible to significantly improve performance. It should also demonstrate
> that it isn't the optimizer that has the problem but either the data model
> or the sql being issued.

I agree generally. How SQL statements are written has a great effect on the performance. The issue in our environment is that advanced users use SQL*Plus as their query tool. So they write their own SQL's without asking from the DBA :-). Also, the query tools we use like Discoverer generate complex queries and there is very little possibilities to rewrite them. And of course, everybody expects great query performance...

But now back to the original issue. Noel pointed out that the subquery of "select count(*) from a1 where id not in (select id from a2);", which is "select id from a2" is executed 35294 times and every time as a full scan of a2. I fully understand that this kind of SQL would take very very long if the tables are large. But the tables are small! The database has db_block_size = 8192 and db_block_buffers = 25600. When the database starts it says "Database Buffers 209715200 bytes". Now checking the tables - remember that we have 1 MB uniform size as an allocation unit.

select bytes,blocks,extents from dba_segments where segment_name='A1';

      BYTES BLOCKS EXTENTS
---------- ---------- ----------

    4194304 512 4

select bytes,blocks,extents from dba_segments where segment_name='A2';

      BYTES BLOCKS EXTENTS
---------- ---------- ----------

    7340032 896 7

select sum(bytes),sum(blocks),sum(extents) from dba_segments where segment_name='A1' or segment_name='A2'; SUM(BYTES) SUM(BLOCKS) SUM(EXTENTS)
---------- ----------- ------------

   11534336 1408 11

It seems that the tables take 1408/25600*100 = 5,5 percent of the total block buffer space available and should remain in memory during the SQL execution. Well, as we know, if Spotlight is right, lots of IO seems to happen and the query takes very long. This happens even when there are no other users connected. It seems like Oracle's buffer space is not used but the data is read again and again from the disk or there is some other memory-related problem goin' on like excessive swapping. To make a point: there IS a problem.

--
Heikki