ASH Report For OBIEE/OBIEE

DB Name DB Id Instance Inst num Release RAC Host

OBIEE 3098539300 OBIEE 1 11.2.0.3.0 NO erpbi.sunresort.local

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
OBIEE	3098539300	OBIEE	1	11.2.0.3.0	NO	erpbi.sunresort.local

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

1 12,233M (100%) 8,864M (72.5%) 2,427M (19.8%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
1	12,233M (100%)	8,864M (72.5%)	2,427M (19.8%)	M (%)

Sample Time Data Source

Analysis Begin Time: 27-Jun-16 16:00:35 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 8026

Analysis End Time: 27-Jun-16 16:30:35 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 8027
+ V$ACTIVE_SESSION_HISTORY

Elapsed Time: 30.0 (mins)

Sample Count: 726

Average Active Sessions: 4.03

Avg. Active Session per CPU: 4.03

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	27-Jun-16 16:00:35	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 8026
Analysis End Time:	27-Jun-16 16:30:35	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 8027 + V$ACTIVE_SESSION_HISTORY
Elapsed Time:	30.0 (mins)
Sample Count:	726
Average Active Sessions:	4.03
Avg. Active Session per CPU:	4.03
Report Target:	None specified

Top Events

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

Top User Events

Event	Event Class	% Event	Avg Active Sessions
virtual circuit wait	Network	41.87	1.69
direct path write temp	User I/O	31.27	1.26
local write wait	User I/O	5.79	0.23
control file parallel write	System I/O	5.10	0.21
CPU + Wait for CPU	CPU	4.27	0.17

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
db file async I/O submit	System I/O	5.65	0.23
CPU + Wait for CPU	CPU	1.65	0.07

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
virtual circuit wait	41.87	"8","2","0"	8.13	circuit#	type	NOT DEFINED
virtual circuit wait	41.87327823691460055096418732782369146006	"10","2","0"	7.85	circuit#	type	NOT DEFINED
virtual circuit wait	41.87327823691460055096418732782369146006	"19","2","0"	6.89	circuit#	type	NOT DEFINED
direct path write temp	31.27	"201","2697758","15"	0.14	file number	first dba	block cnt
local write wait	5.79	"201","2","0"	4.68	file#	block#	NOT DEFINED
db file async I/O submit	5.65	"1","0","0"	5.65	requests	interrupt	timeout
control file parallel write	5.23	"2","1","2"	1.65	files	block#	requests

Load Profile

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

Top Service/Module

Service	Module	% Activity	Action	% Action
SYS$USERS	Realtime Connection	48.48	UNNAMED	48.48
SYS$USERS	UNNAMED	40.50	UNNAMED	40.50
SYS$BACKGROUND	UNNAMED	7.44	UNNAMED	7.44
SYS$USERS	APEX_EBS_EXTENSION/APEX:APP 102:21	1.65	PAGE 21	1.24

Back to Load Profile
Back to Top

Top Client IDs

Client ID	% Activity	Avg Active Sessions	User	Program	Service
RECIPEMGR:11398252306914	1.52	0.06	ANONYMOUS		SYS$USERS

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	3	49.17	1.98
PL/SQL EXECUTE	11	3.17	0.13

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	51.79	2.09
PLSQL Execution	1.38	0.06

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
57ch9cdc0u9sm	3389070326	2	48.48	direct path write temp	30.72	HASH JOIN	30.72	select O.name objname, O.type#...
57ch9cdc0u9sm	3389070326	2	48.48484848484848484848484848484848484848	local write wait	5.79	SELECT STATEMENT	4.82
57ch9cdc0u9sm	3389070326	2	48.48484848484848484848484848484848484848	control file parallel write	5.10	SELECT STATEMENT	3.99
fjxq2h7d5f6z1		8	1.10	virtual circuit wait	1.10	Row Source Not Available	1.10	declare rc__ number; simple_li...

Top SQL with Top Row Sources

SQL ID	PlanHash	Sampled # of Executions	% Activity	Row Source	% RwSrc	Top Event	% Event	SQL Text
57ch9cdc0u9sm	3389070326	2	48.48	HASH JOIN	33.75	direct path write temp	30.72	select O.name objname, O.type#...
57ch9cdc0u9sm	3389070326	2	48.48484848484848484848484848484848484848	SELECT STATEMENT	11.43	local write wait	4.82
57ch9cdc0u9sm	3389070326	2	48.48484848484848484848484848484848484848	SELECT STATEMENT	3.17	control file parallel write	1.10
fjxq2h7d5f6z1		8	1.10	Row Source Not Available	1.10	virtual circuit wait	1.10	declare rc__ number; simple_li...

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

57ch9cdc0u9sm select O.name objname, O.type# objtype, U.name objowner, L2.*, O.subname from (select /*+ ordered */ 0, S.sid, S.serial#, nvl(S.sql_id, 0) , DECODE(L.type, 'TM', L.id1, 'TX', decode(L.request, 0, NVL(LO.object_id, -1), S.row_wait_obj#), -1) AS object_id , S.username, S.row_wait_obj#, S.row_wait_block# , S.row_wait_row#, S.row_wait_file#, L.type , decode(L.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , decode(L.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , L.id1, L.id2, L.ctime , P.spid, S.sql_hash_value from v$lock L, v$session S, v$process P, (select object_id, session_id, xidsqn from v$locked_object where xidsqn >0) LO where S.sid = L.sid and P.addr = S.paddr and L.type != 'MR' and L.sid = LO.session_id(+) and L.id2 = LO.xidsqn(+) ) L2 , sys."_CURRENT_EDITION_OBJ" O , sys.user$ U where O.obj#(+) = L2.object_id and O.owner# = U.user#(+) and U.type# != 2

fjxq2h7d5f6z1 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__, :nm__, :v__); htp.HTBUF_LEN := 63; null; null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if ((wwv_flow_epg_include_modules.authorize('wwv_flow_utilities.gen_popup_list') = false) or (owa_match.match_pattern(p_string => 'wwv_flow_utilities.gen_popup_list' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then rc__ := 2; else null; null; wwv_flow_utilities.gen_popup_list(p_name=>:p_name, p_lov=>:p_lov, p_lov_checksum=>:p_lov_checksum, p_request=>:p_request, p_element_index=>:p_element_index, p_form_index=>:p_form_index, p_max_elements=>:p_max_elements, p_escape_html=>:p_escape_html, p_eval_value=>:p_eval_value, p_translation=>:p_translation, p_item_id=>:p_item_id, p_return_key=>:p_return_key, p_hidden_elem_name=>:p_hidden_elem_name, p_elem_id=>:p_elem_id, p_hidden_elem_id=>:p_hidden_elem_id, p_ok_to_query=>:p_ok_to_query, p_flow_id=>:p_flow_id, p_page_id=>:p_page_id, p_session_id=>:p_session_id, p_company=>:p_company, p_filter=>:p_filter, p_min_row=>:p_min_row); if (wpg_docload.is_file_download) then rc__ := 1; wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit; owa.get_page(:data__, :ndata__); end if; end if; :rc__ := rc__; end;

SQL Id	SQL Text
57ch9cdc0u9sm	select O.name objname, O.type# objtype, U.name objowner, L2., O.subname from (select /+ ordered */ 0, S.sid, S.serial#, nvl(S.sql_id, 0) , DECODE(L.type, 'TM', L.id1, 'TX', decode(L.request, 0, NVL(LO.object_id, -1), S.row_wait_obj#), -1) AS object_id , S.username, S.row_wait_obj#, S.row_wait_block# , S.row_wait_row#, S.row_wait_file#, L.type , decode(L.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , decode(L.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?') , L.id1, L.id2, L.ctime , P.spid, S.sql_hash_value from v$lock L, v$session S, v$process P, (select object_id, session_id, xidsqn from v$locked_object where xidsqn >0) LO where S.sid = L.sid and P.addr = S.paddr and L.type != 'MR' and L.sid = LO.session_id(+) and L.id2 = LO.xidsqn(+) ) L2 , sys."_CURRENT_EDITION_OBJ" O , sys.user$ U where O.obj#(+) = L2.object_id and O.owner# = U.user#(+) and U.type# != 2
fjxq2h7d5f6z1	declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__, :nm__, :v__); htp.HTBUF_LEN := 63; null; null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if ((wwv_flow_epg_include_modules.authorize('wwv_flow_utilities.gen_popup_list') = false) or (owa_match.match_pattern(p_string => 'wwv_flow_utilities.gen_popup_list' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then rc__ := 2; else null; null; wwv_flow_utilities.gen_popup_list(p_name=>:p_name, p_lov=>:p_lov, p_lov_checksum=>:p_lov_checksum, p_request=>:p_request, p_element_index=>:p_element_index, p_form_index=>:p_form_index, p_max_elements=>:p_max_elements, p_escape_html=>:p_escape_html, p_eval_value=>:p_eval_value, p_translation=>:p_translation, p_item_id=>:p_item_id, p_return_key=>:p_return_key, p_hidden_elem_name=>:p_hidden_elem_name, p_elem_id=>:p_elem_id, p_hidden_elem_id=>:p_hidden_elem_id, p_ok_to_query=>:p_ok_to_query, p_flow_id=>:p_flow_id, p_page_id=>:p_page_id, p_session_id=>:p_session_id, p_company=>:p_company, p_filter=>:p_filter, p_min_row=>:p_min_row); if (wpg_docload.is_file_download) then rc__ := 1; wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit; owa.get_page(:data__, :ndata__); end if; end if; :rc__ := rc__; end;

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
APEX_050000.WWV_FLOW_UTILITIES.GEN_POPUP_LIST	1.24	SQL	1.24

Top Java Workload

No data exists for this section of the report.

Top Call Types

Call Type	Count	% Activity	Avg Active Sessions
V8 Bundled Exec	354	48.76	1.97
LOGOFF	72	9.92	0.40

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
33,11127	24.24	direct path write temp	15.29	DBSNMP	OMS	111/180 [ 62%]
33,11127	24.24242424242424242424242424242424242424	control file parallel write	2.75	DBSNMP	OMS	20/180 [ 11%]
33,11127	24.24242424242424242424242424242424242424	local write wait	2.75	DBSNMP	OMS	20/180 [ 11%]
97, 4193	24.24	direct path write temp	15.43	DBSNMP	OMS	112/180 [ 62%]
97, 4193	24.24242424242424242424242424242424242424	local write wait	3.03	DBSNMP	OMS	22/180 [ 12%]
97, 4193	24.24242424242424242424242424242424242424	control file parallel write	2.34	DBSNMP	OMS	17/180 [ 9%]
10, 1	5.79	db file async I/O submit	5.65	SYS	oracle@erpbi.s....local (DBW0)	41/180 [ 23%]
88, 5	5.51	virtual circuit wait	4.82	ANONYMOUS		35/180 [ 19%]
96, 17	5.51	virtual circuit wait	4.82	ANONYMOUS		35/180 [ 19%]

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 (Inst)	% Activity	Event Caused	% Event	User	Program	# Samples Active	XIDs
10, 1( 1)	5.92	local write wait	5.79	SYS	oracle@erpbi.s....local (DBW0)	24/180 [ 13%]	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

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:00:35 (4.4 min)	93	direct path write temp	29	3.99
16:00:35 (4.4 min)	93	virtual circuit wait	29	3.99
16:00:35 (4.4 min)	93	CPU + Wait for CPU	12	1.65
16:05:00 (5.0 min)	121	virtual circuit wait	56	7.71
16:05:00 (5.0 min)	121	direct path write temp	45	6.20
16:05:00 (5.0 min)	121	control file parallel write	5	0.69
16:10:00 (5.0 min)	175	virtual circuit wait	100	13.77
16:10:00 (5.0 min)	175	direct path write temp	36	4.96
16:10:00 (5.0 min)	175	local write wait	10	1.38
16:15:00 (5.0 min)	140	virtual circuit wait	74	10.19
16:15:00 (5.0 min)	140	direct path write temp	38	5.23
16:15:00 (5.0 min)	140	CPU + Wait for CPU	6	0.83
16:20:00 (5.0 min)	77	direct path write temp	37	5.10
16:20:00 (5.0 min)	77	control file parallel write	10	1.38
16:20:00 (5.0 min)	77	virtual circuit wait	8	1.10
16:25:00 (5.0 min)	109	direct path write temp	39	5.37
16:25:00 (5.0 min)	109	virtual circuit wait	34	4.68
16:25:00 (5.0 min)	109	db file async I/O submit	9	1.24
16:30:00 (35 secs)	11	direct path write temp	3	0.41
16:30:00 (35 secs)	11	virtual circuit wait	3	0.41
16:30:00 (35 secs)	11	db file async I/O submit	2	0.28

End of Report