Transcript Document

ASH – Active Session History
Feel the Power
Kyle Hailey
http://perfvision.com
#.2
ASH
Whole New Paradigm in
Technology
The Power of ASH lies in
Simplifying Performance Tuning
 Totally new and exciting methodology
 Cheaper, Quicker, Richer and better tasting too
Copyright 2006 Kyle Hailey
Why should you care?
Because ASH can Change your life …
 10g immediately Accessible
Geeks: Via scripts in SQL (?/rdbms/admin/ashrpt.sql)
 Managers : Graphical OEM 10g

 Stuck on Oracle 7,8,9 ?
… my apologies because 10g rocks, but …
no worries, the data is accessible for you too via scripts
http://perfvision.com/sash.php
Copyright 2006 Kyle Hailey
#.3
ASH uses Sampling
New Paradigm
 Clear Vision of the future
Use new sampling technology
 Lets go of the need to know 100%
 Statistical approximation

 Cheaper
 Richer Multidimensional Data
 Much more powerful, identifies
SQL
 Sessions
 Objects
 Machine
 User

Copyright 2006 Kyle Hailey
#.4
To identify Players before ASH
 Sessions
#
sessions x (# wait events + statistics)
 Example (150 x (800+200) = 150,000 )
 SQL
 v$sql


Could be 10000s
Takes out latches that compete with other sql executions
 Objects
 V$segstat

9i+
Could be 1000s of objects
Expensive !
Copyright 2006 Kyle Hailey
#.5
#.6
ASH – Intelligence for the new
Millennium
 Intelligently Collects Data
It self adjusts for your needs
 More activity, more data collected
 Less activity, less data collected

 Those old methods collected everything
Obfuscated the problem, too many statistics too late
 Costly
 Too Granular – once an hour ?! Give me a break

Copyright 2006 Kyle Hailey
ASH Samples Session State
Every second
10:00:01
10:00:00
?
10:00:03
10:00:02
?
?
10:00:05
10:00:04
?
?
Sessions change a lot quicker but can get the main
picture via sampling by sampling faster
TIME
#.8
Sampling is like taking
Pictures
Copyright 2006 Kyle Hailey
#.9
If happens a lot or for long …
we’ll catch it, guaranteed
• Long running queries easily found
• Short high execution queries found most of the time
• Short low execution queries rarely found
Produces a good representation of load on DB
Copyright 2006 Kyle Hailey
#.10
Session States
CPU
IO
Work
Wait
Latency
Contention
Copyright 2006 Kyle Hailey
Idle
#.11
IDLE
 Example : SQL*Net Message from Client
 10g
select name
from v$event_name
where wait_class='Idle';
58 Rows
 9i or below – just have to keep track
 Statspack
STATS$IDLE_EVENT
Copyright 2006 Kyle Hailey
#.12
CPU
 ASH: SESSION_STATE = “ON CPU”
 ASH: wait_time > 0
10g: v$session
9i: v$session_wait
w.wait_time != 0
/* on CPU */
and
s.status='ACTIVE' /* ACTIVE */
Identifying CPU via v$session_wait is
a huge break through
Copyright 2006 Kyle Hailey
#.13
IO
 ASH: SESSION_STATE=‘WAITING’
&
 10g: WAIT_CLASS=‘User I/O’
 9i:

Look at 10g for ideas




db file sequential read
db file scattered read
db file parallel read
direct path read
Copyright 2006 Kyle Hailey
#.14
WAITING




ASH: SESSION_STATE=‘WAITING’
ASH: WAIT_TIME <= 0
WAIT_CLASS
1.
Administrative
2.
Application
3.
Cluster
4.
Commit
5.
Concurrency
6.
Configuration
7.
Network
8.
Other
9.
Scheduler
10. System I/O
800+ WAIT
Copyright 2006 Kyle Hailey
Samples for all users
Session 1
Session 2
Session 3
Session 4
10:15:00
10:15:01
10:15:02
10:15:03
10:15:04
10:15:05
10:15:06
10:15:07
TIME
v$active_session_history
SAMPLE_ID
SAMPLE_TIME
NUMBER
TIMESTAMP(3)
SESSION_ID
SESSION_SERIAL#
USER_ID
SERVICE_HASH
SESSION_TYPE
PROGRAM
MODULE
ACTION
CLIENT_ID
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2(10)
VARCHAR2(64)
VARCHAR2(48)
VARCHAR2(32)
VARCHAR2(64)
SESSION_STATE
WAIT_TIME
EVENT
EVENT_ID
EVENT#
SEQ#
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
VARCHAR2(7)
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER0
SQL_ID
SQL_CHILD_NUMBER
SQL_PLAN_HASH_VALUE
SQL_OPCODE
QC_SESSION_ID
QC_INSTANCE_ID
TIME_WAITED
VARCHAR2(13)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
Copyright 2006 Kyle Hailey
NUMBER
#.16
When
Session
State
Wait
SQL
Duration
Groupings – Top Consumer
SESSION_ID
SESSION_SERIAL# (signal SID reuse)
SESSION_TYPE
(FOREGROUND,BACKGROUND)
USER_ID
(SYS, SYSTEM, SCOTT etc)
SERVICE_HASH (OE,GL,HR)
MODULE.ACTION(PLSQL tagging)
CLIENT_ID
(identifying users in a session pool)
PROGRAM
(SQL, JDBC, Forms etc)
SQL_ID
QC_SESSION_ID - Query Coordinator
QC_INSTANCE_ID – RAC
EVENT + P1, P2, P3
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
Copyright 2006 Kyle Hailey
#.17
Amazing things YOU can do with
ASH
Find top consumers by resource usage
Consumers
 Top Session
 Top User
 Top SQL
 Top Object
 Top Module.Action
 Top Program
 Top Service
 Top Client
 Top Wait
X
Resources
• CPU
• Waits
• Event (800*)
• I/O
• File
• Block
• Time
No machine  - collect on logon trigger
Copyright 2006 Kyle Hailey
#.18
Top CPU Session
Who is the rogue session ?
Select
session_id,
count(*)
from
v$active_session_history
where
session_state= ‘ON CPU‘ and
SAMPLE_TIME > sysdate – (5/(24*60))
group by
session_id
order by
count(*) desc;
Copyright 2006 Kyle Hailey
#.19
#.20
Results Top CPU Session
SESSION_ID
COUNT(*)
SESSION_ID
COUNT(*)
---------------------------- ---------256
265 265
256
264
15
264 257
1512
257 271
1212
271 276
12 1
276
1
Copyright 2006 Kyle Hailey
#.21
CPU with Bars
SESSION_ID
COUNT(*)
% Bar
---------- ---------- ---------- -----------257
75
25 |***
|
263
62
21 |**
|
256
32
11 |*
|
264
9
3 |
|
277
3
1 |
|
258
1
0 |
|
280
1
0 |
|
Bar shows 10% increments
Copyright 2006 Kyle Hailey
Top Waiting Session
 Top Waiting Session in last 5 minutes
Select
session_id,
count(*)
from
v$active_session_history
where
session_state=‘WAITING’ and
SAMPLE_TIME > SYSDATE - (5/(24*60))
group by
session_id
order by
count(*) desc;
Copyright 2006 Kyle Hailey
#.22
#.23
Top Waiting Session Results
SESSION_ID
COUNT(*)
---------- ---------272
224
254
8
249
5
276
5
277
4
270
1
Copyright 2006 Kyle Hailey
Top SQL from ASH
#.24
select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0))
"CPU",
sum(decode(ash.session_state,'WAITING',1,0)) sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1))
"TOTAL"
from v$active_session_history ash,
v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
Copyright 2006 Kyle Hailey
#.25
Top SQL from ASH Results
SQL_ID
CPU
WAITING
IO
TOTAL
------------- ---------- ---------- ---------- ---------4c1xvq9ufwcjc
23386
0
0
23386
6wjw6rz5uvbp3
99
0
23
122
968dm8hr9qd03
97
0
22
119
938jp5gasmrah
90
0
25
115
cv8xnv81kf582
42
0
9
51
6p9bzu19v965k
21
0
0
21
5zu8pxnun66bu
15
0
0
15
db2jr13nup72v
9
0
0
9
7ks5gnj38hghv
8
0
0
8
Copyright 2006 Kyle Hailey
#.26
Top Session
select select
ash.session_id,
ash.session_id,
ash.session_serial#,
ash.session_serial#,
ash.user_id,
ash.user_id,
ash.program,
ash.program,
sum(decode(ash.session_state,'ON
CPU',1,0))
sum(decode(ash.session_state,'ON
CPU',1,0))"CPU",
"CPU",
sum(decode(ash.session_state,'WAITING',1,0))
sum(decode(ash.session_state,'WAITING',1,0))- sum(decode(ash.session_state,'WAITING',
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User
I/O',1,
0 ), 0))
, ,
decode(en.wait_class,'User
I/O',1,
0 ), 0))"WAITING"
"WAITING"
sum(decode(ash.session_state,'WAITING',
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User
I/O',1,
0 ), 0))
, ,
decode(en.wait_class,'User
I/O',1,
0 ), 0))"IO"
"IO"
sum(decode(session_state,'ON
CPU',1,1))
sum(decode(session_state,'ON
CPU',1,1))"TOTAL"
"TOTAL"
from v$active_session_history
ash,
from v$active_session_history
ash,
v$event_name
en en
v$event_name
where en.event#
= ash.event#
where en.event#
= ash.event#
group by
session_id,user_id,session_serial#,program
group by session_id,user_id,session_serial#,program
order by
sum(decode(session_state,'ON
CPU',1,1))
order
by sum(decode(session_state,'ON
CPU',1,1))
Copyright 2006 Kyle Hailey
#.27
Top Session Results
SESSION_ID SERIAL#
USER_ID PROGRAM
CPU
WAITING
IO
---------- ------- ---------- ------------------------- ------- ---------- ---------247
61970
277
1
276
1 sqlplus
11698
0
0
0 oracle@labsfrh903 (LGWR)
14
21
0
1
0 oracle@labsfrh903 (CKPT)
19
10
0
278
1
0 oracle@labsfrh903 (DBW0)
29
0
0
280
1
0 oracle@labsfrh903 (PMON)
19
0
0
254
22617
5 Executor.exe
13
0
3
255
12877
5 Executor.exe
11
0
5
257
33729
5 Executor.exe
15
0
1
255
13417
5 Executor.exe
14
0
2
Copyright 2006 Kyle Hailey
Top Session w/ Username
select
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
"STATUS",
topsession.session_id
"SESSION_ID",
u.name "NAME",
topsession.program
"PROGRAM",
max(topsession.CPU)
"CPU",
max(topsession.WAITING)
"WAITING",
max(topsession.IO)
"IO",
max(topsession.TOTAL)
"TOTAL"
from ( {previous query} )
topsession,
v$session s,
user$ u
where
u.user# =topsession.user_id and
/* outer join to v$session because the session might be disconnected */
topsession.session_id
= s.sid
(+) and
topsession.session_serial# = s.serial# (+)
group by topsession.session_id, topsession.session_serial#, topsession.user_id,
topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc
Copyright 2006 Kyle Hailey
#.28
Top Session
Finding a Rogue User
STATUS
SESSION_ID NAME
PROGRAM
CPU
WAITING
IO
--------------- ---------- ---------- ------------------------- ----- ---------- ---CONNECTED
247 CPU_Monger ChMgr304.exe
11704
0
0
CONNECTED
277 SYS
oracle@labsfrh903 (LGWR)
14
19
0
CONNECTED
278 SYS
oracle@labsfrh903 (DBW0)
29
0
0
CONNECTED
276 SYS
oracle@labsfrh903 (CKPT)
18
9
0
CONNECTED
280 SYS
oracle@labsfrh903 (PMON)
20
0
0
DISCONNECTED
255 SYSTEM
Executor.exe
11
4
5
DISCONNECTED
257 SYSTEM
Executor.exe
13
0
3
DISCONNECTED
255 SYSTEM
Executor.exe
14
0
2
DISCONNECTED
257 SYSTEM
Executor.exe
13
0
3
Copyright 2006 Kyle Hailey
#.29
Act.sql : DB Detailed AAS
@act
Analysis Begin Time :
Analysis End
Time :
Start time, mins ago:
Request Duration
:
Collections
:
Data Values
:
Elapsed Time: 15 mins
2007-07-24 11:04:48
2007-07-24 11:19:45
15
15
528
3327
WAIT_EVENT
CNT
% Active Ave_Act_Sess
-------------------------------------- ---------- -----------latch free
10
.3
.02
log buffer space
13
.39
.02
buffer busy waits
14
.42
.03
db file scattered read
15
.45
.03
library cache pin
78
2.34
.15
log file sync
213
6.4
.4
ON CPU
726
21.82
1.38
enqueue
855
25.7
1.62
db file sequential read
1399
42.05
2.65
-----------sum
6.3
Copyright 2006 Kyle Hailey
#.30
Aveact.sql : AAS over time
@aveact
TM
NPTS AVEACT GRAPH
---------------- ------ ------- ---------------------06-AUG 13:00:00
270
.33 +2
06-AUG 14:00:00
1040
2.24 ++--------2--06-AUG 15:00:00
623
6.67 ++++------2---------06-AUG 16:00:00
1088
2.59 ++--------2---06-AUG 17:00:00
1104
1.26 ++----2
06-AUG 18:00:00
1093
1.38 +++---2
06-AUG 19:00:00
1012
1.74 ++------- 2
06-AUG 20:00:00
1131
.99 +---2
06-AUG 21:00:00
1111
1.22 ++----2
06-AUG 22:00:00
1010
1.66 ++------ 2
06-AUG 23:00:00
1120
1.08 +---2
07-AUG 00:00:00
1024
.83 +--2
07-AUG 01:00:00
1006
1.74 ++------- 2
07-AUG 02:00:00
1090
2.47 ++--------2---07-AUG 03:00:00
687
6.59 +++-------2---------07-AUG 04:00:00
1004
1.95 ++++++--- 2
07-AUG 05:00:00
1104
3.08 +++++-----2-----07-AUG 06:00:00
1122
1.91 +++++++-- 2
07-AUG 07:00:00
1115
1.06 +++--2
07-AUG 08:00:00
1140
.81 ++-2
07-AUG 09:00:00
1128
.88 ++--2
Copyright 2006 Kyle Hailey
CPU WAITS
---- ----29
59
341 1984
438 3718
335 2486
349 1043
663
842
373 1388
304
820
344 1012
414 1259
298
913
273
576
319 1428
347 2345
382 4142
1299
659
1170 2226
1582
558
559
618
403
519
386
601
#.31
Aveact.sql
Copyright 2006 Kyle Hailey
#.32
#.33
“-” = WAIT
“+” = CPU
which waits ? -> aveactn.sql
Copyright 2006 Kyle Hailey
#.34
Aveactn.sql
Copyright 2006 Kyle Hailey
Many Ways to Attack Problems
Confusing
 How to Attack the problem?
 Top SQL?

Top wait for that SQL?
 Top Waiting Session ?

Top Waits for that Session
 Top Waits for Database?
Top Session waiting for that wait
 Top SQL for that wait

Solution - Graphics
Copyright 2006 Kyle Hailey
#.35
#.36
Mining Data is Non Trivial
 Many Dimensions to consider
 Constantly Varying time frames
 Luckily 10g automates all of this functionality.
 collects
all of the data
 analyses all the wait events
 reports on bottlenecks
 supplies solutions
 Graphics add tremendous ease and power
Copyright 2006 Kyle Hailey
Graphical ASH
Session 1
Session 2
Session 3
Session 4
TIME
#.38
Graph of User States
Copyright 2006 Kyle Hailey
#.39
One Second Graph
Copyright 2006 Kyle Hailey
#.40
15 Second Averages
Copyright 2006 Kyle Hailey
#.41
Maximum CPU Line
Copyright 2006 Kyle Hailey
#.42
Idle Users
Copyright 2006 Kyle Hailey
OEM Perf Page
Copyright 2006 Kyle Hailey
#.43
OEM Perf Page
Copyright 2006 Kyle Hailey
#.44
ASH
vs
Statistics
 Statistics
 are
more expensive
 have lag time
 lack clear identification of culprits
Copyright 2006 Kyle Hailey
#.45
#.46
Statistic Lag Time
Samples
Slight Lags
Counters
Copyright 2006 Kyle Hailey
#.47
CPU Lag Problem
 ASH is the only way to see CPU usage
realtime
 V$sysstat reports CPU but
 is
only updated at the end of the call.
 Long calls look deceiving like no CPU is being
used
Copyright 2006 Kyle Hailey
CPU in ASH vs Stats
Copyright 2006 Kyle Hailey
#.48
#.49
ASH Sampling Cheap
Three Methods
1. With PL/SQL it’s less that 1 % CPU with 10
average active sessions
2. DMA - direct memory attach to SGA Using C
code 200x Cheaper
3. ASH in 10g should be even cheaper than
DMA because it’s done in the kernel
Copyright 2006 Kyle Hailey
#.50
ASH – In Memory
 Collects active session data only
 History v$session_wait + v$session + extras
 Circular Buffer - 1M to 128M (~2% of SGA)
 Flushed every hour to disk or when buffer 2/3
full (it protects itself so you can relax)
Copyright 2006 Kyle Hailey
#.51
ASH Sizing …
 Avg row around 150bytes
 3600 secs in an hour
 ~ ½ Meg per Active Session per hour
 That’s generally over an hour of ASH
Copyright 2006 Kyle Hailey
#.52
How ASH works
MMON/
MMNL
v$active_session_history
V$session_wait
+ v$session +
extras
Only writes out 1 in 10 rows
via direct path inserts
wrh$_active_session_history
Copyright 2006 Kyle Hailey
#.53
ASH buffer
Insert one direction
Select reads
backwards
- Touch up wait times
-No latching
-No read consistency
-Index on time
Insert point
Copyright 2006 Kyle Hailey
#.54
Family of ASH Tables
v$session_wait
v$active_session_history
V$session_wait_history
DBA_HIST_ACTIVE_SESS_HISTORY
wrh$active_session_history
Copyright 2006 Kyle Hailey
ASH Tables
current
SID
SEQ#
EVENT#
SAMPLE_ID
SAMPLE_TIME
EVENT
SESSION_ID
P1TEXT
SESSION_SERIAL#
P1
USER_ID
P2TEXT
SQL_ID
P2
SQL_CHILD_NUMBER
P3TEXT
P3
WAIT_TIME
SQL_PLAN_HASH_VALUE
SQL_OPCODE
SERVICE_HASH
SESSION_TYPE
SESSION_STATE
QC_SESSION_ID
QC_INSTANCE_ID
SEQ#
EVENT#
P1
P2
v$event_name
P3
WAIT_TIME
EVENT_ID
TIME_WAITED
EVENT#
CURRENT_OBJ#
EVENT_ID
CURRENT_FILE#
NAME
CURRENT_BLOCK#
PARAMETER1
PARAMETER2
PARAMETER3
7 days (disk)
hour
10 samples
v$session_wait v$session_wait_history v$active_session_history
SID
SEQ#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
WAIT_TIME
SECONDS_IN_WAIT
STATE
#.55
PROGRAM
MODULE
ACTION
CLIENT_ID
WAIT_CLASS_ID
1 in 10
DBA_HIST_ACTIVE_SESS_HISTORY
wrh$active_session_history
SNAP_D
DBID
INSTANCE_NUMBER
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
SEQ#
EVENT_ID
P1
P2
P3
WAIT_TIME
TIME_WAITED
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
PROGRAM
MODULE
WAIT_CLASS#
ACTION
WAIT_CLASS
CLIENT_ID
Copyright 2006 Kyle Hailey
Wait Time vs Time Waited
 SESSION_STATE
 Waiting,
on CPU
 Based on WAIT_TIME
 WAIT_TIME (v$session, v$session_wait, v$ash)
0 => waiting
 >0 => CPU

(value is time of last wait)
 TIME_WAITED
 Actual
time waited for event
 0 until wait finishes
 Fix up values (no one else can do this)
Copyright 2006 Kyle Hailey
#.56
#.57
Oradebug
 Dump to trace file
SQL>
oradebug
SQL>
oradebug
dumpdump
ash 5ashdump 5
SQL> Alter session set events ‘immediate
SQL> Alter session set events ‘immediate
tracename
level
tracenameashdump
ashdump
level 5’;
5’;
level 5 = # of minutes
loader file rdbms/demo/ashldr.ctl
Copyright 2006 Kyle Hailey
INIT.ORA
statistics_level
= Typical (default)
PARAMETER
SESSION_VALUE
INSTANCE_VAL
----------------------- ---------- -----------_ash_sampling_interval
1000
1000
milliseconds
_ash_size
1048618
1048618
ASH buffer size
_ash_enable
TRUE
TRUE
Turn on/off ASH sampling, flushing and the V$ views on ASH
_ash_disk_write_enable
TRUE
TRUE
Flush to disk
_ash_disk_filter_ratio
10
10
write 1 in 10 points
_ash_sample_all
FALSE
FALSE
Sample including idle waits
Copyright 2006 Kyle Hailey
#.58
10.2 ASH Extras
#.59
 BLOCKING_SESSION
 BLOCKING_SESSION_STATUS
 BLOCKING_SESSION_SERIAL#
 P1TEXT
 P2TEXT
 P3TEXT
 WAIT_CLASS
 WAIT_CLASS_ID
 XID
 FORCE_MATCHING_SIGNATURE
7/22/2015
59
#.60
10.2 Extras
 10gR2 add fields to ASH
Blocking Session Id, serial# and state ***
 XID, transaction ID
 RAC event Fixup
 Plan Hash Fix up



10gR1 - during parsing no plan, good way to find parsing
problems
10gR2 – get plan hash fixup – good but lose some ability to find
parsing problems
Wait Class, needed this for grouping
 Force_matching_signature – sql profile id

Copyright 2006 Kyle Hailey
#.61
10.2.0.3 ASH Extras
PLSQL_ENTRY_OBJECT_ID
PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID
PLSQL_SUBPROGRAM_ID
ALL_PROCEDURES
where object_id = plsql_object_id
and subprogram_id = plsql_subprogram_id
7/22/2015
61
#.62
PLSQL Tracking
calling_code
SQL_ID
COUNT(*)
----------------------------------------------- ---------Package/Procedure/SQL tracking
10.2.0.3 258
ORDERENTRY.NEWORDER
0uuqgjq7k12nf
ORDERENTRY.NEWCUSTOMER
0bzhqhhj9mpaa
262
ORDERENTRY.BROWSEANDUPDATEORDERS 41zu158rqf4kf
301
ORDERENTRY.NEWORDER
0yas01u2p9ch4
569
PARSE_SAME
3vjxpmhhzngu4
874
ORDERENTRY.BROWSEANDUPDATEORDERS 05s4vdwsf5802
1669
select
object_name package||.|| procedure_name, sql_id, count(*)
from v$active_session_history ash,
all_procedures procs
where
ash.PLSQL_ENTRY_OBJECT_ID = procs.object_id
and ash.PLSQL_ENTRY_SUBPROGRAM_ID = procs.SUBPROGRAM_ID
7/22/2015
62
11g ASH extras
CPU Analysis – non Timed
IN_CONNECTION_MGMT
IN_PARSE
IN_HARD_PARSE
IN_SQL_EXECUTION
IN_PLSQL_EXECUTION
IN_PLSQL_RPC
IN_PLSQL_COMPILATION
IN_JAVA_EXECUTION
IN_BIND
IN_CURSOR_CLOSE
SQL Elapsed
SQL_EXEC_ID
SQL_EXEC_START
SQL Row Source
SQL_PLAN_LINE_ID
SQL_PLAN_OPERATION
SQL_PLAN_OPTIONS
RAC – remote transfers
REMOTE_INSTANCE#
ETC
Recursive SQL
TOP_LEVEL_SQL_ID
TOP_LEVEL_SQL_OPCODE
CURRENT_ROW#
EVENT#
QC_SESSION_SERIAL#
CONSUMER_GROUP_ID
FLAGS
Copyright 2006 Kyle Hailey
#.63
#.64
11g ASH extras
Run-time SQL row source information

Identifies current row source within plan
SQL execution ID


Is this same execution as last sample?
can see how long sql has been running
Operation bit vector


Capture non-timed operations
Examples: fetch, binding, close
Remote instance id for Cache transfers

Which instance sourced requested block?
Copyright 2006 Kyle Hailey
#.65
How Many Active Sessions?
 How much data does ASH Collect ?
 1 CPU means max 1 Avg Active Session unless
there is a bottleneck
Big site examples:
 Oracle 4 way RAC internal apps

10,000 connected, 200 active
 One Site

3000 connected, 30 Active
 Site

12,000 connected, 100 active
Copyright 2006 Kyle Hailey
#.66
Simulating ASH
 ASH is new in 10g
 ASH data exist since V7
 Need Diagnostic Pack License 
 Collect it yourself 
 SASH – Simulated ASH
http://perfvision.com/sash.php
 Graphical Monitor
http://perfvision.com/ashmon.php
Consumes < 1% CPU for 10 active sessions (a lot)
Copyright 2006 Kyle Hailey
#.67
Sampling
 Sampling is the future
 Took clean vision to create
 Bit
of a leap of faith
 Less Accurate but more powerful than past
 Inaccuracies
unimportant for the most part
 Feasible since version 7
 You
can implement it yourself
 Graphics are the only way to full harness the
power
Copyright 2006 Kyle Hailey
#.68
Summary
 To solve hard problems you need Sampling
 If you have 10g and a license you can use
 V$active_session_history
 OEM
10g (can access some 9 sampling)
 ?/rdbms/admim/ashrpt.sql
 If you are on 7,8,9 or 10g without the license
 Need


custom sampling
SASH – Simulated ASH http://perfvision.com/ash.php
ASHMON – graphical http://perfvision.com/ashmon.php
Copyright 2006 Kyle Hailey
#.69
ASH Summary
Faster, Cheaper, instant (no lag) …
but most importantly
Rich & Multidimensional
Overview of system load
 Drilldowns into problems





Sql
Session
Wait event
Details information on problem resolution
 OEM simplifies mining of ASH data
Copyright 2006 Kyle Hailey