Support Tools for Performance Tuning - Released!
By Cwarticki-Oracle on Aug 01, 2007
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
- 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
StackX. 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 firstname.lastname@example.org