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