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”.
“1984” is the recommended value when tracing with “TracePC”.
Utility for analyzing *.tracesql trace files
TraceMagic is a utility that gives PeopleSoft system administrators, programmers and support engineers the ability to quickly isolate performance bottlenecks in SQL Statements and/or PeopleCode functions. It accomplishes this by turning the text-based, time-ordered tracesql file into a sortable-grid display, allowing the user to quickly locate system performance issues.
You may download and read more at My Oracle Support [ID 1470578.1] and Source Code available at [1471144.1], It require .Net Framework 4.0 to run
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
EXECUTE IMMEDIATE
'ALTER SESSION SET MAX_DUMP_FILE_SIZE =
UNLIMITED';
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE
'ALTER SESSION SET TRACEFILE_IDENTIFIER =
''INS6000''';
EXECUTE IMMEDIATE
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
sys=no explain=/
sort=exeela,fchela,prscpu,execpu,fchcpu
