Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed-east.nntpserver.com!nntpserver.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-xit-09!sn-xit-08!supernews.com!postnews1.google.com!not-for-mail From: andkovacs@yahoo.com (Andras Kovacs) Newsgroups: comp.databases.oracle.server Subject: 30GB table: Hardware performance issue ? Date: 3 Mar 2003 12:45:14 -0800 Organization: http://groups.google.com/ Lines: 63 Message-ID: <412ebb69.0303031245.7c291172@posting.google.com> NNTP-Posting-Host: 199.22.61.2 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1046724315 22273 127.0.0.1 (3 Mar 2003 20:45:15 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 3 Mar 2003 20:45:15 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:178305 X-Received-Date: Mon, 03 Mar 2003 13:45:11 MST (news.easynews.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.