Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: vslabs@onwe.co.za (Billy Verreynne)
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to optimize this query?
Date: 8 Aug 2003 03:42:58 -0700
Organization: http://groups.google.com/
Lines: 111
Message-ID: <1a75df45.0308080242.515f0b5a@posting.google.com>
References: <bgn6st$civ@netnews.proxy.lucent.com> <1efdad5b.0308070813.55e6a746@posting.google.com> <bgvh44$s29@netnews.proxy.lucent.com>
NNTP-Posting-Host: 198.54.206.91
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060339379 25146 127.0.0.1 (8 Aug 2003 10:42:59 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 10:42:59 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130361

"music4" <music4@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