ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(1-系统负载)

基于AWR实现STATSPACK报告(1-系统负载)

原创 Linux操作系统 作者:redhouser 时间:2011-07-01 16:45:48 0 删除 编辑

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810335