Sunday, February 22, 2009

%UpdateStats( ) and PLAN_TABLE

We all are well aware of the 'power' of Application Engine Trace. No matter its a simple or most difficult AE program, (most of the time) we put a Trace parameter to get one or more out of the followings :
  • Application Engine Step and SQL trace
  • Application Engine Statement Timings trace
  • PeopleCode Detail Timings trace
  • Database Optimizer trace
Recently, I was working on a custom AE program with over 100 sql actions. Temp Tables, Meta SQL, HINTS, %UpdateStats() and other standard approaches were followed to enhance the performance. During the course of fine-tuning, as I wanted to get most out the trace functionality, I put a trace value - TRACE 4351. To my surprise, the process went to no success. The error log read:
"SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-12899: value too large for column "SYSADM"."PLAN_TABLE"."STATEMENT_ID" (actual: 31, maximum: 30) "

What went wrong ? After an hour of analysis my conclusion was:

a) Trace Value 4096( which I was using)—Initiates the database optimizer "explain", storing the results in the Explain Plan Table of the current database. I was on Oracle, so it was PLAN_TABLE.
b) When outputting to a table, PeopleSoft updates the trace rows as follows:
* EXPLAIN PLAN SET STATEMENT_ID: PeopleSoft updates the STATEMENT ID column:
EXPLAIN PLAN SET STATEMENT_ID = ApplId.Section.Step.Type FOR sqlstmt
* PLAN_TABLE’s REMARKS column: PeopleSoft updates the REMARKS column:
PLAN_TABLE's REMARKS column = 'ProcessInstance-RunControlId(QueryNo)'
queryno is a count of how many SQL statements have been traced up to a particular point.
c) In PLAN_TABLE, the field STATEMENT_ID is VARCHAR(30). One can see the table schema or refer Oracle Documentation.
d) PeopleSoft recommends - To output the trace to a table on Oracle, (i) a PLAN_TABLE table must exist, and (ii) the STATEMENT_ID must be of type VarChar2(254), instead of VarChar2(30).

Hence the Problem !

No comments:

Post a Comment