- Application Engine Step and SQL trace
- Application Engine Statement Timings trace
- PeopleCode Detail Timings trace
- Database Optimizer trace
"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