statpack

 

 

 

D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 5月 20 16:31:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn /as sysdba
已连接。

 

 

参数设置：job_queue_processes和timed_statistics

查询job_queue_processes是否大于0：show parameter job_queue_processes;

如果=0，我们需要修改alter system set job_queue_processes=10;

查看timed_statistics是否为true

修改：alter system set timed_statistics=true;

create tablespace TEST_A '/home/oracle/test_A.dbf' size 300m extent management local; 

 

1、安装statspack软件
SQL> @spcreate

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

输入 perfstat_password 的值:  oracle
oracle

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
------------------------------ --------- ----------------------------
SYSAUX                         PERMANENT *
TEST_A                         PERMANENT
TEST_M                         PERMANENT
USERS                          PERMANENT

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

输入 default_tablespace 的值:  TEST_A

Using tablespace TEST_A 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 <return> will result in the database's default Temporary
tablespace (identified by *) being used.

输入 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.

....................
SQL> set echo off;
Creating Package STATSPACK...

程序包已创建。

没有错误。
Creating Package Body STATSPACK...

程序包体已创建。

没有错误。

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

 

2、必须用perfstat用户连接产生statspack

SQL> conn perfstat/oracle
已连接。

手动收集系统信息：
SQL> execute statspack.snap
PL/SQL 过程已成功完成。

SQL> execute statspack.snap
PL/SQL 过程已成功完成。

SQL> execute statspack.snap
PL/SQL 过程已成功完成。

查看：

SQL>  select snap_id,snap_time,startup_time from stats$snapshot;---必须要有两个到三个以上的快照，才能做statspack
   SNAP_ID SNAP_TIME STARTUP_T
---------- --------- ---------
1 20-MAY-13 20-MAY-13
11 20-MAY-13 20-MAY-13
13 20-MAY-13 20-MAY-13

12 20-MAY-13 20-MAY-13

产生statspack报告：

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

自动获取快照： 默认是1h收集一次统计信息
SQL> @spauto.sql

PL/SQL 过程已成功完成。

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

     JOBNO
----------
        24

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     1000

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

  JOB NEXT_DATE      NEXT_SEC
----- -------------- ----------------
   24 20-5月 -13     18:00:00

可以自动设置自动收集的时间
自动收集信息：
variable jobno number;
  variable instno number; 
begin 
select instance_number into :instno from v$instance;
 dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate+1/144,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); 
commit;
  end;

 

查看任务：
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

  JOB LOG_USER   PRIV_USER  LAST_DATE      NEXT_DATE      INTERVAL
----- ---------- ---------- -------------- -------------- ------------------------------
   23 PERFSTAT   PERFSTAT                  20-5月 -13     trunc(SYSDATE+1/24,'HH')

运行job：

execute dbms_job.run(21);

删除作业：
SQL> execute dbms_job.remove(23);

清理统计信息
 SQL>@sppurge         删除不再需要的数据（ID1-ID2）

SQL>@sptrunc         删除所有的数据  使用sppurge 删除数据需要较大的回滚段，

statpack的收集类型：

level级别：控制收集数据的类型。

threshold门限：设置收集的数据的乏值，默认的收集类型是5，我们可以更改她的收集类型；

i_modify_parameter true/false。是否将参数修改值作为缺省值

i_snap_level
>=0 collect genernal performance statistics
>=5 collect level 0 and high resource SQL
>=10 This level includes capturing Child Latch statistics, along with all data captured by lower levels

execute statpack.snap(i_snap_level=>10,i_modify_parameter=>'true');

statspack报告主要内容：
Instance efficientcy percentages
top wait event
top SQL
statistic
tablespace IO stats for DB
file IO stats for DB
buffer pool statistics for DB
rollback segment stats for DB
rollback segment storage for DB
latch activity for DB
dictionary Cache stats for DB
SGA memory summary for DB
init.ora parameters for DB
 

参考：http://hi.baidu.com/edeed/item/f780c81c09bbb57a7a5f256d

http://database.51cto.com/art/201108/283669.htm