oracle statspack 安裝詳解---03

oracle statspack詳解
這是我的學習筆記，根據eygle老師的及君三思的學習並整理的。
eygle老師的(原連結找不到了，放到我的共享裡了)：
http://down.qiannao.com/space/fi ... -002dv3.0.rar/.page
君三思的http://www.5ienet.com/note/html/ ... tup_induction.shtml
全面介紹oracle statspack的原理，安裝，使用，生成報告等過程和方法……。

+++
介紹:
---
oracle Statspack從Oracle8.1.6被引入，馬上成為DBA和Oracle專家用來診斷資料庫效能的強有力工具。通過Statspack我們可以很容易的確定Oracle資料庫的瓶頸所有，記錄資料庫效能狀態，也可以使遠端技術人員迅速瞭解的的資料庫執行狀況。所以，瞭解和使用Statspack對於DBA來說至關重要。

在Statspack釋出之前，我們經常用來診斷資料庫的工具是兩個指令碼utlbstat.sql和utlestat.sql。BSTAT/ESTAT是一個非常簡單的效能診斷工具。UTLBSTAT獲得開始時很多V$檢視的快照，UTLESTST通過先前的快照和當前檢視生成一個報表。實際上此報表相當於statspack的兩個取樣點。
Statspack通過連續的取樣，能夠給我們提供很重要的趨勢分析資料，這是一個巨大的進步。

所以，能夠使用Statspack的環境，就儘量不使用BSTAT/ESTAT方式來診斷資料庫問題。
(Oracle816以前的版本使用Statspack，需要使用statscbps.sql指令碼建立一個V$buffer_pool_statistics檢視。可以到oracle網站上下載。)

+++
原理：
---
1，執行oracle自帶指令碼，生成一系列的統計表。
2，生成快照，取樣(執行statspack.snap可生成快照，一般通過自動任務生成快照)
3，根據快照生成報告

+++
一，準備
---

1，檢查部分引數值
--job_queue_process：為了能夠建立自動任務，執行資料收集，此引數必須大於0
alter system set job_queue_processess=6;

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

+++
二，安裝
---

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

2，建立表空間，用於儲存取樣資料
create tablespace perfstat datafile 'e:\hs01\dat\perstat.ora'
size 100m
extent management local;
Statspack的報表資料還是相當佔空間的，特別是在多次連續取樣的情況下，所以不能太小，最小100M，否則建立物件會失敗。

3，執行指令碼，安裝statspack
Oracle816：%oracle_home%\rdbms\admin\statscre.sql
816以後：%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;

+++
三，測試statspack
---

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

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

SQL> execute statspack.snap;

PL/SQL 過程已成功完成。

SQL> execute statspack.snap;

PL/SQL 過程已成功完成。

然後執行指令碼%oracle_home%\rdbms\admin\spreport.sql就可以生成基於兩個時間點的報告。
--需要輸入起始快照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;

+++
四，規劃自動任務
---

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

1，使用系統指令碼
使用指令碼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分鐘--&gt
1/24,'HH' 每小時一次
1/48,'MI' 每半小時一次
1/144,'MI' 每10分鐘一次
1/288,'MI' 每5分鐘一次

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

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
set linesize 120
col schema_user for a12
col what for a20
col interval for a15
col next_d for a18

SQL> select job,schema_user,to_char(next_date,'yyyymmdd hhmiss') as next_D,interval,what from user_jobs;

       JOB SCHEMA_USER  NEXT_D             INTERVAL        WHAT
---------- ------------ ------------------ --------------- --------------------
        41 PERFSTAT     20100324 030000    trunc(sysdate+1 statspack.snap;
                                           /24,'hh24')

2)再建立一個job，讓它在第二天凌晨自動停止取樣job的執行。
查出job編號，執行以下語句

Variable  job  number ;
begin
  dbms_job.submit(:job, 'dbms_job.broken(41,true);' ,trunc( sysdate + 1 ), 'null' );
   commit ;
end ;
/

--檢視任務
set linesize 120
col schema_user for a12
col what for a20
col interval for a15
col next_d for a18
select job, next_date, next_sec, what from user_jobs;
select job,schema_user,to_char(next_date,'yyyymmdd hhmiss') as next_D,interval,what from user_jobs;

--檢視產生的快照
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;

+++
五，生成報告
同測試一樣
用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。全部刪除，不留痕跡!但有表空間在~~

+++
七，其它重要指令碼
---
1，spuexp.par，資料匯出用引數檔案。主要內容如下：
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y wner=PERFSTAT consistent=y

我們可以使用以下命令匯出：
exp perfstat/perfstat parfile=spuexp.par
例：
E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>exp perfstat/perfstat parfile=spuexp.par

2，刪除資料指令碼：spdtab.sql--刪除表及同義詞。spdusr.sql刪除使用者

3，oracle92新增指令碼
1)用於升級statspack物件的指令碼，需要sysdba許可權，升級前請先備份存在的schema資料。
spup90.sql：用於升級9.0版本的模式至9.2版本
spup817.sql：如果從statspack8.1.7升級，需要執行這個指令碼
spup816.sql：從statspack8.1.6升級，需要執行這個指令碼，然後執行spup817.sql。
2)sqrepsql.sql 用於根據給定的SQL Hash值生成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);  (？？？是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)

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

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