Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 30GB table: Hardware performance issue ?
You state 8.1.7 - but I understand that there
were some significant changes between
8.1.7.0 and 8.1.7.4 in what Oracle would do
with partition optimisation, so the last digit
makes a difference. Make sure you check
into the execution path in more detail -
in particular the partition start/stop columns
in case your version of Oracle is not (for some
bizarre reason) doing the elimination it claims -
and therefore loading and flushing buffers from
redundant partitions.
When you say 23% selectivity - how are you getting this figure ?
How many rows per partition, and how many partitions in the table, and what is the typical size of a partition, and is a typical query supposed to hit just one (or perhaps two) partitions ?
Based on 10% CPU, 90% disk, I am inclined to agree with you that you are I/O bound. If you are typically selecting 11,000 rows from a very large table, and the rows are extremely scattered, then you are inevitably going to do a lot of I/O on the first execution of a query unless you can cache a very large fraction of the (30GB) table. The second execution will then only be much quicker if there has been insufficient time for other users to do their 11,000 row/block queries in the interim.
Some damage-limitation ideas which may
help.
Option 1
Option 2 (not to be used with option 1)
Option 3 (basic infrastructure)
-- 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.0303031948.8c7aee7_at_posting.google.com...Received on Tue Mar 04 2003 - 02:56:21 CST
> We are running on Oracle Entreprise Edition 8.1.7.
> The index is IDX_HITORY(TimeStamp DATE, ID NUMBER) and it has 23%
> selectivity.
>
> I have maybe included a bad exemple in this post. The IN clause
> doesn't cause much problem. Oracle handles it very well. Any query
> that runs on this HISTORY table and uses the Index, returns 11000
rows
> in about 27-33 seconds. Actually the performance of the read depends
> on the number of physical read.
>
> The CPU usage is OK 10% however the Raid 1 is running 90%.
>
> I think that we are IO bound.