Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newshosting.com!news-xfer2.atl.newshosting.com!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-post-02!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: "Sybrand Bakker" Newsgroups: comp.databases.oracle.server Subject: Re: 30GB table: Hardware performance issue ? Date: Mon, 3 Mar 2003 22:28:04 +0100 Organization: Posted via Supernews, http://www.supernews.com Message-ID: Reply-To: "Sybrand Bakker" References: <412ebb69.0303031245.7c291172@posting.google.com> X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Complaints-To: abuse@supernews.com Lines: 93 Xref: newsfeed1.easynews.com comp.databases.oracle.server:178314 X-Received-Date: Mon, 03 Mar 2003 14:33:52 MST (news.easynews.com) "Andras Kovacs" wrote in message news: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. Without information about - indexes on the table - presence of histograms - cardinality - db_block_buffers you basically state: 'It doesn't work' and my immediate question is: Why isn't the query using an unique index? and why don't you use between and do you have contradicting conditions on the timestamp column. So: you need to provide more info Regards -- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail address