"Heikki Siltala" <heikki.siltala_at_stakes.nojunk.fi> wrote in message
news:bn6oti$6g6$1_at_phys-news1.kolumbus.fi...
>
> 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.
But if you are doing a FTS then the blocks go on the LRU list and get aged
out rapidly. After all no-one in their right mind would scan the same blocks
35000 times when they could do it once now would they :(. It really seems
like these tables get accessed frequently by the id column (and indeed there
may even be RI between the columns) in which case indexing the id columns
will probably cure this and other issues.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
Received on Wed Oct 22 2003 - 15:36:12 CDT
