Support Tools for Performance Tuning - Released!

Performance Tuning Service
Requests are some of the longest running and most complex issues that customers
log with Oracle Support.  They have hundreds of integration points, they
are iterative in nature and troubleshooting any tuning issues requires us to tune
our expectations
first.  Oracle Support's Center of Expertise (COE)
has created several tools to assist in troubleshooting issues with Performance

  • Implementing and Using the PL/SQL Profiler - Note:243755.1  

    When there is a significant gap between user elapsed time and SQL
    processing elapsed time, and there is PL/SQL code involved, the PL/SQL Profiler
    becomes a very useful tool.  It helps to identify the lines of PL/SQL
    code which are taking longer to process.

    Requirements - Can be used on any 8i or higher database, including Apps
    10.7-11i instances
  • Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds
    and/or Waits generated by EVENT 10046 - Note:224270.1  
    Reads a raw SQL Trace generated by standard SQL Trace or by EVENT 10046
    (Level 4, 8 or 12), and generates a comprehensive HTML report with
    performance related details: time summary, call summary (parse, execute,
    fetch), identification of top SQL, row source plan, explain plan, CBO
    statistics, wait events, values of bind variables, I/O summary per schema
    object, latches, hot blocks, etc.

    Output HTML report includes all the details found on TKPROF, plus
    additional information normally requested and used for a transaction
    performance analysis. Generated report is more readable and extensive than
    text format used on prior version of this tool and on current TKPROF.

    Requirements: - RDBMS 9i (9.2), 10g, or higher.  Can be used for
    Oracle Apps 11i or higher, or for any other application running on top of
    an Oracle database 
  • SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info
    for one SQL statement - Note:215187.1    

    Given one SQL statement within a file or from memory, it generates a
    comprehensive report that includes the explain plan for that SQL
    statement, related CBO stats, and a full set of diagnostic information on
    all related objects.

    This tool is designed to help during the diagnostic phase of SQL
    performance issues (SQL tuning). Besides providing a comprehensive report,
    it also facilitates the creation of test cases on separate environments.
    During its execution, it creates a metadata script, that combined with a simple
    procedure to migrate the related CBO stats, it simplifies the replication
    of the SQL analysis environment.

    The SQL statement to diagnose can be provided as a text file, or directly
    from memory passing as a parameter value its ID (hash_value or SQL_ID).

    Requirements - RDBMS 9i (9.2), 10g (10.1 and 10.2), or higher. 
    Can be used for Oracle Apps 11i, 12 or higher, or for any other
    application running on top of an Oracle database
  • Performance Tools Quick Reference Guide �
    Describes all Database performance tools and when to use them � Note:

    Given the complexity of diagnosing performance issues, this guide provides
    summary regarding when to use each of the COE tools to help resolve any
    performance tuning problem.  The guide covers Query Tuning, OS Tuning,
    Database Tuning, Hang/Lock issues and crash issues.
  • Performance Diagnostic Guide (PDG) � Note: 390374.1

    PDG provides a methodology to classifying and solve Database performance
    problems. The guide helps classify a performance problem into one of the
    main areas (query tuning, hang/locking issues, or slow database issues)
    and then leads the user to properly collect and analyze data for the
    chosen tuning area. PDG provides guidance on how to analyze the collected
    data, identify possible causes, and chose the best solution. The guide
    also provides links to selected MetaLink content and a reference section
    that summarizes possible causes and solutions.

recent additions of Performance Tuning resources couple the already posted and
popular, Server Technology Tuning tools - OS Watcher, LTOM, HangFG and
.  If you're not familiar with these tools, please refer to the
Knowledge Tab within MetaLink, under the Tools and Training box and click on
Diagnostic Tools for more information.

All of the Support Tools listed there including; the 300 DBA Script Library,
RDA, Ora-600 Tool, 200+ Diagnostics in the EBusiness Diagnostic Support Pack
and the Maintenance Wizard were created for customers. 
Diagnostics and Tooling is only a part of where the Support investment is being
leveraged.  Maximize your investment in Oracle Support and gain a larger
ROI by using these tools. 

Oracle Global Support is committed to creating a Superior Ownership Experience
for our customers and achieving excellence in customer service. 

If you are interested in any free Support training
for your team or organization, you can email us directly at


Post a Comment:
Comments are closed for this entry.

Chris Warticki
Support Specialist, (HPI)Corporate Athlete, Presenter Extraordinaire, Toastmaster & self-proclaimed Support "spokesmodel".

Chris has been working for Oracle for over 18 years. Chris educates customers how to maximize their Support investment and leverage the support tools and available resources as part of Premier Support.

Chris works for Global Customer Management and speaks with customers on all topics regarding Oracle support services


« February 2017