Re: 30GB table: Hardware performance issue ?

It's always worth mentioning the exact version of Oracle. The definition of the index would also be a good idea.

Your sample query looks as if it should be addressing a single partition according to your description (unless you are partitioning by month in the middle of the month, or other weird boundary) and yet you are using a
partition iterator. Could you use a full explain plan utility rather than autotrace - it might give us some clues.

It seems likely that the most significant obstacle at the moment in the IN-list.
If you can find a way to restructure the index or the query to address that, you
may get a faster result. But if you actually HAVE to hit 7,777 physical data blocks
to find the data, you haven't got much
hope.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Andras Kovacs" <andkovacs_at_yahoo.com> wrote in message
news:412ebb69.0303031245.7c291172_at_posting.google.com...


> We have a very large system that collects historical data.

> There is a partitionned (monthly based) table called HISTORY.

> Its size is 30GB and has over 800 000 000 rows.

> It has a partitionned index IDX_HISTORY.

>

> The present query runs in 33 seconds the first time and 4 seconds

> the second time (100% cache hit).

>

> I have to bring down the first run time below 10 seconds.

>

> The hardware is Win2000 Adv Server on Compaq Proliant DL380.

> HD: Raid 1+0, 15000RPM SCSI.

> The system is very reliable. My problem is only the performance.

>

> What I have to do to improve the performance this query?

>

>

>

> SELECT  timestamp,

>         id,

>         value

> FROM   history

> WHERE  timestamp >= to_date('2002/11/21 17:00:00', 'YYYY/MM/DD

> HH24:MI:SS')

> AND    timestamp <= to_date('2002/11/30 17:00:00', 'YYYY/MM/DD

> HH24:MI:SS')

> AND    to_char( timestamp, 'MI' ) = '00'

> AND t1.id in

(5867,5868,5869,5870,5871,5872,5873,5874,5875,5876,5877,5878,


>

5879,5880,5881,5882,5883,5884,5885,5886,5887,5888,5889,5890,


>

5891,5892,5893,5894,5895,5896,5897,5898,5899,5900,5901,5902,


>

5903,5904,5905,5906,5907,5908,5909,5910,5911,12166,12375);


>

>

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=146 Card=55

> Bytes=880)

>    1    0   PARTITION RANGE (ITERATOR)

>    2    1     INLIST ITERATOR

>    3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'HISTORY'

> (Cost=146 Card=55 Bytes=880)

>    4    3         INDEX (RANGE SCAN) OF 'IDX_HISTORY' (NON-UNIQUE)

> (Cost=81 Card=55)

>

>

>

>

> Statistics

> ----------------------------------------------------------

>           0  recursive calls

>           0  db block gets

>       12025  consistent gets

>        7777  physical reads

>           0  redo size

>      528913  bytes sent via SQL*Net to client

>         647  bytes received via SQL*Net from client

>           4  SQL*Net roundtrips to/from client

>           1  sorts (memory)

>           0  sorts (disk)

>       11255  rows processed

>

>       Performance is 33 seconds.