上篇中我們已經介紹了Statspack工具最基本的使用：安裝解除安裝、Snapshot收集和基本報告生成。Oracle Statspack的靈活性是很強的，針對不同的分析需要和系統環境，我們可以在預設配置基礎上進行定製，更好的完成分析需求。

3、各種Statspack配置引數

我們在AWR中，是不需要關注Snapshot收集過程的。預設情況下，Oracle後臺程式MMON每個一個小時收集動態檢視資料進行儲存。在AWR中的調整主要集中在Snapshot收集頻率設定上。

而Statspack的設定內容要豐富的多。Statspack的Snapshot設定分為兩個大類：收集水平（Level）和效能閾值（threshold）。Snapshot的設定是作為每次收集的引數進行配置，並且可以在stats$snapshot基礎表中檢視到。

SQL> desc statspack.SNAP

Parameter Type Mode Default?

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

I_SNAP_LEVEL NUMBER IN Y

I_SESSION_ID NUMBER IN Y

I_UCOMMENT VARCHAR2 IN Y

I_NUM_SQL NUMBER IN Y

I_EXECUTIONS_TH NUMBER IN Y

I_PARSE_CALLS_TH NUMBER IN Y

I_DISK_READS_TH NUMBER IN Y

I_BUFFER_GETS_TH NUMBER IN Y

I_SHARABLE_MEM_TH NUMBER IN Y

I_VERSION_COUNT_TH NUMBER IN Y

I_SEG_PHY_READS_TH NUMBER IN Y

I_SEG_LOG_READS_TH NUMBER IN Y

I_SEG_BUFF_BUSY_TH NUMBER IN Y

I_SEG_ROWLOCK_W_TH NUMBER IN Y

I_SEG_ITL_WAITS_TH NUMBER IN Y

I_SEG_CR_BKS_SD_TH NUMBER IN Y

I_SEG_CU_BKS_SD_TH NUMBER IN Y

I_ALL_INIT VARCHAR2 IN Y

I_PIN_STATSPACK VARCHAR2 IN Y

I_MODIFY_PARAMETER VARCHAR2 IN Y

(RESULT) NUMBER

SQL> desc stats$snapshot;

Name Type Nullable Default Comments

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

SNAP_ID NUMBER(6)

DBID NUMBER

INSTANCE_NUMBER NUMBER

SNAP_TIME DATE

STARTUP_TIME DATE

SESSION_ID NUMBER

SERIAL# NUMBER Y

SNAP_LEVEL NUMBER Y

UCOMMENT VARCHAR2(160) Y

EXECUTIONS_TH NUMBER Y

PARSE_CALLS_TH NUMBER Y

DISK_READS_TH NUMBER Y

BUFFER_GETS_TH NUMBER Y

SHARABLE_MEM_TH NUMBER Y

VERSION_COUNT_TH NUMBER Y

SEG_PHY_READS_TH NUMBER

SEG_LOG_READS_TH NUMBER

SEG_BUFF_BUSY_TH NUMBER

SEG_ROWLOCK_W_TH NUMBER

SEG_ITL_WAITS_TH NUMBER

SEG_CR_BKS_SD_TH NUMBER

SEG_CU_BKS_SD_TH NUMBER

ALL_INIT VARCHAR2(5) Y

預設snapshot收集動作是可以不設定任何引數的，那個是因為Oracle對Statspack每個版本都有一個經驗值設定。注意：一般情況下，採用預設值可以應對大多數情況，不需要進行修改。

ü Snap_level收集水平

應該說，snapshot收集過程的兩個型別引數，分別反映了我們對效能資料收集的深度和廣度兩個維度範圍。Snap_level表示的收集的廣度，不同的level取值表示不同等級的收集廣度。

在之前的stats$snapshot基表中，我們是可以看到每個snapshot對應的level值的。針對Oracle 9i版本，預設取值為5。

SQL> select snap_id, dbid, to_char(snap_time, 'yyyy-mm-dd hh24:mi:ss') snap_time, snap_level from stats$snapshot;

SNAP_ID DBID SNAP_TIME SNAP_LEVEL

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

1 2629872507 2014-02-07 10:51:30 5

不同level的取值，對應不同的效能資料收集範圍。

如果取值0，表示常規效能資料收集，如wait statistics、system events、system statistics、rollback segment data、row cache、SGA、background events、session events, lock statistics、buffer pool statistics、parent latch statistics。

如果取值5，表示除了level=0對應的資料收集外，還要針對shared pool中的SQL遊標資訊進行收集。注意：針對不同的SQL我們可以設定閾值引數，來設定收集的深度。比如SQL執行次數、解析次數等。

如果取值6，表示除了level=5收集資料外，還要包括shared pool中SQL執行計劃和資源消耗。

如果取值7，表示新增了Segment Level的統計資訊，如資料塊變更、物理邏輯讀的次數。

如果取值10，表示在level=7的基礎上，新增了child latch資訊。

綜合來看，level越高，收集資訊廣度越大。那麼是不是越多越好，答案是否定的。資訊收集的範圍越多，意味著在進行snapshot收集的過程中需要消耗的資源就越多，時間也就越長。同時，儲存在基表中的資料也就越多，對空間也有一定壓力。

ü SQL thresholds

SQL threshold引數配置在snapshot方法中有很多。無論是SQL還是segment結構，在效能檢視中我們關注的都是資源消耗的“大戶”。如何定義大戶，是針對不同系統的實際情況而確定的。

各種thresholds指標，要取決於各自的需要。

ü 其他引數

除了兩大類引數外，statspack還包括一些控制引數，來控制收集行為。i_modify_parameter是一個true/false型別引數，它的作用在於：當我們使用一個與預設值有區別的引數值時，如果需要將其儲存在系統中作為新的預設值，就將這個引數設定為true。

i_session_id是更有針對性的引數，可以針對一個特定的session編號收集效能資料。如果一個執行作業時間很長，我們又可以確定出該作業session的編號，就可以使用i_session_id引數針對特定這個會話連線進行資料收集。這樣的報告形成更加有針對性，也可以將效能資料分割開來。

SQL> exec statspack.SNAP(i_snap_level => 10);

PL/SQL procedure successfully completed

SQL> select snap_id, dbid, to_char(snap_time, 'yyyy-mm-dd hh24:mi:ss') snap_time, snap_level from stats$snapshot;

SNAP_ID DBID SNAP_TIME SNAP_LEVEL

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

1 2629872507 2014-02-07 10:51:30 5

2 2629872507 2014-02-07 11:05:00 5

3 2629872507 2014-02-07 12:32:51 10

在9i中已經可以使用儲存過程的=》指示符。

4、Statspack中SQL report生成

在AWR報告中，我們可以依據報告中sql_id/hash_code，呼叫專門指令碼來生成特定SQL的執行計劃報告。在statspack中，我們也可以實現這樣的功能。

注意：生成執行計劃報告，要求level取值較高的snapshot收集範圍。

SQL> select snap_id, to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') start_time, snap_level from stats$snapshot;

SNAP_ID START_TIME SNAP_LEVEL

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

1 2014-02-07 10:51:30 5

2 2014-02-07 11:05:00 5

3 2014-02-07 12:32:51 10

11 2014-02-07 14:52:44 10

12 2014-02-07 14:54:58 10

先生成11和12兩個snapshot的報告，找到sql語句ID資訊。

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

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

11 11 1.0 0.1 0.00 0.00 114078687

select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob

j#=:1

在statspack報告中，我們沒有sql_id使用，而是hash code。將特定語句hash code記錄下來，將來輸入到語句報告中。

呼叫程式指令碼：

SQL> @?/rdbms/admin/sprepsql.sql

DB Id DB Name Inst Num Instance

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

2629872507 ORA9I 1 ora9i

Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

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

ora9i ORA9I 1 07 2月 2014 10:5 5

1

2 07 2月 2014 11:0 5

5

3 07 2月 2014 12:3 10

2

11 07 2月 2014 14:5 10

2

12 07 2月 2014 14:5 10

4

根據互動提示輸入snapshot range和hash code。

Specify the Begin and End Snapshot Ids

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

輸入 begin_snap 的值:

Specify the Hash Value

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

輸入 hash_value 的值: 114078687

Hash Value specified is: 114078687

生成文字報告中，就可以方便檢視到SQL語句相關消耗和執行計劃。

Start Id Start Time End Id End Time Duration(mins)

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

11 07-2月 -14 14:52:44 12 07-2月 -14 14:54:58 2.23

SQL Statistics

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

-> CPU and Elapsed Time are in seconds (s) for Statement Total and in

milliseconds (ms) for Per Execute

% Snap

Statement Total Per Execute Total

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

Buffer Gets: 11 1.0

.14

Disk Reads: 0 0.0

.00

Rows processed: 0 0.0

CPU Time(s/ms): 0 .0

Elapsed Time(s/ms): 0 .0

Sorts: 0 .0

Parse Calls: 7 .6

Invalidations: 0

Version count: 1

Sharable Mem(K): 9

Executions: 11

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

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

|SELECT STATEMENT |----- 3275652605 ----| | | |

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

5、結論

綜合性的效能診斷工具，是我們日常工作進行分析診斷的重要助力。從Statspack到今天的AWR，自動化、智慧化效能診斷已經成為的工具發展方向。

Oracle 9i配置Statspack工具（下）

相關文章