oracle Statspack安装使用攻略

标签: oracle statspack 攻略 | 发表时间:2013-02-18 19:52 | 作者:l106439814
出处:http://blog.csdn.net
介绍:
---
oracle Statspack从Oracle8.1.6被引入,马上成为DBA和Oracle专家用来诊断数据库性能的强有力工具。通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所有,记录数据库性能状态,也可以使远程技术人员迅速了解的的数据库运行状况。所以,了解和使用Statspack对于DBA来说至关重要。


在Statspack发布之前,我们经常用来诊断数据库的工具是两个脚本utlbstat.sql和utlestat.sql。BSTAT/ESTAT是一个非常简单的性能诊断工具。UTLBSTAT获得开始时很多V$视图的快照,UTLESTST通过先前的快照和当前视图生成一个报表。实际上此报表相当于statspack的两个采样点。
Statspack通过连续的采样,能够给我们提供很重要的趋势分析数据,这是一个巨大的进步。


所以,能够使用Statspack的环境,就尽量不使用BSTAT/ESTAT方式来诊断数据库问题。
(Oracle816以前的版本使用Statspack,需要使用statscbps.sql脚本建立一个V$buffer_pool_statistics视图。可以到oracle网站上下载。)


+++
原理:
---
1,运行oracle自带脚本,生成一系列的统计表。
2,生成快照,采样(运行statspack.snap可生成快照,一般通过自动任务生成快照)
3,根据快照生成报告


+++
一,准备
---


1,检查部分参数值
--job_queue_process:为了能够建立自动任务,执行数据收集,此参数必须大于0
alter system set job_queue_processess=6;


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


+++
二,安装
---


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


2,创建表空间,用于保存采样数据
create tablespace perfstat datafile 'e:\hs01\dat\perstat.ora'
size 100m
extent management local;
Statspack的报表数据还是相当占空间的,特别是在多次连续采样的情况下,所以不能太小,最小100M,否则创建对象会失败。


3,运行脚本,安装statspack
Oracle816:%oracle_home%\rdbms\admin\statscre.sql
816以后:%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;


+++
三,测试statspack
---


用perfstat用户连接
(如果你刚执行完@spcreate,则oracle默认将当前用户切换为perfstat 。)


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


SQL> execute statspack.snap;


PL/SQL 过程已成功完成。


SQL> execute statspack.snap;


PL/SQL 过程已成功完成。




然后执行脚本%oracle_home%\rdbms\admin\spreport.sql就可以生成基于两个时间点的报告。
--需要输入起始快照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;


+++
四,规划自动任务
---


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


1,使用系统脚本
使用脚本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的执行出现在采样数据中)


2,使用自定义语句


1)可以用以下语句创建一个job,我们设定其每个小时自动收集一次采样。
Variable  job  number ;
begin
  dbms_job.submit(:job, 'statspack.snap;' ,trunc( sysdate + 1 / 24 , 'hh24' ), 'trunc(sysdate+1/24,''hh24'')' );
   commit ;
end ;
/
查看当前job
set linesize 120
col schema_user for a12
col what for a20
col interval for a15
col next_d for a18


SQL> select job,schema_user,to_char(next_date,'yyyymmdd hhmiss') as next_D,interval,what from user_jobs;


       JOB SCHEMA_USER  NEXT_D             INTERVAL        WHAT
---------- ------------ ------------------ --------------- --------------------
        41 PERFSTAT     20100324 030000    trunc(sysdate+1 statspack.snap;
                                           /24,'hh24')


2)再创建一个job,让它在第二天凌晨自动停止采样job的执行。
查出job编号,执行以下语句


Variable  job  number ;
begin
  dbms_job.submit(:job, 'dbms_job.broken(41,true);' ,trunc( sysdate + 1 ), 'null' );
   commit ;
end ;
/


--查看任务
set linesize 120
col schema_user for a12
col what for a20
col interval for a15
col next_d for a18
select job, next_date, next_sec, what from user_jobs;
select job,schema_user,to_char(next_date,'yyyymmdd hhmiss') as next_D,interval,what from user_jobs;


--查看产生的快照
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;


+++
五,生成报告
同测试一样
用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。全部删除,不留痕迹!但有表空间在~~


+++
七,其它重要脚本
---
1,spuexp.par,数据导出用参数文件。主要内容如下:
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y


我们可以使用以下命令导出:
exp perfstat/perfstat parfile=spuexp.par
例:
E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>exp perfstat/perfstat parfile=spuexp.par


2,删除数据脚本:spdtab.sql--删除表及同义词。spdusr.sql删除用户


3,oracle92新增脚本
1)用于升级statspack对象的脚本,需要sysdba权限,升级前请先备份存在的schema数据。
spup90.sql:用于升级9.0版本的模式至9.2版本
spup817.sql:如果从statspack8.1.7升级,需要运行这个脚本
spup816.sql:从statspack8.1.6升级,需要运行这个脚本,然后运行spup817.sql。
2)sqrepsql.sql 用于根据给定的SQL Hash值生成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=>10);  (???是10么?)


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)


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

作者:l106439814 发表于2013-2-18 19:52:14 原文链接
阅读:73 评论:0 查看评论

相关 [oracle statspack 攻略] 推荐:

oracle Statspack安装使用攻略

- - CSDN博客数据库推荐文章
oracle Statspack从Oracle8.1.6被引入,马上成为DBA和Oracle专家用来诊断数据库性能的强有力工具. 通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所有,记录数据库性能状态,也可以使远程技术人员迅速了解的的数据库运行状况. 所以,了解和使用Statspack对于DBA来说至关重要.

通过statspack诊断物理dg数据库性能方法

- - CSDN博客推荐文章
  今天是2014-04-23,继续整理一下dg的内容,顺便学习温习. 对于物理dg来说,备库在11g是可以以只读模式打开,可以提供报表查询功能,往往我们在分析备库. 性能的时候需要查询大量的v$视图,那么在11G之后可以在主库获得备库的statspack性能数据,便于分析. 现在就看一下如果在主库上存储备用数据的statspack数.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前, Ksplice 为 Fedora, Ubuntu 免费提供该功能,对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中,同时停止对其他企业级 Linux 发行版的支持,将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

oracle license计算

- Fenng - eagle&#39;s home
Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的,可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.

Oracle Exadata初探

- - 技术改变世界 创新驱动中国 - 《程序员》官网
在我们看来,它是一个把硬件和软件根据合理的配置整合在一起的 Oracle数据库(在本文编写时是11gR2版本)平台. Exadata数据库机器包含了存储子系统,在存储层上运行着研发的新软件,这使得研发人员可以做一些在其他平台上无法完成的事情. 实际上,Exadata一开始是以一个存储系统形式诞生的,如果你跟参与研发此产品的人交谈,你经常会听到他们称存储组件为Exadata或者是SAGE (Storage Appliance for Grid Environments,网格环境存储设备),这是该产品研发项目的代码名称.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧. Oracle,MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大,国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业,包括运营商以及金融机构都已经开始加入到这个潮流之中.

[转]Oracle 碎片

- - 小鸥的博客
  当生成一个数据库时,它会 分成称为表空间( Tablespace )的多个逻辑段( Segment ),如系统( System )表空间 , 临时( Temporary )表空间等. 一个表空间可以包含多个数据范围( Extent )和一个或多个自由范围块,即自由空间( Free Space ).

oracle 索引

- - 数据库 - ITeye博客
        自动:在使用primary和unique后系统会自动创建唯一索引.         手动:create   index   索引名  on 表名(字段1,....). 查询表上有哪些索引(网上找的,能用,表名和索引名要大写). 1、查找表的所有索引(包括索引名,类型,构成列):. select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表.

Oracle索引

- - Oracle - 数据库 - ITeye博客
在关系数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快. 索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容. 对于数据库来说,索引是一个必选项,但对于现在的各种大型数据库来说,索引可以大大提高数据库的性能,以至于它变成了数据库不可缺少的一部分. singlecolumnorconcatenated  对一列或多列建所引.