oracle Statspack从Oracle被引入马上成为DBA和Oracle专家用来诊断数据库性能的强有力工具通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所有记录数据库性能状态也可以使远程技术人员迅速了解的的数据库运行状况所以了解和使用Statspack对于DBA来说至关重要
在Statspack发布之前我们经常用来诊断数据库的工具是两个脚本utlbstatsql和utlestatsqlBSTAT/ESTAT是一个非常简单的性能诊断工具UTLBSTAT获得开始时很多V$视图的快照UTLESTST通过先前的快照和当前视图生成一个报表实际上此报表相当于statspack的两个采样点
Statspack通过连续的采样能够给我们提供很重要的趋势分析数据这是一个巨大的进步
所以能够使用Statspack的环境就尽量不使用BSTAT/ESTAT方式来诊断数据库问题
(Oracle以前的版本使用Statspack需要使用statscbpssql脚本建立一个V$buffer_pool_statistics视图可以到oracle网站上下载)
原理
运行oracle自带脚本生成一系列的统计表
生成快照采样(运行statspacksnap可生成快照一般通过自动任务生成快照)
根据快照生成报告
一准备
检查部分参数值
job_queue_process为了能够建立自动任务执行数据收集此参数必须大于
alter system set job_queue_processess=;
timed_statistics设置为true使收集的时间信息存储在V$sessstats和V$sysstats等动态性能视图中但会消耗资源可以在使用Satspack之前设为true采样过后把该参数动态修改为false
alter system set timed_statistics=true;
脚本
使用此功能需要运行oracle自带脚本在数据库中生成一系列的表和视图用于收集各种信息
脚本位于%oracle_home%\rdbms\admin目录下oracle下是一组以stat开头的文件以后的版本是一组以sp开头的文件
二安装
以sysdba身份登录
i可以用internal用户登录sqlplus internal
i及以后版本可以用sys用户以sysdba身份登录sqlplus / as sysdba
(最好转到脚本所有目录%oracle_home%\rdbms\admin便于执行脚本)
创建表空间用于保存采样数据
create tablespace perfstat datafile e:\hs\dat\perstatora
size m
extent management local;
Statspack的报表数据还是相当占空间的特别是在多次连续采样的情况下所以不能太小最小M否则创建对象会失败
运行脚本安装statspack
Oracle%oracle_home%\rdbms\admin\statscresql
以后%oracle_home%\rdbms\admin\spcreatesql
脚本会创建用户perfstat需要指定此用户密码
输入 perfstat_password 的值: perfstat
需要输入用户perfstat使用的表空间指定新建的表空间即可
输入 default_tablespace 的值: perfstat
需要指定用户perfstat使用的临时表空间
输入 temporary_tablespace 的值: tmp
安装成功可以看到如下信息
Creating Package STATSPACK
程序包已创建
没有错误
Creating Package Body STATSPACK
程序包体已创建
没有错误
NOTE:
SPCPKG complete Please check spcpkglis for any errors
查看错误信息
NT下
host find ORA *LIS
host find err *LIS
Unix下
grep ORA *lis
grep err *lis
如果出现错误可以运行脚本删除相关内容@%oracle_home%\rdbms\admin\spdropsql
(注意也要在sysdba下运行脚本删除相关对象)
然后再重新运行脚本安装
这个安装过程创建了一系列的表用于存入采样数据
查看新建表
select dttable_name from dba_tables dt where dtowner=PERFSTAT
conn perfstat/perfstat
select table_name from user_tables;
三测试statspack
用perfstat用户连接
(如果你刚执行完@spcreate则oracle默认将当前用户切换为perfstat )
运行statspacksnap可以产生系统快照运行两次产生两次快照
SQL> execute statspacksnap;
PL/SQL 过程已成功完成
SQL> execute statspacksnap;
PL/SQL 过程已成功完成
然后执行脚本%oracle_home%\rdbms\admin\spreportsql就可以生成基于两个时间点的报告
需要输入起始快照ID和结束快照ID以及报告文件名
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:
Begin Snapshot Id specified:
输入 end_snap 的值:
End Snapshot Id specified:
输入 report_name 的值: e:\sreporttxt
如果一切正常说明安装成功
查看产生的快照
select tsnap_idto_char(tsnap_timeyyyymmdd hh:mi:ss) as S_Timetsnapshot_exec_time_s from STATS$SNAPSHOT t;
四规划自动任务
安装之后我们就可以设置定时任务定时采样收集数据
使用系统脚本
使用脚本spautosql来定义自动任务
spautosql在关键内容
begin
select instance_number into :instno from v$instance;
dbms_jobsubmit(:jobno statspacksnap; trunc(sysdate+/HH) trunc(SYSDATE+/HH) TRUE :instno);
commit;
end;
执行spquto就建立了一个每小时执行一次的数据收集计划
这个Job任务定义了收集数据(执行statspacksnap)的时间间隔为小时
一天小时分钟>
/HH 每小时一次
/MI 每半小时一次
/MI 每分钟一次
/MI 每分钟一次
关于采样间隔通常建议小时如有特殊需要可以设置更短如半小时但不推荐更短因为statpack的执行本身需要消息资源太短的采样对系统的性能会产生较大的影响(甚至会使statspack的执行出现在采样数据中)
使用自定义语句
)可以用以下语句创建一个job我们设定其每个小时自动收集一次采样
Variable job number ;
begin
dbms_jobsubmit(:job statspacksnap; trunc( sysdate + / hh ) trunc(sysdate+/hh) );
commit ;
end ;
/
查看当前job
set linesize
col schema_user for a
col what for a
col interval for a
col next_d for a
SQL> select jobschema_userto_char(next_dateyyyymmdd hhmiss) as next_Dintervalwhat from user_jobs;
JOB SCHEMA_USER NEXT_D INTERVAL WHAT
PERFSTAT trunc(sysdate+ statspacksnap;
/hh)
)再创建一个job让它在第二天凌晨自动停止采样job的执行
查出job编号执行以下语句
Variable job number ;
begin
dbms_jobsubmit(:job dbms_jobbroken(true); trunc( sysdate + ) null );
commit ;
end ;
/
查看任务
set linesize
col schema_user for a
col what for a
col interval for a
col next_d for a
select job next_date next_sec what from user_jobs;
select jobschema_userto_char(next_dateyyyymmdd hhmiss) as next_Dintervalwhat from user_jobs;
查看产生的快照
select tsnap_idto_char(tsnap_timeyyyymmdd hh:mi:ss) as S_Timetsnapshot_exec_time_s from STATS$SNAPSHOT t;
五生成报告
同测试一样
用perfstat用户连接
执行脚本%oracle_home%\rdbms\admin\spreportsql输入起始快照ID和结束快照ID以及报告文件名生成statspack报告
一次statspack报告不能跨越一次停机但是之前或之后的连续区间收集的信息依然有效你可以选择之前或之后的采样生成report
如果中间停过机会收到以下错误信息
第 行出现错误:
ORA: The instance was shutdown between snapshots and
ORA: 在 line
六后续处理
移除定时任务
当你完成一个采样报告你应该及时移除这个job任务遗漏一个无人照顾的job是非常危险的……
查看任务并移除任务
select job next_date next_sec what from user_jobs;
execute dbms_jobremove(ID)
删除历史数据
)删除采样数据
只要删除stat$snapshot数据表中的相应数据其他表中的数据会相应的级连删除
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=;
删除过程中你可以看到所有相关的表都被锁定了
select aobject_id aoracle_username bobject_name
from v$locked_object adba_objects b
where aobject_id=bobject_id
)truncate统计信息表使用脚本sptruncsql删除所有的采样数据但保留statspack的库结构
如果有大量数据直接delete采样数据是非常缓慢的使用脚本sptruncsql可以快速的删除所有统计信息
查看脚本可以看出是用truncate table截掉所有相关的表中的内容
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
delete from STATS$DATABASE_INSTANCE;
)删除statspack所有数据和各种对象使用脚本spdropsql全部删除不留痕迹!但有表空间在~~
七其它重要脚本
spuexppar数据导出用参数文件主要内容如下
file=spuexpdmp log=spuexplog compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y
我们可以使用以下命令导出
exp perfstat/perfstat parfile=spuexppar
例
E:\oracle\product\\db_\RDBMS\ADMIN>exp perfstat/perfstat parfile=spuexppar
删除数据脚本spdtabsql删除表及同义词spdusrsql删除用户
oracle新增脚本
)用于升级statspack对象的脚本需要sysdba权限升级前请先备份存在的schema数据
spupsql用于升级版本的模式至版本
spupsql如果从statspack升级需要运行这个脚本
spupsql从statspack升级需要运行这个脚本然后运行spupsql
)sqrepsqlsql 用于根据给定的SQL Hash值生成SQL报告
九调整statspack的收集门限
statspack有两种类型的收集选项
level级别控制收集数据的类型
threshold门限设置收集的数据阀值
都保存在表stats$statspack_parameter中
级别(level)有三种快照级别默认值为
查看当前level级别
select snap_level from stats$statspack_parameter;
)level=一般性能统计包括等待事件系统事件系统统计回滚段统计行缓存SGA会话锁缓沖池统计等等
)level=在level的基础上增加SQL语句的收集SQL语句收集结果记录在ststs$sql_summary表中
)level=增加子锁统计包括level的所有内容还会将附加子锁的入stats$latc_children表中使用这个级别需要慎重建议在oracle support的指导下进行
可以通过statspack包修改缺省的级别设置如
SQL>execute statspacksnap(i_snap_level=>i_modify_parameter=>true);
通过这条语句
以后的收集级别都将是级
如果你只是想修改本次收集级别可以忽略i_modify_parameter参数如下
SQL>execute statspacksnap(i_snap_level=>); (？？？是么？)
快照门限只应用于stats$sql_summary表中获取的Sql语句
因为每一个快照都会收集很数据及sql语句所以stats$sql_summary很快就会成为statspack中最大的表
查看当前各种门限
select executions_thdisk_reads_thparse_calls_thbuffer_gets_th from stats$statspack_parameter;
各种门限
)executions_thsql语句执行的数量(默认值=)
)disk_reads_thsql语句执行的磁盘读入数量(默认值=)
)parse_calls_thsql语句执行的解析调用数量(默认值=)
)buffer_gets_thsql语句执行的缓沖区获取数量(默认值=)
任何一个门限值超过以上参数就会产生一条记录
通过调用statspackmodify_statspack_parameter函数我们可以改变门限的默认值
SQL>execute statspackmodify_statspack_parameter(i_buffer_get_th=>i_disk_reads_th=>;
十Statspack 报告分析
Statspack 报告分为如下部分
数据库总体信息
含实例版本是否RACCPU物理内存oracle内存设置等等
每秒每事务的资源消耗情况
实例的各组件的命中率
共享池总体情况(Shared Pool Statistics)
等待时间最长的前个等待事件(Top Timed Events)
含前等待事件两次采样间cpu占用内存分配等信息Oracle各版本等待事件并不完全相同数量依版本升高而增加关于各项等待事情的说明三思之前的学习动态性能表系列文章中有过介绍有心的朋友可以去搜搜看
DB 所有等待事件(Wait Events)Total wait time>= 的事件
后台等待事件(Background Wait Events)Total wait time>= 的事件
柱状显示的等待事件(Wait Event Histogram)显示各等待事件不同响应时间的比例
根据CPU开销进行排序的SQL(SQL ordered by CPU)
根据执行时间进行排序的SQL(SQL ordered by Elapsed)
根据BufferGets进行排序的SQL(SQL ordered by Gets)
根据物理读进行排序的SQL(SQL ordered by Reads)
根据执行次数排序的SQL(SQL ordered by Executions)
根据解析调用次数排序的SQL(SQL ordered by Parse Calls)
实例记录的各项活动的统计数据(Instance Activity Stats)
表空间的IO统计(Tablespace IO Stats)
数据文件的IO统计(File IO Stats)
数据文件读柱状图形式统计(File Read Histogram Stats)
Buffer 池统计数据(Buffer Pool Statistics)含实例恢复的统计数据buffer池大小设置建议等等
PGA 统计数据(PGA Aggr Target Stats)含PGA缓存命中率柱状图形式的统计以及PGA设置建议等等
进程的内存占用情况(Process Memory Summary Stats)含占用内存较多的进程等
undo 段摘要
undo 段统计
锁存器的当前情况
锁存器睡眠等待统计
锁存器失败情况
数据字典cache性能统计(Dictionary Cache Stats)
库缓存的活动情况(Library Cache Activity)
Rule 集(Rule Sets)
共享池设置建议(Shared Pool Advisory)
SGA 摘要(SGA Memory Summary)
SGA 统计信息(SQL Memory Statistics)
系统参数(initora Parameters)
