ANBOB™

《Know More about Oracle AWR》 姐妹篇，oracle 10g引入，ASH 是性能诊断工具，不像sql trace总是默认打开， 保存历史上发生了什么， 提供比AWR更细粒度的数据，即使当系统负载很高时，它也工作的很好，因为使用类似memcopy的数据收集，不会有太大的额外负担，没用任何类型的数据库lock。  Active(‘Active’ == Non-idle sessions) Session History Licensed as part of the Diagnostic pack,

An instrumentation mechanism that actualizes an important concept

Time-based sampling of foreground session state
– Highly multi-dimensional view of database activity and therefore DB Time
Observations of specific values of the (DB Time/time) function
– This function is called: Average Active Sessions

Samples represent “snapshots” of session activity at “same time”
– Not really true since using latchless mechanism
Sampling is time independent of session activity
– Important since otherwise sessions may be over or under-sampled

Session activity sampled efficiently into memory and onto disk

In-memory ASH sampling:
– Dedicated background process: MMNL
– Circular SGA memory buffer: one writer; many readers
– Lean and robust mechanism: no locking or latching
– Default 1000ms (1 sec) sampling interval
ASH sub-sampling to disk:
– Flush to AWR with snapshot or on emergency flush
– Default: 1-in-10 of the 1-sec samples are persisted
– Future: continuous sub-sampling

Reading / Writing in Opposite Directions

MMNL writes to ASH circular buffer one way
• Readers of V$ASH start at current write pointer
• Readers proceed in opposite direction of
MMNL through buffer
• Stop when current sample_id > last read sample_id
• SELECT from V$ASH returned recent-last order

Sampling Pseudo-code

1) FOR ALL SESSION STATE OBJECTS
2) IS SESSION CONNECTED?
NO => NEXT SESSION
YES:
3) IS SESSION ACTIVE?
NO => NEXT SESSION
YES:
4) MEMCPY SESSION STATE OBJ
5) CHECK CONSISTENCY OF COPY WITH LIVE SESSION
6) IS COPY CONSISTENT?
YES:
WRITE ASH ROW FROM COPY
NO:
IF FIRST COPY, REPEAT STEPS 4-6
ELSE => NEXT SESSION (NO ASH ROW WRITTEN)

Default Settings

These are carefully chosen for maximum general utility

Sampling interval = 1000ms = 1 sec
Disk filter ratio = 10 = 1 in 10 samples written to AWR
ASH buffer size:
– Min( Max (5% shared pool, 2% SGA), 2MB per CPU)
– Absolute Max of 256MB

NOTE: the MMNL sampler session is not sampled

Control Parameters

_ash_size : size of ASH buffer in bytes
– K/M notation works (e.g. 200M)
 _ash_sampling_interval : in milliseconds
– Min = 100, Max = 10,000
_ash_disk_filter_ratio : every Nth sample to AWR
– MOD(sample_id, N) = 0 where N=disk filter ratio
_sample_all : samples idle and active sessions

ASH is Robust when CPU-constrained

1. ASH sampler is very efficient and does not lock
– Should complete a sample within a single CPU slice
2. Sampler uses pre-scheduled sampling times
– After sampling computes next scheduled sample time and sleeps
3. Upon scheduled wake-up, it waits for CPU (runq) and samples again
– Each sample shifted by one runq wait
– Intervals stay close to 1 second when runq times are short and consistent

• Samples ‘Active’ sessions every second
• Writes into ASH buffer in SGA memory
• Data volume based on activity

SQL> select * from v$sgastatwhere name like ‘ASH buffers’;

SQL> select min(sample_time), max(sample_time) from v$active_session_history;

if the buffer fills up before the AWR flushes (by default every hour) the MMNL process will flush the data to disk (data stored in dba_hist_active_session_history).

ASH 架构

— update 2020-01-14 —-
从12c开始 ASH 从memroy flush 到DISK的频率有所改变，而是每300秒就开始从v$active_session_history刷新到dba_hist_active_sess_history中查询到。12c中与ash相关的参数有

SQL> select
  2        n.indx
  3      , to_char(n.indx, 'XXXX') i_hex
  4      , n.ksppinm pd_name
  5      , c.ksppstvl pd_value
  6      , n.ksppdesc pd_descr
  7     from sys.x$ksppi n, sys.x$ksppcv c
  8     where n.indx=c.indx
  9     and  
 10        lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('%\_ash%')
 11  escape '\'   ;

      INDX I_HEX NAME                                               VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------- ------------------------------ ----------------------------------------------------------------------
      4546  11C2 _ash_sampling_interval                             1000                           Time interval between two successive Active Session samples in
                                                                                                   millisecs

      4547  11C3 _ash_size                                          1048618                        To set the size of the in-memory Active Session History buffers
      4548  11C4 _ash_enable                                        TRUE                           To enable or disable Active Session sampling and flushing
      4549  11C5 _ash_disk_write_enable                             TRUE                           To enable or disable Active Session History flushing
      4550  11C6 _ash_disk_filter_ratio                             10                             Ratio of the number of in-memory samples to the number of samples
                                                                                                   actually written to disk

      4551  11C7 _ash_eflush_trigger                                66                             The percentage above which if the in-memory ASH is full the emergency
                                                                                                   flusher will be triggered

      4552  11C8 _ash_sample_all                                    FALSE                          To enable or disable sampling every connected session including ones
                                                                                                   waiting for idle waits

      4553  11C9 _ash_dummy_test_param                              0                              Oracle internal dummy ASH parameter used ONLY for testing!
      4554  11CA _ash_min_mmnl_dump                                 90                             Minimum Time interval passed to consider MMNL Dump
      4555  11CB _ash_compression_enable                            TRUE                           To enable or disable string compression in ASH
      4556  11CC _ash_progressive_flush_interval                    300                            ASH Progressive Flush interval in secs

11 rows selected.

References

https://www.oracle.com/technetwork/database/manageability/con9577-ash-deep-dive-oow2013-2031468.pdf

打赏

微信扫一扫，打赏作者吧～