基於AWR實現STATSPACK報告(1-系統負載)

redhouser發表於2011-07-01
負載

目的：
透過分析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--&gtv$database.dbid,dba_hist_database_instance.dbid
inst_name--&gtv$instance.inst_name,dba_hist_database_instance.instance_name
inst_num--&gtv$instance.instance_number,dba_hist_snapshot.instance_number
sutime--&gtv$instance.startup_time,dba_hist_snapshot.startup_time
versn--&gtv$instance.version,dba_hist_database_instance.version
para--&gtv$instance.parallel,dba_hist_database_instance.parallel
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Host  Name:   netdb1           Num CPUs:   32              Phys Memory (MB): 65,536
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
host_name--&gtv$instance.host_name,dba_hist_database_instance.host_name
bncpu--&gtNUM_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--&gtPHYSICAL_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--&gtdba_hist_snapshot.snap_id
snaptime--&gtdba_hist_snapshot.end_interval_time

sessions:blog/elog--&gt
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--&gt
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)--&gt
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--&gtsnap 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--&gtshared 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--&gtdb_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--&gtlog_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--&gtelapsed 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--&gttransaction: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--&gtRedo 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--&gtLogical 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--&gtBlock 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--&gtPhysical 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--&gtPhysical 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--&gtUser 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--&gtParses:
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--&gtHard 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--&gtSorts:srtm+srtd
srtm--&gt
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--&gt
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--&gtLogons:
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--&gtExecutes:
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--&gtround(100*:chng/:slr,2)
Recursive Call %--&gtround(100*:recr/:call,2) where:
call--&gtucal + recr
ucal--&gt
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--&gt
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 %--&gtround(100*:urol/:tran,2)
Rows per Sort--&gtround(:srtr/(:srtm+:srtd),2) where:

srtr--&gt
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章