Oracle之Statspack

Oracle之Statspack

---

Statspack是一款oracle自带的性能分析工具。需要用具有sysdba权限的用户登陆进行安装

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1   安装步骤：
使用@spcreate安装
在$ORACLE_HOME/rdbms/admin目录下的spcreate.sql文件


创建表空间：
[oracle@test1 admin]:testdb> cd $ORACLE_HOME/rdbms/admin
[oracle@test1 admin]:testdb> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 8 15:37:18 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/testdb/system01.dbf
/app/oracle/oradata/testdb/sysaux01.dbf
/app/oracle/oradata/testdb/undotbs01.dbf
/app/oracle/oradata/testdb/users01.dbf
/app/oracle/oradata/testdb/dbadatatbs01.dbf
/app/oracle/oradata/testdb/system02.dbf
/app/oracle/oradata/testdb/perfstat.dbf

7 rows selected.

SQL> create tablespace perfstat datafile '/app/oracle/oradata/testdb/perfstat.dbf' size 2G;

注意创建的数据文件大小。Statspack的报表数据还是相当占空间的；


创建：
SQL> @spcreate

输入密码：123456
输入表空间：perfstat
输入临时表空间：temp


安装完成后，系统会自动切换到perfstat用户下。
如果安装过程中出现错误，可以通过执行@spdrop删除相关对象
然后检查日志，确认出错原因，再次执行@spcreate进行创建

SQL> show user;
USER is "PERFSTAT"
SQL>

安装完成！！！

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2   初步使用(生成report)
PERFSTAT用户下执行：生成两次采样

SQL> show user;
USER is "PERFSTAT"

SQL> execute statspack.snap

SQL> execute statspack.snap


执行@spreport生成报告
SQL> @spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2645472633 TESTDB              1 testdb

SP2-0311: string expected but not found


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 2645472633        1 TESTDB       testdb       test1

Using 2645472633 for database Id
Using          1 for instance number


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.  Pressing without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
testdb       TESTDB               1 08 Dec 2014 15:47     5
                                  2 08 Dec 2014 15:47     5

输入采样的起始值和结束值，注意在生成报告的时候所选择的采样不能跨越一次停机(即stats$snapshot中STARTUP_TIME相同)，不然会报错！！

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: /app/report.txt
报告文件的输出


从生成采样到生成报告的大致操作步骤就是这样，当然我们这样操作在实际业务过程中是不现实的，主要的问题有以下几条：
1 采样生成间隔时间太短，未必能真正体现出服务器在业务实际运行中的状态，并且时间太短的情况下，
   可能statspack运行本身都会对分析结果的生成造成影响，毕竟statspack也是在运行一堆sql，也会被计入统计范围内。
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
SQL> select job,schema_user,next_date,interval,what from user_jobs;


考虑到采样结果还是相当占空间，不能让其一直执行下去，我们再创建一个job，让它在明天凌晨自动停止采样job的执行。
Variable job number;
begin
  dbms_job.submit(:job,'dbms_job.broken(44,true);',trunc(sysdate+1),'null');
  commit;
end;
/


SQL> select snap_id,snap_time,startup_time from stats$snapshot;

重新执行@spreport，再生成一份报告看看吧。



如果你想删除某些statspack收集的历史采样，直接通过delete，指定snap_id即可：
SQL> delete stats$snapshot where snap_id=1;

已删除 1 行。

SQL> select snap_id,snap_time,startup_time from stats$snapshot;

   SNAP_ID SNAP_TIME           STARTUP_TIME
---------- ------------------- -------------------
         1 2014-12-08 15:47:36 2014-11-06 10:20:07
         2 2014-12-08 15:47:55 2014-11-06 10:20:07
         3 2014-12-08 16:02:19 2014-11-06 10:20:07

SQL>

如果你想删除所有的采样数据但保留statspack的库结构，oracle也提供了脚本，
执行@sptrunc即可。该脚本是通过truncate删除各表记录，效率比delete高很多。
如果你想彻底干掉本机安装的statspack的话，那就更简单了，
执行@spdrop，干净不留痕：）


注：如果你在执行@spxxx的时候报SP-0310错误，可能是SQLPATH路径未设，你可以通过三种方式解决：

1、本地设置SQLPATH：
> SET SQLPATH=F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN
如果用这种方式的话，本次会话结束SQLPATH就失效了，下次打开还得再次设置。

2、windows设置注册表(linux环境的话就设置环境变量)：
开始->运行->regedit
HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_ORADB10G->新建字符串值SQLPATH，数值数据：F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN

3、指定全路径：
SQL> @%ORACLE_HOME%\RDBMS\ADMIN\spreport.sql




+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3  查看report

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)

　　

statspack进行了什么操作。

SQL> select s.text,s.line from user_source s where type='PACKAGE BODY' and NAME='STATSPACK';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1360886/，如需转载，请注明出处，否则将追究法律责任。