Oracle效能優化視訊學習筆記－診斷和調優工具

這個章節恐怕是最長的了，耗時2個多小時，總算聽完了，先別管效果如何了，呵呵；恐怕還得再聽一次

診斷和工具
show parameter background_dump_dest;
指出告警日誌檔案的目錄
log_checkpoint_to_alert=true
記錄檢查點資訊和未完成的檢查點
記錄歸檔時間
使用者跟蹤文件
跟蹤使用者釋出的SQL資訊
alter session set sql_trace=true;
能夠在會話一級進行SQL操作跟蹤
execute dbms_system.set_sql_trace(sid號,子序列號，true)
跟蹤會話中相應的SQL
使用tkprof解析跟蹤的SQL跟蹤檔案
catproc.sql用來生成建立跟蹤的包

檢視和工具
V$XXX，動態效能檢視
v$lock,v$..
base on x$table and listed in v$fixed_table
is v_$xxx的同義詞
當前狀態檢視V$session,v$open_cursor,v$session_wait
累加檢視V$sys_event,...
動態檢視v$parameter
DBA_XXX，資料字典
DBA_tables,DBA_Segments,DBA_Tab_Columns,DBA_Clusters,DBA_INDEXES,INDEX_STATS,INDEX_HISTOGRAM,DBA_TAB_HISTOGRAMS
以上統計資訊是靜態的，需要dbms_stats和analyze來完成更新
StatSpack
Oracle9i新的功能包
UTLBstat.sql,UtlEstat.sql script取兩者指令碼執行之間的效能比較
企業管理器
等待事件
Oracle診斷和優化packs

v@sysstat啟動之後所有的系統統計
select name,value from v@sysstat
where name in ('db block gets','consistent gets','physical reads');
hitrate=(db block gets+consistent gets)/(physical reads'+db block gets+consistent gets)

v@sgastat SGA區的統計資訊
select * from v@sgastat
where pool='shared pool' and name='free memory'

v@event_name,v@system_event
事件資訊詳細清單和統計

select sum(pins-reloads)/sum(pins) "lib cache" from v$library
庫快取命中率

select (sum(gets-getmisses))/sum(gets) "direc_hit" from v$rowcache
資料字典命中率

v$db_cache_advice預計緩衝區的大小和影響
v$pgastat
Disk
v$dbfile
v$tempstat
v$tempfile
v$filestat
v$datafile
v$segment_statist
v$log,v$log_history
Contention
v$latch,v$lock,v$rollname,v$rollstat,v$waitstat

關於會話統計資訊
select
a.statistic#,a.name,a.class,
b.sid,b.statistic#,b.value,
c.sid,c.serial#,c.username,c.osuser
from v$statname a,v$sesstat b,v$session c
where a.statistic#=b.statistic#
and b.sid=c.sid

關於會話等待事件的統計
select
a.sid,a.event,a.total_waits,a.total_timeouts,a.time_waited,a.averate_wait,a.max_wait,
b.event#,b.name,b.parameter1,b.parameter2,b.parameter3,
c.sid,c.seq#,c.event,c.p1/2/3 c.p1/2/2 text,c.p1/2/3 raw,c.wait time,c.seconds_in_wait,c.state
from v$session_event a,v$event_name b,v$session_wait c
where a.sid=c.sid
and a.event=b.name
and b.name=c.event

Statspack
需要100兆空間（8i開始提供）
且TIMED_STATISTICS=true
alter system set timed_statistics=true
spcreate.sql安裝Statspack
statspack.snap手工收集統計資訊
spauto.sql自動收集統計資訊
spreport.sql產生統計資訊報告

show parameter job_queue_processes>0方可自動收集統計資訊
job_queue_processes>0即OK

第一步,建立statpack統計表空間
create tablespace perfstat datafile '......' size 100M extent management local;
第二步
@%ORACLE_HOME%RDBMSADMINSPCREATE.SQL
建立statpack資料字典和使用者
並且輸入剛才建立的表空間perfstat
第三步，切換使用者
connect perfstat/perfstat
手工執行拍照
execute statpack.snap
第四步，建立自動拍照指令碼，預設1個小時執行一次
@%ORACLE_HOME%RDBMSADMINSPAUTO.SQL
select job,log_user,last_date,next_date,interval from user_jobs
第五步，執行spreport.sql
select snap_id from stats$snapshot;

report內容19部分
資料庫和例項名稱
快照時間起至時間
各當前快取大小
負載load profile
例項的效率百分比
共享池統計資訊
Top5事件
效能較差的SQL by Diks,By CPU,By memory
表空間I/O統計資料
資料檔案I/O統計資料
緩衝池統計
例項恢復統計
PGA記憶體統計
回滾段統計
鎖存器活動的統計
資料字典緩衝區統計
庫緩衝區統計
SGA記憶體概要統計
初始化引數值列表

@sppurge.sql可以刪除快照資訊
使用DBA刪除快照和統計資訊
執行spdrop即可

UTLBSTAT，UTLESTAT指令碼可以做簡單的快照統計
TIMED_STATSTICS=TRUE
run UTLBSTAT
run UTLESTAT
檢視report.txt報告

企業管理器

Oracle Performance Manager

調優方法論
明確調優範圍
收集資料
編輯資料和規則
分析資料
評估方案
驗證方案

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