Oracle PeopleSoft SQL Performance Tuning

PeopleSoft Trace Settings

Update: I have changed this post based on Graham’s recent comments regarding Trace Magic since the trace settings that I recommended were not actually compatible with Trace Magic. Please see the following link for information regarding Trace Magic:

https://communities.oracle.com/portal/server.pt?open=514&objID=224&mode=2&threadid=382561

However, when manually reading a trace file (e.g. in Sublime Text, Notepad++, etc.) I would recommend the following trace settings:

-TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 4044

I still find these settings useful for non-performance related debugging issues.

2 thoughts on “PeopleSoft Trace Settings”

  1. Hi,
    I’m Graham Simpson, the author of TraceMagic. tracesql files contain many points where “Dur=” can be seen in the raw file, these are timers explicitly set during code execution and part of the PeopleTools code. In the case of nested PeopleCode calls, These reports are aggregated and separated by TraceMagic to provide real insight as to how long a given piece of PeopleCode took. The numbers are accurate. In cases such as internal calls the trace file contains no explicit timer information set for these “one-liners” thus TraceMagic uses Dur=~0.000001 showing the approximate duration of these calls, again the reports are accurate to sub-millisecond levels. As for SQL statements The time to acquire the connection, parse the sql, name and fill bind variables, execute, fetch the data, and destroy the connection are all rolled up to individual SQL statements, which are in turn rolled up on the statistics tab for multiple executions of the same SQL. Lastly, in the newest version of TraceMagic (released last week) Un-Traced time is reported which can provide even deeper insight into performance issues. I suggest visiting the Oracle Community thread for Trace Magic for a full explanation. https://communities.oracle.com/portal/server.pt?open=514&objID=224&mode=2&threadid=382561

    As the author of the tool, I can assure you the flags you should be using when you want to examine your files in TraceMagic are clearly shown in the Community, the KB and even the tool itself (with the latest version) I do not recommend other flags – unless you’re prepared to examine your files in notepad.

    Thanks for Reading & Happy Tracing!
    Graham

    1. Hi Graham

      I just wanted to know how the sql id is showing in trace magic tool,because we are providing only the tracefile ,but that file does not contain any sql id.Ana also trace magic is not even connected to DB.From where it fetches the sql_id?

Leave a Reply to Velu Cancel reply

Your email address will not be published. Required fields are marked *

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.