| Enterprise Manager (SQL Tuning Advisor) | The SQL Tuning Advisor is a feature included with the EM Tuning Pack for Oracle 10g or higher. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. |
| AWR SQL Report (awrsqrpt.sql) | The AWR SQL reporting utility is used to focus on the historical performance of a single SQL statement (identified by SQL_ID). It will tell you performance statistics and execution plans for each execution within a specified period (even if the statement had different child cursors). The awrsqrpt.sql script is found in the $ORACLE_HOME/rdbms/admin directory. Please note you must have a license to use this script. |
| SQLTXPLAIN | SQLTXPLAIN is a tool that generates a comprehensive report about a SQL statement which has been provided as input to the tool. The report includes the explain plan for a given SQL statement, cost-based optimizer statistics for the objects accessed by the statement, and details of related objects. The output HTML report produced can be used to analyze and diagnose SQL performance issues (SQL tuning). |
| TRCANLZR | TRCANLZR (Trace Analyzer) 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. |
| PL/SQL Profiler | PL/SQL Profiler is useful 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 is a very useful tool to help identify the lines of PL/SQL code which are responsible for taking the most time. |
| LTOM (Session Trace Collector) | The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM is proactive rather than reactive and provides real-time automatic problem detection and data collection. LTOM runs on the customer's UNIX server, is tightly integrated with the host operating system and provides an integrated solution for detecting and collecting trace files for system performance issues. |
| OPDG | Oracle Performance Diagnostic Guide is intended to help resolve query tuning, hang/locking, and slow database issues. The guide is not an automated tool but rather seeks to show methodologies, techniques, common causes, and solutions to performance problems. |
Os Tuning
When troubleshooting performance issues, it is helpful to gather and examine OS related data. Use the below tool to gather data specific to the OS.
| OS Watcher | OSW is a series of shell scripts that collect specific kinds of data, using Unix operating system diagnostic utilities. Control is passed to individually spawned operating system data collector processes, which in turn collect specific data, timestamp the data output, and append the data to pre-generated and named files. Each data collector will have its own file, created and named by the File Manager process. OSW invokes the distinct operating system utilities listed below as data collectors. These utilities will be supported, or their equivalents, as available for each supported target platform.
|
A slow database or performance issue can manifest itself as:
· A large number of sessions that run slower than usual.
· The database permits logons and seems to be working (not hung) but takes much longer than usual to show results.
· Many different types of activity all slow down at around the same time.
Use these utilities for collecting data or diagnosing cluster or instance-wide database performance issues.
| Enterprise Manager Automatic Diagnostic Database Monitor (ADDM) | The Automatic Database Diagnostic Monitor (ADDM) provides a holistic tuning solution. ADDM analysis can be performed over any time period defined by a pair of AWR snapshots taken on a particular instance. Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of performance problems. |
| ADDM Report (addmrpt.sql) | The ADDM reporting utility creates a report of its database performance findings. The addmrpt.sql script is found in the $ORACLE_HOME/rdbms/admin directory. Please note you must have a license to use this script. |
| Statspack (spreport.sql) | The Statspack reporting utility provides an overview of database performance within a specified period of time. It essentially computes the net change in database activity within the period. The spreport.sql script is found in the $ORACLE_HOME/rdbms/admin directory. You must install the statspack tables and package before using it by running spcreate.sql (see spdoc.txt). You do not need an additional license to use statspack. |
| AWR Report (awrrpt.sql) | The AWR reporting utility provides an overview of database performance within a specified period of time. It essentially computes the net change in database activity within the period. The awrrpt.sql script is found in the $ORACLE_HOME/rdbms/admin directory. Please note you must have a license to use this script. |
| RDA (DBPERF Profile) | Remote Diagnostic Agent (RDA) is a set of command line diagnostic scripts that are executed by an engine written in the Perl programming language. RDA is used to gather detailed information about an Oracle environment; the data gathered is in turn used to aid in problem diagnosis. The output is also useful for seeing the overall system configuration. RDA can be executed using a setup profile. A setup profile consists of a list of data collection modules and predefined settings. Therefore, the data collected using a profile can be slightly different than using the module separately. Profiles are typically used to setup all modules required to diagnose a specific class of problems. The DB_Perf profile is the suggest module to use for RDA when troubleshooting performance tuning issues. |
| OPDG | Oracle Performance Diagnostic Guide is intended to help resolve query tuning, hang/locking, and slow database issues. The guide is not an automated tool but rather seeks to show methodologies, techniques, common causes, and solutions to performance problems. |
Hang, Locking and Transient Issues
Some examples are:
· A database that is no longer allowing users to connect.
· A database that is no longer performing work.
· "Select 1 from dual" does not produce output.
· Create table does not complete.
One or more sessions that have completely stopped functioning is usually identified as a locking issue.
Transient issues related to performance are issues that generally occur for a time and then disappear but, during the occurrence, the system is hung or very slow. LTOM and HangFG are tools that assist in collecting the appropriate data when these issues occur.
Use these helpful tools and utilities when troubleshooting hang, locking, and transient issues.
| ASH Report (ashrpt.sql) | The ASH report utility is useful for determining the amount of active sessions, what they were doing, and which SQL statements were most active during a period of time. It is especially useful for analyzing transient performance issues. The ashrpt.sql script is found in the $ORACLE_HOME/rdbms/admin directory. |
| LTOM (Hang Detector, Data Recorder) | The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM is proactive rather than reactive and provides real-time automatic problem detection and data collection. LTOM runs on the customer's UNIX server, is tightly integrated with the host operating system and provides an integrated solution for detecting and collecting trace files for system performance issues. Automatic Hang Detection uses a rule based hang detection algorithm. When operating LTOM and this mode is enabled, automatic hang detection proceeds silently in the background while periodically checking for hangs. Once any session has been identified as hung, diagnostic traces are automatically generated. The type of hang diagnostic and number of diagnostic traces collected is determined by what has been defined in the rules file, $TOM_HOME/init/hangDetect.properties. Automatic Data Recording uses a set of rules to determine when to collect data based on monitoring specific wait events. The main advantage of using automatic data recording is that data is collected only when the database is experiencing a performance problem. The rules for this feature are based on database wait events. The automatic data recorder monitors certain V$ views at specified intervals and computes the average wait time between intervals for each event. This computed average wait time is compared to the rule definition for that event, if any. If a rule has been defined for that event and if the average wait time exceeds the rule threshold for that event then LTOM collects data based on an algorithm. |
| HangFG | HANGFG (Hang file generator) is a series of unix shell scripts used to automate the generation and collection of hanganalyze and systemstate trace files. HANGFG generates and collects hang trace files based on the impact of taking diagnostic traces on a system which is already in a degraded state. The overall decision on what level of impact the user can afford is left up to the user when he runs HANGFG, as the level of impact is passed in as an argument to the tool. HANGFG is also capable of making this decision for the user if the user selects light or medium impact (option 1 or 2) as an argument to the tool. HANGFG is RAC aware and can run in either a RAC or non RAC environment. |
Error/Crash Issues
In this section, error and crash issues refer to ORA-600, ORA-7445 errors, and core dumps. Use the tools below to assist in troubleshooting these types of issues.
| Stackx | stackx is a shell script utility for extracting diagnostic stack trace information from core files on UNIX/LINUX platforms. Core files are typically created when an application program fails in such a way it cannot continue operation. Core files contain information on program functions called immediately prior to program failure. This information is called a "stack trace" or "back trace" and is embedded within the binary core file, which is not human readable. In order to extract the useful information and put it into human readable form, some effort is required. stackx assists in that effort. This information can be instrumental in determining the cause of a program failure, if it is extracted properly. |
| ORA-600/ORA-7445 Troubleshooter | ORA-600 errors are raised from the kernel code of the Oracle RDBMS software when an internal inconsistency is detected or an unexpected condition is met. An ORA-7445 error is raised by an Oracle server process when it has received a fatal signal from the operating system. The ORA-600/ORA-7445 Troubleshooter is a form based tool accessed via MetaLink. The tool provides a description for of the ORA-600 and ORA-7445 errors based on the argument reported with the error. The tool will also perform an advanced MetaLink search to find documents containing relevant sections from the call stack which is generated at the time of the error. |
RAC Tuning
Use the RDA tool to obtain helpful performance related data across a RAC cluster.
| RDA | Remote Diagnostic Agent (RDA) is a set of command line diagnostic scripts that are executed by an engine written in the Perl programming language. RDA is used to gather detailed information about an Oracle environment; the data gathered is in turn used to aid in problem diagnosis. The output is also useful for seeing the overall system configuration. Starting from release 4.2, RDA has the capability to perform remote data collections. RDA which is installed on one node can control the installation and execution of RDA on multiple nodes/Oracle homes. |
No comments:
Post a Comment