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

Jack,

I was going to suggest, what you did, but then I noticed the version Rahul was using.

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 5:58 PM

> Igor,
>
> How right you are! I answered without looking back at Rahul's original
> message.
>
> 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 10:26 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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).