Re: How to optimize this query?

"music4" <music4_at_163.net> wrote in message news:<bgvh44$s29_at_netnews.proxy.lucent.com>...
> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
> news:1efdad5b.0308070813.55e6a746_at_posting.google.com...
> > "music4" <music4_at_163.net> wrote in message
> news:<bgn6st$civ_at_netnews.proxy.lucent.com>...
> > > Greetings,
> > >
> > > I have this query:
> > >
> > > select some field from tbl where flag1=0 and flag2=1 and flag3=2 and
> flag4=3
> > > and flag5=4
> > >
> > > flag1, flag2 and flag3 are all checked in (0,1,2,3,4,5).
> > >
> > > And I have a index on flag1, flag2 and flag3 since flag4 and flag5 in
> more
> > > case are constants(3 and 4).
> > >
> > > When tbl has huge number of data (over a half million records), I find
> that
> > > if tbl contains data meet query condition, the select performs well. But
> if
> > > not, the select cosumes huge resource.
> > >
> > > Do you have any idea to optimize this kind of query? I am using Oracle
> > > 8.1.5.
> > >
> > > Thanks in advance!
> > > Evan
> >
> >
> > please post the exact query and explain plan including statistics What
> > do you mean by consume alot of resources? how is performance?
> >
> > also please include an explain plan when data is returned. andrew may
> > be right. try a bitmap index if this table does not have transactions
> > on it.
> >
> > 500k records is not that many with the hardware we have today. What
> > kind of system are you on?
> >
> > I need more details.
>
> 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, and caues
> system IO Wait to about 20%.
>
> Since we have only Oracle 8.1.5 standard edition, bitmap index feature is
> not available.
>
> 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?
>
> Thanks,
> Evan

also your statistics have 0 physical reads and 0 redo size, so there is no way this is increasing I/O.

so im assuming you have run this query multiple times, so the first time the data is loaded into memory. if so your not loading much.

are these I/O increases apparently 'random'? How are you measuring increases in I/O? Are you by chance doing really large DML statements particularly Updates before this query? If so you could have an issue with block cleanout. look that up on asktom.oracle.com Received on Fri Aug 08 2003 - 07:13:59 CDT