ca[K[Kbppfedb1.lst ASH Report - From 05-Mar-14 10:30:00 To 05-Mar-14 11:00:00

ASH Report For BPPFEDB/bppfedb1

DB Name DB Id Instance Inst num Release RAC Host

BPPFEDB 1195271196 bppfedb1 1 11.2.0.3.0 YES bppfedb1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
BPPFEDB	1195271196	bppfedb1	1	11.2.0.3.0	YES	bppfedb1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

48 101,943M (100%) 49,408M (48.5%) 9,472M (9.3%) 96.0M (0.1%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
48	101,943M (100%)	49,408M (48.5%)	9,472M (9.3%)	96.0M (0.1%)

Sample Time Data Source

Analysis Begin Time: 05-Mar-14 10:30:00 V$ACTIVE_SESSION_HISTORY

Analysis End Time: 05-Mar-14 11:00:00 V$ACTIVE_SESSION_HISTORY

Elapsed Time: 30.0 (mins)

Sample Count: 56,343

Average Active Sessions: 31.30

Avg. Active Session per CPU: 0.65

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	05-Mar-14 10:30:00	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	05-Mar-14 11:00:00	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	30.0 (mins)
Sample Count:	56,343
Average Active Sessions:	31.30
Avg. Active Session per CPU:	0.65
Report Target:	None specified

Top Events

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

Top User Events

Event	Event Class	% Event	Avg Active Sessions
SQL*Net break/reset to client	Application	92.76	29.04
CPU + Wait for CPU	CPU	4.89	1.53

Top Background Events

No data exists for this section of the report.

Top Cluster 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
SQL*Net break/reset to client	92.76	"1413697536","0","0"	92.76	driver id	break?	NOT DEFINED

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types
Top Phases of Execution
Top Remote Instances

Top Service/Module

Service	Module	% Activity	Action	% Action
bppfedb	tmservice@BPPF_EAS2 (TNS V1-V3)	47.75	UNNAMED	47.75
bppfedb	tmservice@BPPF_EAS4 (TNS V1-V3)	16.08	UNNAMED	16.08
bppfedb	tmservice@BPPF_EAS3 (TNS V1-V3)	15.96	UNNAMED	15.96
bppfedb	tmservice@BPPF_EAS1 (TNS V1-V3)	14.54	UNNAMED	14.54
bppfedb	Hibernate with C3P0	1.86	UNNAMED	1.86

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	1,463	10.53	3.30

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	5.45	1.71

Back to Load Profile
Back to Top

Top Remote Instances

No data exists for this section of the report.

Back to Load Profile
Back to Top

Top SQL

Top SQL with Top Events
Top SQL with Top Row Sources
Top SQL using literals
Top Parsing Module/Action
Complete List of SQL Text

Top SQL with Top Events

SQL ID	Planhash	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
dg6mhx1u8tr60	2804724739	0	3.18	SQL*Net break/reset to client	3.18	SELECT STATEMENT	3.18	SELECT CAAC_CODE, ACCT_CODE, A...
4bnrsdguwh2nb	210764818	0	1.88	SQL*Net break/reset to client	1.88	SELECT STATEMENT	1.88	SELECT * FROM (SELECT ROW_.*, ...

Top SQL with Top Row Sources

SQL ID	PlanHash	Sampled # of Executions	% Activity	Row Source	% RwSrc	Top Event	% Event	SQL Text
dg6mhx1u8tr60	2804724739	0	3.18	SELECT STATEMENT	3.18	SQL*Net break/reset to client	3.18	SELECT CAAC_CODE, ACCT_CODE, A...
4bnrsdguwh2nb	210764818	0	1.88	SELECT STATEMENT	1.88	SQL*Net break/reset to client	1.88	SELECT * FROM (SELECT ROW_.*, ...

Top SQL using literals

No data exists for this section of the report.

Top Parsing Module/Action

No data exists for this section of the report.

Complete List of SQL Text

SQL Id SQL Text

4bnrsdguwh2nb SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(SELECT o.ORDER_ID, o.EXT_VALUE1, o.EXT_VALUE2, o.EXT_VALUE3, o.EXT_VALUE4, (SELECT CUST_CODE FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_CODE, (SELECT CUST_NAME FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_NAME, (SELECT CUST_TYPE FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_TYPE, (SELECT TF1.DUE_MOENY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS DUE_MOENY, (SELECT TF1.PAY_MONEY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS PAY_MONEY, (SELECT TF1.PREMIUM_MONEY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS PREMIUM_MONEY, o.STAT STAT, o.PAY_STAT, o.ACT_STAT, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_ORDERSTAT' AND DICT_ID = o.STAT) AS STATEN, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_PAYSTAT' AND DICT_ID = o.PAY_STAT) AS PAY_STATEN, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_ACTSTAT' AND DICT_ID = o.ACT_STAT) AS ACT_STATEN, TO_CHAR(o.ACCT_DATE, 'YYYYMMDDHH24MISS') AS ACCT_DATE, TO_CHAR(o.END_DATE, 'YYYYMMDDHH24MISS') AS END_DATE, o.RET_MSG, (SELECT F2.AREA_NAME FROM T_SYM_AREA F2 WHERE F2.AREA_CODE = o.AREA_CODE) AREA_NAME, o.TERM_ID, o.TERM_SEQ, o.ORDER_TYPE, o.EXT_ORDER_ID, o.MEMO, TO_CHAR(o.OPER_TIME, 'YYYYMMDDHH24MISS') AS OPER_TIME, o.HDL_WAY, (SELECT t.DICT_NAME FROM T_SYM_DICT t WHERE t.DICT_TYPEID = 'SCS_HDLWAY' AND t.DICT_ID = o.HDL_WAY) AS HDL_WAYCN, (SELECT T.DICT_NAME FROM T_SYM_DICT T WHERE T.DICT_TYPEID = 'SCS_ORDERTYPE' AND T.DICT_ID = o.ORDER_TYPE) ORDER_TYPENAME, 'OAS01' AS ORDER_ARC_STAT FROM T_SCS_ORDER o WHERE 1=1 AND o.CUST_ID IN (SELECT c.CUST_ID FROM T_CUM_INFO c WHERE 1=1 AND c.PRTN_ID IN(SELECT PRTN_ID FROM T_PNM_PARTNER START WITH PRTN_ID = 12040 CONNECT BY PRIOR PRTN_ID = PARENT_ID)) ORDER BY ACCT_DATE DESC) ROW_ WHERE ROWNUM <= 10) WHERE ROWNUM_ >= 1

dg6mhx1u8tr60 SELECT CAAC_CODE, ACCT_CODE, ACCT_TYPE, CURRENCY_CODE, CURRENCY_UNIT, ACCT_PWD, TIME_LMT, DAY_LMT, TOTAL_LMT, TOTAL_BAL, VALID_BAL, FROZEN_BAL, STAT, STAT_REASON, BAL_VERIFY, TO_CHAR(EFF_DATE, 'YYYYMMDDHH24MISS') as EFF_DATE, TO_CHAR(EXP_DATE, 'YYYYMMDDHH24MISS') as EXP_DATE, INIT_BAL FROM T_EBK_ACCTINFO where CAAC_CODE='110440101000072525'and ACCT_TYPE='0007'

SQL Id	SQL Text
4bnrsdguwh2nb	SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(SELECT o.ORDER_ID, o.EXT_VALUE1, o.EXT_VALUE2, o.EXT_VALUE3, o.EXT_VALUE4, (SELECT CUST_CODE FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_CODE, (SELECT CUST_NAME FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_NAME, (SELECT CUST_TYPE FROM T_CUM_INFO WHERE CUST_ID = o.CUST_ID AND STAT = 'S0A') AS CUST_TYPE, (SELECT TF1.DUE_MOENY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS DUE_MOENY, (SELECT TF1.PAY_MONEY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS PAY_MONEY, (SELECT TF1.PREMIUM_MONEY FROM T_SCS_ORDFEE TF1 WHERE TF1.ORDER_ID = o.ORDER_ID) AS PREMIUM_MONEY, o.STAT STAT, o.PAY_STAT, o.ACT_STAT, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_ORDERSTAT' AND DICT_ID = o.STAT) AS STATEN, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_PAYSTAT' AND DICT_ID = o.PAY_STAT) AS PAY_STATEN, (SELECT DICT_NAME FROM T_SYM_DICT WHERE DICT_TYPEID = 'SCS_ACTSTAT' AND DICT_ID = o.ACT_STAT) AS ACT_STATEN, TO_CHAR(o.ACCT_DATE, 'YYYYMMDDHH24MISS') AS ACCT_DATE, TO_CHAR(o.END_DATE, 'YYYYMMDDHH24MISS') AS END_DATE, o.RET_MSG, (SELECT F2.AREA_NAME FROM T_SYM_AREA F2 WHERE F2.AREA_CODE = o.AREA_CODE) AREA_NAME, o.TERM_ID, o.TERM_SEQ, o.ORDER_TYPE, o.EXT_ORDER_ID, o.MEMO, TO_CHAR(o.OPER_TIME, 'YYYYMMDDHH24MISS') AS OPER_TIME, o.HDL_WAY, (SELECT t.DICT_NAME FROM T_SYM_DICT t WHERE t.DICT_TYPEID = 'SCS_HDLWAY' AND t.DICT_ID = o.HDL_WAY) AS HDL_WAYCN, (SELECT T.DICT_NAME FROM T_SYM_DICT T WHERE T.DICT_TYPEID = 'SCS_ORDERTYPE' AND T.DICT_ID = o.ORDER_TYPE) ORDER_TYPENAME, 'OAS01' AS ORDER_ARC_STAT FROM T_SCS_ORDER o WHERE 1=1 AND o.CUST_ID IN (SELECT c.CUST_ID FROM T_CUM_INFO c WHERE 1=1 AND c.PRTN_ID IN(SELECT PRTN_ID FROM T_PNM_PARTNER START WITH PRTN_ID = 12040 CONNECT BY PRIOR PRTN_ID = PARENT_ID)) ORDER BY ACCT_DATE DESC) ROW_ WHERE ROWNUM <= 10) WHERE ROWNUM_ >= 1
dg6mhx1u8tr60	SELECT CAAC_CODE, ACCT_CODE, ACCT_TYPE, CURRENCY_CODE, CURRENCY_UNIT, ACCT_PWD, TIME_LMT, DAY_LMT, TOTAL_LMT, TOTAL_BAL, VALID_BAL, FROZEN_BAL, STAT, STAT_REASON, BAL_VERIFY, TO_CHAR(EFF_DATE, 'YYYYMMDDHH24MISS') as EFF_DATE, TO_CHAR(EXP_DATE, 'YYYYMMDDHH24MISS') as EXP_DATE, INIT_BAL FROM T_EBK_ACCTINFO where CAAC_CODE='110440101000072525'and ACCT_TYPE='0007'

Top PL/SQL Procedures

No data exists for this section of the report.

Top Java Workload

No data exists for this section of the report.

Top Call Types

Call Type	Count	% Activity	Avg Active Sessions
FETCH	33,673	59.76	18.71
COMMIT	16,645	29.54	9.25
V8 Bundled Exec	3,813	6.77	2.12
ROLLBACK	1,793	3.18	1.00

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
511, 113	3.18	SQL*Net break/reset to client	3.18	BPPF_EAS	tmservice@BPPF...2 (TNS V1-V3)	1,793/1,800 [100%]
571, 13	3.18	SQL*Net break/reset to client	3.18	BPPF_EAS	tmservice@BPPF...2 (TNS V1-V3)	1,793/1,800 [100%]
574, 85	3.18	SQL*Net break/reset to client	3.18	BPPF_EAS	tmservice@BPPF...2 (TNS V1-V3)	1,793/1,800 [100%]
634, 17	3.18	SQL*Net break/reset to client	3.18	BPPF_EAS	tmservice@BPPF...2 (TNS V1-V3)	1,793/1,800 [100%]
637, 287	3.18	SQL*Net break/reset to client	3.18	BPPF_EAS	tmservice@BPPF...2 (TNS V1-V3)	1,793/1,800 [100%]

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
77892	22.29	SQL*Net break/reset to client	22.28	BPPF_EAS.T_EBK_ACCTINFO (TABLE)	BPPF_EAS_DAT_B01
77924	10.13	SQL*Net break/reset to client	10.12	BPPF_EAS.PK_T_SCS_OPERLOG (INDEX)	BPPF_EAS_IDX_B01
77932	3.73	SQL*Net break/reset to client	3.18	BPPF_EAS.T_SCS_ORDER (TABLE)	BPPF_EAS_DAT_B01
77949	3.20	SQL*Net break/reset to client	3.18	BPPF_EAS.T_SCS_PAYMENT (TABLE)	BPPF_EAS_DAT_B01
77926	3.19	SQL*Net break/reset to client	3.18	BPPF_EAS.IX_SCS_OPERLOG_OPERTYPE (INDEX)	BPPF_EAS_IDX_B01

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
10:30:00 (3.0 min)	6,154	SQL*Net break/reset to client	5,760	10.22
10:30:00 (3.0 min)	6154	CPU + Wait for CPU	236	0.42
10:30:00 (3.0 min)	6154	direct path read	56	0.10
10:33:00 (3.0 min)	6,040	SQL*Net break/reset to client	5,728	10.17
10:33:00 (3.0 min)	6040	CPU + Wait for CPU	224	0.40
10:33:00 (3.0 min)	6040	direct path read	24	0.04
10:36:00 (3.0 min)	6,023	SQL*Net break/reset to client	5,728	10.17
10:36:00 (3.0 min)	6023	CPU + Wait for CPU	239	0.42
10:36:00 (3.0 min)	6023	direct path read	30	0.05
10:39:00 (3.0 min)	6,122	SQL*Net break/reset to client	5,760	10.22
10:39:00 (3.0 min)	6122	CPU + Wait for CPU	308	0.55
10:39:00 (3.0 min)	6122	direct path read	23	0.04
10:42:00 (3.0 min)	4,095	SQL*Net break/reset to client	3,808	6.76
10:42:00 (3.0 min)	4095	CPU + Wait for CPU	233	0.41
10:42:00 (3.0 min)	4095	direct path read	22	0.04
10:45:00 (3.0 min)	6,083	SQL*Net break/reset to client	5,728	10.17
10:45:00 (3.0 min)	6083	CPU + Wait for CPU	314	0.56
10:45:00 (3.0 min)	6083	direct path read	16	0.03
10:48:00 (3.0 min)	5,285	SQL*Net break/reset to client	4,826	8.57
10:48:00 (3.0 min)	5285	CPU + Wait for CPU	351	0.62
10:48:00 (3.0 min)	5285	direct path read	33	0.06
10:51:00 (3.0 min)	6,426	SQL*Net break/reset to client	5,875	10.43
10:51:00 (3.0 min)	6426	CPU + Wait for CPU	367	0.65
10:51:00 (3.0 min)	6426	db file sequential read	69	0.12
10:54:00 (3.0 min)	4,909	SQL*Net break/reset to client	4,501	7.99
10:54:00 (3.0 min)	4909	CPU + Wait for CPU	294	0.52
10:54:00 (3.0 min)	4909	direct path read	41	0.07
10:57:00 (3.0 min)	5,206	SQL*Net break/reset to client	4,550	8.08
10:57:00 (3.0 min)	5206	CPU + Wait for CPU	342	0.61
10:57:00 (3.0 min)	5206	direct path read	89	0.16

End of Report [oracle@bppfedb1 mouse]$