ASH Report For dbone/dbone

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
dbone	118511790	dbone	1	10.2.0.5.0	NO	dbone.com

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
16	40,960M (100%)	37,584M (91.8%)	3,297M (8.0%)	29.0M (0.1%)

	Sample Time	Data Source
Analysis Begin Time:	06-Nov-13 18:01:00	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	06-Nov-13 18:09:00	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	8.0 (mins)
Sample Count:	1,468
Average Active Sessions:	3.06
Avg. Active Session per CPU:	0.19
Report Target:	None specified

ASH Report

Top Events

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
latch: library cache	Concurrency	63.22	1.93
library cache lock	Concurrency	19.21	0.59
CPU + Wait for CPU	CPU	14.17	0.43
cursor: pin S wait on X	Concurrency	2.59	0.08

Top Background Events

No data exists for this section of the report.

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
latch: library cache	63.28	"43640529536","217","0"	39.17	address	number	tries
		"43640529216","217","0"	7.08
		"43640530176","217","0"	5.11
library cache lock	19.21	"44486143872","40921970944","201"	2.25	handle address	lock address	100*mode+namespace
		"44486143872","43054431224","201"	2.04
		"44486143872","41090646224","201"	1.84
cursor: pin S wait on X	2.59	"155740597","26942329847808","21474836631"	0.07	idn	value	where\|sleeps

Load Profile

Top Service/Module

Service	Module	% Activity	Action	% Action
SYS$USERS	UNNAMED	64.51	UNNAMED	64.51
	JDBC Thin Client	11.92	UNNAMED	11.92
dbone	PL/SQL Developer	9.13	SQL Window - create profile PF_a	7.63
			SQL Window - lock.sql	1.43
SYS$USERS	perl@dbone.com (TNS V1-V3)	6.88	UNNAMED	6.88
	emagent_SQL_oracle_database	4.16	streams_processes_count	2.66

Back to Load Profile
Back to Top

Top Client IDs

No data exists for this section of the report.

Back to Load Profile
Back to Top

Top SQL Command Types

'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period

SQL Command Type	Distinct SQLIDs	% Activity	Avg Active Sessions
SELECT	21	36.51	1.12
INSERT	9	15.19	0.46
PL/SQL EXECUTE	7	12.94	0.40
GRANT	6	7.49	0.23
UPDATE	5	2.93	0.09

Back to Load Profile
Back to Top

Top SQL

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
cpj497kjx0dya	4281692526	11.99	CPU + Wait for CPU	5.79	select u.* from......
			latch: library cache	3.95
			library cache lock	1.63
3w4bwg9ubxzuy		7.77	latch: library cache	7.77	BEGIN protect_check_limit(:1, ...
2fry4cd25dnbx	2699508762	6.68	library cache lock	4.09	select t.cn, t.master, t.p...
			latch: library cache	1.36
			cursor: pin S wait on X	1.16
ff9yk9p2xgqqj	2924976181	6.06	library cache lock	4.43	INSERT INTO USER_CERTIFICATION...
			latch: library cache	1.57
		3.68	library cache lock	2.79	INSERT INTO USER_CERTIFICATION...

Top SQL using literals

Plan Hash	% Activity	# of Sampled SQL Versions	Example SQL 1	Example SQL TEXT 1	Example SQL 2	Example SQL TEXT 2
4281692526	24.52	360	0001yvmrg657p	select security_email from use...	gzza6b2scxk03	select security_email from use...
2924976181	10.63	156	0np5nasdr3z4u	INSERT INTO ACCOUNT_LOG (LOG_I...	gtc78n8hbx8mk	INSERT INTO ACCOUNT_LOG (LOG_I...

Top PL/SQL Procedures

'PL/SQL entry subprogram' represents the application's top-level entry-point(procedure, function, trigger, package initialization or RPC call) into PL/SQL.
'PL/SQL current subprogram' is the pl/sql subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular plsql entry subprogram

PLSQL Entry Subprogram	% Activity	PLSQL Current Subprogram	% Current
usert.MEMBER_REGISTERS	7.02	SQL	7.02
userb.MEMBER_REGISTERS	2.04	SQL	2.04

Top Sessions

'# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.

Sid, Serial#	% Activity	Event	% Event	User	Program	# Samples Active	XIDs
5851,17875	7.63	CPU + Wait for CPU	7.63	SYS	plsqldev.exe	112/480 [ 23%]	1
6161,47552	3.54	latch: library cache	3.54	DBSNMP	perl@dbone.com (TNS V1-V3)	52/480 [ 11%]	0
6076,27670	2.93	latch: library cache	2.93	app_user04_USER		43/480 [ 9%]	0
6388,40120	2.79	latch: library cache	2.11	app_user00_USER		31/480 [ 6%]	0
5912,12591	2.72	latch: library cache	2.72	app_user00_USER		40/480 [ 8%]	0

Top Blocking Sessions

Blocking session activity percentages are calculated with respect to waits on enqueues, latches and "buffer busy" only
'% Activity' represents the load on the database caused by a particular blocking session
'# Samples Active' shows the number of ASH samples in which the blocking session was found active.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the blocking session was found active.

Blocking Sid	% Activity	Event Caused	% Event	User	Program	# Samples Active	XIDs
5851,17875	19.21	library cache lock	19.21	SYS	plsqldev.exe	112/480 [ 23%]	1

Top Sessions running PQs

No data exists for this section of the report.

Top Objects/Files/Latches

Top DB Objects

No data exists for this section of the report.

Top DB Files

No data exists for this section of the report.

Top Latches

Latch	% Latch	Blocking Sid	% Activity	Max Sampled Wait secs	# Waits Sampled	# Sampled Wts < 10ms	# Sampled Wts 10ms - 100ms	# Sampled Wts 100ms - 1s	# Sampled Wts > 1s
latch: library cache	63.28	Held Shared	63.28	42.27	162	0	0	49	113

Activity Over Time

Analysis period is divided into smaller time slots
Top 3 events are reported in each of those slots
'Slot Count' shows the number of ASH samples in that slot
'Event Count' shows the number of ASH samples waiting for that event in that slot
'% Event' is 'Event Count' over all ASH samples in the analysis period

Slot Time (Duration)	Slot Count	Event	Event Count	% Event
18:01:00 (1.0 min)	1	CPU + Wait for CPU	1	0.07
18:02:00 (1.0 min)	6	log file parallel write	2	0.14
		log file sync	2	0.14
		CPU + Wait for CPU	1	0.07
18:03:00 (1.0 min)	877	latch: library cache	579	39.44
		library cache lock	164	11.17
		CPU + Wait for CPU	128	8.72
18:04:00 (1.0 min)	75	latch: library cache	56	3.81
		CPU + Wait for CPU	11	0.75
		cursor: pin S wait on X	8	0.54
18:05:00 (1.0 min)	254	latch: library cache	160	10.90
		library cache lock	61	4.16
		CPU + Wait for CPU	33	2.25
18:06:00 (1.0 min)	1	CPU + Wait for CPU	1	0.07
18:07:00 (1.0 min)	159	latch: library cache	96	6.54
		library cache lock	30	2.04
		CPU + Wait for CPU	22	1.50
18:08:00 (1.0 min)	95	latch: library cache	38	2.59
		library cache lock	27	1.84
		cursor: pin S wait on X	17	1.16

End of Report