Way to get Query execution plan in Oracle10g

traditional method before 10g

select * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;

10g

select * from v$sql where sql_id='&SQL_ID';
select * from v$sqlstats where sql_id='&SQL_ID';
select * from dba_hist_sqlstat where sql_id='&SQL_ID';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));

great scenario for 2gbps insertion speed......

great scenario for 2gbps insertion speed......

please look at
http://forums.oracle.com/forums/thread.jspa?threadID=533820&tstart=-2

BT (British Telecom) ran a 82 node (or more) OPS cluster of Pyramid Nil

Recovery Writer process (RVWR) on 10g onwards

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later).


U enable the flashback feature in your database and c its power

Log_buffer has no effect from 10g

I installed ORACLE SPOTLIGHT for RAC and found LOG_BUFFER was shown to occupy unusually large value, then I investigated and found fixed sga and log buffer are given a one granule size(4m) irrespective of log_buffer value.

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1290952 No
Redo Buffers 2899968 No
Buffer Cache Size 243269632 Yes
Shared Pool Size 163577856 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 419430400 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0

11 rows selected.

SQL> select (1290952+ 2899968) /1024 from dual;

(1290952+2899968)/1024
----------------------
4092.69531

Query ...

Asked by a old DBA friend about waits caused by wait event "OS THREAD STARTUP" found in his 10g RAC(4 nodes on solaris with ASM)


SELECT DECODE (session_state, 'WAITING', event, NULL) event,
session_state, COUNT(*), SUM (time_waited) time_waited
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state order by time_waited desc;

Node 1

enq: TX - row lock contention
WAITING
3517
1734721441

os thread startup
WAITING
743
681425655

log file sync
WAITING
3557
530889167

DFS lock handle
WAITING
1131
528346071

db file sequential read
WAITING
34162
502368757

PX Deq: Signal ACK
WAITING
3917
406436176

reliable message
WAITING
283
251896304

gc buffer busy
WAITING
580
175092687

db file parallel read
WAITING
2606
169615544

Streams AQ: qmn coordinator waiting for slave to start
WAITING
123
121679315


I'm concluding on base of my experiecne of parallel queries(on test
machine with single CPU!!!! so no benefit in my case no matter I had RAC)

I see there are PX events indicating your RAC database
instances has parallel query/dml executions but I
guess the os thread startup wait event has nothing to
do with rac environment. This event indicates the
waitupon starting os process(thread) to start query
slave process for execution of parallel query. if u
set init parameter parallel_min_server sufficently
high,you may slightly cut this overhead but its not
recomendded. But instead of following rules of thumb
you check trade-off if you have significant waits.

Making 10g RAC ready for worst

focussing on vulenrable part of RAC
RAC gives you high availablity and performance,but what if you dont have clustered disks and your voting disk or cluster registry fails. from 10g R2 these can be mirrored but you should regularly backup and voting/ocr disk.Also you should be heedfull to run integrity test.


manual mirriring of voting disk be

query for current voting disk :
C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk

located 1 votedisk(s).

adding redundant voting disk:
C:\>crsctl add css votedisk Q:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

C:\>crsctl add css votedisk Q:\cdata\crs\votedsk -force
Now formatting voting disk: Q:\cdata\crs\votedsk
successful addition of votedisk Q:\cdata\crs\votedsk.

C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk
1. 0 Q:\cdata\crs\votedsk

located 2 votedisk(s).

C:\>crsctl add css votedisk O:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

investigate ( is CRS running/stopped)

C:\>crsctl add css votedisk O:\cdata\crs\votedsk -force
Now formatting voting disk: O:\cdata\crs\votedsk
successful addition of votedisk O:\cdata\crs\votedsk.


running integrity test
C:\>cluvfy comp ocr -n all

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.

Uniqueness check for OCR device passed.

Checking the version of OCR...
OCR of correct Version "2" exists.

Checking data integrity of OCR...
Data integrity check for OCR passed.

OCR integrity check passed.

Verification of OCR integrity was successful.

installing-9i10g-on-rhel-3

Prerequisite: RHEL 3 has been installed with all required packages.
Also check, if you need OS Patch. For install of 9i u need 3006854 patch.Not needed for oracle 10g

Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh

1. system configuration change
Login from root user and add following entries to /etc/sysctl.conf file:

kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

2. install groups/user creation
Create the new groups and users:

groupadd oinstall
groupadd dba

useradd -g oinstall -G dba oracle

change password for oracle user
passwd oracle

3. install directory creation /setup
Create the directories in which the Oracle software will be installed and give ownership to install user

mkdir /u01
chown oracle.dba /u01
chmod 777 /u01

4. install user configuration
Login as the install user ,here oracle user and add the following lines at the end of the .bash_profile file:

# Oracle 9i /10g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1

5. run OUI
For 9i /mnt/cdrom/runInstallerv

10g does not support installation by cd so
For 10g ,copy the 10g software install cd to disk and begin installation .

10g-r2-rac-on-vmware

10g RAC installed on windows 2003 enterprize edition using vmware.

below links were major help to me.

http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml

http://forums.oracle.com/forums/thread.jspa?messageID=1123638


http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_14.shtml#Overview

http://www.oracle.com/technology/obe/10gr2_db_vmware/manage/clusterintro/clusterintro.htm



some more useful links:


-- Listed in order as I found them and kept in store for U in random fashion --
http://www.vmware.com/community/thread.jspa?messageID=737640

Statistics Package (STATSPACK) Guide

Subject: Statistics Package (STATSPACK) Guide
Doc ID: Note:394937.1 Type: HOWTO
Last Revision Date: 11-APR-2007 Status: PUBLISHED

In this Document
Goal
Solution
References



--------------------------------------------------------------------------------




Applies to: Oracle Server - Enterprise Edition - Version: 10.2.
Information in this document applies to any platform.


Goal
RDBMS version 10g offers a new and improved tool for diagnosing Database Perfromance issues. It is the Automated WorkLoad Repository (AWR).
However, there are still a number of customers using statistics package (statspack) intially introduced in RDBMS version 8.1.
The goal of this document is to further assist customers/engineers when installing and using the database performance tool Statspack.

During install of the RBBMS product, Oracle stores a document entitled spdoc.txt.
The spdoc.txt file will be located in the following directory upon successful install of the RDBMS product 8.1.7 or higher: $ORACLE_HOME/rdbms/admin/.
The StatsPack README files (spdoc.txt) include specific updated information, and history on this tool as well as platform and release specific information that will help when installing and using this product.

A number of cutomers do not realize spdoc.txt is available on their systems, or would like to have it available through Oracle's Knowledge Repository for easy access.
Therefore, the latest version, 10.2, spdoc.txt is published in this note.

Please find below spdoc.txt for version 10.2 in it's entirety to help guide you through installation, and the most common issues you may encounter while running statspack.

Information in this document will help you with all versions of RDBMS statspack product.
However, Oracle still suggests you go to your $ORACLE_HOME/rdbms/admin/spdoc.txt to reference your statspack platform and version specific information on running statspack reports (i.e section 4 below).

Solution
-----------------------------------------------------------------------

Oracle10g Server

Release 10.2

Production

-------------------------------------------------------------------------

Copyright (C) 1993, 2005, Oracle Corporation. All rights reserved.

Author: Connie Dialeris Green
Contributors: Cecilia Gervasio, Graham Wood, Russell Green, Patrick Tearle,
Harald Eri, Stefan Pommerenk, Vladimir Barriere

Please refer to the Oracle10g server README file in the rdbms doc directory,
for copyright, disclosure, restrictions, warrant, trademark, disclaimer,
and licensing information. The README file is README_RDBMS.HTM.

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

-------------------------------------------------------------------------


Statistics Package (STATSPACK) README (spdoc.txt)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE OF CONTENTS
-----------------

0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications


0. Introduction and Terminology
-----------------------------------

To effectively perform reactive tuning, it is vital to have an established baseline for later comparison when the system is running poorly. Without a baseline data point, it becomes very difficult to identify what a new problem is attributable to: Has the volume of transactions on the system increased? Has the transaction profile or application changed? Has the
number of users increased?

Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tuning scripts by collecting more information, and also by storing the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an 'instance health and load' summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.

Statspack improves on the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

- Statspack collects more data, including high resource SQL (and the optimizer execution plans for those statements)

- Statspack pre-calculates many ratios useful when performance tuning, such as cache hit ratios, per transaction and per
second statistics (many of these ratios must be calculated manually when using BSTAT/ESTAT)

- Permanent tables owned by PERFSTAT store performance statistics; instead of creating/dropping tables each time, data is inserted into the pre-existing tables. This makes historical data comparisons easier

- Statspack separates the data collection from the report generation. Data is collected when a 'snapshot' is taken; viewing the data collected is in the hands of the performance engineer when he/she runs the performance report

- Data collection is easy to automate using either dbms_job or an OS utility


NOTE: The term 'snapshot' is used to denote a set of statistics gathered at a single time, identified by a unique Id which includes the snapshot number (or snap_id). This term should not be confused with Oracle's Snapshot Replication technology.


How does Statspack work?

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 is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning.

Statspack users will become familiar with the concept of a 'snapshot'. 'snapshot' is the term used to identify a single collection of performance data. Each snapshot taken is identified by a 'snapshot id' which is a unique number generated at the time the snapshot is taken; each time a new collection is taken, a new snap_id is generated.

The snap_id, along with the database identifier (dbid) and instance number (instance_number) comprise the unique key for a snapshot (using this unique combination allows storage of multiple instances of a Clustered database in the same tables).

Once snapshots are taken, it is possible to run the performance report. The performance report will prompt for the two snapshot id's the report will process. The report produced calculates the activity on the instance between the two snapshot periods specified, in a similar way to the BSTAT/ESTAT report; to compare - the first snap_id supplied can be considered the equivalent of running BSTAT; the second snap_id specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT which can by its nature only compare two static data points, the report can compare any two snapshots specified.



1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
----------------------------------------------------------------------------------------------

Enterprise Manager
------------------
Statspack allows you to capture Oracle instance-related performance data, and report on this data in a textual format.

For EM managed databases in 9i, Oracle Enterprise Manager uses Statspack data and displays it graphically. Starting with 10g, Enterprise Manager instead uses data collected by the Automatic Workload Repository (AWR). AWR data is internally captured and stored by Oracle 10g databases.

For more information about Oracle Enterprise Manager visit the Oracle website oracle.com --> Database --> Manageability

Automatic Workload Repository and Statspack
-------------------------------------------
The Automatic Workload Repository (AWR) is an integrated part of the Oracle server. Its purpose is to collect server-related performance data automatically every 60 minutes (by default) when the statistics_level parameter is set to 'typical' (or 'all'). As the data is collected by the server itself, the Automated Database Diagnostic Monitor (ADDM) component of the server uses this data automatically to diagnose performance issues.
DBAs and performance engineers can access the performance recommendations by using EM, or view the captured data in the AWR report, which is similar to the Statspack Instance report.

To compare, Statspack is a manually installed and configured set of SQL and PL/SQL scripts which gather performance statistics. The data gathered is used by DBAs and performance engineers to manually diagnose performance
problems.

The AWR schema was initially based on the Statspack schema, but has since been modified. Because of this shared history, there are some similarities (e.g. concept of a snapshot, similar base tables). However, AWR is separate from Statspack.

For more information on using AWR, please see the Oracle 10g Server Performance Tuning Guide. For license information regarding AWR, please see the Oracle database Licensing Information Manual.

If you are going to use AWR instead of Statspack, and you have been using Statspack at your site, it is recommended that you continue to capture Statspack data for a short time (e.g. one month) after the upgrade to 10g. This is because comparing post-upgrade Statspack data to pre-upgrade Statspack data may make diagnosing initial upgrade problems easier to detect.

WARNING: If you choose to continue Statspack data collection after upgrading to 10g, and statistics_level is set to typical or
all (which enables AWR collection), it is advised to stagger Statspack data collection so it does not coincide with AWR
data collection (AWR data collection is by default is every hour, on the hour). Staggering data collection should be done to avoid the potential for any interference (e.g. stagger data collection by 30 minutes).

Long term, typically, there is little reason to collect data through both AWR and Statspack. If you choose to use AWR instead of Statspack, you should ensure you should keep a representative set of baselined Statspack data for future reference.

2. Statspack Configuration
------------------------------

2.1. Database Space Requirements

The amount of database space required by the package will vary considerably based on the frequency of snapshots, the size of the database and instance, and the amount of data collected (which is configurable).

It is therefore difficult to provide general storage clauses and space utilization predictions that will be accurate at each site.

Space Requirements
------------------
The default initial and next extent sizes are 100k, 1MB, 3MB or 5MB for all Statspack tables and indexes. To install Statspack, the minimum space requirement is approximately 100MB. However, the amount of space actually allocated will depend on the Tablespace storage characteristics of the tablespace Statspack is installed in (for example, if your minimum
extent size is 10m, then the storage requirement will be considerably more than 100m).

Using Locally Managed Tablespaces
---------------------------------
If you install the package in a locally-managed tablespace, such as SYSAUX, modifying storage clauses is not required, as the storage characteristics are automatically managed.

Using Dictionary Managed Tablespaces
------------------------------------
If you install the package in a dictionary-managed tablespace, Oracle suggests you monitor the space used by the objects created, and adjust the storage clauses of the segments, if required.

2.2. Installing the Tool

Installation scripts create a user called PERFSTAT, which will own all PL/SQL code and database objects created (including the STATSPACK tables, constraints and the STATSPACK package).

During the installation you will be prompted for the PERFSTAT user's password and default and temporary tablespaces.

The default tablespace will be used to create all Statspack objects (such as tables and indexes). Oracle recommend using the
SYSAUX tablespace for the PERFSTAT user's default tablespace; the SYSAUX tablespace will be the tablespace defaulted during the installation, if no other is specified.

A temporary tablespace is used for workarea activities, such as sorting (for more information on temporary tablespaces, see
the Oracle10g Concepts Manual). The Statspack user's temporary tablespace will be set to the database's default temporary tablespace by the installation, if no other temporary tablespace is specified.

NOTE:
o A password for PERFSTAT user is mandatory and there is no default password; if a password is not specified, the installation will abort with an error indicating this is the problem.

o For security reasons, keep PERFSTAT's password confidential.

o Do not specify the SYSTEM tablespace for the PERFSTAT users DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the installation will terminate with an error indicating this is the problem. This is enforced as Oracle does not recommend using the SYSTEM tablespace to store statistics data, nor for workareas. Use the SYSAUX (or a TOOLS) tablespace to store the data, and your instance's TEMPORARY tablespace for workareas.

o During the installation, the dbms_shared_pool PL/SQL package is created. dbms_shared_pool is used to pin the Statspack
package in the shared pool dbms_job is no longer created as part of the installation, as it is already created by catproc.sql (dbms_job can be used by the DBA to schedule periodic snapshots automatically).


To install the package, either change directory to the ORACLE_HOME rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, spcreate.

To run the installation script, you must use SQL*Plus and connect as a user with SYSDBA privilege.

e.g. Start SQL*Plus, then:
on Unix:

SQL> connect / as sysdba
SQL> @?/rdbms/admin/spcreate


on Windows:

SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate



The spcreate install script runs 3 other scripts - you do not need to run these - these scripts are called automatically:
1. spcusr -> creates the user and grants privileges
2. spctab -> creates the tables
3. spcpkg -> creates the package

Check each of the three output files produced (spcusr.lis, spctab.lis, spcpkg.lis) by the installation to ensure no errors were encountered, before continuing on to the next step.

Note that there are two ways to install Statspack - interactively (as shown above), or in 'batch' mode; batch mode is useful when you do not wish to be prompted for the PERFSTAT user's password, and default and temporary tablespaces.


Batch mode installation
~~~~~~~~~~~~~~~~~~~~~~~
To install in batch mode, you must assign values to the SQL*Plus variables which specify the password and the default and temporary tablespaces before running spcreate.

The variables are:
perfstat_password -> for the password
default_tablespace -> for the default tablespace
temporary_tablespace -> for the temporary tablespace

e.g.
on Unix:

SQL> connect / as sysdba
SQL> define default_tablespace='tools'
SQL> define temporary_tablespace='temp'
SQL> define perfstat_password='erg8oiw'
SQL> @?/rdbms/admin/spcreate
SQL> undefine perfstat_password

spcreate will no longer prompt for the above information.


2.3. Errors during installation

Specifying SYSTEM tablespace A possible error during installation is to specify the SYSTEM tablespace for the PERFSTAT user's DEFAULT or TEMPORARY tablespace. In such a situation, the installation will fail, stating the problem.

To install Statspack after receiving errors during the installation To correctly install Statspack after such errors, first run the
de-install script, then the install script. Both scripts must be run from SQL*Plus.

e.g. Start SQL*Plus, connect as a user with SYSDBA privilege, then:

SQL> @spdrop
SQL> @spcreate

3. Gathering data - taking a snapshot
--------------------------------------------------

The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
e.g.

SQL> connect perfstat/perfstat_password
SQL> execute statspack.snap;


Note: In a Clustered database environment, you must connect to the instance you wish to collect data for.

This will store the current values for the performance statistics in the Statspack tables, and can be used as a baseline snapshot
for comparison with another snapshot taken at a later time.

For better performance analysis, set the initialization parameter timed_statistics to true; this way, Statspack data collected will include important timing information. The timed_statistics parameter is also dynamically changeable using the 'alter system' command. Timing data is important and is usually required by Oracle support to diagnose performance problems.

The default level of data collection is level 5. It is possible to change the amount of data captured by changing the snapshot level, and the default thresholds used by Statspack. For information on how to do this, please see the 'Configuring the amount of data captured' section of this file.

Typically, in the situation where you would like to automate the gathering and reporting phases (such as during a benchmark), you may need to know the snap_id of the snapshot just taken. To take a snapshot and display the snap_id, call the statspack.snap function. Below is an example of calling the snap function using an anonymous PL/SQL block in SQL*Plus:

e.g.

SQL> variable snap number;
SQL> begin :snap := statspack.snap; end;
2 /
PL/SQL procedure successfully completed.
SQL> print snap
SNAP
----------
12




3.1. Automating Statspack statistics gathering

To be able to make comparisons of performance from one day, week or year to the next, there must be multiple snapshots taken over a period of time.

The best method to gather snapshots is to automate the collection on a regular time interval. It is possible to do this:

- within the database, using the Oracle dbms_job procedure to schedule the snapshots

- using Operating System utilities. On Unix systems, you could use utilities such as 'cron' or 'at'. On Windows, you could schedule a task (e.g. via Start> Programs> Accessories> System Tools> Scheduled Tasks).


3.2. Using dbms_job

To use an Oracle-automated method for collecting statistics, you can use dbms_job. A sample script on how to do this is supplied in spauto.sql, which schedules a snapshot every hour, on the hour.

You may wish to schedule snapshots at regular times each day to reflect your system's OLTP and/or batch peak loads. For example take snapshots at 9am, 10am, 11am, 12 midday and 6pm for the OLTP load, then a snapshot at
12 midnight and another at 6am for the batch window.

In order to use dbms_job to schedule snapshots, the job_queue_processes initialization parameter must be set to a value greater than 0 for the job to run automatically.

Example of setting the job_queue_processes parameter in an init.ora file:
# Set to enable the job queue process to start. This allows dbms_job
# to schedule automatic statistics collection using STATSPACK
job_queue_processes=1

If using spauto.sql in a Clustered database environment, the spauto.sql script must be run once on each instance in the cluster. Similarly, the job_queue_processes parameter must also be set for each instance.


Changing the interval of statistics collection
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To change the interval of statistics collection use the dbms_job.interval procedure
e.g.


execute dbms_job.interval(1,'SYSDATE+(1/48)');



Where 'SYSDATE+(1/48)' will result in the statistics being gathered each 1/48th of a day (i.e. every 30 minutes).

To force the job to run immediately, execute dbms_job.run();

To remove the auto collect job,


execute dbms_job.remove();


For more information on dbms_job, see the Supplied Packages Reference Manual.


4. Running the Performance reports
----------------------------------------

Once snapshots are taken, it is possible to generate a performance report.

There are two reports available - an Instance report, and a SQL report:

- The Instance Report (spreport.sql and sprepins.sql) is a general instance health report, covering all aspects of instance
performance. The instance report calculates and prints ratios, increases etc. for all statistics between the two snapshot periods,
in a similar way to the BSTAT/ESTAT report.

Note: spreport.sql calls sprepins.sql, first defaulting the dbid and instance number of the instance you are connected to. For more information on the difference between sprepins and spreport, see the 'Running the instance report when there are multiple
instances' section of this document.

- The SQL report (sprepsql.sql and sprsqins.sql) is a report for a specific SQL statement. The SQL report is usually
run after examining the high-load SQL sections of the instance health report. The SQL report provides detailed statistics and data for a single SQL statement (as identified by the Hash Value).

Note: sprepsql.sql calls sprsqins.sql, first defaulting the dbid and instance number of the instance you are connected to. For more information on the difference between sprsqins and sprepsql, see the 'Running the SQL report when there are multiple instances' section of this document.

Both reports prompt for the beginning snapshot id, the ending snapshot id, and the report name. The SQL report additionally requests the Hash Value for the SQL statement to be reported on.

Note: It is not correct to specify begin and end snapshots where the begin snapshot and end snapshot were taken from different instance startups. In other words, the instance must not have been shutdown between the times that the begin and end snapshots were taken.

We ask that you reference file $ORACLE_HOME/rdbms/admin/spdoc.txt, as mentioned in "Goal" section above, for details on running your specific version of statspack reports.




5. Configuring the amount of data captured
-----------------------------------------------

Both the snapshot level, and the thresholds specified will affect the amount of data Statspack captures.

5.1. Snapshot Level

It is possible to change the amount of information gathered by the package, by specifying a different snapshot 'level'. In other words, the level chosen (or defaulted) will decide the amount of data collected.

The higher the snapshot level, the more data is gathered. The default level set by the installation is level 5.

For typical usage, level 5 snapshot is effective on most sites. There are certain situations when using a level 6 snapshot is beneficial, such as when taking a baseline.

The events listed below are a subset of events which should prompt taking a new baseline, using level 6:
- when taking the first snapshots
- when a new application is installed, or an application is modified/upgraded
- after gathering optimizer statistics
- before and after upgrading

The various levels are explained in detail 'Snapshot Levels - details' section of this document.


5.2. Snapshot SQL thresholds

There are other parameters which can be configured in addition to the snapshot level.

These parameters are used as thresholds when collecting data on SQL statements; data will be captured on any SQL statements that breach the specified thresholds.

Snapshot level and threshold information used by the package is stored in the stats$statspack_parameter table.


5.3. Changing the default values for Snapshot Level and SQL Thresholds

If you wish to, you can change the default parameters used for taking snapshots, so that they are tailored to the instance's workload.

The full list of parameters which can be passed into the modify_statspack_parameter procedure are the same as those for the
snap procedure. These are listed in the 'Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETERS procedures' section of this document.


Temporarily using new values
------------------------------
To temporarily use a snapshot level or threshold which is different to the instance's default snapshot values, simply specify the required threshold or snapshot level when taking the snapshot. This value will only be used for immediate snapshot taken - the new value will not be saved as the default.

e.g. Take a single level 6 snapshot (do not save level 6 as the default):

SQL> execute statspack.snap(i_snap_level=>6);



Saving new defaults
--------------------
If you wish to save the new value as the instance's default, you can do this either by:

o Taking a snapshot, and specifying the new defaults to be saved to the database (using statspack.snap, and using the i_modify_parameter input variable).


SQL> execute statspack.snap -
(i_snap_level=>10, i_modify_parameter=>'true');


Setting the i_modify_parameter value to true will save the new thresholds in the stats$statspack_parameter table; these thresholds will be used for all subsequent snapshots.

If the i_modify_parameter was set to false or if it were omitted, the new parameter values would not be saved. Only the snapshot taken at that point will use the specified values, any subsequent snapshots will use the preexisting values in the stats$statspack_parameter table.

o Changing the defaults immediately without taking a snapshot, using the statspack.modify_statspack_parameter procedure. For example to change the snapshot level to 10, and the SQL thresholds for buffer_gets and disk_reads, the following statement can be issued:



SQL> execute statspack.modify_statspack_parameter -
(i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);


This procedure changes the values permanently, but does not take a snapshot.


5.4 Snapshot Levels - details

Levels >= 0 General performance statistics Statistics gathered:
This level and any level greater than 0 collects general performance statistics, such as: wait statistics, system events,
system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics,
latch statistics, resource limit, enqueue statistics, and statistics for each of the following, if enabled: automatic undo management, buffer cache advisory data, auto PGA memory management, Cluster DB statistics.

Levels >= 5 Additional data: SQL Statements

This level includes all statistics gathered in the lower level(s),
and additionally gathers the performance data on high resource usage SQL statements.

In a level 5 snapshot (or above), note that the time required for the snapshot to complete is dependent on the shared_pool_size and on the number of SQL statements in the shared pool at the time the snapshot is taken: the larger the shared pool, the longer the time taken to complete the snapshot.

SQL 'Thresholds'
The SQL statements gathered by Statspack are those which exceed one of six predefined threshold parameters:
- number of executions of the SQL statement (default 100)

- number of disk reads performed by the SQL statement (default 1,000)
- number of parse calls performed by the SQL statement (default 1,000)
- number of buffer gets performed by the SQL statement (default 10,000)
- size of sharable memory used by the SQL statement (default 1m)
- version count for the SQL statement (default 20)

The values of each of these threshold parameters are used when deciding which SQL statements to collect - if a SQL statement's resource usage exceeds any one of the above threshold values, it is captured during the snapshot.

The SQL threshold levels used are either those stored in the table stats$statspack_parameter, or by the thresholds specified when the snapshot is taken.

Levels >= 6 Additional data: SQL Plans and SQL Plan usage

This level includes all statistics gathered in the lower level(s),
and additionally gathers optimizer execution plans, and plan usage data for each of the high resource usage SQL statements captured.

A level 6 snapshot gathers information which is invaluable when determining whether the execution plan used for a SQL statement has changed. Therefore level 6 snapshots should be used whenever there is the possibility a plan may change, such as after large data loads, or after gathering new optimizer statistics.

To capture the plan for a SQL statement, the statement must be in the shared pool at the time the snapshot is taken, and must exceed one of the SQL thresholds. To gather plans for all statements in the shared pool, you can temporarily specify the executions threshold (i_executions_th) to be zero (0) for those snapshots. For information on how to do this, see the 'Changing the default values for Snapshot Level and SQL Thresholds' section of this document.

Levels >= 7 Additional data: Segment level statistics
This level includes all statistics gathered in the lower level(s), and additionally gathers the performance data on highly used segments.

A level 7 snapshot captures Segment-level statistics for segments which are heavily accessed or heavily contended for.

Segment-level statistics captured are:
- logical reads
- db block changes
- physical reads
- physical writes
- physical reads direct
- physical writes direct
- global cache cr blocks served *
- global cache current blocks served *
- buffer busy waits
- ITL waits
- row lock waits

* Denotes the Statistic is Real Application Clusters specific.

There are many uses for segment-specific statistics. Below are three examples:
- The statistics relating to physical reads and writes can help you decide to modify the physical layout of some segments (or of the tablespaces they reside in). For example, to better spread the segment IO load, you can add files residing on different disks to a tablespace storing a heavily accessed segment, or you can (re)partition a segment.
- High numbers of ITL waits for a specific segment may indicate a need to change segment storage attributes such as PCTFREE and/or INITRANS.
- In a Real Application Clusters database, global cache statistics make it easy to spot the segments responsible for much of the
cross-instance traffic.

Although Statspack captures all segment statistics, it only displays the following statistics in the Instance report:
- logical reads
- physical reads
- buffer busy waits
- ITL waits
- row lock waits
- global cache cr blocks served *
- global cache current blocks served *

Segment statistics 'Thresholds'
The segments for which statistics are gathered are those whose statistics exceed one of the following seven threshold parameters:
- number of logical reads on the segment (default 10000)
- number of physical reads on the segment (default 1000)
- number of buffer busy waits on the segment (default 100)
- number of row lock waits on the segment (default 100)
- number of ITL waits on the segment (default 100)
- number of global cache Consistent Read blocks served* (default 1000)
- number of global cache CUrrent blocks served* (default 1000)

The values of each of these thresholds are used when deciding which segments to collect statistics for. If any segment's statistic value exceeds its corresponding threshold value, all statistics for this segment are captured.

The threshold levels used are either those stored in the table stats$statspack_parameter, or by the thresholds specified when
the snapshot is taken.

Levels >= 10 Additional statistics: Parent and Child latches
This level includes all statistics gathered in the lower levels, and additionally gathers Parent and Child Latch information. Data
gathered at this level can sometimes cause the snapshot to take longer to complete i.e. this level can be resource intensive, and should only be used when advised by Oracle personnel.


5.5. Specifying a Session Id

If you would like to gather session statistics and wait events for a particular session (in addition to the instance statistics and wait events), it is possible to specify the session id in the call to Statspack. The statistics gathered for the session will include session statistics, session events and lock activity. The default behaviour is to not to gather session level statistics.


SQL> execute statspack.snap(i_session_id=>3);


Note that in order for session statistics to be included in the report output, the session's serial number (serial#) must be the same in the begin and end snapshot. If the serial numbers differ, it means the session is not the same session, so it is not valid to generate session statistics. If the serial numbers differ, the following warning will appear (after the begin/end snapshot has been entered by the user) to signal the session statistics cannot be printed:

WARNING: SESSION STATISTICS WILL NOT BE PRINTED, as session statistics captured in begin and end snapshots are for different sessions (Begin Snap sid,serial#: 10,752, End Snap sid,serial#: 10,754).


5.6. Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETERS procedures

Parameters able to be passed in to the statspack.snap and statspack.modify_statspack_parameter procedures are as follows:

Range of Default
Parameter Name Valid Values Value Meaning

---------------------------------------------------
i_snap_level 0,5,6,7,10 5 Snapshot Level
i_ucomment Text Comment to be stored with Snapshot
i_executions_th Integer >=0 100 SQL Threshold: number of times the statement was executed
i_disk_reads_th Integer >=0 1,000 SQL Threshold: number of disk reads the statement made
i_parse_calls_th Integer >=0 1,000 SQL Threshold: number of parse
calls the statement made
i_buffer_gets_th Integer >=0 10,000 SQL Threshold: number of buffer
gets the statement made
i_sharable_mem_th Integer >=0 1048576 SQL Threshold: amount of sharable
memory
i_version_count_th Integer >=0 20 SQL Threshold: number of versions
of a SQL statement
i_seg_phy_reads_th Integer >=0 1,000 Segment statistic Threshold: number
of physical reads on a segment.
i_seg_log_reads_th Integer >=0 1,0000 Segment statistic Threshold: number
of logical reads on a segment.
i_seg_buff_busy_th Integer >=0 100 Segment statistic Threshold: number
of buffer busy waits for a segment.
i_seg_rowlock_w_th Integer >=0 100 Segment statistic Threshold: number
of row lock waits for a segment.
i_seg_itl_waits_th Integer >=0 100 Segment statistic Threshold: number
of ITL waits for a segment.
i_seg_cr_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number
of Consistent Reads blocks served by
the instance for the segment*.
i_seg_cu_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number
of CUrrent blocks served by the
instance for the segment*.
i_session_id Valid sid 0 (no Session Id of the Oracle Session
from session) to capture session granular
v$session statistics for
i_modify_parameter True,False False Save the parameters specified for
future snapshots?

6. Time Units used for Performance Statistics
--------------------------------------------------

Oracle now supports capturing certain performance data with millisecond and
microsecond granularity.

Views which include microsecond timing include:
- v$session_wait, v$system_event, v$session_event (time_waited_micro column)
- v$sql, v$sqlarea (cpu_time, elapsed_time columns)
- v$latch, v$latch_parent, v$latch_children (wait_time column)
- v$sql_workarea, v$sql_workarea_active (active_time column)

Views which include millisecond timings include:
- v$enqueue_stat (cum_wait_time)

Note that existing columns in other views continue to capture centi-second
times.

As centi-second and microsecond timing may not be appropriate for rolled
up data such as that displayed by Statspack, Statspack displays most
cumulative times in seconds, and average times in milliseconds (for easier
comparison with Operating System monitoring utilities which often report
timings in milliseconds).

For clarity, the time units used are specified in the column headings of
each timed column in the Statspack report. The convention used is:
(s) - a second
(cs) - a centisecond - which is 100th of a second
(ms) - a millisecond - which is 1,000th of a second
(us) - a microsecond - which is 1,000,000th of a second



7. Event Timings
-----------------
If timings are available, the Statspack report will order wait events by time
(in the Top-5 and background and foreground Wait Events sections).

If timed_statistics is false for the instance, however a subset of users or
programs set timed_statistics set to true dynamically, the Statspack report
output may look inconsistent, where some events have timings (those which the
individual programs/users waited for), and the remaining events do not.
The Top-5 section will also look unusual in this situation.

Optimally, timed_statistics should be set to true at the instance level for
ease of diagnosing performance problems.

8. Managing and Sharing performance data
-----------------------------------------

8.1. Baselining performance data

It is possible to identify snapshot data worthy of keeping, which will not
be purged by the Statspack purge. This is called baselining. Once you have
determined which snap Ids or times of day most represent a particular
workload whose performance data you would like to keep, you can mark the
data representing those times as baselines. Baselined snapshots will not
be purged by the Statspack purge.

If you later decide you no longer want to keep previously baselined
snapshots, you can clear the baseline (clearing the baseline does not
remove the data, it just identifies the data as candidates for purging).

NOTE: Statspack baseline does not perform any consistency checks on the
snapshots requested to be baselined (e.g. it does not check whether
the specified baselines span an instance shutdown). Instead, the
baseline feature merely marks Snapshot rows as worthy of keeping,
while other data can be purged.

New procedures and functions have been added to the Statspack package to
make and clear baselines: MAKE_BASELINE, and CLEAR_BASELINE. Both of these
are able to accept varying parameters (e.g. snap Ids, or dates, etc), and
can be called either as a procedure, or as a function (the function returns
the number of rows operated on, whereas the procedure does not).

Snap Ids or Begin/End dates
---------------------------
The Statspack MAKE_BASELINE procedures and functions provide flexibility in
the manner baselines are made or cleared. These can take various input
parameters:

- Begin Snap Id and End Snap Id

A begin and end snap Id pair can be specified. In this case, you choose
either to baseline the range of snapshots between the begin and end
snapshot pair, or just the two snapshots. The default is to baseline
the entire range of snapshots.

- Begin Date and End Date

A begin and end date pair can be specified. All snapshots which fall in
the date range specified will be marked as baseline data.

Similarly to the MAKE_BASELINE procedures and functions, the CLEAR_BASELINE
procedures and functions accept the same arguments.

Procedure or Function
---------------------
It is possible to call either the MAKE_BASELINE procedure, or the
MAKE_BASELINE function. The only difference is the MAKE_BASELINE function
returns the number of snapshots baselined, whereas the MAKE_BASELINE
procedure does not.
Similarly, the CLEAR_BASELINE procedure performs the same task as the
CLEAR_BASELINE function, however the function returns the number of
baselined snapshots which were cleared (i.e. no longer identified as
baselines).

8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids

This section describes the input parameters for the MAKE_BASELINE and
CLEAR_BASELINE procedure and function which accept Snap Ids. The input
parameters for both MAKE and CLEAR baseline are identical. The
procedures/functions will either baseline (or clear the baseline for) the
range of snapshots between the begin and end snap Ids identified (the
default), or if i_snap_range parameter is FALSE, will only operate on
the two snapshots specified.
If the function is called, it will return the number of snapshots
operated on.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_begin_snap Any Valid Snap Id - SnapId to start the baseline at
i_end_snap Any valid Snap Id - SnapId to end the baseline at
i_snap_range TRUE/FALSE TRUE Should the range of snapshots
between the begin and end snap
be included?
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
To make a baseline of snaps 45 and 50 including the range of snapshots
in between (and you do not wish to know the number of snapshots
baselined, so call the MAKE_BASELINE procedure). Log into the PERFSTAT
user in SQL*Plus, and:

SQL> exec statspack.make_baseline -
(i_begin_snap => 45, -
i_end_snap => 50);

Or without specifying the parameter names:

SQL> exec statspack.make_baseline(45, 50);

Example 2:
To make a baseline of snaps 1237 and 1241 (including the range of
snapshots in between), and be informed of the number of snapshots
baselined (by calling the function), log into the PERFSTAT
user in SQL*Plus, and:

SQL> variable num_snaps number;
SQL> begin
SQL> :num_snaps := statspack.make_baseline(1237, 1241);
SQL> end;
SQL> /
SQL> print num_snaps

Example 3:
To make a baseline of only snapshots 1237 and 1241 (excluding the
snapshots in between), log into the PERFSTAT user in SQL*Plus,
and:

SQL> exec statspack.make_baseline(5, 12, false);

All of the prior examples apply equally to CLEAR_BASELINE.


8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates

The input parameters for the MAKE_BASELINE and CLEAR_BASELINE procedure and
function which accept begin and end dates are identical. The procedures/
functions will either baseline (or clear the baseline for) all snapshots
which were taken between the begin and end dates identified.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_begin_date Any valid date - Date to start the baseline at
i_end_date Any valid date > - Date to end baseline at
begin date
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
To make a baseline of snapshots taken between 12-Feb-2003 at 9am, and
12-Feb-2003 at 12 midday (and be informed of the number of snapshots
affected), call the MAKE_BASELINE function. Log into the PERFSTAT
user in SQL*Plus, and:

SQL> variable num_snaps number;
SQL> begin
SQL> :num_snaps := statspack.make_baseline
(to_date('12-FEB-2003 09:00','DD-MON-YYYY HH24:MI'),
to_date('12-FEB-2003 12:00','DD-MON-YYYY HH24:MI'));
SQL> end;
SQL> /
SQL> print num_snaps

Example 2:
To clear an existing baseline which covers the times 13-Dec-2002 at
11pm and 14-Dec-2002 at 2am (without wanting to know how many
snapshots were affected), log into the PERFSTAT user in SQL*Plus, and:

SQL> exec statspack.clear_baseline -
(to_date('13-DEC-2002 23:00','DD-MON-YYYY HH24:MI'), -
to_date('14-FEB-2002 02:00','DD-MON-YYYY HH24:MI'));


8.2. Purging/removing unnecessary data

It is possible to purge unnecessary data from the PERFSTAT schema using the
PURGE procedures/functions. Any Baselined snapshots will not be purged.

NOTE:
o It is good practice to ensure you have sufficient baselined snapshots
before purging data.
o It is recommended you export the schema as a backup before running this
script, either using your own export parameters, or those provided in
spuexp.par
o WARNING: It is no longer possible to rollback a requested purge operation.
o The functionality which was in the sppurge.sql SQL script has been moved
into the STATSPACK package. Moving the purge functionality into the
STATSPACK package has allowed significantly more flexibility in how
the data to be purged can be specified by the performance engineer.


Purge Criteria for the STATSPACK PURGE procedures and functions
---------------------------------------------------------------
Data to be purged can either be specified by:

- Begin Snap Id and End Snap Id

A begin and end snap Id pair can be specified. In this case, you choose
either to purge the range of snapshots between the begin and end
snapshot pair (inclusive, which is the default), or just the two
snapshots specified.
The preexisting Statspack sppurge.sql SQL script has been modified to
use this PURGE procedure (which purges by begin/end snap Id range).

- Begin Date and End Date

A begin and end date pair can be specified. All snapshots which were
taken between the begin and end date will be purged.

- Purge before date

All snapshots which were taken before the specified date will be purged.

- Number of days (N)

All snapshots which were taken N or more days prior to the current date
and time (i.e. SYSDATE) will be purged.

Extended Purge
--------------
In prior releases, Statspack identifier tables which contained SQL Text,
SQL Execution plans, and Segment identifiers were not purged.

It is now possible to purge the unreferenced data in these tables. This is
done by requesting the 'extended purge' be performed at the same time as
the normal purge. Requesting the extended purge be performed along with a
normal purge is simply a matter of setting the input parameter
i_extended_purge to TRUE when calling the regular purge.

Purging this data may be resource intensive, so you may choose to perform
an extended purge less frequently than the normal purge.

Procedure or Function
---------------------
Each of the purge procedures has a corresponding function. The function
performs the same task as the procedure, but returns the number of
Snapshot rows purged (whereas the procedure does not).


8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id

This section describes the input parameters for the PURGE procedure and
function which accept Snap Ids. The input parameters for both procedure
and function are identical. The procedure/function will purge all
snapshots between the begin and end snap Ids identified (inclusive, which
is the default), or if i_snap_range parameter is FALSE, will only purge
the two snapshots specified. If i_extended_purge is TRUE, an extended purge
is also performed.
If the function is called, it will return the number of snapshots purged.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_begin_snap Any Valid Snap Id - SnapId to start purging from
i_end_snap Any valid Snap Id - SnapId to end purging at
i_snap_range TRUE/FALSE TRUE Should the range of snapshots
between the begin and end snap
be included?
i_extended_purge TRUE/FALSE FALSE Determines whether unused
SQL Text, SQL Plans and
Segment Identifiers will be
purged in addition to the
normal data purged
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
Purge all snapshots between the specified begin and end snap ids. Also
purge unused SQL Text, SQL Plans and Segment Identifiers, and
return the number of snapshots purged. Log into the PERFSTAT user
in SQL*Plus, and:

SQL> variable num_snaps number;
SQL> begin
SQL> :num_snaps := statspack.purge
( i_begin_snap=>1237, i_end_snap=>1241
, i_extended_purge=>TRUE);
SQL> end;
SQL> /
SQL> print num_snaps


8.2.2. Input Parameters for the PURGE procedures and functions
which accept Begin Date and End Date

This section describes the input parameters for the PURGE procedure and
function which accept a begin date and an end date. The procedure/
function will purge all snapshots taken between the specified begin and
end dates. The input parameters for both procedure and function are
identical. If i_extended_purge is TRUE, an extended purge is also performed.
If the function is called, it will return the number of snapshots purged.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_begin_date Date - Date to start purging from
i_end_date End date > begin - Date to end purging at
date - SnapId to end the baseline at
i_extended_purge TRUE/FALSE FALSE Determines whether unused
SQL Text, SQL Plans and
Segment Identifiers will be
purged in addition to the
normal data purged
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
Purge all snapshots which fall between 01-Jan-2003 and 02-Jan-2003.
Also perform an extended purge. Log into the PERFSTAT user in
SQL*Plus, and:

SQL> exec statspack.purge -
(i_begin_date=>to_date('01-JAN-2003', 'DD-MON-YYYY'), -
i_end_date =>to_date('02-JAN-2003', 'DD-MON-YYYY'), -
i_extended_purge=>TRUE);


8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date

This section describes the input parameters for the PURGE procedure and
function which accept a single date. The procedure/function will purge
all snapshots older than the date specified. If i_extended_purge is TRUE,
also perform an extended purge. The input parameters for both
procedure and function are identical.
If the function is called, it will return the number of snapshots purged.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_purge_before_date Date - Snapshots older than this date
will be purged
i_extended_purge TRUE/FALSE FALSE Determines whether unused
SQL Text, SQL Plans and
Segment Identifiers will be
purged in addition to the
normal data purged.
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
To purge data older than a specified date, without wanting to know the
number of snapshots purged, log into the PERFSTAT user in SQL*Plus,
and:

SQL> exec statspack.purge(to_date('31-OCT-2002','DD-MON-YYYY'));


8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep

This section describes the input parameters for the PURGE procedure and
function which accept the number of days of snapshots to keep. All data
older than the specified number of days will be purged. The input
parameters for both procedure and function are identical. If
i_extended_purge is TRUE, also perform an extended purge.
If the function is called, it will return the number of snapshots purged.

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ----------------- ------- -------------------------------
i_num_days Number > 0 - Snapshots older than this
number of days will be purged
i_extended_purge TRUE/FALSE FALSE Determines whether unused
SQL Text, SQL Plans and
Segment Identifiers will be
purged in addition to the
normal data purged
i_dbid | Any valid DBId/ Current Caters for RAC databases
i_instance_number | inst number DBId/ where you may wish to baseline
combination Inst # snapshots on one instance
in this which were physically taken
Statspack on another instance
schema

Example 1:
To purge data older than 31 days, without wanting to know the number
of snapshots operated on, log into the PERFSTAT user in SQL*Plus, and:

SQL> exec statspack.purge(31);


8.2.5. Using sppurge.sql

When sppurge is run, the instance currently connected to, and the
available snapshots are displayed. The DBA is then prompted for the
low Snap Id and high Snap Id. All snapshots which fall within this
range will be purged.

WARNING: sppurge.sql has been modified to use the new Purge functionality
in the STATSPACK package, therefore it is no longer possible to
rollback a requested purge operation - the purge is automatically
committed.

e.g. Purging data - connect to PERFSTAT using SQL*Plus, then run the
sppurge.sql script - sample example output appears below.

SQL> connect perfstat/perfstat_password
SQL> set transaction use rollback segment rbig;
SQL> @sppurge

Database Instance currently connected to
========================================

Instance
DB Id DB Name Inst Num Name
----------- ---------- -------- ----------
720559826 PERF 1 perf


Snapshots for this database instance
====================================

Base- Snap
Snap Id Snapshot Started line? Level Host Comment
-------- --------------------- ----- ----- --------------- --------------------
1 30 Feb 2000 10:00:01 6 perfhost
2 30 Feb 2000 12:00:06 Y 6 perfhost
3 01 Mar 2000 02:00:01 Y 6 perfhost
4 01 Mar 2000 06:00:01 6 perfhost

WARNING
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to. Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1
Using 1 for lower bound.

Enter value for hisnapid: 2
Using 2 for upper bound.

Deleting snapshots 1 - 2

Purge of specified Snapshot range complete.

SQL> -- end of example output




Batch mode purging
------------------
To purge in batch mode, you must assign values to the SQL*Plus
variables which specify the low and high snapshot Ids to purge.

The variables are:
losnapid -> Begin Snapshot Id
hisnapid -> End Snapshot Id

e.g.
SQL> connect perfstat/perfstat_password
SQL> define losnapid=1
SQL> define hisnapid=2
SQL> @sppurge

sppurge will no longer prompt for the above information.


8.3. Removing all data

If you wish to truncate all performance data indiscriminately, it is
possible to do this using sptrunc.sql This script truncates all
statistics data gathered, including snapshots marked as baselines.

NOTE:
It is recommended you export the schema as a backup before running this
script either using your own export parameters, or those provided in
spuexp.par

If you run sptrunc.sql in error, the script allows you to exit before
beginning the truncate operation (you do this at the 'begin_or_exit'
prompt by typing in 'exit').

To truncate all data, connect to the PERFSTAT user using SQL*Plus,
and run the script - sample output which truncates data is below:

SQL> connect perfstat/perfstat_password
SQL>

References
Note 94224.1:FAQ- STATSPACK COMPLETE REFERENCE ErrorsORA-2017 integer value required
ORA-2245 invalid ROLLBACK SEGMENT name
ORA-6512 "at %sline %s"
Keywords'PERFORMANCE~PROBLEMS' 'PERFORMANCE~STATISTICS' 'PERFORMANCE~TUNING' 'PERFORMANCE~ISSUE' 'MEMORY~USAGE' 'GATHER~STATISTICS' 'QUERY~PERFORMANCE' 'PERFORMANCE~PROBLEMS'
--------------------------------------------------------------------------------

Oracle 10g:Exploring Data Pump

Introduction:
Oracle 10g offers several new features, one of which is Data Pump technology for fast data movement between databases. Most Oracle shops still use their traditional export and import utility scripts rather this new technology. Data Pump technology is entirely different from the export/import utility, although they have a similar look and feel. Data Pump runs inside the database as a job, which means jobs are somewhat independent of the process that started the import or export. Another advantage is that other DBAs can login to the database and check the status of the job. The advantages of Data Pump, along with Oracle's plan to deprecate the traditional import/export utilities down the road, make Data Pump a worthwhile topic for discussion.

Oracle claims Data Pump offers a transfer of data and metadata at twice the speed of export and twenty to thirty times the speed of the import utility that DBAs have been using for years. Data Pump manages this speed with multiple parallel streams of data to achieve maximum throughput. Please note that Data Pump does not work with utilities older than the 10g release 1 utility.

Data Pump consists of two components: the Data Pump export utility called “expdp,” to Export the objects from a database, and the Data Pump Import utility called “impdp,” to load the objects into database. Just like traditional export and import utilities, the DBA can control these jobs with several parameters.

For example:

$expdp username/password (other parameters here)$impdp username/password (other parameters here)We can get a quick summary of all parameters and commands by simply issuing

$expdp help=y$impdp help=ySimilar to the export and import utility, Data Pump export and import utilities are extremely useful for migrating especially large databases from an operating system and importing them into a database running on a different platform and operating system in a short amount of time.

The Oracle supplied package, DBMS_DATAPUMP, can be used to implement the API, through which you can access the Data Pump export and import utilities programmatically. In other words, we can create a much powerful, custom Data Pump utility using Data Pump technology, if you have hundreds of databases to manage.

One of the interesting points is how Data Pump initiates the export session. In the traditional export utility, the user process writes the exported data to the disk that is requested from the server process, as a part of regular session. The Data Pump expdp - user process launches a server-side process or job that writes data to disks on the server node, and this process runs independently of the session established by expdp client. However, similar to the traditional export utility, Data Pump writes the data into dump files in an Oracle proprietary format that only the Data Pump import utility can understand.

New Features of Data Dump that improve the performance of Data movement:
Below are some of the features that differentiate the traditional export and import utility from Data Pump. These features not only enhance the speed of the data transfer but also are handy for the DBA to asses how the job would run before actually running Data Dump.

Parallel Threads: The parallel parameter specifies the maximum number of threads of active execution operating on behalf of the export job. This execution set consists of a combination of worker processes and parallel I/O server processes. Because each active worker processes or I/O server process works on one active file at a time, the DBA must specify a sufficient number of files. Therefore, the value the DBA specifies for this parameter should be less than or equal to the number of files in the dump file set. This important parameter helps the DBA to make a trade-off between resource consummation and the elapsed time.
Ability to restart the job: The ability to restart a job is an extremely useful feature if DBA is involved in moving large amounts of data. The Data Pump job can be restarted without any data loss or corruption after unexpected failure or if the DBA stopped the job with stop_job parameter.
Ability to detach from and reattach the job: This allows other DBAs to monitor jobs from multiple locations. We can attach the Data Pump export and import utilities to one job at a time but we can have multiple clients attached to the same job.
Support for export and import operations over the network: The NETWORK_LINK parameter initiates an export using a database link. It means that the system, to which expdp is connected, contacts the source database referenced by the source_database_link, retrieves data from it and writes the data to a dump file set back on the connected system.
Ability to change the name of source datafile to a different name: The DBA can change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced.
Support for filtering the metadata: The DBA can filter metadata using the “EXCLUDE” and “INCLUDE” options. If the object is excluded, all of its dependent objects are also excluded. For example, EXCLUDE=CONSTRAINT will exclude all constraints except “NOT NULL” and constraints needed for table creation, which includes: INCLUDE=TABLE:"IN('EMPLOYEES','DEPARTMENTS')"
· Space Estimate: The DBA can estimate how much space an export job will consume, without actually performing the export.

· Query Parameter: The DBA can filter data during the export by specifying a clause for a “SELECT” statement.

· Content Parameter: The DBA can specify what is exported or imported, for example, Meta data only or data only or both.

Init.ora parameters that affect the performance of Data Pump:
Oracle recommends the following settings to improve performance.

Disk_Asynch_io= true

Db_block_checking=false

Db_block_checksum=false

Additionally, the number of processes and sessions allowed to the database must be set to high, to allow for maximum parallelism.

How Data Pump accesses loading and unloading of Data:
Oracle has provided direct path to unload or export operations since Oracle 7.3. This method has been very useful for DBAs that want a quick export of the database and this process has been further enhanced in the Data Pump technology. Oracle uses the direct path method for loading (impdp) and unloading (expdp) when the structure of the tables allows it. If the table is part of a cluster, or it has a global index on a partitioned table, then Data Pump accesses the data in a different method called External Table. Both the direct path load and external table method support the same external data representation, so we can load the data that was unloaded with External Table method and vice versa.

Getting Started...
As stated earlier, Data Pump is a server-based utility, rather than client-based; dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory path on the file system.

1. The following SQL statements creates a user, a directory object named dpump_dir1 and grants the permissions to the user.

SQLPLUS system/manager@TDB10G as sysdba SQL> create user dpuser identified by dpuser; SQL> grant connect, resource to dpuser; SQL> CREATE DIRECTORY dpump_dir1 AS '/opt/app/oracle'; SQL> grant read, write on directory dpump_dir1 to dpuser 2. Let us see how the INCLUDE and EXCLUDE parameters can be used to limit the load and unload of particular objects. When the INCLUDE parameter is used, only the objects specified by it will be included in the export. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export: Assume we have EMP,EMP_DETAILS and DEPT tables owned by dpuser.

$ expdp dpuser/dpuser@TDB10G schemas=dpuser include= TABLE:\"IN (\'EMP\', \'DEPT\')\" directory=dpump_dir1 dumpfile=dpuser.dmp log=dpuser.log $expdp dpuser/dpuser@TDB10G schemas=dpuser exclude=TABLE:\"= \'EMP_DETAILS\'\" directory=dpump_dir1 dumpfile=dpuser2.dmp logfile=dpuser.logAs stated earlier, Data pump performance can be significantly improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

$expdp dpuser/dpuser@TDB10G schemas=dpuser directory=dpump_dir1 parallel=4 dumpfile=dpuser_%U.dmp logfile=dpuser.logData Pump API:
The Data Pump API, DBMS_DATAPUMP, provides a high-speed mechanism to move the data from one database to another. Infact, the Data Pump Export and Data Pump Import utilities are based on the Data Pump API. The structure used in the client interface of this API is a job handle. Job handle can be created using the OPEN or ATTACH function of the DBMS_DATAPUMP package. Other DBA sessions can attach to a job to monitor and control its progress so that remote DBA can monitor the job that was scheduled by an on-site DBA.

The following steps list the basic activities involved in using Data Pump API.

1. Execute DBMS_DATAPUMP.OPEN procedure to create job.

2. Define parameters for the job like adding file and filters etc.

3. Start the job.

4. Optionally monitor the job until it completes.

5. Optionally detach from job and attach at later time.

6. Optionally, stop the job

7. Restart the job that was stopped.

Example of the above steps:

Declare P_handle number; --- -- Data Pump job handleP_last_job_state varchar2(45); ---- -- To keep track of job stateP_job_state varchar2(45);P_status ku$_Status ----- -- The status object returned by get_status BEGIN P_handle:=DBMS_DATAPUMP.OPEN ('EXPORT','SCHEMA', NULL,'EXAMPLE','LATEST'); -- Specify a single dump file for the job (using the handle just returned)-- and a directory object, which must already be defined and accessible-- to the user running this procedure DBMS_DATAPUMP.ADD_FILE (p_handle,'example.dmp','DMPDIR'); -- A metadata filter is used to specify the schema that will be exported. DBMS_DATAPUMP.METADATA_FILTER (p_handle,'SCHEMA_EXPR','IN (''dpuser'')'); -- Start the job. An exception will be generated if something is not set up-- Properly. DBMS_DATAPUMP.start_job (p_handle); ----The export job should now be running.The status of the job can be checked by writing a separate procedure and capturing the errors and status until it is completed. Overall job status can also be obtained by querying “SELECT * from dba_datapump_jobs”.

Conclusion:
Oracle Data Pump is a great tool for the fast movement of data between the databases and much of this performance improvement is derived from the use of parameter “parallelism.” Even when the Transportable Tablespace feature of Oracle is used to move self-contained data between the databases, Data Pump is still required for handling the extraction and recreation of the metadata for that tablespace. Whenever possible, Data Pump performance is further maximized by using Direct-Path driver. Otherwise, Data Pump accesses the data using an External Table access driver.Data Pump provides flexibility, with the implementation of parameters such as INCLUDE, EXCLUDE, QUERY, and TRANSFORM that gives the DBA more control of data and objects being loaded and unloaded. With all of these features, Data Pump is a welcome addition to DBA tools in a world that constantly redefines the size of the “large database”.

ORA-1401 has been discared in oracle 10g

A new error message ORA-12899 has been created instead of the dreaded ORA-01401 error message in Oracle 10g version.

What is the benefit of changing this error ?


Well while this new error message is displayed Oracle will also display the field name in which the NULL was not allowed.



"ORA-01401: Inserted value too large for column"ORA-12899: value too large for column"DB"."TABLE_NAME"."FIELD_NAME" (actual: 6, maximum:5)This was a nice enhancement.

Ask Tom Article:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::
P11_QUESTION_ID:7143933880166

Migrating to 10g?Check Wheter u have used -1401

Starting from Oracle 10g, Oracle have changed the error code 1401 to 12899. For applications migrating to Oracle 10g make sure that there is no direct reference to -1401 anywhere in code.

There can be either

PRAGMA EXCEPTION INIT(field_length_error, -1401);

or

if sqlcode = -1401 then

type of errors handled in the program.

Use the following query to (not fully) find out whether there are any references to the code or not.

select name,line from user_source where text like '%-1401%';

If there are any references, change them to -12899.