ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful for understanding the performance capabilities of a storage system, either to uncover issues that would impact the performance of an Oracle database or to [...]
Archive of posts tagged Advisors
Reading, Interpreting, and Components of Oracle STATSPACK Report
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables. In general, it is recommended to get the snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. Following is the compilation from metalink doc id 228913.1; and the statspack report shown here generated on a low end desktop system to see how the advisors work in 10gr2 (10.2.0.4)
Setting up Oracle Statistics Package (STATSPACK)
To install it, you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. To uninstall statspack, connect to database as SYSDBA and run spdrop.sql located under $ORACLE_HOME/rdbms/admin.
Oracle Metadata: PLAN_TABLE
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table. STATEMENT_ID: VARCHAR2(30) – Value of the optional [...]
Oracle Statistics Package (STATSPACK): A Free Performance Analysis Tool
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables.
How to identify PL/SQL performance bottlenecks using DBMS_PROFILER
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.
Tuning SQL statements with AUTOTRACE in SQL*Plus
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements. To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role.
Plan Table and methods for obtaining a formatted Explain Plan
Methods required to obtain a formatted explain plan differ depending on which version of Oracle you are using. The initial steps to create a plan table and make the optimizer populate the plan table with the execution plan are common to all versions of Oracle.
The On-Board Monitor (LTOM) – Embedded Real-Time Data Collection and Diagnostics Platform
The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection.