Statspack Reports


/*
When installing the Statspack package, you can either change to the ORACLE_HOME/rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin DIRECTORY WHEN calling the installation script,
SPCREATE.SQL.
*/
-- install statspack
@?/rdbms/ADMIN/spcreate

/*
Enter appropriate information when prompted for the PERFSTAT user's
password, default tablespace, and temporary tablespace.
The SPCREATE.SQL install script runs the following scripts automatically:
*/
 
/*
SPCUSR.SQL: Creates the user and grants privileges
SPCTAB.SQL: Creates the tables
SPCPKG.SQL: Creates the package

*/

-- To install without prompting

SQL> CONNECT / AS SYSDBA
SQL> define default_tablespace='TOOLS'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='my_perfstat_password'
SQL> @?/rdbms/ADMIN/spcreate
/*

-- automatic collection of statistics

oracle_home/rdbms/admin/spauto.sql
*/

-- executing statspack

SQL> CONNECT perfstat/my_perfstat_password
SQL> EXECUTE statspack.snap;
-- For better performance analysis, set the initialization parameter
TIMED_STATISTICS to TRUE.
-- Statspack will then include important timing information in the data it
collects.
-- create statspack report

SQL> CONNECT perfstat/my_perfstat_password
SQL> @?/rdbms/ADMIN/spreport
-- statspack in a pl/sql block

DECLARE
variable snap NUMBER;
BEGIN
snap := statspack.snap;
dbms_output.put_line(snap);
END;
-- automatic statspac
-- A sample script on how to do this is supplied in SPAUTO.SQL
-- create statspack report without prompt

SQL> CONNECT perfstat/my_perfstat_password
SQL> define begin_snap=1
SQL> define end_snap=2
SQL> define report_name=batch_run
SQL> @?/rdbms/ADMIN/spreport
/*
When you examine the instance report, you often find high-load SQL
statements that you want to examine more closely. The SQL report,
SPREPSQL.SQL, displays statistics, the complete SQL text, and (if a level
six snapshot has been taken), information on any SQL plan(s) associated
with that statement.
*/

SQL> CONNECT perfstat/my_perfstat_password
Connected.
SQL> @?/rdbms/ADMIN/sprepsql
-- truncate all statspack data

SQL> CONNECT perfstat/my_perfstat_password
SQL> @?/rdbms/ADMIN/sptrunc
-- remove statspack

SQL> CONNECT / AS SYSDBA
SQL> @?/rdbms/ADMIN/spdrop
/*
SPDTAB.SQL - drops tables and public synonyms
SPDUSR.SQL - drops the user
*/

-- Purge unnecessary data by using the SPPURGE.SQL script

SQL> CONNECT perfstat/my_perfstat_password
SQL> SET TRANSACTION USE ROLLBACK SEGMENT rbig;
SQL> @?/rdbms/admin/spurge
SQL> CONNECT perfstat/my_perfstat_password
SQL> DEFINE losnapid=1
SQL> DEFINE hisnapid=2
SQL> @?/rdbms/admin/spurge
-- snap parameters

EXECUTE STATSPACK.SNAP(i_ucomment=>'this is a temporary commment');
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(i_ucomment=>'this is a
commment that is saved');
EXECUTE STATSPACK.SNAP(i_snap_level=>10, i_modify_parameter=>'true');
EXECUTE STATSPACK.SNAP(i_snap_level=>6);
Levels >= 0 General Performance STATISTICS
Levels >= 5 Additional Data: SQL Statements
Levels >= 6 Additional Data: SQL Plans AND SQL PLAN Usage
Levels >= 7 Additional data: SEGMENT LEVEL STATISTICS
Levels >= 10 Additional STATISTICS: Parent AND Child Latches
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: