30GB table: Hardware performance issue ?

From: Andras Kovacs <andkovacs_at_yahoo.com>
Date: 3 Mar 2003 12:45:14 -0800
Message-ID: <412ebb69.0303031245.7c291172@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.

Received on Mon Mar 03 2003 - 14:45:14 CST