BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Test the STATSPACK Install

Oracle Tips by Burleson Consulting

Step 3: Test the STATSPACK Install

To ensure that everything is installed correctly, we can demand two snapshots and then request an elapsed-time report. To execute a STATSPACK snapshot, we enter the statspack.snap procedure. If we do this twice, we will have two snapshots, and we can run the statsrep.sql report to ensure that everything is working properly. Here is the test to ensure that the install works properly. If you get a meaningful report after entering statsrep, then the install was successful. Also, note that the statsrep.sql script has an EXIT statement, so it will return you to the UNIX prompt when it has completed:

L 3-5

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> @spreport
. . .

Step 4: Schedule Automatic STATSPACK Data Collections

Now that we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule an hourly data collection for STATSPACK. The statsauto.sql script contains the following directive:

L 3-6

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

The important thing to note in this call to dbms_job.submit is the execution interval. The SYSDATE+1/24 is the interval that is stored in the Remote DBA_jobs view to produce hourly snapshots. You can change this as follows for different sample times. There are 1,440 minutes in a day, and you can use this figure to adjust the execution times.

Table 1 gives you the divisors for the snapshot intervals. 

Minutes per Day

Minutes between Snapshots

Required Divisor

1,440

60

24

1,440

30

48

1,440

10

144

1,440

5

288

Table 1: Determining the Snapshot Interval

Hence, if we want a snapshot every ten minutes we would issue the following command:

L 3-7

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

In the real world, you may have times where you want to sample the database over short time intervals. For example, if you have noticed that a performance problem happens every day between 4:00 p.m. and 5:00 p.m., you can request more frequent snapshots during this period.

For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto.sql script:

L 3-8

SQL> connect perfstat/perfstat;
Connected.
SQL> @statsauto
PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

      JOBNO
----------
         1


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE    VALUE
------------------------------------ ------- -----------------------------
job_queue_processes                  integer 1

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
         1 12-MAY-02 16:00:00

We can now see that a STATSPACK snapshot will automatically be executed every hour. We see that this is scheduled as job number 1, and we can use this job number to cancel this collection at any time using the dbms_job.remove procedure:

L 3-9

SQL> execute dbms_job.remove(1);

PL/SQL procedure successfully completed.

Now that we have installed and tested STATSPACK, we are ready to look at the configuration and maintenance issues.

(1)STATSPACK Configuration and Maintenance

This section will investigate the procedures for viewing and modifying your STATSPACK jobs. In this section we will examine a quick script for viewing your STATSPACK snapshots, adjusting the STATSPACK levels, and adjusting the STATSPACK thresholds for capturing SQL into the stats$sql_summary table.

(2)Viewing STATSPACK Snapshots

Because STATSPACK reports are generally made by comparing a starting snapshot with an ending snapshot, it is useful to have a tool to quickly display all of the available snapshots. To see the snapshots, you can enter a query directly from SQL*Plus.  Here is an example from Oracle8i.

L 3-10

SQL> select * from stats$snapshot;

   SNAP_ID       DBID INSTANCE_NUMBER SNAP_TIME STARTUP_T SESSION_ID   
ERIAL#
---------- ---------- ---------- ---------- ---------- --------- ---------
SNAP_LEVEL
----------
UCOMMENT
----------------------------------------------------------------------------
EXECUTIONS_TH PARSE_CALLS_TH DISK_READS_TH BUFFER_GETS_TH
------------- -------------- ------------- --------------
         1 2289877879            1 12-MAY-02 12-MAY-02          0          0
         5

          100           1000          1000          10000

Here is the same query against Oracle9i.

SQL> select * from stats$snapshot;

   SNAP_ID       DBID INSTANCE_NUMBER SNAP_TIME STARTUP_T SESSION_ID    SERIAL#
---------- ---------- --------------- --------- --------- ---------- ----------
SNAP_LEVEL
----------
UCOMMENT
--------------------------------------------------------------------------------
EXECUTIONS_TH PARSE_CALLS_TH DISK_READS_TH BUFFER_GETS_TH SHARABLE_MEM_TH
------------- -------------- ------------- -------------- ---------------
VERSION_COUNT_TH ALL_I
---------------- -----
          100           1000          1000          10000         1048576
              20 FALSE

An easier method to see your snapshots is to invoke a UNIX shell script that you can call directly from the UNIX prompt. The script next quickly lists all of the snapshots in a single command. Let's look at this script, list_snaps.ksh.

The script below is a UNIX Korn shell script that will work in any UNIX server environment.

(3)list_snaps.ksh

L 3-11

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus –s perfstat/perfstat<<!
select
   name,
   snap_id,
   to_char(snap_time,' dd Mon YYYY HH24:mi:ss') 
from
   stats\$snapshot,
   v\$database
order by
   snap_id
;
exit
!

When executed from the UNIX prompt, this script gives you a fast and complete description of all available snapshots. Let's now execute this script and see the results:

L 3-12

UNIX> list_snaps.ksh
NAME         SNAP_ID TO_CHAR(SNAP_TIME,'DD
--------- ---------- ---------------------
PRODB1             1  12 May 2002 15:30:03
PRODB1             2  12 May 2002 15:51:09
PRODB1             3  12 May 2002 15:51:14
PRODB1             4  12 May 2002 15:51:57
PRODB1             5  12 May 2002 15:52:02
PRODB1             6  12 May 2002 15:54:26
PRODB1             7  12 May 2002 15:54:31

Here we see each snapshot, the snapshot number, and the date of the snapshot. This script is useful because we must be careful never to run queries that span times where the database instance has been shut down and restarted. As we know, this resets the v$ accumulators, and causes invalid STATSPACK reports.

Now let's look at how we can restrict the amount of SQL that is collected in a snapshot by setting the collection thresholds.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter