waits on sequential scans - how did i solve it

list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed
range scan..

i also put the indexes on raw devices.

regards

> ----------
> From: Jack C. Applewhite[SMTP:japplewhite_at_inetprofit.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Tuesday, July 17, 2001 8:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: how to improve sequential scans ?
>
> Rahul,
>
> Could this table be partitioned and the partitions spread across multiple
> disks? Could the index be partitioned as well? The concept here is, of
> course, "divide and conquer".
>
> Could a column or two (or three) be added to the index to satisfy the
> query
> without having to hit the table?
>
> How frequently are these literal queries being issued? Are they shredding
> your shared pool and chewing up CPU by making Oracle do extra work in
> shared
> pool memory management?
>
> Are there aggregation (vs aggravation <g>) functions or order bys in the
> queries that might be causing sorts to disk? If aggregation, could you
> use
> materialized views to satisfy the queries?
>
> ...just a few ideas.
>
> Jack
>
> --------------------------------
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com
> japplewhite_at_inetprofit.com
> (512)327-9068
>
>
> -----Original Message-----
> Sent: Tuesday, July 17, 2001 7:36 AM
> To: Multiple recipients of list ORACLE-L
>
>
> list (AIX, 7.3.2)
>
> 5 clients are shooting the same sql to read data from an 18 million rows
> table.
> each time the sql uses a different literal value in the where clause... no
> bind variables.
>
> I CANNOT TOUCH THE APPLICATION, and have been given the task to
> re-configure
> the
> DB to increase performance.
>
> i have moved the table and it;s associated index to separate disks. and
> iostat show that
> only that only those two disks are being read.
>
> the session wait show that all the times the sessions are waiting on
> "db file sequential read"
>
> the db file being sequentially read in the above sessiion is the TABLE
> from
> which
> all the sid's are reading
>
> the table is analyzed and the sql's issued use the index.
>
> how can i further tune this config. ?
>
> TIA
>
> Rahul
>
> PS: my next step is to put the files on raw disks.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jack C. Applewhite
> INET: japplewhite_at_inetprofit.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  INET: rahul_at_ratelindo.co.id

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).