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 Tuning.
- 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:
438452.1
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.
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 support-training_us@oracle.com
