statpack的安装和使用方法

共计 7099 个字符，预计需要花费 18 分钟才能阅读完成。

这篇文章主要讲解了“statpack 的安装和使用方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“statpack 的安装和使用方法”吧！

简介

Oracle Statspack 是用来诊断数据库性能的强有力的工具，通过 Statspack 我们很容易的确定 Oracle 的所有瓶颈，记录数据库的性能状态。

安装步骤

一、Statpack 原理：

运行 oracle 自带脚本，生成一系列的统计表。

生成快照，采样。

根据快照生成报告。

二、安装准备

检查部分参数值

–job_queue_process: 取值范围为 0 到 1000，总共可创建多少个 job 进程，为了能够建立自动任务，执行数据收集，此参数大于零

alter system set job_queue_processess=60;

—timed_statistics，设置为 true，使收集的时间信息存储在 V$sessstats 和 V$sysstats 等动态性能视图中，但会消耗资源，可以在使用 Satspack 之前设为 true，采样过后，把该参数动态修改为 false.
alter system set timed_statistics=true;
2.  脚本
使用此功能，需要运行 oracle 自带脚本，在数据库中生成一系列的表和视图，用于收集各种信息。脚本位于 %oracle_home%\rdbms\admin(224 数据库的该目录为 /oracle/app/product/10.2.0/Db_1/rdbms/admin)目录下，oracle816 下是一组以 stat 开头的文件，以后的版本是一组以 sp 开头的文件。

三、安装

 1.shell 中切换到 oracle 用户：su – oracle

2. 以 sysdba 身份登录 sqlplus。
9i 及以后版本，可以用 sys 用户以 sysdba 身份登录：sqlplus / as sysdba
(最好转到脚本所有目录 %oracle_home%\rdbms\admin，便于执行脚本)

3. 创建表空间，用于保存采样数据

create  tablespace  perfstat  datafile  /data/cams/perfstat.dbf   size 500m;

Statspack 的报表数据还是相当占空间的，特别是在多次连续采样的情况下，所以不能太小，最小 100M，否则创建对象会失败。

查询表空间：select file_name from dba_data_files;

4. 运行脚本，安装 statspack
在 sqlplus 中执行命令：

@$ORACLE_HOME/rdbms/admin/spcreate.sql

– 脚本会创建用户 perfstat，需要指定此用户密码。
输入 perfstat_password 的值: perfstat

– 需要输入用户 perfstat 使用的表空间：指定新建的表空间即可。
输入 default_tablespace 的值:   perfstat

– 需要指定用户 perfstat 使用的临时表空间。
输入 temporary_tablespace 的值: tmp10

– 安装成功，可以看到如下信息：
Creating Package STATSPACK…

程序包已创建。

没有错误。
Creating Package Body STATSPACK…

程序包体已创建。

没有错误。

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

– 查看错误信息
NT 下：
host find ORA- *.LIS
host find err- *.LIS

Unix 下：
grep ORA- *.lis
grep err *.lis

如果出现错误，可以运行脚本删除相关内容：@%oracle_home%\rdbms\admin\spdrop.sql
(注意：也要在 sysdba 下运行脚本删除相关对象)
然后再重新运行脚本安装。

这个安装过程创建了一系列的表，用于存入采样数据。
查看新建表：
select dt.table_name from dba_tables dt where dt.owner= PERFSTAT
conn perfstat/perfstat
select table_name from user_tables;

安装完成后，系统会自动切换到 perfstat 用户下。

四、测试 statpack

运行 statspack.snap 可以产生系统快照，运行两次，产生两次快照。

SQL execute statspack.snap;

PL/SQL 过程已成功完成。

SQL execute statspack.snap;

PL/SQL 过程已成功完成。

然后执行脚本 @%oracle_home%\rdbms\admin\spreport.sql 就可以生成基于两个时间点的报告。
– 需要输入起始快照 ID 和结束快照 ID，以及报告文件名。
(快照 ID 必须存在)
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 1
Begin Snapshot Id specified: 1

输入 end_snap 的值: 13
End   Snapshot Id specified: 13

输入 report_name 的值: e:\s1report.txt

– 查看产生的快照
select t.snap_id,to_char(t.snap_time, yyyy-mm-dd hh:mi:ss) as S_Time,t.snapshot_exec_time_s from STATS$SNAPSHOT t;

五、规划自动任务

安装之后，我们就可以设置定时任务，定时采样，收集数据。

使用脚本 spauto.sql 来定义自动任务。

@$ORACLE_HOME/rdbms/admin/spauto.sql

spauto.sql 在关键内容：
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);
 commit;
end;

执行 spquto，就建立了一个每小时执行一次的数据收集计划。

这个 Job 任务定义了收集数据 (执行 statspack.snap) 的时间间隔为 1 小时：
一天 24 小时，1440 分钟 –
1/24, HH 每小时一次
1/48, MI 每半小时一次
1/144, MI 每 10 分钟一次
1/288, MI 每 5 分钟一次

关于采样间隔，通常建议 1 小时，如有特殊需要，可以设置更短，如半小时。但不推荐更短。因为 statpack 的执行本身需要消息资源，太短的采样对系统的性能会产生较大的影响(甚至会使 statspack 的执行出现在采样数据中)

六．生成报告

同测试一样
用 perfstat 用户连接
执行脚本 %oracle_home%\rdbms\admin\spreport.sql，输入起始快照 ID 和结束快照 ID，以及报告文件名，生成 statspack 报告。

注：一次 statspack 报告不能跨越一次停机，但是之前或之后的连续区间，收集的信息依然有效，你可以选择之前或之后的采样生成 report.
如果中间停过机，会收到以下错误信息：

第 1 行出现错误:
ORA-20200: The instance was shutdown between snapshots 1 and 31
ORA-06512: 在 line 48

七、后续处理

1，移除定时任务
当你完成一个采样报告，你应该及时移除这个 job 任务。遗漏一个无人照顾的 job 是非常危险的……

– 查看任务并移除任务
select job, next_date, next_sec, what from user_jobs;
execute dbms_job.remove(ID)

2，删除历史数据

1)，删除采样数据
只要删除 stat$snapshot 数据表中的相应数据，其他表中的数据会相应的级连删除。
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id =166;

删除过程中，你可以看到所有相关的表都被锁定了。
select a.object_id, a.oracle_username, b.object_name
from v$locked_object a,dba_objects b
where a.object_id=b.object_id

2)，truncate 统计信息表：使用脚本 sptrunc.sql 删除所有的采样数据但保留 statspack 的库结构。
如果有大量数据，直接 delete 采样数据是非常缓慢的。使用脚本 sptrunc.sql 可以快速的删除所有统计信息。
查看脚本，可以看出是用 truncate table 截掉所有相关的表中的内容。
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
…
delete from STATS$DATABASE_INSTANCE;

3)，删除 statspack 所有数据和各种对象：使用脚本 spdrop.sql。全部删除，不留痕迹! 但有表空间在~~

八、调整 statspack 的收集门限

statspack 有两种类型的收集选项
level– 级别：控制收集数据的类型
threshold– 门限：设置收集的数据阀值
都保存在表 stats$statspack_parameter 中

1，级别(level)– 有三种快照级别，默认值为 5

– 查看当前 level 级别：
select snap_level from stats$statspack_parameter;

1)level=0：一般性能统计。包括等待事件，系统事件，系统统计，回滚段统计，行缓存，SGA，会话，锁，缓冲池统计等等。

2)level=5：在 level0 的基础上，增加 SQL 语句的收集。SQL 语句收集结果记录在 ststs$sql_summary 表中。

3)level=10：增加子锁统计，包括 level5 的所有内容，还会将附加子锁的入 stats$latc_children 表中。使用这个级别需要慎重，建议在 oracle support 的指导下进行。

可以通过 statspack 包修改缺省的级别设置，如：
SQL execute statspack.snap(i_snap_level= 0,i_modify_parameter= true
通过这条语句，
以后的收集级别都将是 0 级。

如果你只是想修改本次收集级别，可以忽略 i_modify_parameter 参数，如下：
SQL execute statspack.snap(i_snap_level=  

2，快照门限 – 只应用于 stats$sql_summary 表中获取的 Sql 语句。
因为每一个快照都会收集很数据，及 sql 语句，所以 stats$sql_summary 很快就会成为 statspack 中最大的表。

– 查看当前各种门限
select executions_th,disk_reads_th,parse_calls_th,buffer_gets_th from stats$statspack_parameter;

各种门限：
1)executions_th–sql 语句执行的数量(默认值 =100)
2)disk_reads_th–sql 语句执行的磁盘读入数量(默认值 =1000)
3)parse_calls_th–sql 语句执行的解析调用数量(默认值 =1000)
4)buffer_gets_th–sql 语句执行的缓冲区获取数量(默认值 =10000)

任何一个门限值超过以上参数就会产生一条记录。
通过调用 statspack.modify_statspack_parameter 函数我们可以改变门限的默认值：
SQL execute statspack.modify_statspack_parameter(i_buffer_get_th= 100000,i_disk_reads_th= 100000;

九．Statspack 报告分析

Statspack 报告分为如下部分
1.  数据库总体信息
含实例、版本、是否 RAC、CPU、物理内存、oracle 内存设置等等
2.  每秒每事务的资源消耗情况
3.  实例的各组件的命中率
4.  共享池总体情况(Shared Pool Statistics)
5.  等待时间最长的前 5 个等待事件(Top 5 Timed Events)
含前 5 等待事件，两次采样间 cpu 占用，内存分配等信息。Oracle 各版本等待事件并不完全相同，数量依版本升高而增加，关于各项等待事情的说明，三思之前的 学习动态性能表 系列文章中有过介绍，有心的朋友可以去搜搜看。
6. DB 所有等待事件(Wait Events)–Total wait time =0.001 的事件。
7.  后台等待事件(Background Wait Events)–Total wait time =0.001 的事件。
8.  柱状显示的等待事件(Wait Event Histogram)– 显示各等待事件不同响应时间的比例
9.  根据 CPU 开销进行排序的 SQL(SQL ordered by CPU)
10.  根据执行时间进行排序的 SQL(SQL ordered by Elapsed)
11.  根据 BufferGets 进行排序的 SQL(SQL ordered by Gets)
12.  根据物理读进行排序的 SQL(SQL ordered by Reads)
13.  根据执行次数排序的 SQL(SQL ordered by Executions)
14.  根据解析调用次数排序的 SQL(SQL ordered by Parse Calls)
15.  实例记录的各项活动的统计数据(Instance Activity Stats)
16.  表空间的 IO 统计(Tablespace IO Stats)
17.  数据文件的 IO 统计(File IO Stats)
18.  数据文件读柱状图形式统计(File Read Histogram Stats)
19. Buffer 池统计数据(Buffer Pool Statistics)– 含实例恢复的统计数据，buffer 池大小设置建议等等。
20. PGA 统计数据(PGA Aggr Target Stats)– 含 PGA 缓存命中率，柱状图形式的统计以及 PGA 设置建议等等。
21.  进程的内存占用情况(Process Memory Summary Stats)– 含占用内存较多的进程等。
22. undo 段摘要
23. undo 段统计
24.  锁存器的当前情况
25.  锁存器睡眠等待统计
26.  锁存器失败情况
27.  数据字典 cache 性能统计(Dictionary Cache Stats)
28.  库缓存的活动情况(Library Cache Activity)
29. Rule 集(Rule Sets)
30.  共享池设置建议(Shared Pool Advisory)
31. SGA 摘要(SGA Memory Summary)
32. SGA 统计信息(SQL Memory Statistics)
33.  系统参数(init.ora Parameters)

报告很长，确实非常全面。如何分析，还需要再学习。

十、实际应用

  经初步分析决定自动采集 8:00-18:00 这个时间段中 5 个时间点的快照，每两小时采集一次，并把每天的快照信息存储下来，对 10:00 和 14：00 这两个点的快照形成报告。

  在 sqlplus 中运行 5 个 job，这 5 个 job 每天定时对不同时间点自动生成快照，8 点采集的脚本内容：

 begin
 select instance_number into :instno from v$instance;
 dbms_job.submit(:jobno, statspack.snap; , trunc(sysdate) +(8*60)/(24*60), trunc(SYSDATE+1, HH) , TRUE, :instno);
 commit;
end;
其他时间点的 job 内容类似，只是更改 dbms_job_submit()函数的第二个参数值。

在 sqlplus 中执行这 5 个 job。

      由于要自动生成性能报告，而 admin 目录中的 spreport.sql 文件在执行的时候需要键入快照编号以及报告名称，所以对此文件进行了改写，改写之后执行该文件后会自动对当天 10 点和 16 点两个时间点的快照生成报告，报告名称命名方式为 sp_日期_起始快照编号_结束快照编号.txt 存储路径为 /oracle 目录下。自动生成报告的 sql 文件需定时执行，oracle 的 dbms_job.submit()不支持对 sql 文件的调用，所以采取在 shell 中调用   crontab 定时工具执行生成报告的 sql 文件：

在 oracle 用户下输入 crontab –e 0 23 * * * sqlplus sys/sys as sysdba @ /oracle/app/product/10.2.0/Db_1/rdbms/admin/spreport.sql

表示每天 23 点执行后面的任务

感谢各位的阅读，以上就是“statpack 的安装和使用方法”的内容了，经过本文的学习后，相信大家对 statpack 的安装和使用方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！