Tuning Question

Hi

We run Oracle 8.1.7.4 on Solaris 8. The main table contains approx 6 million rows and is approx 2.2gb in size . The CBO is set to first_rows

Most of the queries generated seem to use index range scan and table access by rowid. I am lead to believe that this can be quite inefficient for large tables such as ours! (I am no oracle expert) I am also seeing large disk I/O with wait time of around 70%

My question is,

 if I were to partition the index only (if that is possible) would that help the index range scan and reduce disk I/O ?

OR

Partition the table and use PQ to do full table scan by adjusting the index adj % and multi block read count? I think most of the data that get queried is around about 1 million rows

OR

A combinantion of 1 and 2 (Partition index and table) without forceing oracle to do a tablescan?

Unfortunately I have no control over the SQL as it is application generated.

Any suggestion would be help full,

R   Received on Thu Feb 05 2004 - 18:16:58 CST