ORACLE 9i中statpack的简单用法

维护的一套EBS系统用的数据库用的数据仍然是9i，最近一次月结的时候高峰期CPU占用率达100%，发现大量客户化的资产报表在运行，导致过账的请求被搁置，用户意见较大，打算升级硬件，于是要进行评估，先收集一些性能数据，只用用statpack了。用两种方式采集，一个是crontab，另一个是用ORACLE里面的job管理。

Drop 以前的perfstat用户下的所有对象和数据

bash-4.2$ sqlplus " / as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 14:37:06 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>   @?/rdbms/admin/spdrop
Dropping old versions (if any)

初始环境

会默认创建一个用户perfstat，最好提前创建一个专用的表空间。

SQL>   @?/rdbms/admin/spcreate

... Creating PERFSTAT user ...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

Specify PERFSTAT password
#输入用户密码
Enter value for perfstat_password: perfstat123
perfstat123

PL/SQL procedure successfully completed.

Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the STATSPACK tables
and indexes.  This will also be the PERFSTAT user's default tablespace.

Specify PERFSTAT user's default   tablespace
#输入监控数据存放的表空间
Enter value for default_tablespace: APPS_TS_TOOLS
Using APPS_TS_TOOLS for the default tablespace

PL/SQL procedure successfully completed.



Choose the PERFSTAT user's temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Specify PERFSTAT user's temporary tablespace.
#确定临时表空间
Enter value for temporary_tablespace: temp
Using temp for the temporary tablespace



No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
#创建完成

statpack sql脚本所在路径

$ cd $ORACLE_HOME

$ cd rdbms
$ cd admin

用ORACLE的job功能来实现自动采样

这地方可以自定义下

$ vi spauto.sql
"spauto.sql" 68 lines, 1892 characters 
Rem
Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
Rem
Rem spauto.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      spauto.sql
Rem
Rem    DESCRIPTION
Rem      SQL*PLUS command file to automate the collection of STATPACK
Rem      statistics.
Rem
Rem    NOTES
Rem      Should be run as the STATSPACK owner, PERFSTAT.
Rem      Requires job_queue_processes init.ora parameter to be
Rem      set to a number >0 before automatic statistics gathering
Rem      will run.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    12/06/99 - 1059172, 1103031
Rem    cdialeri    08/13/99 - Created
Rem


spool spauto.lis

"spauto.sql" 68 lines, 1892 characters
spool spauto.lis

--
--  Schedule a snapshot to be run on this instance every hour, on the hour

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
--  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
#这儿设置的是采样级别和采样频率，涉及到dbms_job包的用法和stat包的用法
  dbms_job.submit(:jobno, 'statspack.snap(i_snap_level=>7);', sysdate, 'trunc(SYSDATE+1/24/4,''MI'')', TRUE, :instno);
  commit;
end;
/


prompt
prompt  Job number for automated statistics collection for this instance
prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt  Note that this job number is needed when modifying or removing
prompt  the job:
print jobno

prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt  Below is the current setting of the job_queue_processes init.ora
prompt  parameter - the value for this parameter must be greater
prompt  than 0 to use automatic statistics gathering:
:q
$ 

开始采样

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 10:05:52 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> alter user perfstat identified by perf123;

User altered.

SQL> connect perfstat/perf123
Connected.
#执行完这个sql就把定时采集放到oracle里面的任务去了。
SQL> @spauto.sql

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


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     10


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- ----------------
      6274 08-DEC-15 10:06:16

SQL> desc dba_jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(16)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(16)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(16)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER
#看下任务的设置情况
SQL> select job,what from dba_jobs;
SQL> set linesize 132
SQL> set pagesize 999
SQL> /

23 rows selected.

SQL> col what for a50
SQL> / 

       JOB WHAT
---------- --------------------------------------------------

      6274 statspack.snap(i_snap_level=>7);

23 rows selected.

SQL> select job,what, broken  from dba_jobs;

       JOB WHAT                                               B
---------- -------------------------------------------------- -


      6274 statspack.snap(i_snap_level=>7);                   N

23 rows selected.

SQL> quit

用采样的snap快照来生成报告

SQL>  connect perfstat/my_perfstat_password
#用这个sql来生成报告

SQL>  @?/rdbms/admin/spreport

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1812013746 SDDEV               1 SDDEV



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

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1812013746        1 SDDEV        SDDEV        IT2SerpDT3

Using 1812013746 for database Id
Using          1 for instance number

Completed Snapshots

                               Snap                    Snap
Instance     DB Name             Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
SDDEV        SDDEV                1 08 Dec 2015 15:45     5
                                  2 08 Dec 2015 16:12     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#键入开始快照id
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
#键入结束快照id
Enter value for end_snap: 2
End   Snapshot Id specified: 2


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.
#键入快照名字，默认的是sp_1_2.lst，会放在当前的pwd文件夹下 
Enter value for report_name: second_stat_report

清除不需要的数据

用这个语句 select * from stats$snapshot;来查下快照信息

bash-4.2$ sqlplus perfstat/perfstat123

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 17:37:47 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
--执行这个命令然后输入要清除的起始id就ok了
SQL>  @?/rdbms/admin/sppurge


Database Instance currently connected to
========================================

                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
 1812013746 SDDEV             1 SDDEV


Snapshots for this database instance
====================================

          Snap
 Snap Id Level Snapshot Started      Host            Comment
-------- ----- --------------------- --------------- -------------------------
       1     5  08 Dec 2015 15:45:26 IT2SerpDT3
       2     5  08 Dec 2015 16:12:01 IT2SerpDT3


Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 1
Using 1 for lower bound.

Enter value for hisnapid: 2
Using 2 for upper bound.

Deleting snapshots 1 - 2.


Purge of specified Snapshot range complete.  If you wish to ROLLBACK
the purge, it is still possible to do so.  Exitting from SQL*Plus will
automatically commit the purge.

SQL> commit;

Commit complete.

暂停statpack收集

broken 方式

$ sqlplus "/ as sysdba"

SQL> conn perfstat/*********
Connected.

SQL> show user
USER is "PERFSTAT"
SQL> exec dbms_job.broken(6294,true)

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> select job,what,broken from dba_jobs where job=6294;

   JOB WHAT                                                    BROKEN
------ ------------------------------------------------------- ---------------------------------
  6294 statspack.snap;                                         Y

--如果重新启动的话用false参数
启动作业 
SQL> exec dbms_job.broken(1,false) 
PL/SQL 过程已成功完成。 
SQL>commit; 
--停其他用户的job 
SQL>exec sys.dbms_ijob.broken(98,true); 
SQL>commit; 

remove 方式

SQL> begin 
 dbms_job.remove(4); 
  end; 
  / 
PL/SQL procedure successfully completed 
SQL> commit; 
Commit complete