statspack安裝與使用

1.安裝statspack
 1)建立表空間perfstat
 SQL> create tablespace perfstat datafile 'E:\oracle\product\10.2.0\oradata\orcl\perfstat01.dbf' size 50m autoextend on next 10m;

表空間已建立。
 2)建立statspack
 SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spcreate.sql    --執行spcreate.sql指令碼
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
輸入 perfstat_password 的值:  perfstat    --輸入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
------------------------------ --------- ----------------------------
EAMCS                          PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
TB                             PERMANENT
TB1                            PERMANENT
TBS_TEST01                     PERMANENT
TEST                           PERMANENT
TESTING_LMT_ASSM               PERMANENT
TEST_UNIFORM                   PERMANENT
USERS                          PERMANENT

Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
輸入 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 *
Pressing will result in the database's default Temporary
tablespace (identified by *) being used.
...............
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> --  Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: spcpkg.sql 31-may-2005.14:07:53 cdgreen Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2005, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spcpkg.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     02/28/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     01/25/05 - 4143812
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     10/25/04 - 3970898
SQL> Rem    cdgreen     07/16/04 - 10g R2
SQL> Rem    vbarrier    03/18/04 - 3517841
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    07/31/03 - 2804307
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    01/28/03 - 10g F2: baseline, purge
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    10/29/02 - 2648471
SQL> Rem    cdialeri    09/11/02 - 1995145
SQL> Rem    vbarrier    04/18/02 - 2271895
SQL> Rem    vbarrier    03/20/02 - 2184504
SQL> Rem    spommere    03/19/02 - 2274095
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    spommere    02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem    spommere    02/08/02 - 2212357
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/09/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    hbergh      08/23/01 - 1940915: use substrb on sql_text
SQL> Rem    cdialeri    04/26/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/28/00 - sp_purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cgervasi    06/16/98 - Remove references to wrqs
SQL> Rem    cmlim       07/30/97 - Modified system events
SQL> Rem    gwood.uk    02/30/94 - Modified
SQL> Rem    densor.uk   03/31/93 - Modified
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
Creating Package STATSPACK...

程式包已建立。

沒有錯誤。
Creating Package Body STATSPACK...
程式包體已建立。

沒有錯誤。

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

2、檢查引數
 1)job_queue_processes>0
 2)timed_statistics=true
3、手動收集統計
 用perfstat使用者連線(如果剛執行完@spcreate,則oracle預設將當前使用者切換為perfstat)
執行statspack.snap可以產生系統快照，執行兩次，產生兩次快照
SQL> show user
USER 為 "PERFSTAT"
 SQL> exec statspack.snap
PL/SQL 過程已成功完成。

SQL> exec statspack.snap
PL/SQL 過程已成功完成。

4.生成報告
SQL>@E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spreport.sql

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26937943/viewspace-1669399/，如需轉載，請註明出處，否則將追究法律責任。