ASH Report For CRMDB/crmdb1

DB Name DB Id Instance Inst num Release RAC Host

CRMDB 3793421815 crmdb1 1 10.2.0.4.0 YES xjcrmdb1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
CRMDB	3793421815	crmdb1	1	10.2.0.4.0	YES	xjcrmdb1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

32 20,480M (100%) 13,312M (65.0%) 6,145M (30.0%) 50.0M (0.2%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
32	20,480M (100%)	13,312M (65.0%)	6,145M (30.0%)	50.0M (0.2%)

Sample Time Data Source

Analysis Begin Time: 06-Jan-13 16:48:17 V$ACTIVE_SESSION_HISTORY

Analysis End Time: 06-Jan-13 16:53:17 V$ACTIVE_SESSION_HISTORY

Elapsed Time: 5.0 (mins)

Sample Count: 11,845

Average Active Sessions: 39.48

Avg. Active Session per CPU: 1.23

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	06-Jan-13 16:48:17	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	06-Jan-13 16:53:17	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	5.0 (mins)
Sample Count:	11,845
Average Active Sessions:	39.48
Avg. Active Session per CPU:	1.23
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
library cache lock	Concurrency	37.05	14.63
CPU + Wait for CPU	CPU	35.80	14.14
db file sequential read	User I/O	10.00	3.95
db file scattered read	User I/O	2.35	0.93
cursor: pin S wait on X	Concurrency	1.31	0.52

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
CPU + Wait for CPU	CPU	2.24	0.88

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
library cache lock	37.37	"504403178705344824","504403174825585024","301"	0.41	handle address	lock address	100*mode+namespace
db file sequential read	10.04	"1","27542","1"	0.01	file#	block#	blocks
db file scattered read	2.35	"1","78245","10"	0.01	file#	block#	blocks
cursor: pin S wait on X	1.31	"551687782","27951647162368","21475487537"	0.01	idn	value	where\|sleeps
direct path read	1.07	"10","881729","1"	0.03	file number	first dba	block cnt

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
crmdb	UNNAMED	14.06	UNNAMED	14.06
	POcsInterface@xjas2 (TNS V1-V3)	13.78	UNNAMED	13.78
	oracle@xjaccdb2 (TNS V1-V3)	9.60	UNNAMED	9.60
SYS$BACKGROUND	UNNAMED	3.84	UNNAMED	3.84
crmdb	RPubSelect@xjas1 (TNS V1-V3)	3.82	UNNAMED	3.82

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	930	80.89	31.94
INSERT	162	8.52	3.36
UPDATE	66	2.45	0.97

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
cj9cmxzd4vdr8	1239013623	8.16	CPU + Wait for CPU	8.01	select ID_NO , OP_CODE , ROWID...
7sp5b83m9g1wd	3911912079	5.18	CPU + Wait for CPU	4.94	select count(*) into :b0 fro...
cw45zxnsfqxva	3557565946	2.09	CPU + Wait for CPU	2.09	SELECT UNIT_ID FROM DCUSTGRPDO...
0dfhdtxqtknkx	1598062372	1.96	CPU + Wait for CPU	1.25	SELECT "SERV_ID", "OFFER_ID", ...
dr28gby3gm1rd	4132347108	1.84	library cache lock	1.84	select PARAM_ID, PARAM_NAM...

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
877220239	2.52	298	02rydx74kh07p	select Org_id , ORG_CODE into ...	gzhhc9jqv008y	SQL Text Not Available
4132347108	2.17	257	0jnb9tp207ubc	select param_value into :b0 f...	gxpqvus1mugq0	select param_value from ssysct...

Complete List of SQL Text

SQL Id SQL Text

02rydx74kh07p select Org_id , ORG_CODE into :b0, :b1 from dLoginMsg where login_no=:b2

0dfhdtxqtknkx SELECT "SERV_ID", "OFFER_ID", "ATTR_ID", "ATTR_VAL", "STATE_CD" FROM "PRODUCT"."PRODUCT_OFFER_INSTANCE_ATTR" "B" WHERE ("ATTR_ID"=49074108 OR "ATTR_ID"=49074109) AND TRIM("STATE_CD")='A'

0jnb9tp207ubc select param_value into :b0 from ssysctrlparam where param_id=211

7sp5b83m9g1wd select count(*) into :b0 from WOCSUSERINTERFACE where ((RET_CODE is null or RET_CODE=' ') or (RET_CODE='0' and mod(ID_NO, 10)=:b1))

cj9cmxzd4vdr8 select ID_NO , OP_CODE , ROWID , NVL(F_WOCSUSERINTERFACE(SERV_ORDER_ID, LOGIN_ACCEPT, ID_NO, SERVICE_NO, SM_CODE, OP_CODE, OP_TIME), '0') into :b0, :b1, :b2, :b3 from WOCSUSERINTERFACE where ((((RET_CODE is null or RET_CODE=' ') or RET_CODE='0') and mod(ID_NO, 10)=:b4) and ROWNUM=1)

cw45zxnsfqxva SELECT UNIT_ID FROM DCUSTGRPDOC WHERE TRIM(SUBSTR(LPAD(CUST_NUMBER, 25, ' '), -20))=:B1

dr28gby3gm1rd select PARAM_ID, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, DISPLAY_VALUE, DEFAULT_VALUE, USED_SYSTEM, PARAM_PURPOSE, INDEX_TYPE, INDEX_VALUE, PARAM_DESC, IS_VALID, to_char(VALID_DATE, 'yyyymmdd hh24:mi:ss'), CREATE_PERSON, MODIFY_PERSON, to_char(MODIFY_DATE, 'yyyymmdd hh24:mi:ss'), MODIFY_REASON from sSysCtrlParam where param_id=:paramId

gxpqvus1mugq0 select param_value from ssysctrlparam where param_id='15' and IS_VALID='Y'

gzhhc9jqv008y ** SQL Text Not Available **

SQL Id	SQL Text
02rydx74kh07p	select Org_id , ORG_CODE into :b0, :b1 from dLoginMsg where login_no=:b2
0dfhdtxqtknkx	SELECT "SERV_ID", "OFFER_ID", "ATTR_ID", "ATTR_VAL", "STATE_CD" FROM "PRODUCT"."PRODUCT_OFFER_INSTANCE_ATTR" "B" WHERE ("ATTR_ID"=49074108 OR "ATTR_ID"=49074109) AND TRIM("STATE_CD")='A'
0jnb9tp207ubc	select param_value into :b0 from ssysctrlparam where param_id=211
7sp5b83m9g1wd	select count(*) into :b0 from WOCSUSERINTERFACE where ((RET_CODE is null or RET_CODE=' ') or (RET_CODE='0' and mod(ID_NO, 10)=:b1))
cj9cmxzd4vdr8	select ID_NO , OP_CODE , ROWID , NVL(F_WOCSUSERINTERFACE(SERV_ORDER_ID, LOGIN_ACCEPT, ID_NO, SERVICE_NO, SM_CODE, OP_CODE, OP_TIME), '0') into :b0, :b1, :b2, :b3 from WOCSUSERINTERFACE where ((((RET_CODE is null or RET_CODE=' ') or RET_CODE='0') and mod(ID_NO, 10)=:b4) and ROWNUM=1)
cw45zxnsfqxva	SELECT UNIT_ID FROM DCUSTGRPDOC WHERE TRIM(SUBSTR(LPAD(CUST_NUMBER, 25, ' '), -20))=:B1
dr28gby3gm1rd	select PARAM_ID, PARAM_NAME, PARAM_TYPE, PARAM_VALUE, DISPLAY_VALUE, DEFAULT_VALUE, USED_SYSTEM, PARAM_PURPOSE, INDEX_TYPE, INDEX_VALUE, PARAM_DESC, IS_VALID, to_char(VALID_DATE, 'yyyymmdd hh24:mi:ss'), CREATE_PERSON, MODIFY_PERSON, to_char(MODIFY_DATE, 'yyyymmdd hh24:mi:ss'), MODIFY_REASON from sSysCtrlParam where param_id=:paramId
gxpqvus1mugq0	select param_value from ssysctrlparam where param_id='15' and IS_VALID='Y'
gzhhc9jqv008y	SQL Text Not Available

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
WORKFLOWADM.PRO_GETPRODUCT_INFO	1.91	SYS.XMLTYPE.EXTRACT#1	1.78
PRODUCT.OFFER_LOGIN_LIMIT_CHECK	1.44	SQL	1.29
DBCUSTADM.F_WOCSUSERINTERFACE	1.06	SQL	1.04

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	Program	# Samples Active	XIDs
6538, 3861	2.51	CPU + Wait for CPU	2.10	PRODUCT	sqlplus@xjas1 (TNS V1-V3)	249/300 [ 83%]	1
6007,42811	2.32	CPU + Wait for CPU	2.09	DBCUSTOPR	POcsInterface@...2 (TNS V1-V3)	248/300 [ 83%]	19
3681,37567	2.29	CPU + Wait for CPU	2.21	DBCUSTOPR	POcsInterface@...2 (TNS V1-V3)	262/300 [ 87%]	14
5156,23857	2.25	CPU + Wait for CPU	2.18	DBCUSTOPR	POcsInterface@...2 (TNS V1-V3)	258/300 [ 86%]	21
3740,56588	2.22	CPU + Wait for CPU	2.19	DBCUSTOPR	POcsInterface@...2 (TNS V1-V3)	259/300 [ 86%]	16

Top Blocking Sessions

No data exists for this section of the report.

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
61738	1.08	direct path read	1.02	WORKFLOWADM.SYS_LOB0000061737C00003$$ (LOB)	SERVERDESK_DATA01

Top DB Files

No data exists for this section of the report.

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
16:48:17 (43 secs)	991	CPU + Wait for CPU	627	5.29
		db file sequential read	162	1.37
		db file scattered read	47	0.40
16:49:00 (1.0 min)	3,876	library cache lock	2,381	20.10
		CPU + Wait for CPU	830	7.01
		db file sequential read	173	1.46
16:50:00 (1.0 min)	1,389	CPU + Wait for CPU	912	7.70
		db file sequential read	271	2.29
		db file scattered read	44	0.37
16:51:00 (1.0 min)	1,920	CPU + Wait for CPU	927	7.83
		library cache lock	423	3.57
		db file sequential read	265	2.24
16:52:00 (1.0 min)	3,293	library cache lock	1,615	13.63
		CPU + Wait for CPU	974	8.22
		db file sequential read	239	2.02
16:53:00 (17 secs)	376	CPU + Wait for CPU	236	1.99
		db file sequential read	79	0.67
		gc buffer busy	25	0.21

End of Report