ASH Report For AZHDB/azhdb1

DB Name DB Id Instance Inst num Release RAC Host

AZHDB 2008981896 azhdb1 1 10.2.0.4.0 YES wxazhdb1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
AZHDB	2008981896	azhdb1	1	10.2.0.4.0	YES	wxazhdb1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

8 26,624M (100%) 15,632M (58.7%) 10,197M (38.3%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
8	26,624M (100%)	15,632M (58.7%)	10,197M (38.3%)	M (%)

Sample Time Data Source

Analysis Begin Time: 13-Jul-12 14:15:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 18173

Analysis End Time: 13-Jul-12 14:30:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 18173

Elapsed Time: 15.0 (mins)

Sample Count: 371

Average Active Sessions: 4.12

Avg. Active Session per CPU: 0.52

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	13-Jul-12 14:15:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 18173
Analysis End Time:	13-Jul-12 14:30:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 18173
Elapsed Time:	15.0 (mins)
Sample Count:	371
Average Active Sessions:	4.12
Avg. Active Session per CPU:	0.52
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	81.13	3.34
db file scattered read	User I/O	12.40	0.51
db file sequential read	User I/O	3.77	0.16

Top Background 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
db file scattered read	12.40	"8","888919","16"	0.27	file#	block#	blocks
db file sequential read	3.77	"8","1273392","1"	0.27	file#	block#	blocks

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
azhdb	UNNAMED	81.40	UNNAMED	81.40
SYS$USERS	UNNAMED	17.52	UNNAMED	17.52

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	16	95.15	3.92
DELETE	1	2.43	0.10

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
3b9x3mgs0xk74	3995869637	36.66	CPU + Wait for CPU	36.66	select id, parent_use...
5ytu41d5xtcdx	3333574043	35.85	CPU + Wait for CPU	35.85	select count(1) f...
3h2dpudbw7yct	3720084833	17.52	db file scattered read	12.40	Select ID, PLATFORM_IP, USER_C...
			db file sequential read	2.96
			CPU + Wait for CPU	1.89
2ppcfrxygfjsm	582941941	2.43	CPU + Wait for CPU	2.16	DELETE FROM IM_ONCE_PASSWORD W...
amcjddd82z7gw	1459789953	1.08	CPU + Wait for CPU	0.81	select 1 from sm_role_menu sr...

Top SQL using literals

No data exists for this section of the report.

Complete List of SQL Text

SQL Id SQL Text

2ppcfrxygfjsm DELETE FROM IM_ONCE_PASSWORD WHERE CREATED_DATE > :B3 - 1/(24*60)*:B2 AND PHONE = :B1

3b9x3mgs0xk74 select id, parent_user_group_id, user_group_name, parent_name, name_path, type_flag, ACTIVE_FLAG from ( select id, parent_user_group_id, user_group_name, parent_name, name_path, type_flag, rownum rn, ACTIVE_FLAG from ( select org.id, org.parent_user_group_id, org.user_group_name, '' as parent_name, org.name_path, org.type_flag, org.ACTIVE_FLAG from sm_organization org where org.parent_user_group_id != 0 and org.ACTIVE_FLAG = :1 and org.user_group_name like :2 start with org.id in ( select t.id from sm_organization t where exists (select u.id from sm_user_visible u where u.oid=t.id and u.userid=:3) ) connect by prior org.id=org.parent_user_group_id order by org.user_group_name ) where 1=1 and rownum <= :4 ) where rn >= :5

3h2dpudbw7yct Select ID, PLATFORM_IP, USER_CODE, PROGRAM_NAME, RESOURCE_TYPE, TARGET_IP, TARGET_RESOURCE_NAME, TARGET_USER, DATABASE_NAME, SESSION_ID, BEGIN_TIME, SUCCEED_FLAG, REMARK, 'AUDIT_SYS_SSO_LOG' as "AUDIT_SYS_SSO_LOG" From JS4AZH50.AUDIT_SYS_SSO_LOG Where ID > :1

5ytu41d5xtcdx select count(1) from sm_organization org where org.parent_user_group_id != 0 and org.ACTIVE_FLAG = :1 and org.user_group_name like :2 start with org.id in ( select t.id from sm_organization t where exists (select u.id from sm_user_visible u where u.oid=t.id and u.userid=:3) ) connect by prior org.id=org.parent_user_group_id

amcjddd82z7gw select 1 from sm_role_menu srm where srm.menu_id = :1 and srm.role_id = :2

SQL Id	SQL Text
2ppcfrxygfjsm	DELETE FROM IM_ONCE_PASSWORD WHERE CREATED_DATE > :B3 - 1/(2460):B2 AND PHONE = :B1
3b9x3mgs0xk74	select id, parent_user_group_id, user_group_name, parent_name, name_path, type_flag, ACTIVE_FLAG from ( select id, parent_user_group_id, user_group_name, parent_name, name_path, type_flag, rownum rn, ACTIVE_FLAG from ( select org.id, org.parent_user_group_id, org.user_group_name, '' as parent_name, org.name_path, org.type_flag, org.ACTIVE_FLAG from sm_organization org where org.parent_user_group_id != 0 and org.ACTIVE_FLAG = :1 and org.user_group_name like :2 start with org.id in ( select t.id from sm_organization t where exists (select u.id from sm_user_visible u where u.oid=t.id and u.userid=:3) ) connect by prior org.id=org.parent_user_group_id order by org.user_group_name ) where 1=1 and rownum <= :4 ) where rn >= :5
3h2dpudbw7yct	Select ID, PLATFORM_IP, USER_CODE, PROGRAM_NAME, RESOURCE_TYPE, TARGET_IP, TARGET_RESOURCE_NAME, TARGET_USER, DATABASE_NAME, SESSION_ID, BEGIN_TIME, SUCCEED_FLAG, REMARK, 'AUDIT_SYS_SSO_LOG' as "AUDIT_SYS_SSO_LOG" From JS4AZH50.AUDIT_SYS_SSO_LOG Where ID > :1
5ytu41d5xtcdx	select count(1) from sm_organization org where org.parent_user_group_id != 0 and org.ACTIVE_FLAG = :1 and org.user_group_name like :2 start with org.id in ( select t.id from sm_organization t where exists (select u.id from sm_user_visible u where u.oid=t.id and u.userid=:3) ) connect by prior org.id=org.parent_user_group_id
amcjddd82z7gw	select 1 from sm_role_menu srm where srm.menu_id = :1 and srm.role_id = :2

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
JS4AZH50.GETSJPTPASSWORD	2.43	SQL	2.16

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
2317,40751	17.52	db file scattered read	12.40	A4ZHQRY	46/90 [ 51%]
		db file sequential read	2.96		11/90 [ 12%]
		CPU + Wait for CPU	1.89		7/90 [ 8%]
2960,22176	7.82	CPU + Wait for CPU	7.82	JS4AZH50	29/90 [ 32%]
2651,31435	7.01	CPU + Wait for CPU	7.01	JS4AZH50	26/90 [ 29%]
2049,14880	5.66	CPU + Wait for CPU	5.66	JS4AZH50	21/90 [ 23%]
3131,29822	5.66	CPU + Wait for CPU	5.66	JS4AZH50	21/90 [ 23%]

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
76422	1.89	db file scattered read	1.35	JS4AZH50.AUDIT_SYS_SSO_LOG.P_R_201201 (TABLE PARTITION)	TS_TAB_ACC_01
76424	1.89	db file scattered read	1.62	JS4AZH50.AUDIT_SYS_SSO_LOG.P_R_201203 (TABLE PARTITION)	TS_TAB_ACC_01
76419	1.62	db file scattered read	1.35	JS4AZH50.AUDIT_SYS_SSO_LOG.P_R_201110 (TABLE PARTITION)	TS_TAB_ACC_02
76420	1.62	db file scattered read	0.81	JS4AZH50.AUDIT_SYS_SSO_LOG.P_R_201111 (TABLE PARTITION)	TS_TAB_ACC_01
76421	1.62	db file sequential read	1.08	JS4AZH50.AUDIT_SYS_SSO_LOG.P_R_201112 (TABLE PARTITION)	TS_TAB_ACC_02

Top DB Files

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

File ID	% Activity	Event	% Event	File Name	Tablespace
9	2.43	db file scattered read	1.35	/dev/rvg01_10g_002	TS_TAB_ACC_01
25	2.16	db file scattered read	1.89	/dev/rvg01_10g_018	TS_TAB_ACC_02
15	1.89	db file scattered read	1.35	/dev/rvg01_10g_008	TS_TAB_ACC_01
11	1.62	db file scattered read	0.81	/dev/rvg01_10g_004	TS_TAB_ACC_01
27	1.35	db file scattered read	1.35	/dev/rvg02_10g_002	TS_TAB_ACC_02

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
14:15:00 (5.0 min)	90	CPU + Wait for CPU	73	19.68
		db file scattered read	13	3.50
		db file sequential read	2	0.54
14:20:00 (5.0 min)	140	CPU + Wait for CPU	119	32.08
		db file scattered read	15	4.04
		db file sequential read	3	0.81
14:25:00 (5.0 min)	141	CPU + Wait for CPU	111	29.92
		db file scattered read	18	4.85
		db file sequential read	9	2.43

End of Report