Oracle Statspack安装使用
C:\Users\Administrator>cd \oracle\product\10.2.0\db_1\RDBMS\ADMINC:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>sqlplus /nolog
SQL*Plus:> Copyright (c) 1982, 2007, Oracle.All Rights Reserved.a
SQL> conn /as sysdba
已连接。
一.系统参数
1.job_queue_processes
为了建立自动任务，执行数据收集，该参数要大于0。可以在初始化参数文件中修改,可以使用both参数。
SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
2.timed_statistics
收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和SQL语句。
SQL> show parameter timed_statistics;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL>
二.安装Statspack
安装Statspack需要internal身份登录，或者拥有sysdba用户权限登录。需要在本地安装或者登录到服务器。
首先登录到数据库，转到$ORACLE_HOME\RDBMS\ADMIN
检查数据文件路径及磁盘空间，以决定创建数据文件的位置。
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------
C:\ORADATA\ORADB\USERS01.DBF
C:\ORADATA\ORADB\SYSAUX01.DBF
C:\ORADATA\ORADB\UNDOTBS01.DBF
C:\ORADATA\ORADB\SYSTEM01.DBF
SQL>
创建存储数据的表空间，如果采集间隔较短，周期较长，打算长期采集，需要大一点的表空间，
如果每半小时一次，采集一周，数据量是很大的。本例创建一个500MB测试空间。
SQL>create tablespace perfstat
datafile 'C:\ORADATA\ORADB\perfstat.DBF'
size 500M
extent management local;
SQL> create tablespace perfstat
2 datafile 'C:\ORADATA\ORADB\perfstat.DBF'
3 > 4 extent management local;
表空间已创建。
SQL> host dir /w sp*
驱动器 C 中的卷没有标签。
卷的序列号是 12D1-1A7F
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN 的目录
spauto.sql spcpkg.sql spcreate.sql spctab.sql spcusr.sql
spdoc.txt spdrop.sql spdtab.sql spdusr.sql sppurge.sql
sprepcon.sql sprepins.sql spreport.sql sprepsql.sql sprsqins.sql
sptrunc.sql spuexp.par spup10.sql spup816.sql spup817.sql
spup90.sql spup92.sql
22 个文件 925,371 字节
0 个目录 23,800,688,640 可用字节
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 CONTENTSSTATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressingwill 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 CONTENTSDB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
Pressingwill result in the database's default Temporary
tablespace (identified by *) being used.
输入 temporary_tablespace 的值:temp
.........
.........
.........
SQL>
SQL> set echo off;
Creating Package STATSPACK...
程序包已创建。
没有错误。
Creating Package Body STATSPACK...
程序包体已创建。
没有错误。
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> host dir *.lis
驱动器 C 中的卷没有标签。
卷的序列号是 12D1-1A7F
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN 的目录
2012/09/0622:39 202 spcpkg.lis
2012/09/0622:38 4,317 spctab.lis
2012/09/0622:38 2,268 spcusr.lis
3 个文件 6,787 字节
0 个目录 23,800,406,016 可用字节
SQL> host find "ORA-" *.lis
---------- SPCPKG.LIS
---------- SPCTAB.LIS
---------- SPCUSR.LIS
SQL> host find "err" *.lis
---------- SPCPKG.LIS
SPCPKG complete. Please check spcpkg.lis for any errors.
---------- SPCTAB.LIS
SPCTAB complete. Please check spctab.lis for any errors.
---------- SPCUSR.LIS
SPCUSR complete. Please check spcusr.lis for any errors.
SQL>
如果建立出错可以删除以上对象
SQL>@spdrop.sql
三、生成报告
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> execute statspack.snap
PL/SQL 过程已成功完成。
SQL> @spreport.sql
............
............
............
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.Pressingwithout
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap> ------------ ------------ --------- ----------------- ----- ------------------
oradb ORADB 1 06 9月2012 22:4 5
4
2 06 9月2012 22:4 5
4
Specify the Begin and End Snapshot> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:1
Begin Snapshot> 输入 end_snap 的值:2
End Snapshot> Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.To use this name,
pressto continue, otherwise enter an> 输入 report_name 的值:statspack_rept
..................
..................
..................
nls_territory CHINA
open_cursors 300
pga_aggregate_target 409993216
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 1241513984
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\OR
-------------------------------------------------------------
End of Report ( statspack_rept.lst )
SQL>
四、运行定时
1.定时配置
@ORACLE_HOME\RDBMS\ADMIN目录下spauto.sql文件
默认定时
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
定时配置
这个Job定义了收集数据的时间间隔：
一天有24个小时，1440分钟，那么：
1/24 HH 每小时一次
1/48 MI 每半小时一次（专家建议故障时间内，每15-30分钟执行一次）
1/144 MI 每十分钟一次
1/288 MI 每五分钟一次
我们可以修改spauto.sql来更改执行间隔，如
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
2.执行定时
然后执行spauto,这样我们就建立了一个每30分钟执行一次的数据收集计划。你可以查看spauto.lis来获得输出信息；
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\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
----------
21
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
---------- -------------- ----------------
21 11-9月 -12 20:03:00
SQL>
五、移动定时任务
1.显示定时任务
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
---------- ----------------------------------------------------------------------------------------
21 SYS SYS 11-9月 -12 trunc(SYSDATE+1/48,'MI')
2.移除定时任务
SQL> execute dbms_job.remove('21');
PL/SQL 过程已成功完成。
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
未选定行
SQL>
六、删除历史数据
1.删除采集数据
删除stats$snapshot数据表中得相应数据，其他表中的数据会级联删除
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
11
SQL> delete from stats$snapshot where snap_idselect * from stats$snapshot;
未选定行
2.查看锁定表
在删除数据过程中查看锁定的表
SQL> select a.object_id,a.oracle_username,b.object_name
2from v$locked_object a,dba_objects b
3where a.object_id=b.object_id;
oracle提供了直接trunk相关统计表的语句
$ORACLE_HOME\RDBMS\ADMIN目录下sptrunc.sql
七、调整STATSPACK的收集门限
Statspack有两种类型的收集选项：
级别（level）：控制收集数据的类型
门限（threshold）：设置收集数据的阀值
1.级别（level）
Statspack共有3种快照级别，默认值是5
a.level 0：一般性能统计，包括等待事件、系统事件、系统统计、回滚段统计、行缓存
、SGA、会话、锁、缓冲池统计等等。
b.level 5：增加SQL语句。除了包含level 0 所有内容，还包括SQL语句的收集，收集结果
记录在stats$sql_summary中。
c.level 10：增加子锁存统计。包含level 5 所有内容。并且还附加的子锁存存入stats$latc_children中。
在使用这个级别时需要慎重，建议在Oracle support的指导下进行。
可以通过statspack包修改缺省的级别设置
SQL>ececute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
如果只想本次改变级别，可以忽略i_modify_parameter参数。
SQL>ececute statspack.snap(i_snap_level=>10);
2.快照门限
快照门限只应用于stats$sql_summary中的SQL语句。
因为每次快照都会收集很多数据，每行代表获取快照时数据库中得一个SQL语句
，所以stats$sql_summary很快成为Statspack中最大的表。
门限在stats$statspack_parameter表中，让我们来了解一下门限：
a.executions_th 这是SQL语句中的执行数量，默认为100
b.disk_reads_th 这是SQL语句中执行的磁盘读入数量，默认为1000
c.parse_calls_th 这是SQL语句中执行的解析调用的数量，默认为1000
d.buffer_gets_th 这是SQL语句中执行的缓冲区获取的数量，默认为10000
任何一个门限值超过以上参数就会产生一条记录。
通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值。
SQL>execute statspack.modify_statspack_parameter（i_buffer_gets_th=>10000,i_disk_reads_th=>100000);
页:
[1]