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

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with a PeopleSoft Database

Re: Performance problem with a PeopleSoft Database

From: Bob Fazio <rfazio_at_home.com.nospam>
Date: 2000/06/07
Message-ID: <nmx%4.238565$Tn4.2156913@news1.rdc2.pa.home.com>#1/1

David,

Thanks for the response, but I am aware of all you mentioned, and did look at those first. The problem has apparently been solved though. Last night while going through metalink, thats the only time I can actually get in anymore, I found an article talking about issues with excessive times on "db file sequential read". The article mentioned that this can be caused by high fragmention in the Library Cache. So at noon today, I increased my SGA to 4x it's current size and bounced the database. The 17+ hour run, was re-run in only 30 minutes. I need to look into exactly what really needs adjusted, but it definately appears that the problem was fragmentation

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
<ddf_dba_at_my-deja.com> wrote in message news:8hlrc9$5hp$1_at_nnrp1.deja.com...

> Robert,
>
> Without output from 'explain plan' this is difficult to diagnose. If my
> memory serves me correctly if PLAN_TABLE is present then tkprof will
> generate 'explain plan' output for each SQL statement in addition to the
> normal tkprof output. Run utlxplan.sql then run tkprof again on the
> same trace file -- you should see query plans that can assist in
> narrowing down the causes for your delays.
>
> I agree that the update statements, taking from around 4 to around 5
> seconds each, add up quite rapidly. The 'explain plan' output on these
> statements should help you decide on the proper course of action.
>
> One item I feel should mention is that these update statments are not
> reusable, i.e., there are no bind variables in any of them. This will
> cause each statement to load into the shared SQL area and result in
> additional parsing overhead for the database. [Usage of bind
> variables can reduce loading and parsing overhead as the statement
> is already loaded and parsed by Oracle. Value substitution is far
> less expensive than loading and parsing individual SQL statements.]
> This may be part of the problem, and, yes, I realize that this may not
> be a correctable area. It is one area to examine, though.
>
> Sorry I cannot be of more assistance.
>
>
> David Fitzjarrell
> Oracle DBA
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jun 07 2000 - 00:00:00 CDT

Original text of this message

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