|
|
Oracle STATSPACK installation steps
Don Burleson
|
Step 1: Create the perfstat Tablespace
The STATSPACK utility requires an isolated
tablespace to contain all of the objects and data. For uniformity, it
is suggested that the tablespace be called perfstat, the same name as
the schema owner for the STATSPACK tables. Note that I have
deliberately not used the AUTOEXTEND option. It is important for the
Oracle DBA to closely watch the STATSPACK data to ensure that the
stats$sql_summary table is not taking an inordinate amount of space.
We will talk about adjusting the STATSPACK thresholds later in this
chapter.
Next, we create a tablespace called perfstat
with at least 180 megabytes of space in the datafile:
>sqlplus /
SQL*Plus: Release 8.1.6.0.0 - Production on Tue Dec 12 14:08:11 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production
SQL> create tablespace perfstat
2 datafile '/u03/oradata/prodb1/perfstat.dbf'
size 500m;
Step 2: Run the create Scripts
Now that the tablespace exists, we can begin
the installation process of the STATSPACK software.
Because of the version differences, we will
break this section into one for pre-8.1.7 installation and another for
post-8.1.7 installs.
Run the pre-8.1.7 install scripts
The statscre.sql script creates a user called
PERFSTAT, executes the script to create all of the STATSPACK tables,
and installs the STATSPACK PL/SQL package. When you run this script,
you will be prompted for the following information:
·
Specify PERFSTAT user's default tablespace: perfstat
·
Specify PERFSTAT user's temporary tablespace: temp
·
Enter tablespace where STATSPACK objects will be
created: perfstat
Install Prerequisites
Note that you must have performed the following
before attempting to install STATSPACK:
1.
Run catdbsyn.sql when connected as SYS.
2.
Run dbmspool.sql when connected as SYS.
3.
Allocate a tablespace called perfstat with at least 180 megabytes of
storage.
NOTE: The STATSPACK scripts are designed to stop
whenever an error is encountered. The statsctab.sql script contains
the SQL*Plus directive whenever
sqlerror exit;. This means that the script will cease
execution if any error is encountered. If you encounter an error and
you need to restart the script, just comment out the
whenever sqlerror exit line
and run the script again. Also, note that the STATSPACK install script
contains SQL*Plus commands. Hence, be sure you run it from SQL*Plus
and do not try to run it in SVRMGRL or SQL*Worksheet.
Once you have completed running the
spcreate.sql script, you will need to ensure that you do not have
errors. The STATSPACK utility creates a series of files with the .lis
extension as shown here:
prodb2-/u01/app/oracle/product/8.1.6_64/rdbms/admin
>ls -al *.lis
-rw-r--r-- 1 oracle oinstall 4170 Dec 12 14:28 spctab.lis
-rw-r--r-- 1 oracle oinstall 3417 Dec 12 14:27 spcusr.lis
-rw-r--r-- 1 oracle oinstall 201 Dec 12 14:28 spcpkg.lis
To check for errors, you need to look for any
lines that contain "ORA-" or the word "error", since the presence of
these strings indicates an error. If you are using Windows NT, you can
check for errors by searching the output file in MS Word. However,
most Oracle administrators on NT get a freeware grep for DOS, which is
readily available on the Internet.
The code here shows the UNIX grep
commands that are used to check for creation errors.
mysid-/u01/app/oracle/product/9.0.2/rdbms/admin>
grep ORA- *.lis
mysid-/u01/app/oracle/product/9.0.2/rdbms/admin> grep -i error *.lis
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:STATSCUSR complete. Please check spcusr.lis for any
errors.
spcpkg.lis:No errors.
Now that we have installed the user, tables,
indexes, and the package, we are ready to start collecting STATSPACK
data. We will begin by testing the STATSPACK functionality and then
schedule a regular STATSPACK collection job.
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:
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:
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 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:
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:
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:
SQL>
execute dbms_job.remove(1);
PL/SQL procedure successfully completed.
|