Enter value for begin_time: 07/29/13 09:50:00 Report begin time specified: 07/29/13 09:50:00 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: 25 Report duration specified: 25 Using 29-Jul-13 09:50:00 as report begin time Using 29-Jul-13 10:15:00 as report end time Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- In the 'Activity Over Time' section of the ASH report, -- the analysis period is divided into smaller slots -- and top wait events are reported in each of those slots. -- Default: -- The analysis period will be automatically split upto 10 slots -- complying to a minimum slot width of -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY. Specify Slot Width in seconds to use in the 'Activity Over Time' section: Defaults to a value as explained above: Slot Width specified: Specify Report Targets (using ashrpti.sql) to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- ASH Report can accept "Report Targets", -- like a particular SQL statement, or a particular SESSION, -- to generate the report on. If one or more report targets are -- specified, then the data used to generate the report will only be -- the ASH samples that pertain to ALL the specified report targets. -- Default: -- If none of the report targets are specified, -- then the target defaults to all activity in the database instance. Specify SESSION_ID (eg: from V$SESSION.SID) report target: Defaults to NULL: SESSION report target specified: Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: Defaults to NULL: (% and _ wildcards allowed) SQL report target specified: Specify WATI_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target specified: Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target: Defaults to NULL: SERVICE report target specified: Specify MODULE name (eg: from V$SESSION.MODULE) report target: Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified: Specify ACTION name (eg: from V$SESSION.ACTION) report target: Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified: Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target: Defaults to NULL: (% and _ wildcards allowed) CLIENT_ID report target specified: Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target: Defaults to NULL: (% and _ wildcards allowed) PLSQL_ENTRY report target specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is ashrpt_1_0729_1015.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Using the report name ashrpt_1_0729_1015.html Summary of All User Input ------------------------- Format : HTML DB Id : 2291972440 Inst num : 1 Begin time : 29-Jul-13 09:50:00 End time : 29-Jul-13 10:15:00 Slot width : Default Report targets : 0 Report name : ashrpt_1_0729_1015.html ASH Report - From 29-Jul-13 09:50:00 To 29-Jul-13 10:15:00

ASH Report For ESBSID/ESBSID1

DB Name DB Id Instance Inst num Release RAC Host

ESBSID 2291972440 ESBSID1 1 10.2.0.5.0 YES SIDDB01

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ESBSID	2291972440	ESBSID1	1	10.2.0.5.0	YES	SIDDB01

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

32 30,000M (100%) 18,080M (60.3%) 11,334M (37.8%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
32	30,000M (100%)	18,080M (60.3%)	11,334M (37.8%)	M (%)

Sample Time Data Source

Analysis Begin Time: 29-Jul-13 09:50:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 10161

Analysis End Time: 29-Jul-13 10:15:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 10163

Elapsed Time: 25.0 (mins)

Sample Count: 240

Average Active Sessions: 1.60

Avg. Active Session per CPU: 0.05

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	29-Jul-13 09:50:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 10161
Analysis End Time:	29-Jul-13 10:15:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 10163
Elapsed Time:	25.0 (mins)
Sample Count:	240
Average Active Sessions:	1.60
Avg. Active Session per CPU:	0.05
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
CPU + Wait for CPU	CPU	49.58	0.79
db file sequential read	User I/O	10.42	0.17
gc cr multi block request	Cluster	8.33	0.13
SQL*Net message from dblink	Network	4.17	0.07
db file scattered read	User I/O	3.33	0.05

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
CPU + Wait for CPU	CPU	6.67	0.11
reliable message	Other	3.33	0.05
log file parallel write	System I/O	1.25	0.02

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
db file sequential read	10.83	"3","4931","1"	0.42	file#	block#	blocks
gc cr multi block request	8.33	"37","534322","1"	0.42	file#	block#	class#
SQL*Net message from dblink	4.17	"1413697536","1","0"	4.17	driver id	#bytes	NOT DEFINED
db file scattered read	3.33	"107","2548153","16"	0.42	file#	block#	blocks
reliable message	3.33	"46248353024","46269966384","46301345704"	0.83	channel context	channel handle	broadcast message

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
SIDsrv	JDBC Thin Client	30.83	UNNAMED	30.83
	PL/SQL Developer	20.00	SQL ´°¿Ú - ÐÂ½¨	11.25
			SQL Window - ÒµÎñ½»Ò×Ê§°Ü´íÎóÐÅ?	6.25
	oracle@KMSASMDB (TNS V1-V3)	14.58	UNNAMED	14.58
SYS$BACKGROUND	UNNAMED	11.25	UNNAMED	11.25
SIDsrv	framework@SIDUP03 (TNS V1-V3)	10.83	UNNAMED	10.83

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	59	64.17	1.03
INSERT	16	12.50	0.20
PL/SQL EXECUTE	1	3.75	0.06
UPDATE	4	1.67	0.03

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
b058pgbmzfsaz	3079067433	8.33	CPU + Wait for CPU	5.42	SELECT "A1"."ITEM_ID", NVL("A1...
			db file scattered read	1.25
b31ya73qzzj3w	2910912486	6.67	gc cr multi block request	2.50	SELECT err_msg, COUNT(*) FROM ...
			gc current block 2-way	1.67
			db file parallel read	1.25
53xfykjfrfv9b	611719352	5.83	gc cr multi block request	3.33	SELECT c.prov as "Ê¡·Ý", trans_i...
			CPU + Wait for CPU	1.25
			gc cr block 2-way	1.25
c57j9r1p0hcjc	1546270724	5.00	enq: TX - index contention	2.08	INSERT INTO SALSEL.TF_F_USERTA...
			CPU + Wait for CPU	1.67
a7nfsa9g020gq	2910912486	3.75	gc cr multi block request	2.50	SQL Text Not Available

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	10.42	25	030zytrv08ns0	select chr('0x01') from dual	ghn6wfz8ss0uy	SELECT TO_CHAR(SYSDATE, 'YYYYM...

Complete List of SQL Text

SQL Id SQL Text

030zytrv08ns0 select chr('0x01') from dual

53xfykjfrfv9b SELECT c.prov as "Ê¡·Ý", trans_ido as "Á÷Ë®ÐÅÏ¢", err_msg as "±¨´íÐÅÏ¢", id as "IDÐÅÏ¢" FROM ( select /*+ ordered use_nl(req rsp) full(req) index(rsp(trans_idc)) index(tsf(trans_idc)) */ SELF_LOG_FIELD_value AS prov, trans_ido, replace(TRANSLATE(rsp.busi_retmsg, '0123456789', ' '), ' ', '') AS err_msg, translate(rsp.busi_retmsg, '#' || translate(rsp.busi_retmsg, '0123456789', '#'), '/') as id from dbesblog_out.tlog_req partition(tlog_req_20130726) req , dbesblog_out.tlog_rsp partition(tlog_rsp_20130726) rsp, DBESBLOG_out.TLOG_SELF_FIELD partition(TLOG_SELF_FIELD_20130726) tsf where req.trans_idc = rsp.trans_idc and req.trans_idc=tsf.trans_idc and req.parent_trans_idc is not null and REQ.svc_name ='UserTagSer_JK' and tsf.SELF_LOG_FIELD_NAME='PROVINCE_CODE' and rsp_code='9999' AND SELF_LOG_FIELD_value='86') c

a7nfsa9g020gq ** SQL Text Not Available **

b058pgbmzfsaz SELECT "A1"."ITEM_ID", NVL("A1"."PRODUCT_ID", 0), NVL("A1"."PACKAGE_ID", 0), NVL("A1"."USER_ID", 0), NVL("A1"."PROD_ITEM_ID", 0), NVL("A1"."SERVICE_ID", 0), NVL("A1"."MAIN_TAG", '0'), 0, TO_CHAR(NVL("A1"."START_DATE", TO_DATE('20000101', 'yyy ymmdd')), 'yyyymmddhh24miss'), TO_CHAR(NVL("A1"."END_DATE", TO_DATE('20370101', 'yyyymmdd')), 'yyyymmddhh24miss') FROM "TF_F_USER_SVC" "A1"

b31ya73qzzj3w SELECT err_msg, COUNT(*) FROM ( select /*+ ordered use_nl(req rsp) full(req) index(rsp(trans_idc)) index(tsf(trans_idc)) */ SELF_LOG_FIELD_value AS prov, trans_ido, replace(TRANSLATE(rsp.busi_retmsg, '0123456789', ' '), ' ', '') AS err_msg from dbesblog_out.tlog_req partition(tlog_req_20130726) req , dbesblog_out.tlog_rsp partition(tlog_rsp_20130726) rsp , DBESBLOG_out.TLOG_SELF_FIELD partition(TLOG_SELF_FIELD_20130726) tsf where req.trans_idc = rsp.trans_idc and req.trans_idc=tsf.trans_idc and req.parent_trans_idc is not null and REQ.svc_name ='UserTagSer_JK' and tsf.SELF_LOG_FIELD_NAME='PROVINCE_CODE' and rsp_code='9999' ) c GROUP BY c.err_msg /* order by prov*/

c57j9r1p0hcjc INSERT INTO SALSEL.TF_F_USERTAG_CALLPROC_LOG (LOG_ID, PROC_NAME, ORD_NUM, OP_DATE, TYPE, IN_PARA, OUT_PARA, RETCODE, RETMSG) VALUES(:B9 , :B8 , :B7 , TO_DATE(:B6 , 'YYYYMMDDHH24MISS'), :B5 , :B4 , :B3 , :B2 , :B1 )

ghn6wfz8ss0uy SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), ROUND((SYSDATE - TO_DATE('19700101', 'YYYYMMDD')) * 86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(DBTIMEZONE), 1, 3)) * 3600) FROM DUAL

SQL Id	SQL Text
030zytrv08ns0	select chr('0x01') from dual
53xfykjfrfv9b	SELECT c.prov as "Ê¡·Ý", trans_ido as "Á÷Ë®ÐÅÏ¢", err_msg as "±¨´íÐÅÏ¢", id as "IDÐÅÏ¢" FROM ( select /+ ordered use_nl(req rsp) full(req) index(rsp(trans_idc)) index(tsf(trans_idc)) / SELF_LOG_FIELD_value AS prov, trans_ido, replace(TRANSLATE(rsp.busi_retmsg, '0123456789', ' '), ' ', '') AS err_msg, translate(rsp.busi_retmsg, '#' \|\| translate(rsp.busi_retmsg, '0123456789', '#'), '/') as id from dbesblog_out.tlog_req partition(tlog_req_20130726) req , dbesblog_out.tlog_rsp partition(tlog_rsp_20130726) rsp, DBESBLOG_out.TLOG_SELF_FIELD partition(TLOG_SELF_FIELD_20130726) tsf where req.trans_idc = rsp.trans_idc and req.trans_idc=tsf.trans_idc and req.parent_trans_idc is not null and REQ.svc_name ='UserTagSer_JK' and tsf.SELF_LOG_FIELD_NAME='PROVINCE_CODE' and rsp_code='9999' AND SELF_LOG_FIELD_value='86') c
a7nfsa9g020gq	SQL Text Not Available
b058pgbmzfsaz	SELECT "A1"."ITEM_ID", NVL("A1"."PRODUCT_ID", 0), NVL("A1"."PACKAGE_ID", 0), NVL("A1"."USER_ID", 0), NVL("A1"."PROD_ITEM_ID", 0), NVL("A1"."SERVICE_ID", 0), NVL("A1"."MAIN_TAG", '0'), 0, TO_CHAR(NVL("A1"."START_DATE", TO_DATE('20000101', 'yyy ymmdd')), 'yyyymmddhh24miss'), TO_CHAR(NVL("A1"."END_DATE", TO_DATE('20370101', 'yyyymmdd')), 'yyyymmddhh24miss') FROM "TF_F_USER_SVC" "A1"
b31ya73qzzj3w	SELECT err_msg, COUNT() FROM ( select /+ ordered use_nl(req rsp) full(req) index(rsp(trans_idc)) index(tsf(trans_idc)) / SELF_LOG_FIELD_value AS prov, trans_ido, replace(TRANSLATE(rsp.busi_retmsg, '0123456789', ' '), ' ', '') AS err_msg from dbesblog_out.tlog_req partition(tlog_req_20130726) req , dbesblog_out.tlog_rsp partition(tlog_rsp_20130726) rsp , DBESBLOG_out.TLOG_SELF_FIELD partition(TLOG_SELF_FIELD_20130726) tsf where req.trans_idc = rsp.trans_idc and req.trans_idc=tsf.trans_idc and req.parent_trans_idc is not null and REQ.svc_name ='UserTagSer_JK' and tsf.SELF_LOG_FIELD_NAME='PROVINCE_CODE' and rsp_code='9999' ) c GROUP BY c.err_msg / order by prov*/
c57j9r1p0hcjc	INSERT INTO SALSEL.TF_F_USERTAG_CALLPROC_LOG (LOG_ID, PROC_NAME, ORD_NUM, OP_DATE, TYPE, IN_PARA, OUT_PARA, RETCODE, RETMSG) VALUES(:B9 , :B8 , :B7 , TO_DATE(:B6 , 'YYYYMMDDHH24MISS'), :B5 , :B4 , :B3 , :B2 , :B1 )
ghn6wfz8ss0uy	SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), ROUND((SYSDATE - TO_DATE('19700101', 'YYYYMMDD')) * 86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(DBTIMEZONE), 1, 3)) * 3600) 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
SALSEL.PAC_REAL_USERTAG_TEST.P_DEAL_REALUSERTAG_INFO	30.42	SQL	25.42
		SALSEL.PAC_REAL_USERTAG_TEST.P_DEAL_REALUSERTAG_INFO	2.08

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
3831,15786	10.42	gc cr multi block request	5.00	PFTEST	plsqldev.exe	12/150 [ 8%]	0
		gc current block 2-way	1.67			4/150 [ 3%]	0
		CPU + Wait for CPU	1.25			3/150 [ 2%]	0
1705, 6424	8.33	CPU + Wait for CPU	5.42	PFTEST	oracle@KMSASMDB (TNS V1-V3)	13/150 [ 9%]	0
		db file scattered read	1.25			3/150 [ 2%]	0
2998,33627	6.25	gc cr multi block request	3.33	PFTEST	plsqldev.exe	8/150 [ 5%]	0
		CPU + Wait for CPU	1.67			4/150 [ 3%]	0
		gc cr block 2-way	1.25			3/150 [ 2%]	0
4481, 1	5.42	CPU + Wait for CPU	4.17	SYS	oracle@SIDDB01 (LGWR)	10/150 [ 7%]	0
		log file parallel write	1.25			3/150 [ 2%]	0
3521,38559	3.75	CPU + Wait for CPU	2.08	SALSEL	JDBC Thin Client	5/150 [ 3%]	3

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
3539,15410	2.08	enq: TX - index contention	2.08	SALSEL	JDBC Thin Client	5/150 [ 3%]	5
4481, 1	1.67	log file sync	1.67	SYS	oracle@SIDDB01 (LGWR)	13/150 [ 9%]	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
351850	7.92	gc cr multi block request	5.83	DBESBLOG_OUT.TLOG_REQ.TLOG_REQ_20130726 (TABLE PARTITION)	TS_ESB_LOG
		db file parallel read	1.25
351856	2.92	gc current block 2-way	1.67	DBESBLOG_OUT.TLOG_RSP.TLOG_RSP_20130726 (TABLE PARTITION)	TS_ESB_LOG
		gc cr block 2-way	1.25
61999	2.08	db file scattered read	1.25	SID.TF_F_USER_SVC (TABLE)	TS_SID
356154	2.08	gc cr multi block request	2.08	DBESBLOG_OUT.TLOG_REQ.TLOG_REQ_20130728 (TABLE PARTITION)	TS_ESB_LOG
62003	1.25	db file scattered read	1.25	SID.TF_F_USER_SVCSTATE (TABLE)	TS_SID

Top DB Files

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

File ID	% Activity	Event	% Event	File Name	Tablespace
107	2.08	gc cr multi block request	1.67	/oracle/oradata/esbdata4/ts_esb_log32.dbf	TS_ESB_LOG
194	1.67	db file scattered read	0.83	/oracle/oradata/siddata2/ts_sid18.dbf	TS_SID
229	1.67	gc cr multi block request	0.83	/oracle/oradata/siddata3/dbfile_esb/ts_esb_log68.dbf	TS_ESB_LOG
109	1.25	gc current block 2-way	0.83	/oracle/oradata/esbdata4/ts_esb_log34.dbf	TS_ESB_LOG
204	1.25	db file sequential read	0.83	/oracle/oradata/siddata1/datafile/ts_sid25.dbf	TS_SID

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
09:50:00 (5.0 min)	39	CPU + Wait for CPU	14	5.83
		gc cr multi block request	8	3.33
		reliable message	5	2.08
09:55:00 (5.0 min)	42	CPU + Wait for CPU	30	12.50
		db file sequential read	4	1.67
		reliable message	3	1.25
10:00:00 (5.0 min)	69	CPU + Wait for CPU	38	15.83
		SQL*Net message from dblink	6	2.50
		gc cr multi block request	6	2.50
10:05:00 (5.0 min)	60	CPU + Wait for CPU	35	14.58
		db file sequential read	10	4.17
		enq: TX - index contention	5	2.08
10:10:00 (5.0 min)	30	CPU + Wait for CPU	18	7.50
		db file scattered read	3	1.25
		db file sequential read	3	1.25

End of Report Report written to ashrpt_1_0729_1015.html