Transcript It’s time to do ASH! Tuesday, Februrary 8th 2005 Gaja Krishna Vaidyanatha Principal, DBPerfMan LLC [email protected] http://www.dbperfman.com.

It’s time to do ASH!
Tuesday, Februrary 8th 2005
Gaja Krishna Vaidyanatha
Principal, DBPerfMan LLC
[email protected]
http://www.dbperfman.com
Confession#1
I am not an expert…not by any
stretch of the imagination.
Confession#2
I am an engineer not a scientist.
Confession#3
Advanced Tuning, Turbo-charged
Tuning, Push-Me-For-More-Power
Tuning…Lies…Just Plain Lies…
Or is it called Marketing these days!!!
Confession#4
There is only one way to optimize
Oracle performance – The Right
Way...Using the Wait Interface
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
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”
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
Oracle 10g ASH!
ASH = V$SESSION_WAIT++ with History

Note: In 10g V$SESSION_WAIT is integrated with
V$SESSION
It facilitates spot analysis of both foreground and
background sessions
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 < 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
Components of ASH
Memory buffers in the fixed areas
New Oracle Background Process

MMNL – MMON Lite
V$ACTIVE_SESSION_HISTORY
X$ASH
DBA_HIST_ACTIVE_SESS_HISTORY

Based on WRH$_ACTIVE_SESSION_HISTORY
ASH Architecture
Indexed on time
Indexed on time
V$ACTIVE_SESSION_HISTORY
X$ASH
WRH$_ACTIVE_SESSION_HISTORY
Circular buffer
MMON Lite
(MMNL)
in SGA
Every
30 mins
or
when buffer is
full
AWR
Direct-path
inserts
Samples with variable
size rows
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)
ASH Details - General
30-minute circular buffer in the SGA - GOAL

May scale down to smaller duration on large systems
Circular Buffer Sizing Formula:
Max( 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
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 = 100*60*17*300 bytes ~ 29.18MB
Fudge Factor of 0.82 MB
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
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 < 1
millisecond
The sampler (MMNL) does not take any latches
It supports dirty reads
Can write to the in-memory buffer without any issues
ASH Details – View Describe
SQL> desc v$active_session_history
Name
--------------------------------------SAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
EVENT
EVENT_ID
EVENT_#
SEQ#
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
ACTION
CLIENT_ID
Null?
-------
Type
---------------------------NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(10)
VARCHAR2(7)
NUMBER
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(64)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
ASH Details – View Definition
SQL> select view_definition from v$fixed_view_definition
2* where view_name = 'GV$ACTIVE_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_value, 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 x$kewash s, x$ash 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
ASH Details – WRH$_ASH View Describe
SQL> desc wrh$_active_session_history
Name
-----------------------------------SNAP_ID
DBID
INSTANCE_NUMBER
SAMPLE_ID
SAMPLE_TIME
SESSION_ID
SESSION_SERIAL#
USER_ID
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SERVICE_HASH
SESSION_TYPE
SQL_OPCODE
QC_SESSION_ID
QC_INSTANCE_ID
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
SEQ#
EVENT_ID
P1
P2
P3
WAIT_TIME
TIME_WAITED
PROGRAM
MODULE
ACTION
CLIENT_ID
Null?
-------NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
Type
--------------NUMBER
NUMBER
NUMBER
NUMBER
TIMESTAMP(3)
NUMBER
NUMBER
NUMBER
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(48)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
Using ASH – Some Initial Findings
Querying V$ACTIVE_SESSION_HISTORY
needs a session

New logins may be impossible on badly crippled
systems
Query V$ACTIVE_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
Proposed Workaround for Limitations
1. Data in buffer is first dumped to a process trace file –
Host 1
2. Transport the file (ftp) to another box – Host 2
3. Sanitize the file of its headers and other information on
Host 2
4. Build a SQL*Loader Control File for the load on Host 2
5. Create a user-defined ASH table (with the same
structure) in a database in Host2
6. Load the data from #3 using #4 into #5
Optional Demo!!!
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 SQL*Load trace data into
another database
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…;-)
Thank You!
Visit us at http://www.dbperfman.com
Gaja Krishna Vaidyanatha, Principal, DBPerfMan LLC, [email protected]