Oracle11g

Oracle Statistics Package (STATSPACK): A Free Performance Analysis Tool

Leave a Comment / Oracle / By / June 19, 2009 / , , , , , ,

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 »

How to identify PL/SQL performance bottlenecks using DBMS_PROFILER

Leave a Comment / Oracle / By / June 15, 2009 / , , , , , , , ,

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 »

DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID

Leave a Comment / Oracle / By / June 15, 2009 / , , , ,

To display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format. Following steps will load two children cursors in the cursor cache By querying V$SQL, We can see that SQL_ID gfvh124qr5nzy has two childrens, 0 and 1; Let us execute DBMS_XPLAN.DISPLAY_CURSOR with …

DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID Read More »

How to run multiple Oracle database instances on a single server

2 Comments / Oracle / By / June 9, 2009 / , , , , ,

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 run multiple Oracle database instances on a single server Read More »

How to read an Oracle SQL Execution Plan?

3 Comments / Oracle / By / June 8, 2009 / , , , , ,

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 The basic …

How to read an Oracle SQL Execution Plan? Read More »

Tuning SQL statements with AUTOTRACE in SQL*Plus

Leave a Comment / Oracle / By / June 8, 2009 / , , , , , ,

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 »

Plan Table and methods for obtaining a formatted Explain Plan

1 Comment / Oracle / By / June 8, 2009 / , , , , , ,

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 »

Prepare your Linux Server to install Oracle database using oracle-validated RPM

Leave a Comment / Linux, Oracle / By / May 13, 2009 / , , , ,

Preparing a Linux server to install oracle has been made easy with the help of oracle-validated rpm package. Install of oracle-validated automates the download and install of all required RPMs including dependencies, setup of Linux kernel parameters, creation of oracle user (Linux user) and dba, oinstall groups I have been thinking of trying out oracle-validated …

Prepare your Linux Server to install Oracle database using oracle-validated RPM Read More »