基於AWR實現STATSPACK報告(1-系統負載)
目的：
透過分析STATSPACK報告各統計項計算方法，並將其中對STATS$*表的操作對映到AWR中對應表上(DBA_HIST_*),給出每個統計項
的計算方式(SQL)。
參考本文件，可以基於AWR構造系統執行監控報表（實時或事後）。
注意：
1,如果要比較兩個快照(snap),需要確保這兩個快照在dba_hist_snapshot表中dbid,instance_number,snap_id,startup_time一致。
2,stats$idle_event中維護了idle event,本文分析中使用wait_class='Idle'代替,存在差異。
下面為具體分析：
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2405642122 oraDB1 1 14-Dec-08 07:19 10.2.0.3.0 YES
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
dbid-->v$database.dbid,dba_hist_database_instance.dbid
inst_name-->v$instance.inst_name,dba_hist_database_instance.instance_name
inst_num-->v$instance.instance_number,dba_hist_snapshot.instance_number
sutime-->v$instance.startup_time,dba_hist_snapshot.startup_time
versn-->v$instance.version,dba_hist_database_instance.version
para-->v$instance.parallel,dba_hist_database_instance.parallel
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Host Name: netdb1 Num CPUs: 32 Phys Memory (MB): 65,536
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
host_name-->v$instance.host_name,dba_hist_database_instance.host_name
bncpu-->NUM_CPUS:
SELECT VALUE
FROM dba_hist_osstat t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.stat_id = 0;
bpmem/1024/1024-->PHYSICAL_MEMORY_BYTES:
SELECT VALUE
FROM dba_hist_osstat t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.stat_id = 1008;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 66 15-Dec-08 10:09:51 249 11.4
End Snap: 67 15-Dec-08 11:00:09 224 11.6
Elapsed: 50.30 (mins)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
snapid-->dba_hist_snapshot.snap_id
snaptime-->dba_hist_snapshot.end_interval_time
sessions:blog/elog-->
SELECT VALUE
FROM dba_hist_sysstat t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.stat_name ='logons current';
Curs/Sess:bocur/blog,eocur/eocur,其中:
bocur/eocur-->
SELECT VALUE
FROM dba_hist_sysstat t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.stat_name ='opened cursors current';
Elapsed(mins)-->
SELECT (CAST(e.end_interval_time AS DATE) -
CAST(b.end_interval_time AS DATE)) * 1440
FROM dba_hist_snapshot b, dba_hist_snapshot e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,296M 3,392M Std Block Size: 8K
Shared Pool Size: 704M 608M
Log Buffer: 13,917K
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
bbc/ebc-->snap block cache
SELECT SUM(e.VALUE) - SUM(b.VALUE)
FROM dba_hist_parameter b, dba_hist_parameter e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.parameter_name = e.parameter_name
AND b.parameter_name IN
('db_cache_size', '__db_cache_size', 'db_keep_cache_size',
'db_recycle_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size');
bsp/esp-->shared pool
SELECT VALUE
FROM dba_hist_parameter t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.parameter_name = 'shared_pool_size';
bs-->db_block_size
SELECT VALUE
FROM dba_hist_parameter t
WHERE t.dbid = &dbid
AND t.instance_number = &inst_num
AND t.snap_id = &bid
AND t.parameter_name = 'db_block_size';
blb-->log_buffer
SELECT e.VALUE - b.VALUE
FROM dba_hist_parameter b, dba_hist_parameter e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.parameter_name = e.parameter_name
AND b.parameter_name ='log_buffer';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 44,098.80 1,335.79
Logical reads: 40,067.19 1,213.67
Block changes: 259.90 7.87
Physical reads: 1,848.84 56.00
Physical writes: 24.93 0.76
User calls: 1,117.53 33.85
Parses: 152.76 4.63
Hard parses: 0.82 0.02
Sorts: 11.28 0.34
Logons: 0.03 0.00
Executes: 660.69 20.01
Transactions: 33.01
% Blocks changed per Read: 0.65 Recursive Call %: 9.36
Rollback per transaction %: 3.40 Rows per Sort: 4702.62
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
演算法：Per Second:diff A/ela;Per Transaction:diff A/tran
ela-->elapsed time:
SELECT (CAST(e.end_interval_time AS DATE) -
CAST(b.end_interval_time AS DATE)) * 1440 * 60
FROM dba_hist_snapshot b, dba_hist_snapshot e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid;
tran-->transaction:ucom+urol
SELECT sum(e.VALUE) - sum(b.VALUE)
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name in('user commits','user rollbacks');
rsiz-->Redo size:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='redo size';
slr-->Logical reads:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='session logical reads';
chng-->Block changes:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='db block changes';
phyr-->Physical reads:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='physical reads';
phyw-->Physical writes:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='physical writes';
ucal-->User calls:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='user calls';
prse-->Parses:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='parse count (total)';
hprs-->Hard parses:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='parse count (hard)';
Sorts-->Sorts:srtm+srtd
srtm-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='sorts (memory)';
srtd-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='sorts (disk)';
logc-->Logons:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='logons cumulative';
exe-->Executes:
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='execute count';
% Blocks changed per Read-->round(100*:chng/:slr,2)
Recursive Call %-->round(100*:recr/:call,2) where:
call-->ucal + recr
ucal-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='user calls';
recr-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='recursive calls';
Rollback per transaction %-->round(100*:urol/:tran,2)
Rows per Sort-->round(:srtr/(:srtm+:srtd),2) where:
srtr-->
SELECT e.VALUE - b.VALUE
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.dbid = e.dbid
AND b.instance_number = e.instance_number
AND b.dbid = &dbid
AND b.instance_number = &inst_num
AND b.snap_id = &bid
AND e.snap_id = &eid
AND b.stat_name = e.stat_name
AND b.stat_name ='sorts (rows)';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-701146/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- awr-----一份經典的負載很高的awr報告負載
- statspack、awr、addm,ash影片分享
- AWR報告基礎操作
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- Unity——基於ShaderLab實現光照系統Unity
- Keepalived+HAProxy基於ACL實現單IP多域名負載功能負載
- 基於Sentinel自研元件的系統限流、降級、負載保護最佳實踐探索元件負載
- 基於Docker + Consul + Nginx + Consul-template的服務負載均衡實現DockerNginx負載
- 基於滴滴雲部署 HAProxy 實現 7 層和 4 層負載均衡負載
- 【Tony 老師】基於 Maxscale 實現讀寫分離和負載均衡負載
- Linux基礎命令---tload顯示系統負載Linux負載
- 基於gRPC的註冊發現與負載均衡的原理和實戰RPC負載
- 聊聊系統平均負載負載
- Ribbon實現負載均衡負載
- GRPC 負載均衡實現RPC負載
- nginx實現負載均衡Nginx負載
- Crane-scheduler：基於真實負載進行排程負載
- 基於開源Tars的動態負載均衡實踐負載
- Django實戰1-許可權管理功能實現-04：系統入口Django
- AWR TOP SQL實現SQL
- 基於Spring Security實現許可權管理系統Spring
- HaProxy 實現 MySQL 負載均衡MySql負載
- Locust如何實現逐步負載？負載
- 基於施耐德PLC的水位測控系統如何實現遠端監控上下載
- Oracle生成awr報告操作步驟Oracle
- 實現基於內容的電影推薦系統—程式碼實現
- 基於ZooKeeper，Spring設計實現的引數系統Spring
- 基於Netty的Android系統IM簡單實現原理NettyAndroid
- Spring RSocket：基於服務註冊發現的 RSocket 負載均衡Spring負載
- ORACLE AWR效能報告和ASH效能報告的解讀Oracle
- windows第七層負載均衡 基於IIS的ARR負載均衡詳解Windows負載
- linux實現DNS輪詢實現負載平衡LinuxDNS負載
- Linux 如何檢視系統負載Linux負載
- 基於 gRPC 的服務註冊與發現和負載均衡的原理與實戰RPC負載
- nginx部署基於http負載均衡器NginxHTTP負載
- Docker Swarm ：gRPC 基於 DNS 的負載均衡DockerSwarmRPCDNS負載
- nginx+tomcat實現負載均衡NginxTomcat負載
- dubbo（三）：負載均衡實現解析負載
- 使用YARP來實現負載均衡負載