Re: How to optimize this query?

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0308080242.515f0b5a_at_posting.google.com>...
> "music4" <music4_at_163.net> wrote in message
>
> > The application runs on a Sun Netra 1400 server that has two 440MHz CPU and
> > 1 18G harddisk. When I said "consume alot of resources", I meant that the
> > corresponding oracle process occupy more than 20% CPU resource
>
> This is good. CPU usage is good. CPUs sitting idle is a waste.
>
> > and caues
> > system IO Wait to about 20%.
>
> This is kind of bad.. in that it points to that the disk i/o cannot
> keep up with CPU demand. This is however a fact that one has to live
> with.
>
> Possible tuning to look at: Async i/o, larger db block buffers, larger
> db block sizes (especially not smaller than the physical block size)
> and so on... Or looking at better striping, disk
> management/distribution on the o/s and hardware size.
>
> > Since we have only Oracle 8.1.5 standard edition, bitmap index feature is
> > not available.
>
> A pity.
>
> > The exact query like:
> >
> > select count(*) from tbl_1 where flag1=1 and flag2<>0 and flag3<>-9 and
> > flag4=101 and flag5<30000001
> >
> > COUNT(*)
> > ----------
> > 0
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 SORT (AGGREGATE)
> > 2 1 COUNT (STOPKEY)
> > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_1'
> > 4 3 INDEX (RANGE SCAN) OF 'IND_TBL_1' (NON-UNIQUE)
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 694 consistent gets
> > 0 physical reads
> > 0 redo size
> > 1090 bytes sent via SQL*Net to client
> > 791 bytes received via SQL*Net from client
> > 4 SQL*Net roundtrips to/from client
> > 1 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
> >
> > Do you have any idea on that?
>
> The stats seem good. 0 physical reads. Which means a 100% buffer hit
> ratio. But that is contrary to what you mentioned wrt high system IO
> wait times... The stats does not seem right.
>
> The only potential problem in the above query is the index range scan.
> And is IMO the cause of your slow performance. Your index looks
> something like this, right?
> create index foo_idx on foo ( flag1, flag2, flag3 .. )
>
> As you are only giving a single unique value (flag1), Oracle does an
> index range scan. Thus your query looks something like this internally
> (assuming flag1 to 3 are indexed):
> where flag=1 and (flag2 between 0 and 99999999)
> and (flag3 between 0 and 9999999)
>
> The rows matching those entries are read from the table using the
> index. And then each physically row is checked for: where flag2<>0 and
> flag3<>-9 .. etc.
>
> Bitmap indexes aside, there are basic two ways to address your
> problem.
>
> 1. Use a a full table scan:
> select
> /*+ FULL(tbl_1) */
> count(*)
> from tbl_1
> where flag1=1 and flag2<>0 and flag3<>-9
> and flag4=101 and flag5<30000001
>
> IMO you should see an immediate performance improvement (if my guess
> about flag1's cardinality is right).
>
> Optionally, use this with a PQ clause (but then Oracle SE does not do
> that, right?):
> select
> /*+ FULL(tbl_1) PARALLEL(tbl_1,2) */
> count(*)
> from tbl_1
> where flag1=1 and flag2<>0 and flag3<>-9
> and flag4=101 and flag5<30000001
>
>
> 2. Create separate indexes per flag. See if index merge/hash/whatever
> joins work faster. And it will do away with a potential slow and
> wasteful index range scan.
>
>
> There are other options.. but these involve physical changes to the
> table & design which I think you will not find too appealing. :-)

why would using sepearte indexes for each flag improve performance? why is merging or hashing better than one index?

I noticed that he is using 2 indexes, so I thought he may want to expand the index to all 5 fields. or is that wrong?

im guessing a full table scan is faster here because with the 'range scan'
and the 'not equal' you have to look at every block anyway?

oracle 8i is pretty good at deciding when to use indexes. does this happen often? using an index when its inappropriate? does this indicate that the table is not analyzed? Received on Fri Aug 08 2003 - 11:05:54 CDT