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