| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: waits on sequential scans - how did i solve it
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
-----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).Received on Tue Jul 24 2001 - 08:20:44 CDT
 |
 |