轉自君三思大仙兒的statspack安裝,使用簡介

與刃爭鋒發表於2014-01-09

Statspack 是一款功能強大的，免費的，oracle自帶的效能分析工具。需要用具有sysdba許可權的使用者登陸進行安裝。

    C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"

    SQL*Plus: Release 10.2.0.1.0 - Production on  星期三 9月 5 09:23:33 2007

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

    連線到:

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

    With the Partitioning, OLAP and Data Mining options

    SQL>

    SQL> show user;

    USER is "SYS"

    SQL> set lines 100

    SQL> select file_name from dba_data_files;

    FILE_NAME

    ----------------------------------------------------

    F:\ORACLE\ORADATA\JSSWEB\SYSTEM01.DBF

    F:\ORACLE\ORADATA\JSSWEB\UNDOTBS01.DBF

    F:\ORACLE\ORADATA\JSSWEB\SYSAUX01.DBF

    F:\ORACLE\ORADATA\JSSWEB\USERS01.DBF

    F:\ORACLE\ORADATA\JSSWEB\JSSWEB.DBF

    SQL> create tablespace perfstat datafile ¨f:\oracle\oradata\jssweb\perfstat.dbf¨ size 2000m;

    表空間已建立。

此處需要注意建立的資料檔案大小。Statspack的報表資料還是相當佔空間的，特別是在多次連續取樣的情況下。

    SQL> @spcreate

    執行statspack的安裝包。該檔案物理存放於$oracle_home\rdbms\admin\spcreate.sql

    Choose the PERFSTAT user¨s password

    -----------------------------------

    Not specifying a password will result in the installation FAILING

    輸入 perfstat_password 的值:  perfstat

    perfstat

    Choose the Default tablespace for the PERFSTAT user

    ---------------------------------------------------

    Below is the list of online tablespaces in this database which can

    store user data.  Specifying the SYSTEM tablespace for the user¨s

    default tablespace will result in the installation FAILING, as

    using SYSTEM for performance data is not supported.

    Choose the PERFSTAT users¨s default tablespace.  This is the tablespace

    in which the STATSPACK tables and indexes will be created.

    TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE

    ------------------------------ --------- ----------------------------

    JSSWEB                         PERMANENT

    PERFSTAT                       PERMANENT

    SYSAUX                         PERMANENT *

    USERS                          PERMANENT

    Pressing  will result in STATSPACK¨s recommended default

    tablespace (identified by *) being used.

輸入使用者預設的表空間。就是我們剛建立的那個，以及臨時表空間。

    輸入 default_tablespace 的值:   perfstat

    ..........

    ..........

    輸入 temporary_tablespace 的值:  temp

    ..........

    ..........

    NOTE:

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

安裝完成後，系統會自動切換到perfstat使用者下。安裝過程中的日誌被記錄在spcpkg.lis。關於安裝日誌我看到網上不少文章中都提到其生成路徑與spcreate.sql相同，但是我這裡卻給建立到my documents目錄下了，不知道哪裡設定日誌儲存路徑。

如果安裝過程中出現錯誤，可以通過執行@spdrop刪除相關物件，然後檢查日誌，確認出錯原因，再次執行@spcreate進行建立。

    SQL> show user;

    USER is "PERFSTAT"

ok, 安裝完成了~

以perfstat使用者連線，如果你剛執行完@spcreate，則oracle預設將當前使用者切換為perfstat 。

　　首先生成兩次取樣。

    SQL>execute statspack.snap

    PL/SQL procedure successfully completed.

    SQL>execute statspack.snap

    PL/SQL procedure successfully completed.

執行@spreport生成報告(該檔案物理存放路徑與spcreate相同)。

    SQL> @spreport

    Current Instance

    ~~~~~~~~~~~~~~~~

       DB Id    DB Name     Inst Num Instance

    ----------- ------------ -------- ------------

     3398983194 JSSWEB              1 jssweb

    Instances in this Statspack schema

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       DB Id    Inst Num DB Name      Instance     Host

    ----------- -------- ------------ ------------ ------------

     3398983194        1 JSSWEB       jssweb       JSS

    Using 3398983194 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

    ------------ ------------ --------- ----------------- ----- --------------------

    jssweb       JSSWEB               1 05 9 月  2007 12:5     5

                                        9

                                      2 05 9 月  2007 13:0     5

                                        2

　　輸入取樣的起始值和結束值，注意在生成報告的時候所選擇的取樣不能跨越一次停機(即 stats$snapshot 中STARTUP_TIME相同 ) ，不然會報錯。

    Specify the Begin and End Snapshot Ids

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    輸入 begin_snap 的值:  1

    Begin Snapshot Id specified: 1

    輸入 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.

    輸入 report_name 的值:  d:\report.txt

　　報告檔案的輸出路徑。

    ................

    ................

    ................

    undo_tablespace               UNDOTBS1

    user_dump_dest                F:\ORACLE\PRODUCT\10.2.0\ADMIN\JS

              -------------------------------------------------------------

    End of Report ( d:\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 SCHEMA_USE NEXT_DATE      INTERVAL                       WHAT

    ---------- ---------- -------------- ------------------------------ -----------------------

            44 PERFSTAT    11 -9 月 -07     trunc(SYSDATE+1/24,¨HH¨)       statspack.snap;

　　2 、考慮到取樣結果還是相當佔空間，不能讓其一直執行下去，我們再建立一個job，讓它在明天凌晨自動停止取樣job的執行。

    Variable  job  number ;

    begin

      dbms_job.submit(:job, ¨dbms_job.broken(44,true);¨ ,trunc( sysdate + 1 ), ¨null¨ );

       commit ;

    end ;

    /

　　Ok, 竣工，等上幾個小時，看看取樣生成的怎麼樣了。

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

       SNAP_ID SNAP_TIME           STARTUP_TIME

    ---------- ------------------- -------------------

            11 2007-09-11 11:00:03 2007-09-11 09:16:05

            12 2007-09-11 12:00:03 2007-09-11 09:16:05

            13 2007-09-11 13:00:04 2007-09-11 09:16:05

             1 2007-09-10 12:59:56 2007-09-10 09:04:22

             2 2007-09-10 13:02:01 2007-09-10 09:04:22

　　重新執行@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

    ---------- ------------------- -------------------

            11 2007-09-11 11:00:03 2007-09-11 09:16:05

            12 2007-09-11 12:00:03 2007-09-11 09:16:05

            13 2007-09-11 13:00:04 2007-09-11 09:16:05

             2 2007-09-10 13:02:01 2007-09-10 09:04:22

    SQL>

　　如果你想刪除所有的取樣資料但保留statspack的庫結構，oracle也提供了指令碼，執行@sptrunc即可。該指令碼是通過truncate刪除各表記錄，效率比delete高很多。

　　如果你想徹底幹掉本機安裝的statspack的話，那就更簡單了，執行@spdrop，乾淨不留痕：）。

　　注：如果你在執行@spxxx的時候報SP-0310錯誤，可能是SQLPATH路徑未設，你可以通過三種方式解決：

　　1、 本地設定SQLPATH：

　　F:\> SET SQLPATH=F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN

　　如果用這種方式的話，本次會話結束SQLPATH就失效了，下次開啟還得再次設定。

　　2、 設定登錄檔(linux環境的話就設定環境變數)：

　　開始->執行->regedit

　　HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_ORADB10G-> 新建字串值SQLPATH，數值資料：F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN

　　3、 指定全路徑：

　　SQL> @F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spreport.sql

　　或

　　SQL> @%ORACLE_HOME%\RDBMS\ADMIN\spreport.sql

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 報告確實非常全面 。

　　下面我們來看一下statspack都進行了什麼操作。

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

　　哇好長的程式碼，找到snap，發現有兩個，procedure和function，主要的操作都在function中進行。通過檢視我們發現，statspack.snap主要就是複製v$tables中的記錄。然後我們再來分析一下spreport.sql的奧妙，o原來是呼叫sprepins.sql啊，再開啟看看，哇嘞，更長更復雜，指令碼套指令碼。

　　大致我們可以確認其是根據儲存在stats$tables中的資料做分析，並進行一些運算，將結果輸出。值的一提的是，sp*.sql中不少寫法還是很有意思，執行邏輯執行巧妙，各種函式運用靈活，值的花時間深入研究體會呀：）

　　這僅僅只是statspack自身的操作，至於根據其報告輔助分析，並做適當調優，那涉及到的東西就更多了。在我看來其報告可以分為兩類來看，一類是優化業務邏輯，重點是根據9-14中羅列的各條SQL語句進行語句級調優，這可能跟你的業務邏輯關聯緊密。另一類則是根據其它各項的報告，進行oracle體系結構上的調優，比如增加減少SGA、PGA快取池大小啦，調整回滾段之類oracle自身設定達到優化目的。俺原本認為statspack不過是個輔助分析的小玩易兒，花個二三天功夫還不就輕鬆掌握了，但是分析了其生成原理之後，俺恍然意識到原來的認識太膚淺，初始配置下的statspack不過是個模板，如果你希望更強大的功能，或者想針對某方面進行調優，那你完全可以對其指令碼做改造，打造滿足你自己需求的statspack。但要實現這點就不只是說說這麼簡單了，u not only very clearly in statspack的儲存、分析、運算了若指掌，but 要對oracle的各項機制及相互間的關聯洞若觀火(黑黑，什麼都不懂也是種幸福啊，起碼不用操心啦，有啥用啥唄) 。

　　同樣，如果你真的到了這個境界，那我想statspack對你而言又回到了原點，僅僅只是參考。佛教裡有“見山是山，見水是水”，“見山不是山，見水不是山”，“見山還是山，見水還是水”三重境界，如此比對，一一深合其意啊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25025926/viewspace-1068593/，如需轉載，請註明出處，否則將追究法律責任。

相關文章