Re: waits on sequential scans - how did i solve it

I don't think, 7.3.2 supports Index-Organized tables.

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, July 24, 2001 10:36 AM

> Rahul,
>
> If I'd known that there were only 8 columns in the table, I'd have
included
> the recommendation to investigate an Index-Organized Table. That is where
> the table IS the index and the index IS the table. This saves disc space
> and cuts I/O in half for DML on the table, since a separate index is not
> maintained. Check out the docs on IOTs.
>
> 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 24, 2001 8:53 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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: Igor Neyman
  INET: ineyman_at_perceptron.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).