Statspackでの情報収集(10.2)

Statspackのインストール

初期パラメータの調整

JOB_QUEUE_PROCESSES > 0

alter system set job_queue_processes=5 scope=both;

TIMED_STATISTICS=TRUE 10.2はデフォルトでTRUEになっている。

alter system set timed_statistics=true scope=both;

Statspack用スクリプトの確認

PROD(SYS)>host ls -l $ORACLE_HOME/rdbms/admin/sp*
-rw-r-----  1 oracle oinstall   1771 Mar 15  2000 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
-rw-r-----  1 oracle oinstall 198869 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
-rw-r-----  1 oracle oinstall    861 May 17  2002 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
-rw-r-----  1 oracle oinstall  82281 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
-rw-r-----  1 oracle oinstall  15247 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
-rw-r-----  1 oracle oinstall 149763 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdoc.txt
-rw-r-----  1 oracle oinstall    758 Jun 19  2000 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdrop.sql
-rw-r-----  1 oracle oinstall   7479 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdtab.sql
-rw-r-----  1 oracle oinstall   1669 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdusr.sql
-rw-r-----  1 oracle oinstall   4900 Mar  7  2003 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sppurge.sql
-rw-r-----  1 oracle oinstall   5193 Mar 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
-rw-r-----  1 oracle oinstall 252083 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
-rw-r-----  1 oracle oinstall   1284 Apr 23  2001 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql
-rw-r-----  1 oracle oinstall   1268 Oct 11  2002 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepsql.sql
-rw-r-----  1 oracle oinstall  33643 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprsqins.sql
-rw-r-----  1 oracle oinstall   4604 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sptrunc.sql
-rw-r-----  1 oracle oinstall    588 Mar 15  2000 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spuexp.par
-rw-r-----  1 oracle oinstall  23049 May 31  2005 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spup10.sql
-rw-r-----  1 oracle oinstall  30938 Mar 23  2004 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spup816.sql
-rw-r-----  1 oracle oinstall  23615 Mar 23  2004 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spup817.sql
-rw-r-----  1 oracle oinstall  19412 Mar 23  2004 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spup90.sql
-rw-r-----  1 oracle oinstall  41707 Mar 23  2004 /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spup92.sql

Statspack専用表領域の作成(お勧め)

create tablespace perfstat datafile '/u01/app/oracle/oradata/prod/perfstat01.dbf' size 100M autoextend on maxsize 2G;

インストール

PROD(SYS)>@?/rdbms/admin/spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
perfstat

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
BATCH                          PERMANENT
EXAMPLE                        PERMANENT
INDX                           PERMANENT
OLTP                           PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY
TMP1                           TEMPORARY
TMP2                           TEMPORARY
TMP3                           TEMPORARY

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
....(略)

Statspackの使用と管理

自動情報収集ジョブの実行

?/rdbms/admin/spauto.sqlで情報収集ジョブを登録

SQL>@?/rdbms/admin/spauto.sql

自動情報収集ジョブの削除

PROD(PERFSTAT)>select job,what from dba_jobs;

       JOB WHAT
---------- --------------------------------------------------
         1 statspack.snap;

1 row selected.

PROD(PERFSTAT)>select job,what from dba_jobs;

       JOB WHAT
---------- --------------------------------------------------
         1 statspack.snap;

1 row selected.

PROD(PERFSTAT)>exec dbms_job.remove(1);

PL/SQL procedure successfully completed.

PROD(PERFSTAT)>commit;

Commit complete.

PROD(PERFSTAT)>

レポートの作成

PROD(PERFSTAT)>@?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  102094923 PROD                1 PROD

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  102094923        1 PROD         PROD         db-node01

Using  102094923 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
PROD         PROD                 1 30 Jun 2009 23:58     5
                                  2 01 Jul 2009 00:01     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: