Its time to do ASH

1
Its time to do ASH!

• Tuesday, Februrary 8th 2005

Gaja Krishna VaidyanathaPrincipal, DBPerfMan
LLCgaja_at_dbperfman.com http//www.dbperfman.com
2
I am not an expertnot by any stretch of the
imagination.
Confession1
3
I am an engineer not a scientist.
Confession2
4
Advanced Tuning, Turbo-charged Tuning,
Push-Me-For-More-Power TuningLiesJust Plain
LiesOr is it called Marketing these days!!!
Confession3
5
There is only one way to optimize Oracle
performance The Right Way...Using the Wait
Interface
Confession4
6
Plan of Action

• What is ASH?
• Oracle 10g ASH!
• Why should you use ASH?
• Components of ASH
• ASH Architecture
• ASH Details
• Using ASH - Some Initial Findings
• Future Ideas for ASH
• Conclusion

7
What is ASH?

• Non-Oracle ASH
• Ashland Inc. (NYSE ASH)
• Action on Smoking and Health
• American Society of Hypertension
• ASH Karo!!!
• Means Have FUN in Hindi
• ASH in Oracle 10g
• Active Session History
• ASH Karo
• Said in another way Use Oracle 10g ASH

8
Oracle 10g ASH!

• New source of Oracle database performance data in
  10g
• An active session is one which is in a user call
• Parse
• Execute
• Fetch
• On the CPU
• Provides historical information about recently
  sampled active sessions

9
Oracle 10g ASH!

• ASH VSESSION_WAIT with History
• Note In 10g VSESSION_WAIT is integrated with
  VSESSION
• It facilitates spot analysis of both foreground
  and background sessions

10
Why should you use ASH?

• Great for performance diagnostics
• Logs wait events along with SQL details and
  session-specific context in a circular buffer in
  memory
• Fixed session sampling algorithm uses lt 0.1 of 1
  CPU
• Can be modified by the use of an _ parameter
• Primary data provider for the Automatic Database
  Diagnostic Monitor (ADDM)
• ADDM supports proactive performance diagnostics
  within the Oracle Kernel

11
Components of ASH

• Memory buffers in the fixed areas
• New Oracle Background Process
• MMNL MMON Lite
• VACTIVE_SESSION_HISTORY
• XASH
• DBA_HIST_ACTIVE_SESS_HISTORY
• Based on WRH_ACTIVE_SESSION_HISTORY

12
ASH Architecture
13
ASH Details - General

• No installation or setup required
• Intended 30-min circular buffer in the SGA
• In memory ASH contains as much history as it can
  store.
• Circular buffer not cleared when written to disk
• ASH on Disk (1 of 10 in memory samples)
• Init.ora
• STATISTICS_LEVEL TYPICAL (Default)
• Master Switch
• _ACTIVE_SESSION_HISTORY TRUE (Default)

14
ASH Details - General

• 30-minute circular buffer in the SGA - GOAL
• May scale down to smaller duration on large
  systems
• Circular Buffer Sizing FormulaMax( Min ( of
  CPUs 2MB, 5 of SHARED_POOL_SIZE, 30MB), 1MB)
• If SHARED_POOL_SIZE is not explicitly set
• Formula changes to 2 of SGA target

15
ASH Details - General

• Assumptions for MAX Size - 30MB
• 100 active sessions
• Sampled at once per second (60 samples in 1
  minute)
• Assume 17 minutes of non-stop collection
• Assume 300 bytes per sample
• Size 1006017300 bytes 29.18MB
• Fudge Factor of 0.82 MB

16
ASH Details - General

• History flushed to Automatic Workload Repository
  (AWR) every 30 minutes
• Part of the AWR snapshot
• Database metrics
• Session Wait Information
• Hot files and segments

17
ASH Details - General

• Sampling done every second
• Can support sub-second sampling
• _ash_sampling_interval 1000 (milliseconds by
  default)
• Can dump to process trace (if required)
• Estimated 2500 CPU Instructions per active
  session per sample
• 400 active sessions on a 1 Ghz processor consumes
  lt 1 millisecond
• The sampler (MMNL) does not take any latches
• It supports dirty reads
• Can write to the in-memory buffer without any
  issues

18
ASH Details View Describe

• SQLgt desc vactive_session_history
• Name Null?
  Type
• --------------------------------------- -------
  ----------------------------
• SAMPLE_ID
  NUMBER
• SAMPLE_TIME
  TIMESTAMP(3)
• SESSION_ID
  NUMBER
• SESSION_SERIAL
  NUMBER
• USER_ID
  NUMBER
• SQL_ID
  VARCHAR2(13)
• SQL_CHILD_NUMBER
  NUMBER
• SQL_PLAN_HASH_VALUE NUMBER
• SQL_OPCODE
  NUMBER
• SERVICE_HASH
  NUMBER
• SESSION_TYPE
  VARCHAR2(10)
• SESSION_STATE
  VARCHAR2(7)
• QC_SESSION_ID
  NUMBER
• QC_INSTANCE_ID
  NUMBER
• EVENT VARCHAR2(64)
• EVENT_ID NUMBER

19
ASH Details View Definition

• SQLgt select view_definition from
  vfixed_view_definition
• 2 where view_name 'GVACTIVE_SESSION_HISTORY'
  
• VIEW_DEFINITION
  
• --------------------------------------------------
  ------------------------------
• SELECT / no_merge ordered use_nl(s,a) /
  a.inst_id, s.sample_id, s.sample_time,
  a.session_id, a.session_serial, a.user_id,
  a.sql_id, a.sql_child_number, a.sql_plan_hash_val
  ue, a.sql_opcode, a.service_hash,
• decode(a.session_type, 1,'FOREGROUND',
  2,'BACKGROUND',
• 'UNKNOWN'),
• decode(a.wait_time, 0, 'WAITING', 'ON CPU'),
• a.qc_session_id, a.qc_instance_id, a.event,
  a.event_id, a.event, a.seq, a.p1, a.p2, a.p3,
  a.wait_time, a.time_waited, a.current_obj,
  a.current_file, a.current_block, a.program,
  a.module, a.action, a.client_id
• FROM xkewash s, xash a
• WHERE s.sample_addr a.sample_addr and
• s.sample_id a.sample_id and
• s.sample_time a.sample_time
  
  
  

The New Oracle 10g Car Wash
20
ASH Details WRH_ASH View Describe

• SQLgt desc wrh_active_session_history
• Name
  Null? Type
• ------------------------------------ --------
  ---------------
• SNAP_ID NOT NULL NUMBER
• DBID NOT NULL
  NUMBER
• INSTANCE_NUMBER NOT NULL NUMBER
• SAMPLE_ID NOT NULL NUMBER
• SAMPLE_TIME NOT NULL TIMESTAMP(3)
• SESSION_ID NOT NULL NUMBER
• SESSION_SERIAL NUMBER
• USER_ID NUMBER
• SQL_ID
  VARCHAR2(13)
• SQL_CHILD_NUMBER NUMBER
• SQL_PLAN_HASH_VALUE NUMBER
• SERVICE_HASH NUMBER
• SESSION_TYPE NUMBER
• SQL_OPCODE NUMBER
• QC_SESSION_ID NUMBER
• QC_INSTANCE_ID NUMBER

21
Using ASH Some Initial Findings

• Querying VACTIVE_SESSION_HISTORY needs a session
• New logins may be impossible on badly crippled
  systems
• Query VACTIVE_SESSION_HISTORY requires all
  relevant latches in the SQL layer
• On systems crippled on shared pool and library
  cache latches, queries to ASH will impose even
  more overhead on these latches

22
Proposed Workaround for Limitations

• Data in buffer is first dumped to a process trace
  file Host 1
• Transport the file (ftp) to another box Host 2
• Sanitize the file of its headers and other
  information on Host 2
• Build a SQLLoader Control File for the load on
  Host 2
• Create a user-defined ASH table (with the same
  structure) in a database in Host2
• Load the data from 3 using 4 into 5

23
Optional Demo!!!
24
Future Ideas for ASH

• Keep a persistent and reserved session
• Eliminates the need to logon
• Pre-compile some standard cursors on ASH,
• Eliminates the need to soft parse
• Open a non-PL/SQL API so that data from the
  collector can be directly read
• Eliminates the need to SQLLoad trace data into
  another database

25
Conclusion

• Great performance diagnostic data source
• But it is only for 10g
• Granular enough data for most problems
• Makes for a good performance management suite
  when combined with ADDM and AWR
• Brand new code Maturity will come with time
• Yet to replace 3rd-party direct-SGA-attached
  collectors
• But it may be cheaper-)

26
Thank You!
Visit us at http//www.dbperfman.com
Gaja Krishna Vaidyanatha, Principal, DBPerfMan
LLC, gaja_at_dbperfman.com