Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem with a PeopleSoft Database
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...Received on Wed Jun 07 2000 - 00:00:00 CDT
> 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.