Re: Performance problem with a PeopleSoft Database

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