Note:224270.1 Trace Analyzer 11.2 June 15, 2008 by Carlos Sierra Analyzing SQL Traces with Binds and/or Waits generated by EVENT 10046 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This tool reads and parses one or several SQL traces generated by SQL Trace or by EVENT 10046 any level (1, 4, 8 or 12) and produces a comprehensive report with performance related details. Output report includes all the details found on TKPROF, plus additional information normally used for a process performance analysis. Report generated includes: response time components, db call summaries; list of top SQL according to response time; SQL genealogy with recursive dependencies; segment i/o wait summaries; hot blocks; gaps of no tracing activity; transaction summary; non-default initializatuon parameters; row source plans; explain plans; tables and indexes referenced by explain plans; indexed columns; CBO statistics for tables, indexes and their columns; bind variables used by relevant SQL; time analysis for SQL itself and their progeny; list and details of relevant executions for top SQL. Trace Analyzer connects to one database. For best report content install and use this tool in the same system/database where the trace was generated. If this is not possible, you can still use this tool in a similar system (Dev or Test) but please be aware that the content of some sections will not be accurate, for example: explain plans, i/o wait summaries, init.ora params, etc. Nevertheless, the content of the report can still be used for an initial performance assessment of the process traced. The rest of this document is divided into the following sections: 1. Installing the Trace Analyzer 2. Uninstalling the Trace Analyzer 3. Using the Trace Analyzer 4. Tool Configuration Parameters 5. References and feedback /********************************************************************/ 1. Installing the Trace Analyzer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Characteristics: o This TRCANLZR tool installs into its own schema TRCANLZR o It does not install any objects into application schemas o Its footprint is very small o The RDBMS version can be 9i, 10g or 11g o The OS can be UNIX, LINUX or WINDOWS o Open source (SQL and PL/SQL) o It can be installed in RAC systems o It installs by executing just one script tacreate.sql The set of installation scripts is driven by tacreate.sql, and it generates several objects into schema TRCANLZR: tables, indexes, sequences, views, grants and 6 packages. During the installation you will be prompted for the TRCANLZR user's password, as well as default and temporary tablespaces. The default tablespace will be used to create some TRCANLZR objects (such as tables and indexes). The temporary tablespace will be used for large objects and sort-type activities. You will be asked also for the host string to be used (if any). This string corresponds to the TNS alias. If you connect to that instance using a command like "connect appl/appl@PROD", the host string to be entered would be @PROD. If this host string is not used in your system, then leave blank when asked and just hit the enter key. TRCANLZR uses two server directories: one for input and one for output, although they can be the same. The input directory is from where the trace or traces are read, while the output directory is where the output report is written. The default for both input and output directories is UDUMP, but they can be redirected after installation and using provided scripts trca/install/tacdiri.sql and trca/install/tacdiro.sql for input/output respectively. Documentation for the tacreate.sql script follows. Please read it before executing the script. REM REM SCRIPT REM tacreate.sql REM REM DESCRIPTION REM This script installs the TRCANLZR tool into its own schema. REM REM PRE-REQUISITES REM 1. This script must be executed connected INTERNAL (SYS) as REM SYSDBA REM 2. During the installation you will be asked to enter the REM follwing: REM o TRCANLZR password - Required and it has no default REM o Optional Host String (TNS Alias) - Optional parameter REM to specify TNS alias connection including "@", for REM example @PROD. If not applicable, just hit enter when REM asked for this value. REM o TRCANLZR default tablespace - You will be presented REM with a list, then you will have to enter one tablespace REM name from that list REM o TRCANLZR temporary tablespace - Similar as above REM REM PARAMETERS REM 1. None inline. During the installation you will be asked for REM the values of the 4 parameters described under REM pre-requisites section above REM REM EXECUTION REM 1. Navigate to trca/install directory REM 2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA REM 3. Execute script tacreate.sql and respond to values requested REM REM EXAMPLE REM # cd trca/install REM # sqlplus /nolog REM SQL> connect / as sysdba REM SQL> start tacreate.sql REM REM NOTES REM 1. For possible errors see *.lis files REM 2. If you want to make all TRCANLZR staging tables permanent, REM remove from tacobj.sql all references to "GLOBAL TEMPORARY" REM and "ON COMMIT PRESERVE ROWS" before installing the tool. REM /********************************************************************/ 2. Uninstalling the Trace Analyzer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To uninstall TRCANLZR version 10.5 or higher, connect INTERNAL as SYSDBA i.e. as SYS, and execute tadrop.sql under trca/install directory. Documentation for the tadrop.sql script follows. Please read it before executing the script. REM REM SCRIPT REM tadrop.sql REM REM DESCRIPTION REM This script uninstalls a prior version of the TRCANLZR tool REM droping first existing TRCANLZR objects, then the TRCANLZR REM user itself. REM REM PRE-REQUISITES REM 1. This script must be executed connected INTERNAL (SYS) as REM SYSDBA REM REM PARAMETERS REM 1. None REM REM EXECUTION REM 1. Navigate to trca/install directory REM 2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA REM 3. Execute script tadrop.sql REM REM EXAMPLE REM # cd trca/install REM # sqlplus /nolog REM SQL> connect / as sysdba REM SQL> start tadrop.sql REM REM NOTES REM 1. This script is executed automatically by tacreate.sql REM /********************************************************************/ 3. Using the Trace Analyzer ~~~~~~~~~~~~~~~~~~~~~~~~ Before using this TRCANLZR be sure the installation completed successfully. The trca/run directory contains two scripts. Either one can be used to execute the Trace Analyzer over one or several files. The difference between these two execution scripts is that one of them copies the output report from the server directory where it was created, into the local SQL*Plus directories. It uses high level protocol SQL*Net to copy this output report, and depending on the latency of the network between the SQL*Plus client and the server, the time to copy this file can be long. Trace Analyzer takes one parameter, which is a filename that can be either a trace file, or a small control file with names of several files to be analyzed together. In either case, the input file must exist in the Trace Analyzer input directory at the server. This input directory defaults to UDUMP during the installation but it can be changed to any authorized server directory using provided script trca/install/tacdiri.sql. The output report is written to the Trace Analyzer output directory at the server. This directory also defaults to UDUMP during installation, but it can be changed to some other authorized server directory using trca/install/tacdiro.sql. Documentation of the main execution script follows. Please read it before executing the script. If you decide to use the version of the script that leaves the output report in the corresponding server directory without copying it into SQL*Plus local directory use then the other script. REM REM SCRIPT REM trcanlzr.sql REM REM DESCRIPTION REM This script parses a sql trace file generated by event 10046 REM levels 1, 4, 8 or 12. Then it produces a comprehensive report REM which includes top sql and sql genealogy according to the sql REM statements found in the trace provided. REM REM PRE-REQUISITES REM 1. Install Trace Anallyzer tool as per instructions.txt REM provided. REM 2. Trace file(s) should be located into the input directory, REM which defaults to UDUMP, but can be changed to any server REM directory using script trca/install/tacdiri.sql. REM 3. Execute on same system/database where the trace was produced REM and connect to SQL*Plus as the trace originating schema user REM REM PARAMETERS REM 1. Name of trace file to be analyzed (required) REM This file should be located in the input directory. REM REM EXECUTION REM 1. Navigate to trca/run directory REM 2. Start SQL*Plus connecting as application user that generated REM trace to be analyzed REM 3. Execute script trcanlzr.sql passing name of trace file or REM the name of the text file that contains multiple traces (one REM per line). REM 4. Provide all files generated by this tool to the requestor. REM REM EXAMPLES REM # cd sqlt/run REM # sqlplus [apps user]/[apps pwd] REM SQL> start trcanlzr.sql [name of your trace file] REM SQL> start trcanlzr.sql largesql.trc <== your trace file REM SQL> start trcanlzr.sql control_file.txt <== your text file REM REM NOTES REM 1. To maximize the value of the content of the Trace Analyzer REM report, the user executing this tool should be the same as REM the schema user that created the trace file. REM 2. Trace Analyzer should be executed on the same system and REM database where the trace was generated. Using the Trace REM Analyzer on a different system/database or connected as REM a user other than the one that produced the trace, still REM generates a comprehensive report, but some sections are REM rendered invalid or incomplete. REM 3. For possible errors see trcanlzr_error.log generated under REM the SQL*Plus default directory. REM 4. To analyze multiple files, create a text file that contains REM the names of the trace files to be analyzed, and provide REM this filename as the inline parameter to the Trace Analyzer. REM This small text file with names of traces (one per line), REM should also be located in the the input directory. REM 5. If you need the input directory be something else than UDUMP REM use then trca/install/tacdiri.sql connected as SYS. REM 6. Output server directory can be changed using provided script REM trca/install/tacdiro.sql. Default is UDUMP. REM 7. Generated files are copied from the server directory to the REM SQL*Plus default directory. This flie copy from server to REM local directory can be very slow. Use trcanlzrnc.sql instead REM of this trcanlzr.sql script to avoid this file copy. REM /********************************************************************/ 4. Tool Configuration Parameters ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO | PRO | TRCANLZR Configuration PRO | PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO | PRO | To set any parameter below, connect as any user and execute: PRO | SQL> EXEC trcanlzr.trca$g.set_param('Name', 'Value'); PRO | PRO | Top SQL Threshold (%) PRO | ~~~~~~~~~~~~~~~~~ PRO | Top SQL is identified by largest contribution to response time. PRO | Setting this parameter to 5 causes the Trace Analyzer to report PRO | as Top SQL those statements whose response time is >= 5% of the PRO | total trace response time. PRO | PRO | Name:top_sql_th Values:0-100 Default:5 (%) PRO | PRO | Top Execution Threshold (%) PRO | ~~~~~~~~~~~~~~~~~~~~~~~ PRO | Each execution of a SQL statement is measured in terms of PRO | response time, and compared to the total response time of the PRO | SQL statement. If the execution contributed to at least this PRO | parameter threshold value, the particular performance metrics PRO | of the execution are included in the report as well as its PRO | binds. This parameter is only used if the SQL has been flagged PRO | as "include details". PRO | PRO | Name:top_exec_th Values:0-100 Default:20 (%) PRO | PRO | Hot Block Threshold (blocks) PRO | ~~~~~~~~~~~~~~~~~~~ PRO | This paremeter controls up to how many blocks are displayed as PRO | hot because of aggregate wait time or times waited on. It PRO | requires the trace to be level 8 or 12. To include the name of PRO | the segment, the Trace Analyzer must be executed on same system PRO | where the trace was generated. PRO | PRO | Name:hot_block_th Values:0-100 Default:5 (blocks) PRO | PRO | Aggregate (flag) PRO | ~~~~~~~~~ PRO | If the same SQL is parsed multiple times within trace file this PRO | parameter specifies if SQL should be reported as one or many. PRO | PRO | Name:aggregate Values:Y/N Default:Y (flag) PRO | PRO | Perform COUNT(*) (flag) PRO | ~~~~~~~~~~~~~~~~ PRO | Section "Tables" of report includes actual COUNT(*) of all PRO | Tables accessed by SQL statement. This parameter enables or PRO | disables display of COUNT(*). PRO | PRO | Name:perform_count_star Values:Y/N Default:Y (flag) PRO | PRO | COUNT(*) Threshold (rows) PRO | ~~~~~~~~~~~~~~~~~~ PRO | COUNT(*) is performed in all tables referenced by the row PRO | source plan or the explain plan, up to this threshold limit. PRO | PRO | Name:count_star_th Values:0-999999999 Default:1000000 (rows) PRO | PRO | Errors Threshold (count) PRO | ~~~~~~~~~~~~~~~~ PRO | In case there were ORA errors in trace, this parameter limits PRO | the number of errors reported. PRO | PRO | Name:errors_th Values:0-1000 Default:100 (count) PRO | PRO | Gaps Threshold (count) PRO | ~~~~~~~~~~~~~~ PRO | In case there were gaps of no tracing activity, this parameter PRO | limits the number of gaps reported. PRO | PRO | Name:gaps_th Values:0-1000 Default:100 (count) PRO | PRO | Include Internal SQL (flag) PRO | ~~~~~~~~~~~~~~~~~~~~ PRO | SQL parsed as SYS is usually included in the output report. PRO | This paremeter can be used to hide internal SQL and reduce ths PRO | size of the output report. PRO | PRO | Name:include_internal_sql Values:Y/N Default:Y (flag) PRO | PRO | Include non-Top SQL (flag) PRO | ~~~~~~~~~~~~~~~~~~~ PRO | All SQL found in trace is usually included in output report. PRO | This paremeter allows to reduce the size of the output report PRO | and include only the Top SQL. PRO | PRO | Name:include_non_top_sql Values:Y/N Default:Y (flag) PRO | PRO | Include Initialization Params (flag) PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO | Only relevant if Trace Analyzer is executed on same system PRO | where the trace was generated. This parameter allows the PRO | inclusion of init.ora parameters that have non-default values. PRO | PRO | Name:include_init_ora Values:Y/N Default:Y (flag) PRO | PRO | Include Waits (flag) PRO | ~~~~~~~~~~~~~ PRO | If trace includes waits (level 8 or 12), this paremeter allows PRO | to exclude them from the output report. PRO | PRO | Name:include_waits Values:Y/N Default:Y (flag) PRO | PRO | Include Bind Variables (flag) PRO | ~~~~~~~~~~~~~~~~~~~~~~ PRO | If trace includes binds (level 4 or 12), this paremeter allows PRO | to exclude them from the output report. PRO | PRO | Name:include_binds Values:Y/N Default:Y (flag) PRO | PRO | Include Explain Plans (flag) PRO | ~~~~~~~~~~~~~~~~~~~~~ PRO | For those SQL statements flagged as "include details", this PRO | parameter allows to generate an explain plan at the time the PRO | Trace Analyzer is executed. It requires to be connected to the PRO | same system where the trace was generated. PRO | PRO | Name:include_expl_plans Values:Y/N Default:Y (flag) PRO | PRO | Include Segments (flag) PRO | ~~~~~~~~~~~~~~~~ PRO | Segments are reported from two sources: waits and explain plans PRO | This paremeter allows to disable the display of segments. PRO | PRO | Name:include_segments Values:Y/N Default:Y (flag) PRO | PRO | Detail non-top SQL (flag) PRO | ~~~~~~~~~~~~~~~~~~ PRO | Details include performance metrics of progeny sql, explain PRO | plan, tables and indexes refernced by explain plan, segment i/o PRO | summary and relevant executions for a given SQL. These details PRO | are usually interesting only for Top SQL. This parameter allows PRO | to enable all these details for all SQL, not only for Top SQL. PRO | PRO | Name:detail_non_top_sql Values:N/Y Default:N (flag) PRO | PRO | Time Granularity (factor) PRO | ~~~~~~~~~~~~~~~~ PRO | Time reported in traces is in microseconds (0.0000001 of a sec) PRO | This parameter represents how many units are there in a second. PRO | Before 9i, time was reported as centiseconds (0.01 of a sec). PRO | Thus, to analyze traces prior to 9i this parameter must be set PRO | to 100 instead of the default value of 1000000. PRO | PRO | Name:time_granularity Values:1000000/100 Default:1000000 PRO | PRO | Wait Time Threshold (secs) PRO | ~~~~~~~~~~~~~~~~~~~ PRO | Wait summaries are inlcuded in output report. This parameter PRO | allows to report in these sections only those aggregate wait PRO | times whose value exceeds the threshold specified. PRO | It is used in "Segment I/O Wait Summary" lists. PRO | PRO | Name:wait_time_th Values:0.0001-1.0 Default:0.01 (secs) PRO | PRO | Response Time Threshold (secs) PRO | ~~~~~~~~~~~~~~~~~~~~~~~ PRO | Some sections of the report can become large due to SQL with PRO | insignificant response time. This parameter allows to weed out PRO | such irrelevant SQL from cluttering the lists reported. PRO | It is used for the "Non-Recursive SQL (depth = 0)" list. PRO | PRO | Name:response_time_th Values:0.001-10.0 Default:0.1 (secs) PRO | PRO | Trace File Min Size in Bytes PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO | If the input file is smaller than this threshold, its content PRO | is treated as a control file that contains a list of trace PRO | files to be analyzed (one trace filename per line). PRO | PRO | Name:trace_file_min_size_bytes Values:0-99999 Default:9999 PRO | PRO | Trace File Max Size in Bytes PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO | Trace Analyzer does not impose an upper limit in the size of PRO | traces to be analyzed. Although it is constrained by lower PRO | level code (DBMS_LOB). Most platforms and RDBMS releases do PRO | fine with files of several gigabytes. This parameter allows to PRO | specify an upper limit to Trace Analyzer. Files larger than PRO | this threshold are parsed and analyzed up to the threshold. PRO | PRO | Name:trace_file_max_size_bytes Values:99999-999999999999 PRO | Default:9999999999 (almost 10GB) PRO | PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO /********************************************************************/ 5. References and feedback ~~~~~~~~~~~~~~~~~~~~~~~ References o SQLTXPLAIN Enhanced Explain Plan and related diagnostic information for one SQL Note: 215187.1 o Implementing and Using the PL/SQL Profiler Note: 243755.1 Feedback o Contact author by email: carlos.sierra@oracle.com /********************************************************************/