Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB FILE SEQUENTIAL READ resizing/moving datafiles for perform

RE: DB FILE SEQUENTIAL READ resizing/moving datafiles for perform

From: Johnson, Michael <Michael.Johnson_at_oln-afmc.af.mil>
Date: Fri, 19 Jul 2002 10:23:26 -0800
Message-ID: <F001.0049CD7F.20020719102326@fatcity.com>


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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Johnson, Michael
  INET: Michael.Johnson_at_oln-afmc.af.mil
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US