[oracle@db1 log]$ cat 20120814.lst ASH Report - From 14-Aug-12 11:40:01 To 14-Aug-12 12:10:01

ASH Report For G1DB/G1db1

DB Name DB Id Instance Inst num Release RAC Host

G1DB 2583992970 G1db1 1 10.2.0.4.0 YES db1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
G1DB	2583992970	G1db1	1	10.2.0.4.0	YES	db1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

24 6,144M (100%) 3,712M (60.4%) 2,340M (38.1%) 40.5M (0.7%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
24	6,144M (100%)	3,712M (60.4%)	2,340M (38.1%)	40.5M (0.7%)

Sample Time Data Source

Analysis Begin Time: 14-Aug-12 11:40:01 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 6501

Analysis End Time: 14-Aug-12 12:10:01 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 6502

Elapsed Time: 30.0 (mins)

Sample Count: 4,313

Average Active Sessions: 23.96

Avg. Active Session per CPU: 1.00

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	14-Aug-12 11:40:01	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 6501
Analysis End Time:	14-Aug-12 12:10:01	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 6502
Elapsed Time:	30.0 (mins)
Sample Count:	4,313
Average Active Sessions:	23.96
Avg. Active Session per CPU:	1.00
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
enq: TX - row lock contention	Application	62.00	14.86
gc buffer busy	Cluster	12.15	2.91
log file sync	Commit	9.90	2.37
row cache lock	Concurrency	7.05	1.69
gc current block busy	Cluster	1.67	0.40

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
enq: CF - contention	Other	1.65	0.39
gcs log flush sync	Other	1.62	0.39

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
enq: TX - row lock contention	62.00	"1415053318","196626","23962"	61.67	name\|mode	usn<<16 \| slot	sequence
gc buffer busy	12.15	"6","25911","65537"	2.83	file#	block#	id#
		"24","313263","65537"	2.46
		"6","25912","65537"	2.06
log file sync	9.90	"1506","0","0"	9.90	buffer#	NOT DEFINED	NOT DEFINED
row cache lock	7.05	"3","0","3"	7.05	cache id	mode	request
enq: CF - contention	1.65	"1128660996","0","0"	0.95	name\|mode	0	operation

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
G1db	UNNAMED	89.73	UNNAMED	89.73
SYS$USERS	JDBC Thin Client	5.68	UNNAMED	5.68
SYS$BACKGROUND	UNNAMED	3.48	UNNAMED	3.48

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
UPDATE	17	72.69	17.42
INSERT	8	11.85	2.84
SELECT	16	2.78	0.67

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
gzt34x6x7p8r0	3683798013	57.50	enq: TX - row lock contention	57.50	UPDATE TMSTPOS SET TM_SMP=:1, ...
gmvpuabz0upuy		5.66	gc buffer busy	2.99	INSERT INTO HPSTJNL (TXN_CD, C...
		5.66	row cache lock	2.67	INSERT INTO HPSTJNL (TXN_CD, C...
gtpuh24u3cr3d	3683798013	4.17	enq: TX - row lock contention	4.17	UPDATE TMSTPOS Set ...
1v2p6m8d45ugy	803511549	4.06	gc buffer busy	3.43	UPDATE PUBSEQREC SET TM_SMP=:1...
72zqywap3utzv		2.81	gc buffer busy	1.53	INSERT INTO HPSTSJNL (AC_DT, T...
		2.81	row cache lock	1.28	INSERT INTO HPSTSJNL (AC_DT, T...

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
803511549	6.42	277	008rnu6p8cn2w	UPDATE PUBSEQREC SET TM_SMP='2...	gzc4c5pg461tk	UPDATE PUBSEQREC SET TM_SMP='2...

Complete List of SQL Text

SQL Id SQL Text

008rnu6p8cn2w UPDATE PUBSEQREC SET TM_SMP='20120814161041', SEL_VAL = SEL_VAL WHERE BR_NO='1021' AND SEQ_NM='CUP' AND SEQ_CND='CUP'

1v2p6m8d45ugy UPDATE PUBSEQREC SET TM_SMP=:1, SEL_VAL = SEL_VAL WHERE BR_NO=:2 AND SEQ_NM=:3 AND SEQ_CND=:4

72zqywap3utzv INSERT INTO HPSTSJNL (AC_DT, TM_SMP, LOG_NO, TXN_TM, APP_VER, CSEQ_NO, TRM_SN, APP_NUM) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)

gmvpuabz0upuy INSERT INTO HPSTJNL (TXN_CD, CSEQ_NO, AC_DT, TM_SMP, LOG_NO, TRM_NO, MERC_ID, TXN_TM, BLOG_NO, PRO_COD) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)

gtpuh24u3cr3d UPDATE TMSTPOS Set TM_SMP=:1, SATTLE_SMP=:2, BAT_NO=:3 WHERE MERC_ID=:4 AND TRM_NO=:5

gzc4c5pg461tk UPDATE PUBSEQREC SET TM_SMP='20120814171655', SEL_VAL = SEL_VAL WHERE BR_NO='1021' AND SEQ_NM='BOC' AND SEQ_CND='BOC'

gzt34x6x7p8r0 UPDATE TMSTPOS SET TM_SMP=:1, PIN_KEY=:2, MAC_KEY=:3, BAT_NO=:4, SIG_DT=:5, DATA_KEY=:6, SGN_STS='1', DATA_KEY_TS=:7 WHERE MERC_ID=:8 AND TRM_NO=:9

SQL Id	SQL Text
008rnu6p8cn2w	UPDATE PUBSEQREC SET TM_SMP='20120814161041', SEL_VAL = SEL_VAL WHERE BR_NO='1021' AND SEQ_NM='CUP' AND SEQ_CND='CUP'
1v2p6m8d45ugy	UPDATE PUBSEQREC SET TM_SMP=:1, SEL_VAL = SEL_VAL WHERE BR_NO=:2 AND SEQ_NM=:3 AND SEQ_CND=:4
72zqywap3utzv	INSERT INTO HPSTSJNL (AC_DT, TM_SMP, LOG_NO, TXN_TM, APP_VER, CSEQ_NO, TRM_SN, APP_NUM) VALUES (:1, :2, :3, :4, :5, :6, :7, :8)
gmvpuabz0upuy	INSERT INTO HPSTJNL (TXN_CD, CSEQ_NO, AC_DT, TM_SMP, LOG_NO, TRM_NO, MERC_ID, TXN_TM, BLOG_NO, PRO_COD) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
gtpuh24u3cr3d	UPDATE TMSTPOS Set TM_SMP=:1, SATTLE_SMP=:2, BAT_NO=:3 WHERE MERC_ID=:4 AND TRM_NO=:5
gzc4c5pg461tk	UPDATE PUBSEQREC SET TM_SMP='20120814171655', SEL_VAL = SEL_VAL WHERE BR_NO='1021' AND SEQ_NM='BOC' AND SEQ_CND='BOC'
gzt34x6x7p8r0	UPDATE TMSTPOS SET TM_SMP=:1, PIN_KEY=:2, MAC_KEY=:3, BAT_NO=:4, SIG_DT=:5, DATA_KEY=:6, SGN_STS='1', DATA_KEY_TS=:7 WHERE MERC_ID=:8 AND TRM_NO=:9

Top PL/SQL Procedures

No data exists for this section of the report.

Top Sessions

Top Sessions
Top Blocking Sessions
Top Sessions running PQs

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	# Samples Active
813, 4978	4.17	enq: TX - row lock contention	4.17	POSP	180/180 [100%]
824,57206	4.17	enq: TX - row lock contention	4.17	POSP	180/180 [100%]
829, 1774	4.17	enq: TX - row lock contention	4.17	POSP	180/180 [100%]
845, 5552	4.17	enq: TX - row lock contention	4.17	POSP	180/180 [100%]
863,64974	4.17	enq: TX - row lock contention	4.17	POSP	180/180 [100%]

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
1024,32353	61.67	enq: TX - row lock contention	61.67	NOT FOUND	BLOCKING SESSION NOT FOUND	0/180 [ 0%]
1087, 1	12.47	log file sync	9.90	SYS	oracle@db1 (LGWR)	31/180 [ 17%]	0
		gcs log flush sync	1.62
1006,35636	7.05	row cache lock	7.05	TD_OA	JDBC Thin Client	22/180 [ 12%]	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

With respect to Application, Cluster, User I/O and buffer busy waits only.

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
12801	61.67	enq: TX - row lock contention	61.67	POSP.TMSTPOS (TABLE)	TBS_POSP
13436	6.07	gc buffer busy	5.45	POSP.PUBSEQREC (TABLE)	TBS_POSP
53813	2.99	gc buffer busy	2.99	POSP.HPSTJNL_PK_INT (INDEX)	TBS_POSP_IDX_2
53670	2.41	gc buffer busy	1.55	POSP.HPSTJNL.P201208 (TABLE PARTITION)	TBS_POSP
47390	2.13	gc buffer busy	2.13	POSP.PK_HPSTSJNLN (INDEX)	TBS_POSP

Top DB Files

With respect to Cluster and User I/O events only.

File ID	% Activity	Event	% Event	File Name	Tablespace
6	11.45	gc buffer busy	9.14	+ORADATA/g1db/datafile/tbs_posp.277.736909545	TBS_POSP
		gc current block busy	1.21
24	3.01	gc buffer busy	2.99	+ORADATA/g1db/datafile/tbs_posp_idx_2.dbf	TBS_POSP_IDX_2

Top Latches

No data exists for this section of the report.

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
11:40:01 (5.0 min)	422	enq: TX - row lock contention	420	9.74
		CPU + Wait for CPU	2	0.05
11:45:00 (5.0 min)	449	enq: TX - row lock contention	440	10.20
		CPU + Wait for CPU	6	0.14
		control file parallel write	1	0.02
11:50:00 (5.0 min)	456	enq: TX - row lock contention	450	10.43
		CPU + Wait for CPU	4	0.09
		db file sequential read	1	0.02
11:55:00 (5.0 min)	846	enq: TX - row lock contention	450	10.43
		log file sync	171	3.96
		gc buffer busy	111	2.57
12:00:00 (5.0 min)	1,624	enq: TX - row lock contention	450	10.43
		gc buffer busy	412	9.55
		row cache lock	286	6.63
12:05:00 (5.0 min)	516	enq: TX - row lock contention	464	10.76
		buffer busy waits	21	0.49
		KJC: Wait for msg sends to complete	16	0.37

End of Report