Oracle10g

Setting up Oracle Statistics Package (STATSPACK)

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 »

How to create an Oracle User to use both OS Authentication and Password Authentication

The initialization parameter REMOTE_OS_AUTHENT offers a trusted authentication model to the network, users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication REMOTE_OS_AUTHENT accepts BOOLEAN value, FALSE is a default value, which can be altered via ALTER SYSTEM command. This …

How to create an Oracle User to use both OS Authentication and Password Authentication Read More »

Oracle Database Undocumented Parameters List

Table x$ksppi contains all documented and undocumented (also know as hidden parameters, which starts with an underscore) parameters; joining with x$ksppcv table on column indx with filter predicate substr(x$ksppi.ksppinm,1,1) = ‘_’ get you parameter name, value and description. A complete list of undocumented parameters below: Oracle 11g (11.1.0.6) Undocumented Parameters Oracle 10g (10.2.0.4) Undocumented Parameters Oracle 9i …

Oracle Database Undocumented Parameters List Read More »

OERR: The command line Oracle error code lookup utility

OERR stands for Oracle Error, which is a utility ships with Oracle distribution for Linux and UNIX that helps retrieve messages from message (.msg) files based on the supplied error code. This utility is not available for Windows, but there are variants freely available on the Internet. The error code consists of two parts, facility …

OERR: The command line Oracle error code lookup utility Read More »

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. Column Datatype Description Notes STATEMENT_ID VARCHAR2(30) Value …

Oracle Metadata: PLAN_TABLE Read More »

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 …

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

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. 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

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

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?

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 »