Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!border3.nntp.aus1.giganews.com!nntp.giganews.com!central.cox.net!cox.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: 30GB table: Hardware performance issue ?
Date: Mon, 3 Mar 2003 21:44:01 -0000
Lines: 118
Message-ID: <b40ic5$5ul$1$8302bc10@news.demon.co.uk>
References: <412ebb69.0303031245.7c291172@posting.google.com>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-Trace: news.demon.co.uk 1046727877 6101 158.152.75.41 (3 Mar 2003 21:44:37 GMT)
X-Complaints-To: abuse@demon.net
NNTP-Posting-Date: Mon, 3 Mar 2003 21:44:37 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MSMail-Priority: Normal
Xref: newsfeed1.easynews.com comp.databases.oracle.server:178318
X-Received-Date: Mon, 03 Mar 2003 14:44:30 MST (news.easynews.com)



I managed to send the last post before
I had written the last paragraph, which was:


Have you checked to see if the change in
time is due simply to a reduction in the
physical I/O without any change in logical
I/O ?


--
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@yahoo.com> 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.