Monday, October 5, 2009

Tracing



Online Process
Set the following in psappsrv.cfg, or for a single user session in PIA using trace=y 
"31" is the recommended value when tracing with "TraceSQL".
"1984" is the recommended value when tracing with "TracePC". 

Batch Process
Application Engine

"135" is the recommended value when tracing with "TraceAE" 
if peoplecode tracing is needed, please also set 
TraceSQL= 31 
TracePC = 1984 
You can also do this in process definition of Application Engine to affect this for only 1 program. 
Override options : Append : -TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 1984 
To see which processes are set with this options, run this sql. 
SELECT PRCSNAME, PARMLIST FROM PS_PRCSDEFN WHERE UPPER(PARMLIST) LIKE '%TRACE%' AND PRCSTYPE = 'Application Engine'; 
To generate Database Level Trace, Use TraceAE=2183. This will create file within the "UDUMP" directory on the database server.
It will contain details of each SQL statement that was executed on the database including its runtime execution plan. You can then use this as a input to tkprof for generating formatted trace report. However this will not capture bind variables. To capture bind variables, following trigger is needed.
CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_POCALC 
BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST 
FOR EACH ROW 
WHEN ( NEW.runstatus = 7 
AND OLD.runstatus != 7 
AND NEW.prcstype = 'Application Engine' 
AND NEW.prcsname = 'PO_PO_CALC' 
BEGIN 
EXECUTE IMMEDIATE 
'ALTER SESSION SET TIMED_STATISTICS = TRUE'; 
EXECUTE IMMEDIATE 
'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED'; 
EXECUTE IMMEDIATE 
'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''POCALC'''; 
EXECUTE IMMEDIATE 
'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''; 
END; 

Cobol Process
TraceSQL=128

SQR Process
A database level trigger is the only way for generating SQR trace.
Sample trigger script.
CREATE OR REPLACE TRIGGER MYDB.SET_TRACE_INS6000 
BEFORE UPDATE OF RUNSTATUS ON MYDB.PSPRCSRQST 
FOR EACH ROW 
WHEN ( NEW.runstatus = 7 
AND OLD.runstatus != 7 
AND NEW.prcstype = 'SQR REPORT' 
AND NEW.prcsname = 'INS6000' 
BEGIN 
EXECUTE IMMEDIATE 
'ALTER SESSION SET TIMED_STATISTICS = TRUE';
EXECUTE IMMEDIATE 
'ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED';
EXECUTE IMMEDIATE 
'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''INS6000''';
EXECUTE IMMEDIATE 
'ALTER SESSION SET EVENTS = ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''; 
END; 
Once the raw database trace is captured, execute the program "tkprof" with following sort options: 
tkprof <trace_input_file> <rpt_output_file> sys=no explain=<user_id>/<password> sort=exeela,fchela,prscpu,execpu,fchcpu 
For more information, Read peoplesoft resolution 201049233: E-ORACLE:10g Master Performance Solution for Oracle 10g and download the red paper attached to it. 

0 comments:

Post a Comment

Total Pageviews

Copyright © Tech & Management Notes