| 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 resizing/moving datafiles for perform
The assumption here is that you are competing
for a block on disk. That may not be the problem.
DB FILE SEQUENTIAL READ indicated your session is
waiting while a sequential read from the database
is being done.
Check the V$filestat to see if full scans are happening on that
file. Check to see if there is a missing index on the
SQL that is executing those scans. Check for SQL that
is executed frequently and/or in batch jobs at the times
you are seeing the slowdown. Once you have identified
the culprit , tune that SQL or schedule it accordingly in
batch jobs. Many logical I/Os going on here competing
for resources at the same time.
Note that moving a physical datafile and/or or splitting it up into smaller physical files is probbaly not the answer to your problems.
To find that bad SQL go through your 10046 event data and the wait interface data as you have started at the session level.
Many good books and papers on how to do that. Start with Kirti's and Gaja's book .. Oracle Performance Tuning 101.
FWIW. Mike
-----Original Message-----
Sent: Friday, July 19, 2002 1:58 AM
To: Multiple recipients of list ORACLE-L
Guys,
the DB is on 8.1.6 / Win2K.
there entire DB ( 33 tables and 110 indexes) is in USERS
tablespace.
the USERS tablespace has only one datafile USERS.DAT of size
12GB.
it's on 8 * 10 GB HDD with RAID 5.
we are facing performance bottleneck now.
V$SESSION_WAIT shows many rows for DB FILE SCATTERED READ and DB
FILE SEQUENTIAL READ.
all these events wait for USERS.DAT to be read.
it's a highly read OLTP site.
So,will moving the table and index data to different tablespaces
with relatively smaller datafiles ( say 1 GB ) improve performance
?
OR
since it is on RAID5 , will moving make any difference ?
10-20 MB of data gets added to the DB daily. if resizing/moving improves performance,what should be the size of my datafile ?
kindly give me a solution guys.
TIA.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: oraora oraora
INET: oraoraora_at_rediffmail.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).
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 Fri Jul 19 2002 - 13:23:26 CDT
 |
 |