| 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: db file sequential read
if the waits you are reporting are for physical file reads,
and if you have constructed your disk farm such that it has "stripe sets"
that make up the files
underlying the tablespaces
and if you have at least three "stripe sets" that are independent in operation,
then put the table in a tablespace constructed on a file on "stripe set 1",
put the primary index ..... on "stripe set 2", and put the unique index ..... on "stripe set 3"
Then measure and see if it is faster.
Before you embark no this, make sure the service times you're seeing as the
bottleneck are something
on the order of your physical disk speed, not the cache speed, since you're
unlikely to get faster
than disk cache speed with separate physical threads, and you're not
overruning cache if you're getting
service times faster than the physical disk speed.
If it does get faster, then your bottleneck actually was physical disk
access, and you may or may not
have opportunities for additional speed up by constructing "stripe sets" on
any remaining bottleneck
on faster media, or fine tuning such things I've left out of this somewhat
simple minded layout as
where the rest of the indexes are, where the redo logs are, where undo is,
where control files are, and
other competitive load on your system.
Further, in the construction of "stripe sets" folks far too often start each
volume on the same disk.
To put it simply, if you have 8 drives, mirrored, so logically you have 4
drives, and you carve them
up into 4 volumes, start 1 on disk 1, 1 on disk 2, 1 on disk 3, and 1 on
disk 4.
All of this assumes you've got physical file read waits to get your db file scattered read times.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of DENNIS WILLIAMS
Sent: Monday, June 21, 2004 12:38 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: db file sequential read
Harvinder
Cache the index? Since Oracle must read the index to verify the uniqueness constraint you've created, and then to insert the new values in the indexes, I'm out of ideas here.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Harvinder Singh
Sent: Monday, June 21, 2004 11:37 AM
To: oracle-l_at_freelists.org
Subject: RE: db file sequential read
Dennis,
This option is not possible as we are just prototyping a particular part of application. When insers are going on other sessions can read data from this table and require indexes..we just need to tune inserts with indexes
Thanks
--Harvinder
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DENNIS WILLIAMS
Sent: Monday, June 21, 2004 12:31 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: db file sequential read
Harvinder
What about dropping the indexes before inserting and recreating them afterward? This is a standard technique for increasing insert performance.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Harvinder Singh
Sent: Monday, June 21, 2004 9:45 AM
To: oracle-l_at_freelists.org
Subject: db file sequential read
Hi,
During testing the insert performance in oracle 10g on win2k, we see the wait event very high for the db file sequential read. With trace 10046,8 I can see that all the blocks are in file that contains primary and unique index for the table. How can we reduce the above waits on these indexes. Do freelist and initrans parameter still valid for 10g and what should be the reasonable value. We are inserting 1M records 1000 at a time by selecting from other table. Also if I try to insert from 2 session simultaneously insert become more slow.
Thanks
--Harvinder
=3D20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |