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';