安裝步驟

一、Statpack原理：

執行oracle自帶指令碼，生成一系列的統計表。
生成快照，取樣。
根據快照生成報告。

二、安裝準備

檢查部分引數值

--job_queue_process:取值範圍為0到1000，總共可建立多少個job程式，為了能夠建立自動任務，執行資料收集，此引數大於零

alter system set job_queue_processess=60;

---timed_statistics，設定為true，使收集的時間資訊儲存在V$sessstats和V$sysstats等動態效能檢視中，但會消耗資源，可以在使用Satspack之前設為true，取樣過後，把該引數動態修改為false.
alter system set timed_statistics=true;
2. 指令碼
使用此功能，需要執行oracle自帶指令碼，在資料庫中生成一系列的表和檢視，用於收集各種資訊。指令碼位於%oracle_home%\rdbms\admin(224資料庫的該目錄為/oracle/app/product/10.2.0/Db_1/rdbms/admin)目錄下，oracle816下是一組以stat開頭的檔案，以後的版本是一組以sp開頭的檔案。

三、安裝

1.shell中切換到oracle使用者：su - oracle

2.以sysdba身份登入sqlplus。
9i及以後版本，可以用sys使用者以sysdba身份登入：sqlplus / as sysdba
(最好轉到指令碼所有目錄%oracle_home%\rdbms\admin，便於執行指令碼)

3.建立表空間，用於儲存取樣資料

create tablespace perfstat datafile '/data/cams/perfstat.dbf' size 500m;

Statspack的報表資料還是相當佔空間的，特別是在多次連續取樣的情況下，所以不能太小，最小100M，否則建立物件會失敗。

查詢表空間：select file_name from dba_data_files;

4.執行指令碼，安裝statspack
在sqlplus中執行命令：

@$ORACLE_HOME/rdbms/admin/spcreate.sql

--指令碼會建立使用者perfstat，需要指定此使用者密碼。
輸入 perfstat_password 的值: perfstat

--需要輸入使用者perfstat使用的表空間：指定新建的表空間即可。
輸入 default_tablespace 的值: perfstat

--需要指定使用者perfstat使用的臨時表空間。
輸入 temporary_tablespace 的值: tmp10

--安裝成功，可以看到如下資訊：
Creating Package STATSPACK...

程式包已建立。

沒有錯誤。
Creating Package Body STATSPACK...

程式包體已建立。

沒有錯誤。

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

--檢視錯誤資訊
NT下：
host find "ORA-" *.LIS
host find "err-" *.LIS

Unix下：
grep ORA- *.lis
grep err *.lis

如果出現錯誤，可以執行指令碼刪除相關內容：@%oracle_home%\rdbms\admin\spdrop.sql
(注意：也要在sysdba下執行指令碼刪除相關物件)
然後再重新執行指令碼安裝。

這個安裝過程建立了一系列的表，用於存入取樣資料。
檢視新建表：
select dt.table_name from dba_tables dt where dt.owner='PERFSTAT'
conn perfstat/perfstat
select table_name from user_tables;

安裝完成後，系統會自動切換到perfstat使用者下。

四、測試statpack

執行statspack.snap可以產生系統快照，執行兩次，產生兩次快照。

SQL> execute statspack.snap;

PL/SQL 過程已成功完成。

SQL> execute statspack.snap;

PL/SQL 過程已成功完成。

然後執行指令碼@%oracle_home%\rdbms\admin\spreport.sql就可以生成基於兩個時間點的報告。
--需要輸入起始快照ID和結束快照ID，以及報告檔名。
(快照ID必須存在)
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值:  1
Begin Snapshot Id specified: 1

輸入 end_snap 的值:  13
End Snapshot Id specified: 13

輸入 report_name 的值:  e:\s1report.txt

--檢視產生的快照
select t.snap_id,to_char(t.snap_time,'yyyy-mm-dd hh:mi:ss') as S_Time,t.snapshot_exec_time_s from STATS$SNAPSHOT t;

五、規劃自動任務

安裝之後，我們就可以設定定時任務，定時取樣，收集資料。

使用指令碼spauto.sql來定義自動任務。

@$ORACLE_HOME/rdbms/admin/spauto.sql

spauto.sql在關鍵內容：
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;

執行spquto，就建立了一個每小時執行一次的資料收集計劃。

這個Job任務定義了收集資料(執行statspack.snap)的時間間隔為1小時：
一天24小時，1440分鐘-->
1/24,'HH' 每小時一次
1/48,'MI' 每半小時一次
1/144,'MI' 每10分鐘一次
1/288,'MI' 每5分鐘一次

關於取樣間隔，通常建議1小時，如有特殊需要，可以設定更短，如半小時。但不推薦更短。因為statpack的執行本身需要訊息資源，太短的取樣對系統的效能會產生較大的影響(甚至會使statspack的執行出現在取樣資料中)

六．生成報告

同測試一樣
用perfstat使用者連線
執行指令碼%oracle_home%\rdbms\admin\spreport.sql，輸入起始快照ID和結束快照ID，以及報告檔名，生成statspack報告。

注：一次statspack報告不能跨越一次停機，但是之前或之後的連續區間，收集的資訊依然有效，你可以選擇之前或之後的取樣生成report.
如果中間停過機，會收到以下錯誤資訊：

第 1 行出現錯誤:
ORA-20200: The instance was shutdown between snapshots 1 and 31
ORA-06512: 在 line 48

七、後續處理

1，移除定時任務
當你完成一個取樣報告，你應該及時移除這個job任務。遺漏一個無人照顧的job是非常危險的……

--檢視任務並移除任務
select job, next_date, next_sec, what from user_jobs;
execute dbms_job.remove('ID')

2，刪除歷史資料

1)，刪除取樣資料
只要刪除stat$snapshot資料表中的相應資料，其他表中的資料會相應的級連刪除。
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=166;

刪除過程中，你可以看到所有相關的表都被鎖定了。
select a.object_id, a.oracle_username, b.object_name
from v$locked_object a,dba_objects b
where a.object_id=b.object_id

2)，truncate統計資訊表：使用指令碼sptrunc.sql刪除所有的取樣資料但保留statspack的庫結構。
如果有大量資料，直接delete取樣資料是非常緩慢的。使用指令碼sptrunc.sql可以快速的刪除所有統計資訊。
檢視指令碼，可以看出是用truncate table截掉所有相關的表中的內容。
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
...
delete from STATS$DATABASE_INSTANCE;

3)，刪除statspack所有資料和各種物件：使用指令碼spdrop.sql。全部刪除，不留痕跡!但有表空間在~~

八、調整statspack的收集門限

statspack有兩種型別的收集選項
level--級別：控制收集資料的型別
threshold--門限：設定收集的資料閥值
都儲存在表stats$statspack_parameter中

1，級別(level)--有三種快照級別，預設值為5

--檢視當前level級別：
select snap_level from stats$statspack_parameter;

1)level=0：一般效能統計。包括等待事件，系統事件，系統統計，回滾段統計，行快取，SGA，會話，鎖，緩衝池統計等等。

2)level=5：在level0的基礎上，增加SQL語句的收集。SQL語句收集結果記錄在ststs$sql_summary表中。

3)level=10：增加子鎖統計，包括level5的所有內容，還會將附加子鎖的入stats$latc_children表中。使用這個級別需要慎重，建議在oracle support的指導下進行。

可以透過statspack包修改預設的級別設定，如：
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
透過這條語句，
以後的收集級別都將是0級。

如果你只是想修改本次收集級別，可以忽略i_modify_parameter引數，如下：
SQL>execute statspack.snap(i_snap_level=>10);

2，快照門限--只應用於stats$sql_summary表中獲取的Sql語句。
因為每一個快照都會收集很資料，及sql語句，所以stats$sql_summary很快就會成為statspack中最大的表。

--檢視當前各種門限
select executions_th,disk_reads_th,parse_calls_th,buffer_gets_th from stats$statspack_parameter;

各種門限：
1)executions_th--sql語句執行的數量(預設值=100)
2)disk_reads_th--sql語句執行的磁碟讀入數量(預設值=1000)
3)parse_calls_th--sql語句執行的解析呼叫數量(預設值=1000)
4)buffer_gets_th--sql語句執行的緩衝區獲取數量(預設值=10000)

任何一個門限值超過以上引數就會產生一條記錄。
透過呼叫statspack.modify_statspack_parameter函式我們可以改變門限的預設值：
SQL>execute statspack.modify_statspack_parameter(i_buffer_get_th=>100000,i_disk_reads_th=>100000;

九．Statspack 報告分析

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)

報告很長，確實非常全面。如何分析，還需要再學習。

十、實際應用

經初步分析決定自動採集8:00-18:00這個時間段中5個時間點的快照，每兩小時採集一次，並把每天的快照資訊儲存下來，對10:00和14：00這兩個點的快照形成報告。

在sqlplus中執行5個job，這5個job每天定時對不同時間點自動生成快照，8點採集的指令碼內容：

       begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate) +(8*60)/(24*60),'trunc(SYSDATE+1,''HH'')', TRUE, :instno);
  commit;
end;
其他時間點的job內容類似，只是更改dbms_job_submit()函式的第二個引數值。

在sqlplus中執行這5個job。

由於要自動生成效能報告，而admin目錄中的spreport.sql檔案在執行的時候需要鍵入快照編號以及報告名稱，所以對此檔案進行了改寫，改寫之後執行該檔案後會自動對當天10點和16點兩個時間點的快照生成報告，報告名稱命名方式為sp_日期_起始快照編號_結束快照編號.txt 儲存路徑為/oracle目錄下。自動生成報告的sql檔案需定時執行，oracle的dbms_job.submit()不支援對sql檔案的呼叫，所以採取在shell中呼叫 crontab定時工具執行生成報告的sql檔案：

在oracle使用者下輸入crontab –e

0 23 * * * sqlplus sys/sys as sysdba @ /oracle/app/product/10.2.0/Db_1/rdbms/admin/spreport.sql

表示每天23點執行後面的任務

statpack安裝使用詳解

安裝步驟

相關文章