ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于AWR实现STATSPACK报告(8-实例组件二)

基于AWR实现STATSPACK报告(8-实例组件二)

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

Dictionary Cache Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->"Pct Misses"  should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
                                   Get    Pct    Scan   Pct      Mod      Final
Cache                         Requests   Miss    Reqs  Miss     Reqs      Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control                      52    0.0       0              0          1
dc_global_oids                     652    1.1       0              0         16
dc_histogram_data               86,829    0.4       0              0      3,231
dc_histogram_defs               34,125    2.6       0             21      1,747
dc_object_grants                   156    6.4       0              0        107
dc_object_ids                   41,387    0.4       0              2        535
dc_objects                       4,475    0.8       0              4        668
....
-------------------------------------------------------------
                                   GES          GES          GES
Cache                         Requests    Conflicts     Releases
------------------------- ------------ ------------ ------------
dc_global_oids                       7            0           20
dc_histogram_defs                  862            0        5,399
dc_object_ids                      177            0          811
dc_objects                          32            0          699
dc_segments                         21            1          426
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT lower(b.parameter) param,
       e.gets - b.gets gets,
       to_number(decode(e.gets,
                        b.gets,
                        NULL,
                        (e.getmisses - b.getmisses) * 100 /
                        (e.gets - b.gets))) getm,
       e.scans - b.scans scans,
       to_number(decode(e.scans,
                        b.scans,
                        NULL,
                        (e.scanmisses - b.scanmisses) * 100 /
                        (e.scans - b.scans))) scanm,
       e.modifications - b.modifications mods,
       e.usage usage
  FROM dba_hist_rowcache_summary b, dba_hist_rowcache_summary e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.dbid = &dbid
   AND e.dbid = &dbid
   AND b.dbid = e.dbid
   AND b.instance_number = &inst_num
   AND e.instance_number = &inst_num
   AND b.instance_number = e.instance_number
   AND b.parameter = e.parameter
   AND e.gets - b.gets > 0
 ORDER BY param;

--dlm_requests,RAC related:
select lower(b.parameter)                                        param
     , e.dlm_requests  - b.dlm_requests                          dreq
     , e.dlm_conflicts - b.dlm_conflicts                         dcon
     , e.dlm_releases  - b.dlm_releases                          drel
  from dba_hist_rowcache_summary b
     , dba_hist_rowcache_summary e
 where b.snap_id                       = &bid
   and e.snap_id                       = &eid
   and b.dbid                          = &dbid
   and e.dbid                          = &dbid
   and b.dbid                          = e.dbid
   and b.instance_number               = &inst_num
   and e.instance_number               = &inst_num
   and b.instance_number               = e.instance_number
   and b.parameter                     = e.parameter
   and e.dlm_requests - b.dlm_requests > 0
 order by param;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Library Cache Activity  DB/Inst: ORADB/oraDB1  Snaps: 66-67
->"Pct Misses"  should be very low
                                        Get  Pct        Pin        Pct             Invali-
Namespace                          Requests  Miss     Requests     Miss   Reloads  dations
------------------------------ ------------ ------ -------------- ------ ---------- --------
BODY                                    186    0.5          2,079    0.7         14        0
CLUSTER                                  78    0.0            176    1.1          2        0
SQL AREA                                791   53.4      2,013,353    0.4      2,172    1,137
TABLE/PROCEDURE                       2,996    0.6         86,094    1.5      1,203        0
TRIGGER                                   1  100.0              2   50.0          0        0
                                   GES Lock      GES Pin      GES Pin   GES Inval GES Invali-
Namespace                          Requests     Requests     Releases    Requests     dations
------------------------------ ------------ ------------ ------------ ----------- -----------
BODY                                      1            1           13           1           0
CLUSTER                                 176            0            1           0           0
SQL AREA                                  0            0            0           0           0
TABLE/PROCEDURE                       9,221           14          430           9           0
TRIGGER                                   0            1            0           1           0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select e.namespace
     , e.gets - b.gets                                         gets 
     , to_number(decode(e.gets,b.gets,null,
       100 - (e.gethits - b.gethits) * 100/(e.gets - b.gets))) getm
     , e.pins - b.pins                                         pins 
     , to_number(decode(e.pins,b.pins,null,
       100 - (e.pinhits - b.pinhits) * 100/(e.pins - b.pins))) pinm
     , e.reloads - b.reloads                                   reloads
     , e.invalidations - b.invalidations                       inv
  from DBA_HIST_LIBRARYCACHE b
     , DBA_HIST_LIBRARYCACHE e
 where b.snap_id         = &bid  
   and e.snap_id         = &eid
   and e.dbid            = &dbid
   and b.dbid            = e.dbid
   and e.instance_number = &inst_num
   and b.instance_number = e.instance_number
   and b.namespace       = e.namespace
   and e.gets - b.gets   > 0;

--dlm_lock_requests,RAC related:
SELECT e.namespace,
       e.dlm_lock_requests - b.dlm_lock_requests dlreq,
       e.dlm_pin_requests - b.dlm_pin_requests dpreq,
       e.dlm_pin_releases - b.dlm_pin_releases dprel,
       e.dlm_invalidation_requests - b.dlm_invalidation_requests direq,
       e.dlm_invalidations - b.dlm_invalidations dinv
  FROM dba_hist_librarycache b, dba_hist_librarycache e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND e.dbid = &dbid
   AND b.dbid = e.dbid
   AND e.instance_number = &inst_num
   AND b.instance_number = e.instance_number
   AND b.namespace = e.namespace
   AND e.gets - b.gets > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Global Enqueue Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
acks for commit broadcast(actual)          466,128        154.4          4.7
acks for commit broadcast(logical          492,587        163.2          4.9
broadcast msgs on commit(actual)            97,764         32.4          1.0
broadcast msgs on commit(logical)           98,266         32.6          1.0
broadcast msgs on commit(wasted)             4,694          1.6          0.0
dynamically allocated gcs resourc                0          0.0          0.0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT b.NAME st,
       e.VALUE - b.VALUE dif,
       round(e.VALUE - b.VALUE) / &ela ps,
       round(e.VALUE - b.VALUE) / &tran pt
  FROM dba_hist_dlm_misc b, dba_hist_dlm_misc e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.instance_number = &inst_num
   AND e.instance_number = &inst_num
   AND b.dbid = &dbid
   AND e.dbid = &dbid
   AND e.statistic# = b.statistic#
 ORDER BY b.NAME;
其中:
ela-->
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-->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');
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Global CR Served Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
Statistic                                   Total
------------------------------ ------------------
CR Block Requests                         105,752
CURRENT Block Requests                      1,057
Data Block Requests                       105,752
Undo Block Requests                            17
TX Block Requests                             366
Current Results                           105,862
Private results                                11
Zero Results                                  938
Disk Read Results                               0
Fail Results                                    0
....
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'Statistic                                   Total',
       '------------------------------' nl,
       '------------------',
       'CR Block Requests             ' nl,
       e.cr_requests - b.cr_requests val,
       'CURRENT Block Requests        ' nl,
       e.current_requests - b.current_requests val,
       'Data Block Requests           ' nl,
       e.data_requests - b.data_requests val,
       'Undo Block Requests           ' nl,
       e.undo_requests - b.undo_requests val,
       'TX Block Requests             ' nl,
       e.tx_requests - b.tx_requests val,
       'Current Results               ' nl,
       e.current_results - b.current_results val,
       'Private results               ' nl,
       e.private_results - b.private_results val,
       'Zero Results                  ' nl,
       e.zero_results - b.zero_results val,
       'Disk Read Results             ' nl,
       e.disk_read_results - b.disk_read_results val,
       'Fail Results                  ' nl,
       e.fail_results - b.fail_results val,
       'Fairness Down Converts        ' nl,
       e.fairness_down_converts - b.fairness_down_converts val,
       'Fairness Clears               ' nl,
       e.fairness_clears - b.fairness_clears val,
       'Free GC Elements              ' nl,
       e.free_gc_elements - b.free_gc_elements val,
       'Flushes                       ' nl,
       e.flushes - b.flushes val,
       'Flushes Queued                ' nl,
       e.flushes_queued - b.flushes_queued val,
       'Flush Queue Full              ' nl,
       e.flush_queue_full - b.flush_queue_full val,
       'Flush Max Time (us)           ' nl,
       e.flush_max_time - b.flush_max_time val,
       'Light Works                   ' nl,
       e.light_works - b.light_works val,
       'Errors                        ' nl,
       e.errors - b.errors val
  FROM dba_hist_cr_block_server b, dba_hist_cr_block_server e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.instance_number = &inst_num
   AND e.instance_number = &inst_num
   AND b.dbid = &dbid
   AND e.dbid = &dbid;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Global CURRENT Served Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Pins    = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block Served Operations
-> Writes  = CURRENT Block Fusion Write Operations
Statistic                    Total              % <1ms          % <10ms       % <100ms            % <1s           % <10s
----------- ----------- -------- -------- -------- -------- --------
Pins                    1,228,830
  100.00
    0.00
    0.00
    0.00
    0.00
Flushes                        88
   86.36
   12.50
    0.00
    1.14
    0.00
Writes                     18,000
   24.14
   63.16
   10.36
    0.83
    1.52
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT 'Statistic  ',
       '      Total',
       '  % <1ms',
       ' % <10ms',
       '% <100ms',
       '   % <1s',
       '  % <10s',
       '----------- ----------- -------- -------- -------- -------- --------',
       'Pins      ' stat,
       pins tot,
       lpad(to_char(decode(pins, 0, 0, 100 * pin1 / pins), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(pins, 0, 0, 100 * pin10 / pins), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(pins, 0, 0, 100 * pin100 / pins), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(pins, 0, 0, 100 * pin1000 / pins), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(pins, 0, 0, 100 * pin10000 / pins), '990.99'),
            8,
            ' '),
       'Flushes   ' stat,
       flushes tot,
       lpad(to_char(decode(flushes, 0, 0, 100 * flush1 / flushes), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(flushes, 0, 0, 100 * flush10 / flushes),
                    '990.99'),
            8,
            ' '),
       lpad(to_char(decode(flushes, 0, 0, 100 * flush100 / flushes),
                    '990.99'),
            8,
            ' '),
       lpad(to_char(decode(flushes, 0, 0, 100 * flush1000 / flushes),
                    '990.99'),
            8,
            ' '),
       lpad(to_char(decode(flushes, 0, 0, 100 * flush10000 / flushes),
                    '990.99'),
            8,
            ' '),
       'Writes    ' stat,
       writes tot,
       lpad(to_char(decode(writes, 0, 0, 100 * write1 / writes), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(writes, 0, 0, 100 * write10 / writes), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(writes, 0, 0, 100 * write100 / writes), '990.99'),
            8,
            ' '),
       lpad(to_char(decode(writes, 0, 0, 100 * write1000 / writes),
                    '990.99'),
            8,
            ' '),
       lpad(to_char(decode(writes, 0, 0, 100 * write10000 / writes),
                    '990.99'),
            8,
            ' ')
  FROM (SELECT (e.pin1 + e.pin10 + e.pin100 + e.pin1000 + e.pin10000 -
               (b.pin1 + b.pin10 + b.pin100 + b.pin1000 + b.pin10000)) pins,
               e.pin1 - b.pin1 pin1,
               e.pin10 - b.pin10 pin10,
               e.pin100 - b.pin100 pin100,
               e.pin1000 - b.pin1000 pin1000,
               e.pin10000 - b.pin10000 pin10000,
               (e.flush1 + e.flush10 + e.flush100 + e.flush1000 +
               e.flush10000 - (b.flush1 + b.flush10 + b.flush100 +
               b.flush1000 + b.flush10000)) flushes,
               e.flush1 - b.flush1 flush1,
               e.flush10 - b.flush10 flush10,
               e.flush100 - b.flush100 flush100,
               e.flush1000 - b.flush1000 flush1000,
               e.flush10000 - b.flush10000 flush10000,
               (e.write1 + e.write10 + e.write100 + e.write1000 +
               e.write10000 - (b.write1 + b.write10 + b.write100 +
               b.write1000 + b.write10000)) writes,
               e.write1 - b.write1 write1,
               e.write10 - b.write10 write10,
               e.write100 - b.write100 write100,
               e.write1000 - b.write1000 write1000,
               e.write10000 - b.write10000 write10000
          FROM dba_hist_current_block_server b,
               dba_hist_current_block_server e
         WHERE b.snap_id = &bid
           AND e.snap_id = &eid
           AND b.instance_number = &inst_num
           AND e.instance_number = &inst_num
           AND b.dbid = &dbid
           AND e.dbid = &dbid);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Global Cache Transfer Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Immediate  (Immed) - Block Transfer NOT impacted by Remote Processing Delays
   Busy        (Busy) - Block Transfer impacted by Remote Contention
   Congested (Congst) - Block Transfer impacted by Remote System Load
-> ordered by CR + Current Blocks Received desc
              -------------- CR -------------  ----------- Current -----------
Inst Block         Blocks      %      %      %      Blocks      %      %      %
  No Class       Received  Immed   Busy Congst    Received  Immed   Busy Congst
---- -------- ----------- ------ ------ ------ ----------- ------ ------ ------
   2 data blo     189,236   82.3   17.7     .0   1,682,755  100.0     .0     .0
   2 undo blo      12,111   99.3     .7     .0           0
   2 others           372  100.0     .0     .0         554  100.0     .0     .0
   2 undo hea         693   98.4    1.6     .0          81   97.5    2.5     .0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
with instance_cache_transfer as (
  select snap_id
       , instance
       , case when class in ('data block', 'undo header', 'undo block')
                then class
              else 'others' end as class
       , sum(cr_block)                                                   cr_block
       , sum(cr_busy)                                                    cr_busy
       , sum(cr_congested)                                               cr_congested
       , sum(current_block)                                              current_block
       , sum(current_busy)                                               current_busy
       , sum(current_congested)                                          current_congested
       , sum(cr_block) + sum(cr_busy) + sum(cr_congested)                totcr
       , sum(current_block) + sum(current_busy) + sum(current_congested) totcu
    from DBA_HIST_INST_CACHE_TRANSFER
   where instance_number             = &inst_num
     and dbid                        = &dbid
   group by snap_id
       , instance
       , case when class in ('data block', 'undo header', 'undo block')
                then class
              else 'others' end)
select e.instance                 inst
     , e.class                    class
     , e.totcr - nvl(b.totcr , 0) totcr
     , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0)))    blkimm
     , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy -nvl(b.cr_busy,  0))*100/(e.totcr-nvl(b.totcr, 0)))    blkbus
     , decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt
     , e.totcu - nvl(b.totcu , 0) totcu
     , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0)))    blkimm
     , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy -nvl(b.current_busy,  0))*100/(e.totcu-nvl(b.totcu, 0)))    blkbus
     , decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt
  from (select * from instance_cache_transfer
         where snap_id      = &bid) b
     , (select * from instance_cache_transfer
         where snap_id      = &eid) e
 where b.class(+)           = e.class
   and b.instance(+)        = e.instance
   and e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0) > 0
 order by totcr + totcu desc;

with class_cache_transfer as (
  select snap_id
       , case when class in ('data block', 'undo header', 'undo block')
                then class
              else 'others' end as class
       , sum(cr_block)                                                   cr_block
       , sum(cr_busy)                                                    cr_busy
       , sum(cr_congested)                                               cr_congested
       , sum(current_block)                                              current_block
       , sum(current_busy)                                               current_busy
       , sum(current_congested)                                          current_congested
       , sum(cr_block) + sum(cr_busy) + sum(cr_congested)                totcr
       , sum(current_block) + sum(current_busy) + sum(current_congested) totcu
    from DBA_HIST_INST_CACHE_TRANSFER
   where instance_number             = &inst_num
     and dbid                        = &dbid
   group by snap_id
       , case when class in ('data block', 'undo header', 'undo block')
                then class
                else 'others' end)
select e.class
     , e.totcr              - nvl(b.totcr            , 0) totcr
     , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0)))    blkimm
     , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy-nvl(b.cr_busy, 0))*100/(e.totcr-nvl(b.totcr, 0)))      blkbus
     , decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt
     , e.totcu              - nvl(b.totcu            , 0) totcu
     , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0)))    blkimm
     , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy-nvl(b.current_busy, 0))*100/(e.totcu-nvl(b.totcu, 0)))      blkbus
     , decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt
  from (select * from class_cache_transfer
         where snap_id         = &bid) b
     , (select * from class_cache_transfer
         where snap_id         = &eid) e
 where b.class(+)           = e.class
   and (e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0)) > 0
 order by totcr + totcu desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Dynamic Remastering Stats  DB/Inst: ORADB/oraDB1  Snaps: 66-67
    Remaster Operations:               4   Remaster Time(s):            6.2
     Remastered Objects:               4    Quiesce Time(s):            0.2
 Affinity Objects (Beg):              15     Freeze Time(s):            0.0
 Affinity Objects (End):              15    Cleanup Time(s):            0.2
    Replayed Locks Sent:          85,656     Replay Time(s):            1.2
   Replayed Locks Recvd:         112,604   Fixwrite Time(s):            0.2
      Resources Cleaned:               0       Sync Time(s):            2.7
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由于不存在相关表,AWR中无法实现,摘录STATSPACK实现方法:
create or replace view STATS$V_$DYNAMIC_REM_STATS as
select drms                  remaster_ops
     , avg_drm_time*drms     remaster_time
     , objects_per_drm*drms  remastered_objects
     , quisce_t*drms         quiesce_time
     , frz_t*drms            freeze_time
     , cleanup_t*drms        cleanup_time
     , replay_t*drms         replay_time
     , fixwrite_t*drms       fixwrite_time
     , sync_t*drms           sync_time
     , res_cleaned*drms      resources_cleaned
     , replay_s*drms         replayed_locks_sent
     , replay_r*drms         replayed_locks_received
     , my_objects            current_objects
  from x$kjdrmafnstats;
grant select on STATS$V_$DYNAMIC_REM_STATS to PERFSTAT;
create synonym PERFSTAT.V$DYNAMIC_REMASTER_STATS for STATS$V_$DYNAMIC_REM_STATS;

select '    Remaster Operations:'                                    ch25n
     , e.remaster_ops            - nvl(b.remaster_ops, 0)            numX
     , '  Remaster Time(s):'                                         ch20
     , (e.remaster_time    - nvl(b.remaster_time, 0))/&cstos         numXX
     , '     Remastered Objects:'                                    ch25n
     , e.remastered_objects      - nvl(b.remastered_objects, 0)      numX
     , '   Quiesce Time(s):'                                         ch20
     , (e.quiesce_time     - nvl(b.quiesce_time, 0))/&cstos          numXX
     , ' Affinity Objects (Beg):'                                    ch25n
     , b.current_objects                                             numX
     , '    Freeze Time(s):'                                         ch20
     , (e.freeze_time      - nvl(b.freeze_time, 0))/&cstos           numXX
     , ' Affinity Objects (End):'                                    ch25n
     , e.current_objects                                             numX
     , '   Cleanup Time(s):'                                         ch20
     , (e.cleanup_time     - nvl(b.cleanup_time, 0))/&cstos          numXX
     , '    Replayed Locks Sent:'                                    ch25n
     , e.replayed_locks_sent     - nvl(b.replayed_locks_sent, 0)     numX
     , '    Replay Time(s):'                                         ch20
     , (e.replay_time      - nvl(b.replay_time, 0))/&cstos           numXX
     , '   Replayed Locks Recvd:'                                    ch25n
     , e.replayed_locks_received - nvl(b.replayed_locks_received, 0) numX
     , '  Fixwrite Time(s):'                                         ch20
     , (e.fixwrite_time    - nvl(b.fixwrite_time, 0))/&cstos         numXX
     , '      Resources Cleaned:'                                    ch25n
     , e.resources_cleaned - nvl(b.resources_cleaned, 0)             numX
     , '      Sync Time(s):'                                         ch20
     , (e.sync_time        - nvl(b.sync_time, 0))/&cstos             numXX
  from stats$dynamic_remaster_stats b
     , stats$dynamic_remaster_stats e
 where b.snap_id(+)         = :bid
   and e.snap_id            = :eid
   and e.dbid               = :dbid
   and b.dbid(+)            = e.dbid
   and e.instance_number    = :inst_num
   and b.instance_number(+) = e.instance_number
   and e.remaster_ops - nvl(b.remaster_ops, 0) > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

STREAM
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Rule Sets  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
                                                                No-SQL  SQL
Rule                                *      Eval/sec Reloads/sec Eval % Eval %
----------------------------------- -- ------------ ----------- ------ ------
SYS.ALERT_QUE_R                                   0           0      0      0
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select owner||'.'||name                          rule_name
     , restart
     , decode( elapsed_time, 0, 0
             , evaluations/elapsed_time * 100)   eval_rate
     , reloads/&ela                              reload_rate
     ,   sql_free_evaluations
       / (sql_free_evaluations + sql_executions + .0000001)
       * 100                                     pct_sql_free
     ,   sql_executions
       / (sql_free_evaluations + sql_executions + .0000001 )
       * 100                                     pct_sql
  from (select e.owner
             , e.name
             , decode( e.startup_time, b.startup_time, null, '*') restart
             , e.cpu_time       - nvl(b.cpu_time,0)       cpu_time
             , e.elapsed_time   - nvl(b.elapsed_time,0)   elapsed_time
             , e.evaluations    - nvl(b.evaluations,0)    evaluations
             , e.sql_free_evaluations - nvl(b.sql_free_evaluations,0)
                                                          sql_free_evaluations
             , e.sql_executions - nvl(b.sql_executions,0) sql_executions
             , e.reloads        - nvl(b.reloads,0)        reloads
          from DBA_HIST_RULE_SET b
             , DBA_HIST_RULE_SET e
          where b.snap_id         (+)= &bid
            and e.snap_id            = &eid
            and e.dbid               = &dbid
            and b.dbid            (+)= e.dbid
            and e.instance_number    = &inst_num
            and b.instance_number (+)= e.instance_number
            and b.owner           (+)= e.owner
            and b.name            (+)= e.name
            and b.startup_time    (+)= e.startup_time
          order by e.evaluations desc
       )
 where rownum <= 10;
其中:
ela-->
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;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Streams Pool Advisory  DB/Inst: ORADB/oraDB1  End Snap: 67
Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill
    Size (M)  Size Factor     Count  Time (s)       Count    Time (s)
------------ ------------ --------- --------- ----------- -----------
          .0          1.0         0         0           0           0
          .0          2.0         0         0           0           0
          .0          3.0         0         0           0           0
          .0          4.0         0         0           0           0
          .0          5.0         0         0           0           0
          .0          6.0         0         0           0           0
          .0          7.0         0         0           0           0
          .0          8.0         0         0           0           0
          .0          9.0         0         0           0           0
          .0         10.0         0         0           0           0
          .0         11.0         0         0           0           0
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select size_for_estimate/1024/1024 spsfe
     , size_factor       spsf
     , estd_spill_count               esc
     , estd_spill_time                est
     , estd_unspill_count             eusc
     , estd_unspill_time              eust
  from DBA_HIST_STREAMS_POOL_ADVICE
 where snap_id             = &eid
   and dbid                = &dbid
   and instance_number     = &inst_num
 order by size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Shared Pool Advisory  DB/Inst: ORADB/oraDB1  End Snap: 67
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid
                                        Est LC Est LC  Est LC Est LC
    Shared    SP   Est LC                 Time   Time    Load   Load      Est LC
      Pool  Size     Size       Est LC   Saved  Saved    Time   Time         Mem
  Size (M) Factr      (M)      Mem Obj     (s)  Factr     (s)  Factr    Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
       384    .6       71        4,254 #######    1.0     250    1.0   5,412,042
       448    .7      133        6,608 #######    1.0     246    1.0   5,412,585
       512    .8      170        8,948 #######    1.0     242    1.0   5,413,037
       576    .9      170        8,948 #######    1.0     240    1.0   5,413,311
       640   1.0      170        8,948 #######    1.0     239    1.0   5,413,467
       704   1.1      170        8,948 #######    1.0     239    1.0   5,413,549
       768   1.2      170        8,948 #######    1.0     239    1.0   5,413,589
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT shared_pool_size_for_estimate spsfe,
       shared_pool_size_factor       spsf,
       estd_lc_size                  elcs,
       estd_lc_memory_objects        elcmo,
       estd_lc_time_saved            elcts,
       estd_lc_time_saved_factor     elctsf,
       estd_lc_load_time             elclt,
       estd_lc_load_time_factor      elcltf,
       estd_lc_memory_object_hits    elcmoh
  FROM dba_hist_shared_pool_advice
 WHERE snap_id = &eid
   AND dbid = &dbid
   AND instance_number = &inst_num
 ORDER BY shared_pool_size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Java Pool Advisory
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT java_pool_size_for_estimate jpsfe,
       java_pool_size_factor       jpsf,
       estd_lc_size                elcs,
       estd_lc_memory_objects      elcmo,
       estd_lc_time_saved          elcts,
       estd_lc_time_saved_factor   elctsf,
       estd_lc_load_time           elclt,
       estd_lc_load_time_factor    elcltf,
       estd_lc_memory_object_hits  elcmoh
  FROM dba_hist_java_pool_advice
 WHERE snap_id = &eid
   AND dbid = &dbid
   AND instance_number = &inst_num
   AND estd_lc_memory_objects > 0
 ORDER BY java_pool_size_for_estimate;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Cache Size Changes  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Not all cache size changes may be captured.  Only cache changes which are
   evident at snapshot time are shown
                         Prior      New    Difference
  Snap Id Cache        Size (MB) Size (MB)    (MB)
--------- ------------ --------- --------- ----------
       67 Shared Pool        704       608        -96
          Buffer Cache     3,296     3,392         96
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT snap_id,
       decode(parameter_name,
              '__db_cache_size',
              'Buffer Cache',
              '__shared_pool_size',
              'Shared Pool',
              '__large_pool_size',
              'Large Pool',
              '__java_pool_size',
              'Java Pool',
              '__streams_pool_size',
              'Streams Pool') cache,
       prev_value,
       VALUE,
       (VALUE - prev_value) diff
  FROM (SELECT snap_id,
               parameter_name,
               to_number(VALUE) / 1024 / 1024 VALUE,
               to_number((lag(VALUE, 1, NULL)
                          over(ORDER BY parameter_name, snap_id))) / 1024 / 1024 prev_value,
               (lag(parameter_name, 1, NULL)
                over(ORDER BY parameter_name, snap_id)) prev_name
          FROM dba_hist_parameter
         WHERE snap_id BETWEEN &bid AND &eid
           AND dbid = &dbid
           AND instance_number = &inst_num
           AND parameter_name IN
               ('__shared_pool_size', '__db_cache_size', '__large_pool_size',
                '__java_pool_size', '__streams_pool_size'))
 WHERE VALUE != prev_value
   AND parameter_name = prev_name
 ORDER BY snap_id, diff;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SGA Target Advisory  DB/Inst: ORADB/oraDB1  End Snap: 67
SGA Target SGA Size   Est DB      Est DB   Est Physical
  Size (M)   Factor Time (s) Time Factor          Reads
---------- -------- -------- ----------- --------------
     1,024       .3   85,148         1.2     43,738,481
     2,048       .5   70,017         1.0     16,214,704
     3,072       .8   69,036         1.0     14,428,488
     4,096      1.0   68,590         1.0     13,604,081
     5,120      1.3   68,419         1.0     13,304,791
     6,144      1.5   67,734         1.0     12,055,937
     7,168      1.8   66,917         1.0     10,573,092
     8,192      2.0   66,918         1.0     10,573,092
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select sga_size            sgatsfe
     , sga_size_factor     sgasf
     , estd_db_time        edbts
--     , estd_db_time_factor edbtf
     , estd_physical_reads epr
  from DBA_HIST_SGA_TARGET_ADVICE
 where snap_id             = &eid
   and dbid                = &dbid
   and instance_number     = &inst_num
 order by sga_size;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SGA Memory Summary  DB/Inst: ORADB/oraDB1  Snaps: 66-67
                                                        End Size (Bytes)
SGA regions                      Begin Size (Bytes)       (if different)
------------------------------ -------------------- --------------------
Database Buffers                      3,456,106,496        3,556,769,792
Fixed Size                                2,078,328
Redo Buffers                             14,696,448
Variable Size                           822,086,024          721,422,728
                               -------------------- --------------------
sum                                   4,294,967,296        4,278,192,520
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.NAME,
       b.VALUE bval,
       decode(b.VALUE, e.VALUE, to_number(NULL), e.VALUE) eval
  FROM dba_hist_sga b, dba_hist_sga e
 WHERE e.snap_id = &eid
   AND e.dbid = &dbid
   AND e.instance_number = &inst_num
   AND b.snap_id = &bid
   AND b.dbid = &dbid
   AND b.instance_number = &inst_num
   AND b.NAME = e.NAME
 ORDER BY NAME;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

SGA breakdown difference  DB/Inst: ORADB/oraDB1  Snaps: 66-67
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
   Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
   insignificant, or zero in that snapshot

Pool   Name                                 Begin MB         End MB  % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory                              16.0           16.0     0.00
large  PX msg pool                               1.0            1.0     0.00
large  free memory                              47.0           47.0     0.00
shared ASH buffers                              50.0           50.0     0.00
shared CCursor                                  13.1           10.5   -19.68
shared FileOpenBlock                             8.7            8.7     0.00
shared KGH: NO ACCESS                                         161.9 ########
shared KGLS heap                                 9.0                 -100.00
shared KQR L PO                                  9.9                 -100.00
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT *
  FROM (SELECT nvl(e.pool, b.pool) pool,
               nvl(e.NAME, b.NAME) NAME,
               b.bytes / 1024 / 1024 snap1,
               e.bytes / 1024 / 1024 snap2,
               100 * (nvl(e.bytes, 0) - nvl(b.bytes, 0)) / nvl(b.bytes, 1) diff
          FROM (SELECT *
                  FROM dba_hist_sgastat
                 WHERE snap_id = &bid
                   AND dbid = &dbid
                   AND instance_number = &inst_num) b
          FULL OUTER JOIN (SELECT *
                            FROM dba_hist_sgastat
                           WHERE snap_id = &eid
                             AND dbid = &dbid
                             AND instance_number = &inst_num) e ON b.NAME =
                                                                   e.NAME
                                                               AND nvl(b.pool,
                                                                       'a') =
                                                                   nvl(e.pool,
                                                                       'a')
         ORDER BY nvl(e.bytes, b.bytes))
 WHERE pool IS NULL
    OR NAME = 'free memory'
    OR rownum <= 10
 ORDER BY pool, NAME;
 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL Memory Statistics  DB/Inst: ORADB/oraDB1  Snaps: 66-67
                                   Begin            End         % Diff
                          -------------- -------------- --------------
   Avg Cursor Size (KB):           29.47          10.35        -184.74
 Cursor to Parent ratio:            1.19           1.38          14.39
          Total Cursors:           2,913          2,302         -26.54
          Total Parents:           2,458          1,663         -47.81
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
***由于DBA_HIST_SQL_SUMMARY缺少total_cursors,不能代替stats$sql_statistics,AWR中无法实现
Avg Cursor Size (KB), Cursor to Parent ratio,Total Cursors,这里给出STATSPACK实现思路。注意,
total_cursors使用sum(v$sqlstats.version_count),sum(v$sql.version_count)实现。
--数据采集方法1:new method
create or replace view STATS$V_$SQLSTATS_SUMMARY as
select sql_id
     , sum(parse_calls)           parse_calls
     , sum(disk_reads)            disk_reads
     , sum(buffer_gets)           buffer_gets
     , sum(executions)            executions
     , sum(version_count)         version_count
     , sum(cpu_time)              cpu_time
     , sum(elapsed_time)          elapsed_time
     , sum(sharable_mem)          sharable_mem
  from v$sqlstats
 group by sql_id;
 
create or replace public synonym STATS$V$SQLSTATS_SUMMARY for STATS$V_$SQLSTATS_SUMMARY;


           insert into stats$sql_statistics
                ( snap_id
                , dbid
                , instance_number
                , total_sql
                , total_sql_mem
                , single_use_sql
                , single_use_sql_mem
                , total_cursors  
                )
           select l_snap_id
                , p_dbid
                , p_instance_number
                , count(1)
                , sum(sharable_mem)
                , sum(decode(executions, 1, 1,            0))
                , sum(decode(executions, 1, sharable_mem, 0))
                , sum(version_count)
             from stats$v$sqlstats_summary
            where sharable_mem > 0;

--数据采集方法2:old method
create or replace view STATS$V_$SQLXS as
select max(sql_text)        sql_text
     , max(sql_id)          sql_id
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(fetches)         fetches
     , sum(executions)      executions
     , sum(px_servers_executions) px_servers_executions
     , sum(end_of_fetch_count) end_of_fetch_count
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(direct_writes)   direct_writes
     , sum(buffer_gets)     buffer_gets
     , sum(application_wait_time)  application_wait_time
     , sum(concurrency_wait_time)  concurrency_wait_time
     , sum(cluster_wait_time)      cluster_wait_time
     , sum(user_io_wait_time)      user_io_wait_time
     , sum(plsql_exec_time)        plsql_exec_time
     , sum(java_exec_time)         java_exec_time
     , sum(rows_processed)  rows_processed
     , max(command_type)    command_type
     , address              address
     , old_hash_value       old_hash_value
     , max(hash_value)      hash_value
     , count(1)             version_count
     , sum(cpu_time)        cpu_time
     , sum(elapsed_time)    elapsed_time
     , max(outline_sid)     outline_sid
     , max(outline_category) outline_category
     , max(is_obsolete)     is_obsolete
     , max(child_latch)     child_latch
     , max(sql_profile)     sql_profile
     , max(program_id)      program_id
     , max(program_line#)   program_line#
     , max(exact_matching_signature) exact_matching_signature
     , max(force_matching_signature) force_matching_signature
     , max(last_active_time)         last_active_time
  from v$sql
 group by old_hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

           insert into stats$sql_statistics
                ( snap_id
                , dbid
                , instance_number
                , total_sql
                , total_sql_mem
                , single_use_sql
                , single_use_sql_mem
                , total_cursors
                )
           select l_snap_id
                , p_dbid
                , p_instance_number
                , count(1)
                , sum(sharable_mem)
                , sum(decode(executions, 1, 1,            0))
                , sum(decode(executions, 1, sharable_mem, 0))
                , sum(version_count)
           from stats$v$sqlxs
         where is_obsolete = 'N';

--Shared Pool Statistics
with v as(
SELECT nvl(b.total_cursors, 0) b_total_cursors,
       nvl(e.total_cursors, 0) e_total_cursors,
       nvl(b.total_sql, 0) b_total_sql,
       nvl(e.total_sql, 0) e_total_sql,
       nvl(b.total_sql_mem, 0) b_total_sql_mem,
       nvl(e.total_sql_mem, 0) e_total_sql_mem
  FROM stats$sql_statistics b, stats$sql_statistics e
 WHERE b.snap_id = &bid
   AND e.snap_id = &eid
   AND b.instance_number = &inst_num
   AND e.instance_number = &inst_num
   AND b.dbid = &dbid
   AND e.dbid = &dbid)
SELECT '                                   Begin            End         % Diff' ch78n,
       '                          -------------- -------------- --------------' ch78n,
       '   Avg Cursor Size (KB): ' ch25n,
       v.b_total_sql_mem / 1024 / v.b_total_cursors num8c_2,
       v.e_total_sql_mem / 1024 / v.e_total_cursors num8c_2,
       100 * ((v.e_total_sql_mem / 1024 / v.e_total_cursors) -
       (v.b_total_sql_mem / 1024 / v.b_total_cursors)) /
       (v.e_total_sql_mem / 1024 / v.e_total_cursors) num8c_2,
       ' Cursor to Parent ratio: ' ch25n,
       v.b_total_cursors / v.b_total_sql num8c_2,
       v.e_total_cursors / v.e_total_sql num8c_2,
       100 * ((v.e_total_cursors / v.e_total_sql) -
       (v.b_total_cursors / v.b_total_sql)) /
       (v.e_total_cursors / v.e_total_sql) num8c_2,
       '          Total Cursors: ' ch25n,
       v.b_total_cursors num10c,
       v.e_total_cursors num10c,
       100 * ((v.e_total_cursors) - (v.b_total_cursors)) /
       (v.e_total_cursors) num8c_2,
       '          Total Parents: ' ch25n,
       v.b_total_sql num10c,
       v.e_total_sql num10c,
       100 * ((v.e_total_sql) - (v.b_total_sql)) / (v.e_total_sql) num8c_2
  FROM sys.dual
 WHERE v.b_total_cursors > 0
   AND v.e_total_cursors > 0;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Resource Limit Stats
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT resource_name       rname,
       current_utilization curu,
       max_utilization     maxu,
       initial_allocation  inita,
       limit_value         lim
  FROM dba_hist_resource_limit
 WHERE snap_id = &eid
   AND dbid = &dbid
   AND instance_number = &inst_num
   AND (nvl(current_utilization, 0) / limit_value > .8 OR
       nvl(max_utilization, 0) / limit_value > .8)
 ORDER BY rname;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


init.ora Parameters  DB/Inst: ORADB/oraDB1  Snaps: 66-67
                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
audit_file_dest               /oracle/app/oracle/admin/oraDB/ad
background_dump_dest          /oracle/app/oracle/admin/oraDB/bd
cluster_database              TRUE
cluster_database_instances    2
compatible                    10.2.0.3.0
control_files                 /dev/rPO_CTL1, /dev/rPO_CTL2, /de
core_dump_dest                /oracle/app/oracle/admin/oraDB/cd
db_block_size                 8192
db_domain
db_file_multiblock_read_count 16
db_name                       oraDB
instance_number               1
job_queue_processes           10
...
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT e.parameter_name,
       b.VALUE bval,
       decode(b.VALUE, e.VALUE, ' ', e.VALUE) eval
  FROM dba_hist_parameter b, dba_hist_parameter e
 WHERE b.snap_id(+) = &bid
   AND e.snap_id = &eid
   AND b.dbid(+) = &dbid
   AND e.dbid = &dbid
   AND b.instance_number(+) = &inst_num
   AND e.instance_number = &inst_num
   AND b.parameter_name(+) = e.parameter_name
   AND translate(e.parameter_name, '_', '#') NOT LIKE '##%'
   AND (nvl(b.isdefault, 'X') = 'FALSE' OR
        nvl(b.ismodified, 'X') != 'FALSE' OR e.ismodified != 'FALSE' OR
        nvl(e.VALUE, 0) != nvl(b.VALUE, 0))
 ORDER BY e.parameter_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

End of Report ( sp_66_67.lst )

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-701154/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle11g_alert_log
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809921