v$sesstat檢視和自開發工具

 

今天介紹一個Oracle中的重要效能檢視v$sesstat，以及基於該檢視自己開發的一個小效能評測工具包。

 

 

Oracle內部提供了很多監控效能的資料字典檢視，用來幫助統計各種級別的效能統計量。其中，v$sesstat是我們經常使用到的一個檢視。下面是Oracle官方文件中對該檢視的解釋。

 

“V$SESSTAT displays user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.”

 

官方對該檢視的解釋很簡單：就是現實使用者會話統計量。當使用者登入之後，該使用者活動和消耗都會以統計量的方式彙總到該檢視中。v$sesstat的結構很簡單。

 

 

SQL> desc v$sesstat;

Name       Type   Nullable Default Comments

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

SID        NUMBER Y                        

STATISTIC# NUMBER Y                        

VALUE      NUMBER Y                        

 

 

其中，SID表示進行彙總統計的session編號，有這個編號也就意味著該檢視中可以訪問到多個會話的統計資料。如果當前存在會話編號，並且進行資源消耗和使用並且被記錄下，這些資訊是可以在v$sesstat檢視中找到。但是，當會話結束，該檢視中就不包括該會話的統計資訊了。

 

 

statistic#列表是統計專案編號。在Oracle字典規範中，列名後帶上#表示編號的意思。v$sesstat檢視也是如此，statistic#列對應的統計專案的編號。而真正統計專案是什麼，則需要對應v$statname進行匹配。

 

最後就是value值，表示的是消耗的統計值。

 

 

v$sesstat資料字典記錄了一個尚在活動的session，從開始登陸起消耗的資源彙總量。當使用者會話session結束，該資源統計量也就消失了。

 

 

至於統計事件v$statname資料檢視，分別從不同的幾個級別進行統計量彙總。

 

根據v$sesstat的特點，我們可以二次開發出一些小工具來幫助我們進行日常診斷和處理。下面我們就介紹一個自定義開發的小工具包，用來統計一系列資料庫操作對資源的使用。

 

 

設計需求

 

我們進行Oracle程式碼編寫調優的時候，常常會希望知道一個程式碼片段究竟消耗了多少資源？分別是消耗了那些資源？這種時候，v$sesstat是最好的一個資料來源。

 

 

設計思路

 

v$sesstat能夠做到統計專案分類、會話獨立的目的，但是唯一的缺點是記錄累積量，也就是資源從會話開始到當前的消耗累積量。這時，可以使用snapshot的方法，在開始前保留一個資源資訊快照作為一個基線。之後執行完再看變化量。

 

 

實現程式碼

 

實現該工具一共分為四個步驟。

 

1、構建全域性的臨時表，儲存快照資料；

 

 

create global temporary table SYS_STATS

(

  STATISTIC# NUMBER(4),

  NAME       VARCHAR2(100),

  VALUE      NUMBER(20)

)

on commit preserve rows;

-- Add comments to the table

comment on table SYS_STATS

  is 'System Admin and Tunning Usage';

 

 

選擇臨時表的目的是防止會話資料之間進行相互資料干擾。選擇on commit preserver rows的目的在於映象之間消耗資源相互不影響。結構方面，sys_stats資料表和v$sesstat很像。

 

 

2、包體構建

 

以包方法的形式，進行方法控制。

 

create or replace package PCK_NBS_RUNSTATS is

 

  -- Public type declarations

  type t_tempstats is table of sys_stats%rowtype index by binary_integer;

 

  --start a snap for some session

  procedure start_snap(sid in number);

 

  --end a snap and output result

  procedure end_snap(sid in number);

 

end PCK_NBS_RUNSTATS;

 

（篇幅原因，只貼出了包宣告部分內容。詳細內容，請見附件檔案下載）

 

 

實現使用的方法很簡單。在進行操作之前，呼叫start_snap方法，引數為要監控session的編號。執行完操作之後，呼叫end_snap方法，彙總內容。如果sid引數輸入為空，則表示統計當前的數值。最後，將消耗量儲存在sys_stats資料表中，依據需要的形式進行展現。

 

 

3、結果查詢

 

查詢sys_stats資料表，就可以知道剛剛分析的資料統計量使用情況。還可以根據個人的情況，進行一定程度的排序。

 

 

 

使用例項

 

--開始監控

SQL> set serveroutput on size 10000;

SQL> exec pck_nbs_runstats.start_snap(null);

 

PL/SQL procedure successfully completed

 

 

SQL> select owner, count(*) from dba_objects group by owner;

 

OWNER                            COUNT(*)

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

MDSYS                                 885

（篇幅原因，省略）

OLAPSYS                               720

 

--結束映象

SQL> exec pck_nbs_runstats.end_snap(null);

 

PL/SQL procedure successfully completed

 

 

檢查sys_runstats資料表即可。比如我們希望知道剛才的分組group操作，資源消耗最大的四個操作是什麼？

 

 

SQL> col name for a30;

SQL> select * from (select * from sys_stats order by value desc ) where rownum<5;

 

STATISTIC# NAME                                           VALUE

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

        26 session pga memory max                       1900544

        21 session uga memory max                       1642256

        25 session pga memory                            114044

       320 buffer is pinned count                         53302

 

 

很清楚，就可以看到剛才的操作。因為使用了group by分組，所以使用PGA空間的數量比較大。同時，存在大量資料塊的讀寫pin操作，所以有一定的buffer pinned。

 

 

熟練掌握Oracle常用檢視的功能和使用，適當的自己開發一些有用的工具。可以大大簡化我們的日常工作，也可以增強我們對Oracle的瞭解。