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: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Mon, 13 Jun 2005 16:00:39 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B9444651@wafedixm10.corp.weyer.pri>

That's great advice. My 2 cents would be focus on the APPLICATION level response time first.

Many times, I've looked at the SQL statements only to discover that it was either a) an application
algorithm issue or b) a coding issue.=20

Case in point, Application runs slow -- hmm it seems to be doing a lot of reads. Let's try to tune the SQL (cause that's what we've been told, tune SQL). Well it turns out the SQL runs in an acceptable time, the Application was so poorly written that it was looping and executing the SQL request in each iteration of the loop...So each iteration of the SQL Statement ran in an acceptable time but when aggregated together the response time was unacceptable...

If we analyzed that we would have seen a significant portion of the Application's total run time spent at the DB level...BUT you ALWAYS have to ask yourself WHY? Could it be the Application's fault?

I'm not saying there is no need for SQL Tuning, just saying don't focus too much on 1 side of the equation...

Thanks!=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink Sent: Monday, June 13, 2005 3:37 PM
To: bbellows_at_usg.com
Cc: oracle-l_at_freelists.org
Subject: Re: High db file sequential reads

Bambi,

The first question to ask is "What is the impact to the business of=20 these events?" (Are users complaining about poor performance? Do=20 reports/jobs take too long to run?) When talking with the users, you=20 will probably find that they have no idea what these events mean (nor=20 should they). They will probably tell you that FormA or Job1 takes too=20 long to run. Focus on that. You might find out that the truly important=20 business processes are unimpacted by these events.

In terms of Oracle Financials, what is hidden can be revealed. Extended=20 sql_trace can be enabled and all of the calls and events unveiled. Once=20 you know the specifics of the problem, contact OWS and log a tar. It may

be a case of a known performance issue with an object (table/view) or=20 module and they can supply a patch to resolve the issue.

Regards,
Dan Fink

Bellows, Bambi wrote:

>Hi Everybody!
>=20
>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? =20
>=20
>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?
>=20
>Thanks folks!
>Bambi.
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
> =20
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 13 2005 - 19:05:47 CDT

Original text of this message

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