ASH Report For ORCL/orcl1

DB Name DB Id Instance Inst num Release RAC Host

ORCL 1321195808 orcl1 1 10.2.0.4.0 YES czjbdb1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ORCL	1321195808	orcl1	1	10.2.0.4.0	YES	czjbdb1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

32 30,720M (100%) 13,856M (45.1%) 16,720M (54.4%) 50.0M (0.2%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
32	30,720M (100%)	13,856M (45.1%)	16,720M (54.4%)	50.0M (0.2%)

Sample Time Data Source

Analysis Begin Time: 15-Sep-14 09:00:00 V$ACTIVE_SESSION_HISTORY

Analysis End Time: 15-Sep-14 09:15:00 V$ACTIVE_SESSION_HISTORY

Elapsed Time: 15.0 (mins)

Sample Count: 141,212

Average Active Sessions: 156.90

Avg. Active Session per CPU: 4.90

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	15-Sep-14 09:00:00	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	15-Sep-14 09:15:00	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	15.0 (mins)
Sample Count:	141,212
Average Active Sessions:	156.90
Avg. Active Session per CPU:	4.90
Report Target:	None specified

Top Events

Top User Events
Top Background Events
Top Event P1/P2/P3 Values

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
cursor: pin S wait on X	Concurrency	24.38	38.25
enq: SQ - contention	Configuration	24.14	37.88
latch: library cache	Concurrency	20.98	32.92
row cache lock	Concurrency	13.12	20.59
latch: row cache objects	Concurrency	12.48	19.59

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
cursor: pin S wait on X	24.38	"2508567777","1494648619008","21476095739"	0.00	idn	value	where\|sleeps
enq: SQ - contention	24.14	"1397817350","71639","0"	24.14	name\|mode	object #	0
latch: library cache	20.98	"504403190211540552","215","0"	4.56	address	number	tries
		"504403190211540552","215","1"	4.40
		"504403190211540552","215","2"	4.18
row cache lock	13.76	"13","0","5"	12.04	cache id	mode	request
latch: row cache objects	13.11	"504403190525722264","200","0"	5.70	address	number	tries
		"504403190525722264","200","1"	2.25
		"504403190525722264","200","2"	1.50

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
orcl	JDBC Thin Client	38.91	UNNAMED	38.91
	UNNAMED	31.67	UNNAMED	31.67
SYS$USERS	UNNAMED	13.94	UNNAMED	13.94
	emagent@czjbdb1 (TNS V1-V3)	4.26	UNNAMED	4.26
orcl	PlSqlDev.exe	3.79	UNNAMED	3.79

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	76	49.23	77.25

Back to Load Profile
Back to Top

Top SQL

Top SQL Statements
Top SQL using literals
Complete List of SQL Text

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
4mqz0c67a1yh4	80662471	22.24	enq: SQ - contention	21.60	SELECT TRIM(TO_CHAR(SYSDATE, ...
1594gjs64t9mq	419508951	10.14	row cache lock	10.14	SELECT TO_CHAR(SYSDATE, 'YYYY...
7f5kc7uasbd71		9.35	cursor: pin S wait on X	8.76	Select nvl(bac450, 1) from sb0...
gc3s9xsr2v4td		5.07	cursor: pin S wait on X	4.43	select grantee, name from sys...
81ky0n97v4zsg		3.58	cursor: pin S wait on X	2.94	/* OracleOEM */ select s.sid, ...

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
1546270724	1.62	2282	00024n41zy8v0	SELECT Pkg_a_Comm.Fun_a_Getage...	gzxms6mksudss	select to_date('2000-10-18', '...

Complete List of SQL Text

SQL Id SQL Text

00024n41zy8v0 SELECT Pkg_a_Comm.Fun_a_Getage(to_char(to_date('2008-11-17', 'yyyy-mm-dd'), 'yyyymmdd'), to_char(sysdate, 'yyyymmdd'), '1') age FROM dual

1594gjs64t9mq SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || TO_CHAR(LPAD(SEQ_K_业务支付流水号.NEXTVAL, 10, 0)) FROM DUAL

4mqz0c67a1yh4 SELECT TRIM(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')) || '-' || TRIM(TO_CHAR(TRIM(:B1 ), '00000000')) || '-' || TRIM(TO_CHAR(SEQ_SYS.NEXTVAL, '0000')) FROM DUAL

7f5kc7uasbd71 Select nvl(bac450, 1) from sb04 where sab001='5040201304'

81ky0n97v4zsg /* OracleOEM */ select s.sid, s.serial# from v$session s where s.sid = (select sid from v$mystat where rownum=1)

gc3s9xsr2v4td select grantee, name from sys.plsqldev_authorization where grantee in (user, 'PUBLIC') or grantee in (select role from sys.session_roles)

gzxms6mksudss select to_date('2000-10-18', 'YYYY-MM-DD') from dual

SQL Id	SQL Text
00024n41zy8v0	SELECT Pkg_a_Comm.Fun_a_Getage(to_char(to_date('2008-11-17', 'yyyy-mm-dd'), 'yyyymmdd'), to_char(sysdate, 'yyyymmdd'), '1') age FROM dual
1594gjs64t9mq	SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') \|\| TO_CHAR(LPAD(SEQ_K_业务支付流水号.NEXTVAL, 10, 0)) FROM DUAL
4mqz0c67a1yh4	SELECT TRIM(TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')) \|\| '-' \|\| TRIM(TO_CHAR(TRIM(:B1 ), '00000000')) \|\| '-' \|\| TRIM(TO_CHAR(SEQ_SYS.NEXTVAL, '0000')) FROM DUAL
7f5kc7uasbd71	Select nvl(bac450, 1) from sb04 where sab001='5040201304'
81ky0n97v4zsg	/* OracleOEM */ select s.sid, s.serial# from v$session s where s.sid = (select sid from v$mystat where rownum=1)
gc3s9xsr2v4td	select grantee, name from sys.plsqldev_authorization where grantee in (user, 'PUBLIC') or grantee in (select role from sys.session_roles)
gzxms6mksudss	select to_date('2000-10-18', 'YYYY-MM-DD') from dual

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
AHSIMIS.PKG_K_SIINTERFACE.PRC_K_SIINTERFACE	32.38	SQL	32.38

Top Sessions

Top Sessions
Top Blocking Sessions
Top Sessions running PQs

Top Sessions

No data exists for this section of the report.

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
506,30609	24.14	enq: SQ - contention	24.14	AHSIMIS		895/900 [ 99%]	0

Top Sessions running PQs

No data exists for this section of the report.

Top Objects/Files/Latches

Top DB Objects
Top DB Files
Top 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	20.98	Held Shared	20.98	0.29	29,628	2	552	29,074	0
latch: row cache objects	13.11	Held Shared	13.11	0.29	18,515	4	2,358	16,153	0

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
09:00:00 (2.0 min)	17,486	latch: row cache objects	5,961	4.22
		cursor: pin S wait on X	4,333	3.07
		enq: SQ - contention	4,071	2.88
09:02:00 (2.0 min)	18,412	latch: row cache objects	5,793	4.10
		cursor: pin S wait on X	4,489	3.18
		enq: SQ - contention	4,433	3.14
09:04:00 (2.0 min)	19,202	latch: library cache	4,908	3.48
		enq: SQ - contention	4,680	3.31
		cursor: pin S wait on X	4,675	3.31
09:06:00 (2.0 min)	19,211	latch: library cache	5,011	3.55
		enq: SQ - contention	4,680	3.31
		cursor: pin S wait on X	4,676	3.31
09:08:00 (2.0 min)	19,356	latch: library cache	5,216	3.69
		enq: SQ - contention	4,719	3.34
		cursor: pin S wait on X	4,713	3.34
09:10:00 (2.0 min)	19,301	latch: library cache	5,481	3.88
		enq: SQ - contention	4,680	3.31
		cursor: pin S wait on X	4,676	3.31
09:12:00 (2.0 min)	18,676	latch: library cache	5,491	3.89
		enq: SQ - contention	4,524	3.20
		cursor: pin S wait on X	4,519	3.20
09:14:00 (60 secs)	9,568	latch: library cache	2,807	1.99
		cursor: pin S wait on X	2,345	1.66
		enq: SQ - contention	2,301	1.63

End of Report