目的:
通过分析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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-701146/