1、Oracle 自帶性能診斷工具介紹
1.1 statspack (9i 必用,10g,11g,12c 兼容。)
Oracle Statspack 工具從 Oracle 8.1.6 開始引入,通過 Statspack 可以很容易地收集數據庫性能數據,并通過這些數據進而分析確定 Oracle 數據庫的瓶頸所在。
1.2 awr Oracle 10g 以后提供了一個新的工具:(AWR:Automatic Workload Repository)。
Oracle 建議用戶用這個取代 Statspack。AWR 實質上是一個 Oracle 的內置工具,它采集與性能相關的統計數據,并從那些統計數據中導出性能量度,以跟蹤潛在的問題。與 Statspack 不同,快照由一個稱為 MMON 的新的后臺進程及其從進程自動地每小時采集一次。
采集的數據時間(快照頻率和保留時間都可以由用戶修改):
- oracle10g 在 7 天后自動清除,
- oracle11g 在 8 天后清理。
1.3 addm
ADDM(Automatic Database Diagnostic Monitor) 是 Oracle 數據庫的一個自診斷引擎.ADDM 通過檢查和分析 AWR獲取的數據來判斷 Oracle數據庫中可能的問題.是 Oracle 內部的一個顧問系統,能夠自動的完成最數據庫的一些優化的建議,給出SQL 的優化,索引的創建,統計量的收集等建議。
1.4 ash ASH (Active Session History)
ASH 以 V$SESSION 為基礎,每秒采樣一次,記錄活動會話等待的事件。不活動的會話不會采樣,采樣工作由新引入的后臺進程 MMNL 來完成。
1.5 awrdd
AWRDD 是用于比較兩個 AWR 快照,從而獲得不同時期的性能。
1.6 AWRSQL
在 AWR 中定位到問題 SQL 語句后想要了解該 SQL statement 的具體執行計劃,于是就用 AWR 報告中得到的 SQL ID 去 V$SQL 等幾個動態性能視圖中查詢,但發現V$SQL 或 V$SQL_PLAN 視圖都已經找不到對應 SQL ID 的記錄,一般來說這些語句已經從 shared pool 共享池中被替換出去了。
2、日常維護中的性能工具操作過程
2.1 statspack
01.修改參數
show parameter job
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
10g 默認是 0,
show parameter timed
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
alter system set job_queue_processes=1000 scope=both;
alter system set timed_statistics=TRUE scope=both;
02.創建表空間
create tablespace perfstat datafile '/oracle/app/oracle/oradata/orclfs/perfstat.dbf' size 200m autoextend off;
03.安裝 statspack
@?/rdbms/admin/spcreate.sql
04.測試 statspack
conn perfstat/perfstat
execute statspack.snap
@?/rdbms/admin/spreport.sql
05.自動任務
conn perfstat/perfstat
@?/rdbms/admin/spauto.sql
06.移動定時任務
select job,log_user,last_date,next_date,interval from user_jobs;
execute dbms_job.remove('3');
execute dbms_job.remove('4');
07.刪除歷史數據
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id <=2;
select max(snap_id) from stats$snapshot;
08.刪除 statspack 功能
@?/rdbms/admin/sptrunc.sql
@?/rdbms/admin/spdrop.sql
09.刪除 statspack 表空間
drop tablespace perfstat including contents and datafiles;
2.2 awr
01.單實例
@?/rdbms/admin/awrrpt.sql
02.RAC
每個節點運行
@?/rdbms/admin/awrrpt.sql
每個節點運行/一個節點上運行收集多個。
@?/rdbms/admin/awrrpti.sql
03.一個節點上生成多個節點的。
@?/rdbms/admin/awrgrpti.sql
2.3 addm
01.單實例
@?/rdbms/admin/addmrpt.sql
02.RAC
每個節點運行
@?/rdbms/admin/addmrpt.sql
每個節點運行/一個節點上運行收集多個。
@?/rdbms/admin/addmrpti.sql
2.4 awrsql
01.單實例
@?/rdbms/admin/awrsqrpt.sql
02.RAC
@?/rdbms/admin/awrsqrpti.sql
2.5 ash
01.單實例
@?/rdbms/admin/ashrpt.sql
02.RAC
@?/rdbms/admin/ashrpti.sql
2.6 awrdd
01.單實例
@?/rdbms/admin/awrddrpt.sql
02.RAC
@?/rdbms/admin/awrgdrpt.sql
@?/rdbms/admin/awrgdrpi.sql
03.某一個實例
@?/rdbms/admin/awrddrpi.sql
3、awr 性能數據的收集時間
awr 默認通過 mmon 及 mmnl 進程來每小自動運行一次,為了節省空間,采集的數據在 10g/7,11g/8 天后自動清除。
select * from dba_hist_wr_control
begin
dbms_workload_repository.modify_snapshot_settings(interval =>60,retention => 30*24*60);
end;
/
select * from dba_hist_wr_control;
4、awr 收集性能報告收集過程
01.單實例
@?/rdbms/admin/awrrpt.sql
02.RAC
每個節點運行
@?/rdbms/admin/awrrpt.sql
每個節點運行/一個節點上運行收集多個。
@?/rdbms/admin/awrrpti.sql
03.一個節點上生成多個節點的。
@?/rdbms/admin/awrgrpti.sql
5、awr 手工快照如何實現
01、手工創建快照:
可以使用 create_snapshot 存儲過程手動創建快照來捕獲非自動生成快照的時間內的統計信息;
dbms_workload_repository.create_snapshot(flush_level in varchar2 default 'typical')
return number;
flush_level 參數 flush level 可以是 'typical' 或 'all'
–手工創建快照
begin
dbms_workload_repository.create_snapshot();
end;
/
select * from dba_hist_snapshot;
–手工刪除快照
select * from dba_hist_snapshot;
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id=>30,high_snap_id => 31);
end;
/
6、awr baseline 的手工調整與管理
01,創建一個基線
select * from dba_hist_snapshot;
begin
dbms_workload_repository.create_baseline(start_snap_id => 51, end_snap_id => 52, baseline_name =>'orcl_baseline-51-52',expiration => 30);
end;
/
select * from dba_hist_baseline;
02,重命名一個基線
begin
dbms_workload_repository.rename_baseline(old_baseline_name=>'orcl_baseline-51-52',new_baseline_name =>'orcl_baseline-51-to-52');
end;
/
03,刪除一個基線
begin
dbms_workload_repository.drop_baseline(baseline_name=>'orcl_baseline-51-52');
end;
/
select * from dba_hist_baseline
7、awr 性能相關的視圖介紹
01.v$active_session_history 02.v$ 度量視圖 v$metricgroup
03.dba_hist 視圖
這類視圖包括:
dba_hist_active_sess_history 顯示最近的系統活動的內存中活動會話歷史的歷史信息。
dba_hist_baseline 顯示數據庫捕獲的 baseline 的信息,如每個 baseline 的時間范圍和 baseline type
dba_hist_baseline_details 顯示特定 baseline 的詳細信息
dba_hist_baseline_template 顯示系統用于生成 baseline 的 baseline template
dba_hist_database_instance 顯示數據庫環境相關的信息
dba_hist_db_cache_advice 顯示每行對應的緩存大小所產生的 physical read 的數量的歷史預測信息
dba_hist_dispatcher 顯示捕獲快照時每個 dispatcher 進程的歷史信息
dba_hist_dyn_remaster_stats 顯示動態 remastering 進程相關的信息
dba_hist_iostat_detail 顯示按照文件類型和功能統計的 i/o 統計信息
dba_hist_shared_server_summary 顯示共享服務器的歷史信息,如共享服務器活動、普通隊列和 dispatcher 隊列
dba_hist_snapshot 顯示系統中的快照信息
dba_hist_sql_plan 顯示 sql 執行計劃
dba_hist_wr_control 顯示控制 awr 的設置
用這個查詢:
spool /oracle/dba_hist_all.out
col table_name format a34
col comments format a66
set lin 200
set pagesize 200
select * from dict where table_name like 'DBA_HIST%';
spool off;
8、awr 性能分析數據的遷移
很多時候我們直接在客戶機器上分析 awr 不太方便,需要通過收集客戶 awr 信息到另一臺機器上進行分析數據庫性能等
oracle database 允許我們在幾個數據庫之間傳輸 awr 數據。當您要在單獨的系統上分析 awr 數據時,這非常有用。要傳輸 awr 數據,您必須先從源數據庫上抽取出 awr
快照數據,然后將該數據載入目標數據庫中。以下內容將介紹如何在 oracle database上抽抽取和載入 awr 數據。
01.導出 awr 性能數據
mkdir -p /home/oracle/bakcup
cd /home/oracle/backup
sqlplus "/as sysdba"
create directory awr_dir as '/home/oracle/backup';
@?/rdbms/admin/awrextr.sql
02.導入 awr 性能數據(找一臺新機)
–模擬清空所有的 AWR 數據(同一個實例)
select * from dba_hist_snapshot;
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id => 52);
end;
/
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id => 53);
end;
/
–模擬清空所有的 AWR 數據(非同一個實例)
begin
dbms_swrf_internal.unregister_database(123456789); --dbid
end;
/
–如果以前創建了基線,就是要手工刪除基線
begin
dbms_workload_repository.drop_baseline(baseline_name=>'orcl_baseline-51-to-52',cascade => TRUE);
end;
/
–先創建 awr_dir 目錄
–awrload.sql (sys)
mkdir -p /home/oracle/bakcup
cd /home/oracle/backup
sqlplus "/as sysdba"
create directory awr_dir as '/home/oracle/backup';
@?/rdbms/admin/awrload.sql
--檢查
@?/rdbms/admin/awrrpt.sql
select * from dba_hist_snapshot;
9、awr 性能診斷報告的分析
10、awrsql 報告的獲取與分析
–手工創建快照
begin
dbms_workload_repository.create_snapshot();
end;
/
select * from orcl.orcl_member where name='orcl12345';
select * from orcl.orcl_member where name='orcl12345678';
begin
dbms_workload_repository.create_snapshot();
end;
/
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/awrsqrpt.sql
@?/rdbms/admin/awrsqrpi.sql
11、addm 性能顧問系統的使用
單機
@?/rdbms/admin/addmrpt.sql
RAC
@?/rdbms/admin/addmrpti.sql
查與 awr/addm/ash 類似信息的視圖
select * from v$session;
select * from v$session_wait;
select * from v$session_wait_history
select * from v$acitve_session_history
select * from wrh$_active_session_history
select * from dba_hist_active_sess_history
12、addm 性能建議報告的分析
11gR2
10gR2
13、ash 介紹與生成 ash 報告
4M, 最大 30M,期望值 1 小時
單機
@?/rdbms/admin/ashrpt.sql
RAC
@?/rdbms/admin/ashrpti.sql
14、awrdd 報告生成與分析
01.單實例
@?/rdbms/admin/awrddrpt.sql
02.RAC
@?/rdbms/admin/awrgdrpt.sql
@?/rdbms/admin/awrgdrpi.sql
03.某一個實例
@?/rdbms/admin/awrddrpi.sql




