This is just a short note the point out that RECORD option of TKPROF is extremely useful in recording and capturing the non-recursive SQL statements in the order of execution from the SQL trace file. I have not had a requirement to use it until—today—one of my developers lost the original test script used when …
To install statspack – you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. Additionally, you need have: the password you would like to set for PERFSTATS schema the default tablespace for PERFSTAT schema the temporary tablespace for PERFSTAT schema If you run it through SQL*Plus the output will …
Setting up Oracle Statistics Package (STATSPACK) Read More »
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. Column Datatype Description Notes STATEMENT_ID VARCHAR2(30) Value …
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 …
Oracle Statistics Package (STATSPACK): A Free Performance Analysis Tool Read More »
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. This package enables the collection of profiler (performance) data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus …
How to identify PL/SQL performance bottlenecks using DBMS_PROFILER Read More »
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 …
Tuning SQL statements with AUTOTRACE in SQL*Plus Read More »
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. Note that there are limitations with explain plan when the …
Plan Table and methods for obtaining a formatted Explain Plan Read More »