224270.1 TRCA Trace Analyzer 11.4.4.2 Report: trca_e46032.html

dwhqa_ora_20751.trc (1133271 bytes)

Total Trace Response Time: 78.861 secs.
2012-AUG-07 17:29:16.067 (start of first db call in trace 13859072.946157).
2012-AUG-07 17:30:34.928 (end of last db call in trace 13859151.80751).

Glossary of Terms Used
Response Time Summary
Overall Time and Totals
Non-Recursive Time and Totals
Recursive Time and Totals
Top SQL
Non-Recursive SQL
SQL Genealogy
Individual SQL
Overall Segment I/O Wait Summary
Hot I/O Blocks
Gaps in Trace
ORA errors in Trace
Transactions Summary
Non-default Initialization Params
Trace Header
Tool Data Dictionary
Tool Execution Environment
Tool Configuration Parameters

Glossary of Terms Used

[+]

Response Time Summary

Response Time Component	Time (in secs)	pct of total resp time	Time (in secs)	pct of total resp time	Time (in secs)	pct of total resp time
CPU Time:	40.730	51.6%
Non-idle Wait Time:	33.880	43.0%
ET Unaccounted-for Time:	15.944	20.2%
Total Elapsed Time¹:			90.554	114.8%
Idle Wait Time:			17.101	21.7%
RT Unaccounted-for Time:			-28.793	-36.5%
Total Response Time²:					78.861	100.0%

(1) Total Elapsed Time = "CPU Time" + "Non-Idle Wait Time" + "ET Unaccounted-for Time".
(2) Total Response Time = "Total Elapsed Time" + "Idle Wait Time" + "RT Unaccounted-for Time".
Total Accounted-for Time = "CPU Time" + "Non-Idle Wait Time" + "Idle Wait Time" = 107.655 secs.
Total Unccounted-for Time = "ET Unaccounted-for Time" + "RT Unaccounted-for Time" = -12.849 secs.
Go to Top

Overall Time and Totals (Non-Recursive and Recursive)

[-]

Call	Total Response Time³	Response Time Accounted-for²	Elapsed Time¹	CPU Time	Non-Idle Wait Time	Elapsed Time Unaccounted-for	Idle Wait Time	Response Time Unaccounted-for
Parse:		0.072	0.072	0.010	0.000	0.062	0.000
Execute:		107.583	90.482	40.720	33.880	15.882	17.101
Fetch:		0.000	0.000	0.000	0.000	0.000	0.000
Total:	78.861	107.655	90.554	40.730	33.880	15.944	17.101	-28.793

(1) Elapsed Time = "CPU Time" + "Non-Idle Wait Time" + "Elapsed Time Unaccounted-for".
(2) Response Time Accounted-for = "Elapsed Time" + "Idle Wait Time".
(3) Total Response Time = "Response Time Accounted-for" + "Response Time Unaccounted-for".

Call	Call Count	OS Buffer Gets (disk)	BG Consistent Read Mode (query)	BG Current Mode (current)	Rows Processed or Returned	Library Cache Misses	Times Waited Non-Idle	Times Waited Idle
Parse:	2	0	3	0	0	2	0	0
Execute:	4	12187	2068345	4472	1725	1	10074	2
Fetch:	1	0	0	0	1	0	0	0
Total:	7	12187	2068348	4472	1726	3	10074	2

Event Name	Wait Class	Non-Idle Wait Time	Times Waited Non-Idle	Idle Wait Time	Times Waited Idle	Average Wait Time	Max Wait Time	Blocks	Average Blocks
db file sequential read:	User I/O	32.681	10037			0.003256	0.081095	10037	1
SQL*Net message from client:	Idle			17.101	2	8.550436	11.692698	0
db file scattered read:	User I/O	1.198	35			0.034235	0.071724	2150	61.4
SQL*Net message to client:	Network	0.000	2			0.000002	0.000003	0
Total:		33.880	10074	17.101	2

Go to Top

Non-Recursive Time and Totals (depth = 0)

[-]

Call	Response Time Accounted-for	Elapsed Time	CPU Time	Non-Idle Wait Time	Elapsed Time Unaccounted-for	Idle Wait Time
Parse:	0.071	0.071	0.010	0.000	0.061	0.000
Execute:	107.482	90.381	40.720	33.880	15.781	17.101
Fetch:	0.000	0.000	0.000	0.000	0.000	0.000
Total:	107.553	90.452	40.730	33.880	15.843	17.101

Call	Call Count	OS Buffer Gets (disk)	BG Consistent Read Mode (query)	BG Current Mode (current)	Rows Processed or Returned	Library Cache Misses	Times Waited Non-Idle	Times Waited Idle
Parse:	1	0	0	0	0	1	0	0
Execute:	3	12187	2068345	4472	1725	0	10074	2
Fetch:	0	0	0	0	0	0	0	0
Total:	4	12187	2068345	4472	1725	1	10074	2

Event Name	Wait Class	Non-Idle Wait Time	Times Waited Non-Idle	Idle Wait Time	Times Waited Idle	Average Wait Time	Max Wait Time	Blocks	Average Blocks
db file sequential read:	User I/O	32.681	10037			0.003256	0.081095	10037	1
SQL*Net message from client:	Idle			17.101	2	8.550436	11.692698	0
db file scattered read:	User I/O	1.198	35			0.034235	0.071724	2150	61.4
SQL*Net message to client:	Network	0.000	2			0.000002	0.000003	0
Total:		33.880	10074	17.101	2

Go to Top

Recursive Time and Totals (depth > 0)

[-]

Call	Response Time Accounted-for	Elapsed Time	Elapsed Time Unaccounted-for
Parse:	0.000	0.000	0.000
Execute:	0.101	0.101	0.101
Fetch:	0.000	0.000	0.000
Total:	0.102	0.102	0.102

Call	Call Count	BG Consistent Read Mode (query)	Rows Processed or Returned	Library Cache Misses
Parse:	1	0	0	1
Execute:	1	0	0	1
Fetch:	1	3	1	0
Total:	3	3	1	2

Top SQL

[-]
There are 3 SQL statements with "Response Time Accounted-for" larger than threshold of 10.0% of the "Total Response Time Accounted-for".
These combined 3 SQL statements are responsible for a total of 99.9% of the "Total Response Time Accounted-for".

Rank	Trace RT Pct¹	Self Response Time²	Elapsed Time	CPU Time	Non-Idle Wait Time	Idle Wait Time	Recursive Response Time³	Exec Count	SQL Text	Hash Value	Plan Hash Value
1:	68.1%	73.351	73.351	40.730	33.880	0.000	0.102	1	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
2:	21.7%	23.385	11.693	0.000	0.000	11.693	0.000	1	CURSOR #3	0	0
3:	10.0%	10.816	5.408	0.000	0.000	5.408	0.000	1	CURSOR #2	0	0

(1) Percent of "Total Response Time Accounted-for", which is 107.655 secs.
(2) "Self Response Time Accounted-for" in secs (caused by this SQL statement).
(3) "Recursive Response Time Accounted-for" in secs (caused by recursive SQL invoked by this statement).
Go to Top

There are 2 SQL statements with "Elapsed Time" larger than threshold of 10.0% of the "Total Elapsed Time".
These combined 2 SQL statements are responsible for a total of 93.9% of the "Total Elapsed Time".

Rank	Trace ET Pct¹	Self Elapsed Time²	CPU Time	Non-Idle Wait Time	Recursive Elapsed Time³	Exec Count	User	Depth	SQL Text	Hash Value	Plan Hash Value
1:	81.0%	73.351	40.730	33.880	0.102	1	0	0	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
2:	12.9%	11.693	0.000	0.000	0.000	1	0	0	CURSOR #3	0	0

(1) Percent of "Total Elapsed Time", which is 90.554 secs.
(2) "Self Elapsed Time" in secs (caused by this SQL statement).
(3) "Recursive Elapsed Time" in secs (caused by recursive SQL invoked by this statement).
Go to Top

There is only one SQL statement with "CPU Time" larger than threshold of 10.0% of the "Total CPU Time".

Rank	Trace CPU Pct¹	Self CPU Time²	Recursive CPU Time³	Exec Count	User	Depth	SQL Text	Hash Value	Plan Hash Value
1:	100.0%	40.730	0.000	1	0	0	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562

(1) Percent of "Total CPU Time", which is 40.730 secs.
(2) "Self CPU Time" in secs (caused by this SQL statement).
(3) "Recursive CPU Time" in secs (caused by recursive SQL invoked by this statement).
Go to Top

Non-Recursive SQL (depth = 0)

[-]
List of individual executions of non-recursive SQL in chronological order by first db call timestamp.

#	Total Response Time¹	Trace RT Pct²	Self Response Time³	Recursive Response Time⁴	SQL Text	Start Timestamp	End Timestamp	Hash Value	Plan Hash Value
1:	73.453	68.1%	73.351	0.102	update dsrdba.forecast f set value=0 where 0<(select co	AUG-07 17:29:16.067	AUG-07 17:30:29.520	3733909562
2:	11.693	21.7%	23.385	0.000	CURSOR #3	AUG-07 17:30:23.236	AUG-07 17:30:34.928	0	0
3:	5.408	10.0%	10.816	0.000	CURSOR #2	AUG-07 17:30:29.520	AUG-07 17:30:34.928	0	0

(1) "Total Response Time" in secs, as per start and end timestamps of db calls. It includes "Unaccounted-for" times.
(1) This list is constrained by threshold configuration parameter with current value of 0.1s.
(2) Percent of "Total Response Time Accounted-for", which is 107.655 secs.
(3) "Self Response Time Accounted-for" in secs (caused by this non-recursive SQL statement).
(4) "Recursive Response Time Accounted-for" in secs (caused by recursive SQL invoked by this statement).
Go to Top

SQL Genealogy

[-]
Aggregate view of non-recursive SQL statements (depth = 0) and their recursive SQL at all depths.

#	Trace RT Pct¹	Self Response Time²	Recursive Response Time³	Exec Count	Depth	SQL Text	Hash Value	Plan Hash Value
1:	68.1%	73.351	0.102	1	0	update dsrdba.forecast f set value=0 where 0<(select count(*) from dwhdba.acusto	3733909562
2:	0.1%	0.102	0.000	1	1	. select text from view$ where rowid=:1	1398610540
3:	10.0%	10.816	0.000	1	0	CURSOR #2	0	0
4:	21.7%	23.385	0.000	1	0	CURSOR #3	0	0

Individual SQL

[-]
List of individual SQL in order of first appearance in trace.

#	Rank	Trace RT Pct¹	Self Response Time²	Elapsed Time	CPU Time	Non-Idle Wait Time	Idle Wait Time	Exec Count	Depth	SQL Text	Hash Value	Plan Hash Value	First Call
1:	2	21.7%	23.385	11.693	0.000	0.000	11.693	1	0	CURSOR #3	0	0	17:30:34.928
2:	4	0.1%	0.102	0.102	0.000	0.000	0.000	1	1	select text from view$ where rowid=:1	1398610540		17:29:16.077
3:	1	68.1%	73.351	73.351	40.730	33.880	0.000	1	0	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562		17:29:16.240
4:	3	10.0%	10.816	5.408	0.000	0.000	5.408	1	0	CURSOR #2	0	0	17:30:34.928

(1) Percent of "Total Response Time Accounted-for", which is 107.655 secs.
(2) "Self Response Time Accounted-for" in secs (caused by this SQL statement).
Go to Top

0 0

Rank:2(21.7%) Self:23.385s Recursive:0.000s Invoker:0 Definer:0 Depth:0

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

CURSOR #3

Go to Top

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

Call	Response Time Accounted-for	Elapsed Time	Elapsed Time Unaccounted-for	Idle Wait Time
Parse:	0.000	0.000	0.000	0.000
Execute:	23.385	11.693	11.693	11.693
Fetch:	0.000	0.000	0.000	0.000
Total:	23.385	11.693	11.693	11.693

Call	Call Count	Times Waited Non-Idle	Times Waited Idle
Parse:	0	0	0
Execute:	1	1	1
Fetch:	0	0	0
Total:	1	1	1

Event Name	Wait Class	Non-Idle Wait Time	Times Waited Non-Idle	Idle Wait Time	Times Waited Idle	Average Wait Time	Max Wait Time
SQL*Net message from client:	Idle			11.693	1	11.692698	11.692698
SQL*Net message to client:	Network	0.000	1			0.000001	0.000001
Total:		0.000	1	11.693	1

Go to Top

1398610540

Rank:4(0.1%) Self:0.102s Recursive:0.000s Invoker:0 Definer:0 Depth:1

[-]

select text from view$ where rowid=:1

Go to Top

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

Call	Response Time Accounted-for	Elapsed Time	Elapsed Time Unaccounted-for
Parse:	0.000	0.000	0.000
Execute:	0.101	0.101	0.101
Fetch:	0.000	0.000	0.000
Total:	0.102	0.102	0.102

Call	Call Count	BG Consistent Read Mode (query)	Rows Processed or Returned	Library Cache Misses
Parse:	1	0	0	1
Execute:	1	0	0	1
Fetch:	1	3	1	0
Total:	3	3	1	2

Bind Pos	DTY Code	Data Type	Actual Value Length	Value
0:	11	Internal (native format) rowid	16	000129AC.0000.00D0

ID	PID	Actual Rows	Row Source Operation	BG Consistent Read Mode (cr)	OS Buffer Gets (pr)	OS Write Calls (pw)	Time (secs)	Obj
1:	0	1	TABLE ACCESS BY USER ROWID VIEW$	2	0	0	0.000	6659

Go to Top

3733909562

Rank:1(68.1%) Self:73.351s Recursive:0.102s Invoker:0 Definer:0 Depth:0

SQL Self - Time, Totals, Waits, Binds and Row Source Plan
Recursive SQL - Time and Totals
Segment I/O Wait Summary

[-]

update dsrdba.forecast f
set value=0
where
0<(select count(*) from dwhdba.acustomer_ship_to b, dwhdba.afinished_good c,
   dsrdba.man_fore_entry m
   where f.acustomer_ship_to__code=b.code
   and f.finished_good__code=c.code
   and b.region__code like m.region__code
   and b.sales_area__code like m.sales_area__code
   and b.ship_to_group__code like m.ship_to_group__code
   and c.product_group__code like m.product_group__code
   and c.product_division__code like m.product_division__code
   and c.product_pnl_family__code like m.product_pnl_family__code
   and c.commercial_product__code like m.commercial_product__code
   and m.month__code='M201204')

Go to Top

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

Call	Response Time Accounted-for	Elapsed Time	CPU Time	Non-Idle Wait Time	Elapsed Time Unaccounted-for
Parse:	0.071	0.071	0.010	0.000	0.061
Execute:	73.280	73.280	40.720	33.880	-1.319
Fetch:	0.000	0.000	0.000	0.000	0.000
Total:	73.351	73.351	40.730	33.880	-1.258

Call	Call Count	OS Buffer Gets (disk)	BG Consistent Read Mode (query)	BG Current Mode (current)	Rows Processed or Returned	Library Cache Misses	Times Waited Non-Idle
Parse:	1	0	0	0	0	1	0
Execute:	1	12187	2068345	4472	1725	0	10072
Fetch:	0	0	0	0	0	0	0
Total:	2	12187	2068345	4472	1725	1	10072

Event Name	Wait Class	Non-Idle Wait Time	Times Waited Non-Idle	Average Wait Time	Max Wait Time	Blocks	Average Blocks
db file sequential read:	User I/O	32.681	10037	0.003256	0.081095	10037	1
db file scattered read:	User I/O	1.198	35	0.034235	0.071724	2150	61.4
Total:		33.880	10072

ID	PID	Actual Rows	Row Source Operation	BG Consistent Read Mode (cr)	OS Buffer Gets (pr)	Time (secs)	Obj
1:	0	0	UPDATE FORECAST	2068345	12187	73.276	0
2:	1	1725	FILTER	2068345	12172	73.179	0
3:	2	187564	. PARTITION RANGE ALL PARTITION: 1 4	2166	2157	1.733	0
4:	3	187564	.. TABLE ACCESS FULL FORECAST PARTITION: 1 4	2166	2157	1.512	16040032
5:	2	1725	. NESTED LOOPS	2066179	10015	70.705	0
6:	5	187564	.. NESTED LOOPS	1131343	10012	37.230	0
7:	6	187564	... TABLE ACCESS BY INDEX ROWID AFINISHED_GOOD_NEW	568651	8175	20.228	9568951
8:	7	187564	.... INDEX UNIQUE SCAN AFINISHED_GOOD_NEW_PK	375128	729	2.166	9568971
9:	6	187564	... TABLE ACCESS BY INDEX ROWID ACUSTOMER_SHIP_TO	562692	1837	16.578	16075053
10:	9	187564	.... INDEX UNIQUE SCAN ACUST_SHIP_TO_IDX_CODE	375128	223	2.248	16075065
11:	5	1725	.. TABLE ACCESS BY INDEX ROWID MAN_FORE_ENTRY	934836	3	33.118	2887767
12:	11	14545321	... INDEX RANGE SCAN MAN_FORE_ENTRY_IDX_MM	375128	1	2.535	10066347

Go to Top

Recursive SQL - Time and Totals

[-]

Call	Response Time Accounted-for	Elapsed Time	Elapsed Time Unaccounted-for
Parse:	0.102	0.102	0.102
Execute:	0.000	0.000	0.000
Fetch:	0.000	0.000	0.000
Total:	0.102	0.102	0.102

Call	Call Count	BG Consistent Read Mode (query)	Rows Processed or Returned	Library Cache Misses
Parse:	3	3	1	2
Execute:	0	0	0	0
Fetch:	0	0	0	0
Total:	3	3	1	2

Go to Top

Segment I/O Wait Summary

[-]

#	Type	Segment Name¹	Event Name	Wait Time²	Times Waited	Average Wait Time	Max Wait Time	Blocks	Average Blocks
1	TABLE	E2DWH.AFINISHED_GOOD_NEW	db file sequential read	16.930	7446	0.002274	0.067945	7446	1
2	TABLE	SA_WREF.ACUSTOMER_SHIP_TO	db file sequential read	13.449	1614	0.008333	0.081095	1614	1
3	INDEX	SA_WREF.ACUST_SHIP_TO_IDX_CODE	db file sequential read	1.176	223	0.005274	0.046490	223	1
4	INDEX	E2DWH.AFINISHED_GOOD_NEW_PK	db file sequential read	1.043	729	0.001431	0.041659	729	1
5	TABLE PARTITION	DSRDBA.FORECAST.EU	db file scattered read	0.519	14	0.037094	0.071724	880	62.9
6	TABLE PARTITION	DSRDBA.FORECAST.AP	db file scattered read	0.459	14	0.032790	0.048044	880	62.9
7	TABLE PARTITION	DSRDBA.FORECAST.US	db file scattered read	0.193	6	0.032159	0.064538	329	54.8
8	TABLE PARTITION	DSRDBA.FORECAST.US	db file sequential read	0.035	4	0.008669	0.019625	4	1
9	TABLE PARTITION	DSRDBA.FORECAST.JP	db file scattered read	0.027	1	0.026904	0.026904	61	61
10	TABLE	DSRDBA.MAN_FORE_ENTRY	db file sequential read	0.015	2	0.007310	0.007478	2	1

(1) Content based on Trace Analyzer data dictionary (dbid:1606462732, host:server1).
(2) This list is constrained by threshold configuration parameter with current value of 0.01s.

#	Type	Segment Name¹	Start Timestamp	End Timestamp	Response Time²	Wait Time³	Blocks
1	TABLE PARTITION	DSRDBA.FORECAST.AP	AUG-07 17:29:16.253	AUG-07 17:30:02.141	45.887	0.466	896
2	INDEX	E2DWH.AFINISHED_GOOD_NEW_PK	AUG-07 17:29:16.289	AUG-07 17:30:29.425	73.136	1.043	729
3	INDEX	SA_WREF.ACUST_SHIP_TO_IDX_CODE	AUG-07 17:29:16.300	AUG-07 17:30:26.080	69.780	1.176	223
4	TABLE	DSRDBA.MAN_FORE_ENTRY	AUG-07 17:29:16.320	AUG-07 17:29:16.335	0.015	0.015	2
5	TABLE	SA_WREF.ACUSTOMER_SHIP_TO	AUG-07 17:29:16.353	AUG-07 17:30:29.395	73.042	13.449	1614
6	TABLE	E2DWH.AFINISHED_GOOD_NEW	AUG-07 17:29:16.360	AUG-07 17:30:29.494	73.134	16.930	7446
7	TABLE PARTITION	DSRDBA.FORECAST.EU	AUG-07 17:30:03.004	AUG-07 17:30:20.052	17.048	0.526	881
8	TABLE PARTITION	DSRDBA.FORECAST.JP	AUG-07 17:30:20.320	AUG-07 17:30:20.357	0.037	0.036	62
9	TABLE PARTITION	DSRDBA.FORECAST.US	AUG-07 17:30:22.090	AUG-07 17:30:29.472	7.382	0.228	333

(1) Content based on Trace Analyzer data dictionary (dbid:1606462732, host:server1).
(2) According to timestamps of first and last wait in this segment.
(3) This list is constrained by threshold configuration parameter with current value of 0.01s.
Go to Top

0 0

Rank:3(10.0%) Self:10.816s Recursive:0.000s Invoker:0 Definer:0 Depth:0

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

CURSOR #2

Go to Top

SQL Self - Time, Totals, Waits, Binds and Row Source Plan

[-]

Call	Response Time Accounted-for	Elapsed Time	Elapsed Time Unaccounted-for	Idle Wait Time
Parse:	0.000	0.000	0.000	0.000
Execute:	10.816	5.408	5.408	5.408
Fetch:	0.000	0.000	0.000	0.000
Total:	10.816	5.408	5.408	5.408

Call	Call Count	Times Waited Non-Idle	Times Waited Idle
Parse:	0	0	0
Execute:	1	1	1
Fetch:	0	0	0
Total:	1	1	1

Event Name	Wait Class	Non-Idle Wait Time	Times Waited Non-Idle	Idle Wait Time	Times Waited Idle	Average Wait Time	Max Wait Time
SQL*Net message from client:	Idle			5.408	1	5.408174	5.408174
SQL*Net message to client:	Network	0.000	1			0.000003	0.000003
Total:		0.000	1	5.408	1

Go to Top

Overall Segment I/O Wait Summary

[-]
Aggregate view of I/O wait events per segment, ordered by wait time.

#	Type	Segment Name¹	Event Name	Wait Time²	Times Waited	Average Wait Time	Max Wait Time	Blocks	Average Blocks
1:	TABLE	E2DWH.AFINISHED_GOOD_NEW	db file sequential read	16.930	7446	0.002274	0.067945	7446	1
2:	TABLE	SA_WREF.ACUSTOMER_SHIP_TO	db file sequential read	13.449	1614	0.008333	0.081095	1614	1
3:	INDEX	SA_WREF.ACUST_SHIP_TO_IDX_CODE	db file sequential read	1.176	223	0.005274	0.046490	223	1
4:	INDEX	E2DWH.AFINISHED_GOOD_NEW_PK	db file sequential read	1.043	729	0.001431	0.041659	729	1
5:	TABLE PARTITION	DSRDBA.FORECAST.EU	db file scattered read	0.519	14	0.037094	0.071724	880	62.9
6:	TABLE PARTITION	DSRDBA.FORECAST.AP	db file scattered read	0.459	14	0.032790	0.048044	880	62.9
7:	TABLE PARTITION	DSRDBA.FORECAST.US	db file scattered read	0.193	6	0.032159	0.064538	329	54.8
8:	TABLE PARTITION	DSRDBA.FORECAST.US	db file sequential read	0.035	4	0.008669	0.019625	4	1
9:	TABLE PARTITION	DSRDBA.FORECAST.JP	db file scattered read	0.027	1	0.026904	0.026904	61	61
10:	TABLE	DSRDBA.MAN_FORE_ENTRY	db file sequential read	0.015	2	0.007310	0.007478	2	1

(1) Content based on Trace Analyzer data dictionary (dbid:1606462732, host:server1).
(2) This list is constrained by threshold configuration parameter with current value of 0.01s.
Go to Top

Hot I/O Blocks

[-]
List of blocks with largest wait time or times waited.

#	File	Block	Type	Segment Name¹	Wait Time	Times Waited	Max Wait Time
1:	237	10232	TABLE	SA_WREF.ACUSTOMER_SHIP_TO	0.081	1	0.081095
2:	294	9222	TABLE PARTITION	DSRDBA.FORECAST.EU	0.072	1	0.071724
3:	311	187701	TABLE	E2DWH.AFINISHED_GOOD_NEW	0.068	1	0.067945
4:	294	21318	TABLE PARTITION	DSRDBA.FORECAST.EU	0.065	1	0.064857
5:	293	11270	TABLE PARTITION	DSRDBA.FORECAST.US	0.065	1	0.064538

(1) Content based on Trace Analyzer data dictionary (dbid:1606462732, host:server1).
Go to Top

Gaps in Trace

[-]
List of gaps found in trace(s) with corresponding approximate duration in seconds.

#	Gap Timestamp	Gap Time¹
1:	AUG-07 17:29:16.348	0.001
2:	AUG-07 17:29:26.343	0.001
3:	AUG-07 17:29:36.361	0.007
4:	AUG-07 17:29:46.353	0.002
5:	AUG-07 17:29:56.367	0.002
6:	AUG-07 17:30:06.380	0.024
7:	AUG-07 17:30:16.399	0.027
8:	AUG-07 17:30:26.400	0.064
9:	AUG-07 17:30:37.102	0.000

(1) Blank if duration cannot be computed.

List of recursive calls related to gaps found in trace.

#	Gap Timestamp	Call Type	CPU	Elapsed	Call Timestamp	SQL Text	Hash Value
1:	AUG-07 17:29:16.348	Execute	0.000	11.693	AUG-07 17:30:34.928	CURSOR #3	0
2:	AUG-07 17:29:26.343	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
3:	AUG-07 17:29:36.361	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
4:	AUG-07 17:29:46.353	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
5:	AUG-07 17:29:56.367	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
6:	AUG-07 17:30:06.380	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
7:	AUG-07 17:30:16.399	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
8:	AUG-07 17:30:26.400	Execute	40.720	73.280	AUG-07 17:30:29.520	update dsrdba.forecast f set value=0 where 0<(select count(*	3733909562
9:	AUG-07 17:30:37.102	Execute	0.000	5.408	AUG-07 17:30:34.928	CURSOR #2	0

Go to Top

ORA errors in Trace

[-]
There are no Oracle errors in trace.

Transactions Summary

[-]
List of sessions recognized in trace(s), including transaction counts per type.

#	SID	Serial#	Session Timestamp	Type Read-only Committed	Type Read-only Rolled-back	Type Update Committed	Type Update Rolled-back
1:	199	52650	AUG-07 17:29:04.374	0	0	1	0

Non-default Initialization Params

[-]
List of non-default parameters.

#	Parameter	Value¹
1:	audit_file_dest	/ora_dwhqa/dump/dwhqa
2:	audit_trail	FALSE
3:	background_dump_dest	/ora_dwhqa/dump/dwhqa
4:	compatible	10.2.0.4.0
5:	control_files	/ora_dwhqa/ctrl/dwhqa/ctrl1dwh.ctl
6:	control_files	/ora_dwhqa/ctrl/dwhqa/ctrl2dwh.ctl
7:	core_dump_dest	/ora_dwhqa/dump/dwhqa
8:	db_16k_cache_size	536870912
9:	db_block_size	4096
10:	db_cache_advice	ready
11:	db_domain	world
12:	db_files	1021
13:	db_keep_cache_size	419430400
14:	db_name	dwhqa
15:	db_recycle_cache_size	419430400
16:	disk_asynch_io	FALSE
17:	instance_name	dwhqa
18:	job_queue_processes	10
19:	log_archive_dest	/ora_dwhqa/arch/dwhqa/
20:	log_buffer	14485504
21:	log_checkpoint_interval	0
22:	log_checkpoints_to_alert	TRUE
23:	max_dump_file_size	100000
24:	open_cursors	400
25:	os_authent_prefix
26:	pga_aggregate_target	1073741824
27:	processes	200
28:	remote_os_authent	TRUE
29:	sga_max_size	4294967296
30:	sga_target	4294967296
31:	statistics_level	typical
32:	timed_statistics	FALSE
33:	undo_management	AUTO
34:	undo_retention	1800
35:	undo_tablespace	undotbs
36:	user_dump_dest	/ora_dwhqa/dump/dwhqa
37:	utl_file_dir	/log_recv/dwh/data/ipm
38:	utl_file_dir	/log_recv/pdi/log

(1) Content based on Trace Analyzer data dictionary (dbid:1606462732, host:server1).

Trace Header

[-]

dwhqa_ora_20751.trc (1133271 bytes)

/ora_dwhqa/dump/dwhqa/dwhqa_ora_20751.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /ora_dwhqa/software
System name: HP-UX
Node name: server1
Release: B.11.23
Version: U
Machine: ia64
Instance name: dwhqa
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 20751, image: oracle@server1 (TNS V1-V3)

*** ACTION NAME:() 2012-08-07 17:29:04.374
*** MODULE NAME:(sqlplus@server1 (TNS V1-V3)) 2012-08-07 17:29:04.374
*** SERVICE NAME:(SYS$USERS) 2012-08-07 17:29:04.374

Go to Top

Tool Data Dictionary

[-]

Refresh Date:	20120807
Refresh Days:	5
Database:	DWHQA(1606462732)
Instance:	dwhqa(1)
Host:	server1
Platform:	HPUX
RDBMS Version:	10.2.0.4.0
DB Files:	1021

Go to Top

Tool Execution Environment

[-]

Database:	DWHQA(1606462732)
Instance:	dwhqa(1)
Host:	server1
Platform:	HPUX
RDBMS Version:	10.2.0.4.0
RDBMS Release:	10.2(10.2.X)
DB Files:	1021
Product Version:	Oracle Database 10g Enterprise Edition (64bi)
User:	TRCANLZR(32877)
Stage Directory:	/ora_dwhqa/dump/dwhqa

Go to Top

Tool Configuration Parameters

[-]

Description¹	Name	Value²	Default	Instructions
Top SQL Threshold:	top_sql_th	10	10	0-100
Top Execution Threshold:	top_exec_th	10	10	0-100
Hot Block Threshold:	hot_block_th	5	5	0-100
Aggregate:	aggregate	Y	Y	Y, N
Perform COUNT(*):	perform_count_star	Y	Y	Y, N
COUNT(*) Threshold:	count_star_th	1000000	1000000	0-999999999
Errors Threshold:	errors_th	100	100	0-1000
Gaps Threshold:	gaps_th	100	100	0-1000
Include Internal SQL:	include_internal_sql	Y	Y	Y, N
Include non-Top SQL:	include_non_top_sql	Y	Y	Y, N
Include Initialization Params:	include_init_ora	Y	Y	Y, N
Include Waits:	include_waits	Y	Y	Y, N
Include Bind Variables:	include_binds	Y	Y	Y, N
Include Fetch Calls:	include_fetches	Y	Y	Y, N
Include Explain Plans:	include_expl_plans	Y	Y	Y, N
Include Segments:	include_segments	Y	Y	Y, N
Detail non-top SQL:	detail_non_top_sql	N	N	N, Y
Time Granularity:	time_granularity	1000000	1000000	1000000-100
Wait Time Threshold:	wait_time_th	0.01	0.01	0.0001-1.0
Response Time Threshold:	response_time_th	0.1	0.1	0.001-10.0
Trace File Max Size in Bytes:	trace_file_max_size_bytes	999999999	999999999	99999-999999999999
Copy File Max Size in Bytes:	copy_file_max_size_bytes	99999999	99999999	99999-999999999999
Generate HTML Report:	gen_html_report	Y	Y	Y, N
Generate Text Report:	gen_text_report	Y	Y	Y, N
Split traces generated by SQLT:	split_10046_10053_trc	Y	Y	Y, N
Gather CBO Stats Staging Objs:	gather_cbo_stats	10	10	0-100
Capture Extents in Repository:	capture_extents	S	S	(S)erial, (P)arallel, (N)o
Refresh Dictionary Repository:	refresh_dict_repository	Y	Y	Y, N
Input Directory 1:	input1_dir	TRCA$INPUT1	UDUMP	Set by trca/utl/tacdiri1.sql
Input Directory 2:	input2_dir	TRCA$INPUT2	BDUMP	Set by trca/utl/tacdiri2.sql
Stage Directory:	stage_dir	TRCA$STAGE	UDUMP	Set by trca/utl/tacdirs.sql

(1) For detailed parameter description, refer to trca/trca_instructions.txt
(2) To set a parameter: SQL> EXEC trcanlzr.trca$g.set_param('Name', 'Value');
Go to Top