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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26937943/viewspace-1669399/