Re: 30GB table: Hardware performance issue ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 4 Mar 2003 08:56:21 -0000
Message-ID: <b41q0j$3l2$1$8302bc10@news.demon.co.uk>

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

Switch to Index Organized Tables - then you won't have to do the extra table accesses to find the rows.

Option 2 (not to be used with option 1)

Can you fit the entire index into the cache ? If so, perhaps you will get some benefits from putting the index into a KEEP pool
so that no index blocks get flushed, and putting the table into the RECYCLE pool
so that the very random access to the
table does not cause flushing problems
elsewhere.

Option 3 (basic infrastructure)

Are you using a buffered file-system -
if so, make sure you bypass it. Use the machine memory that is currently buffering the file-system to extent the Oracle buffer. Oracle knows more about what needs to
be buffered.

--
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...


> 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.

Received on Tue Mar 04 2003 - 02:56:21 CST