Re: Tuning Question

Hello RC,

  Please, check "where" clause of the most frequently executed SQL and make sure you have indexes that satisfy your "where" clause. Use SQL "explain plan" to verify.

  Use of the partitions would help optimizer to choose only needed partitions, but only in case if "where" clause of the SQL includes full or partial partition keys.

    Generally it is best to partition both table and corresponding indexes (as local).

    If you can not allocate maintenance window to partition table and indexes then partition index only can be created online with no downtime.

     Please, let us know if you have any additional questions on this topic.

Regards,

    Support
    DBA Infopower
    http://www.dbainfopower.com

<rc_at_die@you@!spammers.sandworm.demon.co.uk> wrote in message news:cvl520lho3jb4nfdutl3hlf53um43nfojj_at_4ax.com...
> 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 Fri Feb 06 2004 - 01:22:27 CST