ASH Report For ZXDB/zxdb1

DB Name DB Id Instance Inst num Release RAC Host

ZXDB 2879712909 zxdb1 1 11.2.0.3.0 YES zhixing-db1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ZXDB	2879712909	zxdb1	1	11.2.0.3.0	YES	zhixing-db1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

16 3,552M (100%) 2,656M (74.8%) 880M (24.8%) 32.0M (0.9%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
16	3,552M (100%)	2,656M (74.8%)	880M (24.8%)	32.0M (0.9%)

Sample Time Data Source

Analysis Begin Time: 28-Jan-13 10:13:46 V$ACTIVE_SESSION_HISTORY

Analysis End Time: 28-Jan-13 10:54:30 V$ACTIVE_SESSION_HISTORY

Elapsed Time: 40.7 (mins)

Sample Count: 104,786

Average Active Sessions: 42.87

Avg. Active Session per CPU: 2.68

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	28-Jan-13 10:13:46	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	28-Jan-13 10:54:30	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	40.7 (mins)
Sample Count:	104,786
Average Active Sessions:	42.87
Avg. Active Session per CPU:	2.68
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
db file sequential read	User I/O	75.45	32.35
enq: TM - contention	Application	7.17	3.07
read by other session	User I/O	5.89	2.52
direct path read	User I/O	2.56	1.10
log file sync	Commit	1.98	0.85

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
log file parallel write	System I/O	1.23	0.53

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
db file sequential read	75.49	"10","282950","1"	0.00	file#	block#	blocks
enq: TM - contention	7.17	"1414332419","82517","0"	5.99	name\|mode	object #	table/partition
read by other session	5.89	"15","270467","1"	0.01	file#	block#	class#
direct path read	2.56	"30","55074","94"	0.01	file number	first dba	block cnt
log file sync	1.98	"2029","296603891","0"	0.00	buffer#	sync scn	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
zxdb	JDBC Thin Client	96.59	UNNAMED	96.59
SYS$BACKGROUND	UNNAMED	2.92	UNNAMED	2.92

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	573	86.79	37.21
INSERT	21	7.24	3.10

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	95.01	40.73

Back to Load Profile
Back to Top

Top Remote Instances

Wait Class	% Wait Class	Remote Instance#	% Activity
Cluster	1.26	2	1.01

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
4sts2w43rb2z8	234735634	52	15.28	db file sequential read	13.42	TABLE ACCESS - BY LOCAL INDEX ROWID	7.45	select * from ( select row_.*,...
4sts2w43rb2z8	234735634	52	15.28257591663008417154963449315748286985	read by other session	1.79	TABLE ACCESS - BY LOCAL INDEX ROWID	1.12
ah59uvrxqu5cd	234735634	33	7.75	db file sequential read	7.71	TABLE ACCESS - BY LOCAL INDEX ROWID	4.41	select * from ( select row_.*,...
dmj9073xrdxst	3023053775	187	6.79	db file sequential read	6.16	TABLE ACCESS - BY GLOBAL INDEX ROWID	6.00	select count(*) endCaseNum fro...
5fzf5v9v3a5rs		209	5.11	enq: TM - contention	4.81	Row Source Not Available	4.81	insert into tb_case_infor (app...
gg15n5vq2wwy6	234735634	18	4.01	db file sequential read	3.74	TABLE ACCESS - BY LOCAL INDEX ROWID	2.19	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
4sts2w43rb2z8	234735634	52	15.28	TABLE ACCESS - BY LOCAL INDEX ROWID	8.59	db file sequential read	7.45	select * from ( select row_.*,...
4sts2w43rb2z8	234735634	52	15.28257591663008417154963449315748286985	TABLE ACCESS - BY LOCAL INDEX ROWID	5.23	db file sequential read	4.64
ah59uvrxqu5cd	234735634	33	7.75	TABLE ACCESS - BY LOCAL INDEX ROWID	4.43	db file sequential read	4.41	select * from ( select row_.*,...
ah59uvrxqu5cd	234735634	33	7.75389842154486286335960910808695818144	TABLE ACCESS - BY LOCAL INDEX ROWID	2.67	db file sequential read	2.65
dmj9073xrdxst	3023053775	187	6.79	TABLE ACCESS - BY GLOBAL INDEX ROWID	6.61	db file sequential read	6.00	select count(*) endCaseNum fro...
5fzf5v9v3a5rs		209	5.11	Row Source Not Available	5.11	enq: TM - contention	4.81	insert into tb_case_infor (app...
gg15n5vq2wwy6	234735634	18	4.01	TABLE ACCESS - BY LOCAL INDEX ROWID	2.32	db file sequential read	2.19	select * from ( select row_.*,...
gg15n5vq2wwy6	234735634	18	4.00721470425438512778424598705933998817	TABLE ACCESS - BY LOCAL INDEX ROWID	1.32	db file sequential read	1.23

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

4sts2w43rb2z8 select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 15) where rownum_ >= 1

5fzf5v9v3a5rs insert into tb_case_infor (apply_action, apply_money, apply_res, apply_rights, auto_exec_money, case_code, case_kind_id, kind_cause_id, case_src_id, case_state_id, cause_id, court_id, create_time, creater_id, defer_begin_date, defer_cause_id, defer_days, defer_end_date, depart_id, exec_incept_date, execed_action, execed_money, execed_rights, execed_res, foreign_factor_id, gist_id, gist_cid, gist_content, gist_date, gist_term, gist_unit, is_deleted, is_report, last_update_time, maturity_date, parent_case_code, parent_court, reg_date, remark, root_id, state_end_date, state_update_time, will_expense, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 )

ah59uvrxqu5cd select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 9) where rownum_ >= 1

dmj9073xrdxst select count(*) endCaseNum from tb_exec_end_case e where e.court_id = :1 and to_char(e.end_case_date, 'yyyyMM') <= :2 and to_char(e.end_case_date, 'yyyyMM') >=:3

gg15n5vq2wwy6 select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 21) where rownum_ >= 1

SQL Id	SQL Text
4sts2w43rb2z8	select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 15) where rownum_ >= 1
5fzf5v9v3a5rs	insert into tb_case_infor (apply_action, apply_money, apply_res, apply_rights, auto_exec_money, case_code, case_kind_id, kind_cause_id, case_src_id, case_state_id, cause_id, court_id, create_time, creater_id, defer_begin_date, defer_cause_id, defer_days, defer_end_date, depart_id, exec_incept_date, execed_action, execed_money, execed_rights, execed_res, foreign_factor_id, gist_id, gist_cid, gist_content, gist_date, gist_term, gist_unit, is_deleted, is_report, last_update_time, maturity_date, parent_case_code, parent_court, reg_date, remark, root_id, state_end_date, state_update_time, will_expense, id) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 )
ah59uvrxqu5cd	select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 9) where rownum_ >= 1
dmj9073xrdxst	select count(*) endCaseNum from tb_exec_end_case e where e.court_id = :1 and to_char(e.end_case_date, 'yyyyMM') <= :2 and to_char(e.end_case_date, 'yyyyMM') >=:3
gg15n5vq2wwy6	select * from ( select row_.*, rownum rownum_ from ( select i.ID, i.REG_DATE regDate, pc.CODE_NAME, pa.iname, i.CASE_CODE, (select LISTAGG(p1.INAME, ', ')WITHIN GROUP(order by p1.ID)from tb_case_party p1 where p1.CASE_ID=i.id and p1.SUIT_IDENTITY_ID=570)proposer, (select LISTAGG(p2.INAME, ', ')WITHIN GROUP(order by p2.ID)from tb_case_party p2 where p2.CASE_ID=i.id and p2.SUIT_IDENTITY_ID=571)party from tb_case_infor i join TB_PARA_CODE pc on pc.ID=i.CASE_STATE_ID left join tb_para_case_cause pa on pa.id=i.CAUSE_ID where i.court_id=:1 and i.creater_id=:2 and i.IS_DELETED=0 order by i.CASE_CODE desc ) row_ where rownum <= 21) where rownum_ >= 1

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
V8 Bundled Exec	98,987	94.47	40.50
FETCH	1,316	1.26	0.54

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
445,10505	2.33	db file sequential read	2.29	ZHIXING	JDBC Thin Client	2,398/2,444 [ 98%]
719, 8805	2.33	db file sequential read	2.06	ZHIXING	JDBC Thin Client	2,163/2,444 [ 89%]
866,24559	2.33	db file sequential read	2.26	ZHIXING	JDBC Thin Client	2,371/2,444 [ 97%]
1149,39221	2.33	db file sequential read	2.18	ZHIXING	JDBC Thin Client	2,287/2,444 [ 94%]
1569,29941	2.33	db file sequential read	2.09	ZHIXING	JDBC Thin Client	2,191/2,444 [ 90%]

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
570, 1( 1)	2.14	log file sync	1.83	SYS	oracle@zhixing-db1 (LGWR)	1,291/2,444 [ 53%]	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
82286	11.55	db file sequential read	10.38	ZHIXING.TB_CASE_PARTY.SYS_P501 (TABLE PARTITION)	TS_CASE_DATA
82405	11.32	db file sequential read	9.19	ZHIXING.TB_CASE_PARTY.SYS_P522 (TABLE PARTITION)	TS_CASE_DATA
82405	11.32498616227358616609089000438989941404	read by other session	1.73	ZHIXING.TB_CASE_PARTY.SYS_P522 (TABLE PARTITION)	TS_CASE_DATA
82308	10.48	db file sequential read	8.92	ZHIXING.TB_CASE_PARTY.SYS_P515 (TABLE PARTITION)	TS_CASE_DATA
82308	10.47754471017120607714771057202298016911	read by other session	1.16	ZHIXING.TB_CASE_PARTY.SYS_P515 (TABLE PARTITION)	TS_CASE_DATA
82293	9.24	db file sequential read	8.04	ZHIXING.TB_CASE_PARTY.SYS_P508 (TABLE PARTITION)	TS_CASE_DATA
82279	7.17	db file sequential read	6.43	ZHIXING.TB_CASE_PARTY.SYS_P494 (TABLE PARTITION)	TS_CASE_DATA

Top DB Files

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

File ID	% Activity	Event	% Event	File Name	Tablespace
8	7.75	db file sequential read	6.90	+DATA/zxdb/datafile/ts_case_data2.dbf	TS_CASE_DATA
27	7.15	db file sequential read	6.54	+DATA/zxdb/datafile/ts_exec_force1.dbf	TS_EXEC_FORCE
9	7.14	db file sequential read	6.24	+DATA/zxdb/datafile/ts_case_data3.dbf	TS_CASE_DATA
7	6.86	db file sequential read	6.00	+DATA/zxdb/datafile/ts_case_data1.dbf	TS_CASE_DATA
13	6.42	db file sequential read	5.56	+DATA/zxdb/datafile/ts_case_data7.dbf	TS_CASE_DATA

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:13:46 (2.2 min)	4,828	db file sequential read	3,701	3.53
10:13:46 (2.2 min)	4828	read by other session	485	0.46
10:13:46 (2.2 min)	4828	direct path read	299	0.29
10:16:00 (4.0 min)	8,014	db file sequential read	6,345	6.06
10:16:00 (4.0 min)	8014	read by other session	531	0.51
10:16:00 (4.0 min)	8014	direct path read	267	0.25
10:20:00 (4.0 min)	9,156	db file sequential read	7,016	6.70
10:20:00 (4.0 min)	9156	read by other session	859	0.82
10:20:00 (4.0 min)	9156	direct path read	362	0.35
10:24:00 (4.0 min)	10,692	db file sequential read	8,635	8.24
10:24:00 (4.0 min)	10692	read by other session	494	0.47
10:24:00 (4.0 min)	10692	log file sync	336	0.32
10:28:00 (4.0 min)	10,309	db file sequential read	8,568	8.18
10:28:00 (4.0 min)	10309	read by other session	323	0.31
10:28:00 (4.0 min)	10309	log file sync	244	0.23
10:32:00 (4.0 min)	9,983	db file sequential read	8,518	8.13
10:32:00 (4.0 min)	9983	read by other session	406	0.39
10:32:00 (4.0 min)	9983	log file sync	240	0.23
10:36:00 (4.0 min)	9,901	db file sequential read	8,206	7.83
10:36:00 (4.0 min)	9901	read by other session	490	0.47
10:36:00 (4.0 min)	9901	log file sync	246	0.23
10:40:00 (4.0 min)	12,625	db file sequential read	7,616	7.27
10:40:00 (4.0 min)	12625	enq: TM - contention	3,215	3.07
10:40:00 (4.0 min)	12625	read by other session	753	0.72
10:44:00 (4.0 min)	13,384	db file sequential read	7,696	7.34
10:44:00 (4.0 min)	13384	enq: TM - contention	4,071	3.89
10:44:00 (4.0 min)	13384	read by other session	586	0.56
10:48:00 (4.0 min)	9,524	db file sequential read	7,682	7.33
10:48:00 (4.0 min)	9524	read by other session	898	0.86
10:48:00 (4.0 min)	9524	direct path read	319	0.30
10:52:00 (2.5 min)	6,370	db file sequential read	5,122	4.89
10:52:00 (2.5 min)	6370	direct path read	431	0.41
10:52:00 (2.5 min)	6370	read by other session	345	0.33

End of Report