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.
Archive of posts tagged Oracle9i
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.
How to run multiple Oracle database instances on a single server
You can have multiple instances on the same machine, each with their own data files, either sharing the ORACLE_HOME or each with different ORACLE_HOME. ORACLE_HOME and ORACLE_SID are the key environment variables used by Oracle to identify an instance. In addition $ORACLE_HOME/bin must be in your PATH environment variable. To check the value of these [...]
How to read an Oracle SQL Execution Plan?
To execute any SQL statement Oracle has to derive an ‘execution plan’ . The execution plan of a query is a description of how Oracle will implement the retrieval of data to satisfy a given SQL statement. It is nothing but a tree which contains the order of steps and relationship between them.
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.
Oracle Analytical Functions – Look Back and Look Ahead
One of my all time favorite analytical functions are the LAG (look back) and LEAD (look ahead) functions, which were first introduced in Oracle 8i. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a [...]
FAQ on Oracle Database Patches for DST USA 2007 Changes
Do I need a patch? Because timezone data is embedded not only in Database tables but also in arguments to PL/SQL functions and Java code, there is no way for Oracle to definitively tell any customer that they do NOT need to patch. The only way to eliminate risk is to patch all your systems. [...]
Managing Oracle database services
This articles summarizes on how to start, stop and check the status of Oracle database services.