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
[EMAIL PROTECTED]
(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:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> 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
> [EMAIL PROTECTED]
> (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
