Re: How to optimize this query?

"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. :-)

--
Billy