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: High db file sequential reads

RE: High db file sequential reads

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 20 Jun 2005 16:36:30 -0700
Message-ID: <960A9B249C99994AA3C7FCA016A0DA8902AA0A58@ussccem08.corp.hds.com>


Hi Bambi,

Long time no hear! First things first - this list don't work off percentages and ratios! Having said that, you haven't stated a few things: The time period for the snapshot - this is important, as the DB consumed 2792+1284 seconds ~= 67 mins. Second: You din't mention DB version, but I am assuming 9.2.x since this mentions "CPU Time" and "Top 5 Timed Event".

As for Oracle Financials DB tuning, I would just concentrate on the Top slow running *business* events such as Pack/Print Slip printing, Order scheduling, ATP, etc - if they run slow, then you are probably not getting products out the door quick enough.. If they are forms based, then you have the opportunity of generating 10046 level 12 trace using the Debug -> Trace with Binds and Waits, and then applying the Cary technique on the traces ;-) If Conc. Managers, then you will have to use Init SQL profiles or use CM trace to generate/fix problems....

It is quite difficult working off summaries such as this, esp on Apps 11i databases... Having said that, it does seem that you have a lot of FTSs... I would check whether you have had the right dose of Workflow and other purges, and if there was, how you are comparing on HWM/Empty blocks.

Hth,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)  

http://tahiti.oracle.com - Manuals for DBAs (English only) http://www.bibleserver.com - Manual for Life (in English, Deutsch, French, Italian, Spanish, Portugese, Turkish,...)

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bellows, Bambi
Sent: Monday, June 13, 2005 12:19 PM
To: oracle-l_at_freelists.org
Subject: High db file sequential reads

Hi Everybody!  

I've been administering a system lately that's used almost exclusively for Oracle Financials and one of the things that Oracle Financials does every well is hide what it's doing. Oh, sure, you can poke around in v$sql but that doesn't really do much for you. So I've been rather dependent on statspack (lovely thing) and from there I can see what's eating up time and analyze the structure and, as needed, apply indexes to the tables. There's not really much more you can do than that as you have no access to the underlying queries, and, even if you did, you sure couldn't change anything, database parameters aside, of course. That being said, these percentages seem out of whack to me... what do you think?  

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
db file sequential read                           438,536       2,792
65.14
CPU time                                                        1,284
29.96
db file scattered read                             32,239         165
3.84
log file parallel write                            12,667          14

.32
SQL*Net break/reset to client 1,384 9
.20
-------------------------------------------------------------
^LWait Events for DB: PFIN Instance: PFIN Snaps: 3962 -3963 Has anyone successfully gotten db file sequential reads into any kind of sane range for any period of time?  

Thanks folks!
Bambi.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 20 2005 - 19:58:04 CDT

Original text of this message

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