TriVaDis eXtended Tracefile Analysis Tool 4.0 Beta 9 (9 April 2009 08:13)
Copyright 2000-2009 by Christian Antognini	Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg / Zürich

Overall Information

Database Version

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

Analyzed Trace File

/ora_dwhqa/dump/dwhqa/dwhqa_ora_20751.trc

Interval

Beginning	7 Aug 2012 17:29:04
End	7 Aug 2012 17:30:34
Duration	90.555 [s]

Transactions

Committed	1
Rollbacked	0

Resource Usage Profile overall current

Component	Total Duration [s]	%	Number of Events	Duration per Event [s]

CPU	40.730	44.978	n/a	n/a
db file sequential read	32.681	36.090	10,037	0.003
SQL*Net message from client	17.101	18.885	2	8.550
db file scattered read	1.198	1.323	35	0.034
SQL*Net message to client	0.000	0.000	2	0.000
unaccounted-for	-1.156	-1.276	n/a	n/a

Total	90.555	100.000

The input file contains 4 distinct statements, 1 of which is recursive.

In the following table, only non-recursive statements are reported.

Statement ID	Type	Total Duration [s]	%	Number of Executions	Duration per Execution [s]

1	UPDATE	80.018	88.364	1	80.018
2	unknown	11.693	12.912	0	n/a
3	COMMIT	0.000	0.000	1	0.000

Total		91.710	101.276

CPU overall

2 statements contributed to this event.

Statement ID	Type	Total Duration [s]	%

1	UPDATE	40.730	100.000
4	SELECT (SYS recursive)	0.000	0.000

Total		40.730	100.000

db file sequential read overall

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]	Blocks [b]	Blocks per Event [b]

8 ≥ duration < 16	0.000	0.001	19	0.189	16	19	1.000
16 ≥ duration < 32	0.017	0.051	810	8.070	20	810	1.000
32 ≥ duration < 64	0.001	0.002	18	0.179	41	18	1.000
64 ≥ duration < 128	0.004	0.011	37	0.369	95	37	1.000
128 ≥ duration < 256	0.005	0.015	27	0.269	178	27	1.000
256 ≥ duration < 512	1.839	5.628	5,336	53.163	345	5,336	1.000
512 ≥ duration < 1024	0.088	0.268	137	1.365	640	137	1.000
1024 ≥ duration < 2048	0.243	0.742	139	1.385	1,745	139	1.000
2048 ≥ duration < 4096	1.123	3.436	397	3.955	2,828	397	1.000
4096 ≥ duration < 8192	10.457	31.996	1,585	15.792	6,597	1,585	1.000
8192 ≥ duration < 16384	13.467	41.208	1,327	13.221	10,149	1,327	1.000
16384 ≥ duration < 32768	3.818	11.682	165	1.644	23,138	165	1.000
32768 ≥ duration < 65536	1.472	4.505	38	0.379	38,748	38	1.000
65536 ≥ duration < 131072	0.149	0.456	2	0.020	74,520	2	1.000

Total	32.681	100.000	10,037	100.000	3,256	10,037	1.000

File	Total Duration [s]	%	Number of Events	%	Blocks [b]	%	Duration per Event [μs]

239	4.794	14.670	560	5.579	560	5.579	8,561
238	4.515	13.814	587	5.848	587	5.848	7,691
237	4.140	12.668	467	4.653	467	4.653	8,866
268	3.816	11.678	1,770	17.635	1,770	17.635	2,156
266	3.813	11.667	1,612	16.061	1,612	16.061	2,365
267	3.645	11.153	1,838	18.312	1,838	18.312	1,983
269	3.397	10.395	1,385	13.799	1,385	13.799	2,453
270	1.553	4.751	421	4.194	421	4.194	3,688
311	0.706	2.159	420	4.185	420	4.185	1,680
241	0.559	1.709	104	1.036	104	1.036	5,371
4	0.318	0.972	71	0.707	71	0.707	4,475
240	0.300	0.917	48	0.478	48	0.478	6,244
310	0.277	0.848	61	0.608	61	0.608	4,546
273	0.238	0.728	78	0.777	78	0.777	3,052
272	0.191	0.584	408	4.065	408	4.065	468
271	0.163	0.500	74	0.737	74	0.737	2,208
370	0.089	0.273	47	0.468	47	0.468	1,900
274	0.085	0.259	61	0.608	61	0.608	1,386
293	0.035	0.106	4	0.040	4	0.040	8,669
349	0.015	0.045	2	0.020	2	0.020	7,310
291	0.010	0.030	1	0.010	1	0.010	9,737
292	0.009	0.029	1	0.010	1	0.010	9,358
294	0.007	0.022	1	0.010	1	0.010	7,163
295	0.007	0.022	16	0.159	16	0.159	441

Total	32.681	100.000	10,037	100.000	10,037	100.000	3,256

1 statement contributed to this event.

Statement ID		Type		Total Duration [s]		%

1		UPDATE		32.681		100.000

SQL*Net message from client overall

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]

4194304 ≥ duration < 8388608	5.408	31.625	1	50.000	5,408,174
8388608 ≥ duration < 16777216	11.693	68.375	1	50.000	11,692,698

Total	17.101	100.000	2	100.000	8,550,436

2 statements contributed to this event.

Statement ID	Type	Total Duration [s]	%

2	unknown	11.693	68.375
1	UPDATE	5.408	31.625

Total		17.101	100.000

db file scattered read overall

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]	Blocks [b]	Blocks per Event [b]

16384 ≥ duration < 32768	0.476	39.765	20	57.143	23,824	1,207	60.350
32768 ≥ duration < 65536	0.650	54.250	14	40.000	46,431	880	62.857
65536 ≥ duration < 131072	0.072	5.986	1	2.857	71,724	63	63.000

Total	1.198	100.000	35	100.000	34,235	2,150	61.429

File	Total Duration [s]	%	Number of Events	%	Blocks [b]	%	Duration per Event [μs]

294	0.519	43.340	14	40.000	880	40.930	37,094
295	0.459	38.311	14	40.000	880	40.930	32,790
293	0.193	16.103	6	17.143	329	15.302	32,159
292	0.027	2.245	1	2.857	61	2.837	26,904

Total	1.198	100.000	35	100.000	2,150	100.000	34,235

1 statement contributed to this event.

Statement ID		Type		Total Duration [s]		%

1		UPDATE		1.198		100.000

SQL*Net message to client overall

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]

0 ≥ duration < 2	0.000	25.000	1	50.000	1
2 ≥ duration < 4	0.000	75.000	1	50.000	3

Total	0.000	100.000	2	100.000	2

2 statements contributed to this event.

Statement ID	Type	Total Duration [s]	%

1	UPDATE	0.000	75.000
2	unknown	0.000	25.000

Total		0.000	100.000

Statement 1 overall

Session ID		199.52650
Service Name		SYS$USERS
Module Name		sqlplus@server1 (TNS V1-V3)
Parsing User		0
Hash Value		3733909562
Text		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')

Execution Plans

Optimizer Mode		CHOOSE

Rows		Operation

0		UPDATE FORECAST (cr=2068345 pr=12187 pw=0 time=73275907 us)
1,725		FILTER (cr=2068345 pr=12172 pw=0 time=73179486 us)
187,564		PARTITION RANGE ALL PARTITION: 1 4 (cr=2166 pr=2157 pw=0 time=1733285 us)
187,564		TABLE ACCESS FULL FORECAST PARTITION: 1 4 (cr=2166 pr=2157 pw=0 time=1511608 us)
1,725		NESTED LOOPS (cr=2066179 pr=10015 pw=0 time=70705165 us)
187,564		NESTED LOOPS (cr=1131343 pr=10012 pw=0 time=37230283 us)
187,564		TABLE ACCESS BY INDEX ROWID AFINISHED_GOOD_NEW (cr=568651 pr=8175 pw=0 time=20227871 us)
187,564		INDEX UNIQUE SCAN AFINISHED_GOOD_NEW_PK (cr=375128 pr=729 pw=0 time=2166172 us) (object id 9568971)
187,564		TABLE ACCESS BY INDEX ROWID ACUSTOMER_SHIP_TO (cr=562692 pr=1837 pw=0 time=16578162 us)
187,564		INDEX UNIQUE SCAN ACUST_SHIP_TO_IDX_CODE (cr=375128 pr=223 pw=0 time=2247964 us) (object id 16075065)
1,725		TABLE ACCESS BY INDEX ROWID MAN_FORE_ENTRY (cr=934836 pr=3 pw=0 time=33118049 us)
14,545,321		INDEX RANGE SCAN MAN_FORE_ENTRY_IDX_MM (cr=375128 pr=1 pw=0 time=2535274 us) (object id 10066347)

Database Call Statistics with Recursive Statements

Call	Count	Misses	CPU [s]	Elapsed [s]	PIO [b]	LIO [b]	Consistent [b]	Current [b]	Rows

Parse	1	1	0.010	0.173	0	3	3	0	0
Execute	1	0	40.720	73.280	12,187	2,072,817	2,068,345	4,472	1,725
Fetch	0	0	0.000	0.000	0	0	0	0	0

Total	2	1	40.730	73.453	12,187	2,072,820	2,068,348	4,472	1,725

Average (per row)	0	0	0.024	0.043	7	1,201	1,199	2	1

Database Call Statistics without Recursive Statements

Call	Count	Misses	CPU [s]	Elapsed [s]	PIO [b]	LIO [b]	Consistent [b]	Current [b]	Rows

Parse	1	1	0.010	0.071	0	0	0	0	0
Execute	1	0	40.720	73.280	12,187	2,072,817	2,068,345	4,472	1,725
Fetch	0	0	0.000	0.000	0	0	0	0	0

Total	2	1	40.730	73.351	12,187	2,072,817	2,068,345	4,472	1,725

Average (per row)	0	0	0.024	0.043	7	1,201	1,199	2	1

Resource Usage Profile overall current

Component	Total Duration [s]	%	Number of Events	Duration per Event [s]

CPU	40.730	50.901	n/a	n/a
db file sequential read	32.681	40.843	10,037	0.003
SQL*Net message from client	5.408	6.759	1	5.408
db file scattered read	1.198	1.497	35	0.034
SQL*Net message to client	0.000	0.000	1	0.000
recursive statements	0.000	0.000	n/a	n/a

Total	80.018	100.000

1 recursive statement was executed.

Statement ID		Type		Total Duration [s]		%

4		SELECT (SYS recursive)		0.000		0.000

db file sequential read overall current

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]	Blocks [b]	Blocks per Event [b]

8 ≥ duration < 16	0.000	0.001	19	0.189	16	19	1.000
16 ≥ duration < 32	0.017	0.051	810	8.070	20	810	1.000
32 ≥ duration < 64	0.001	0.002	18	0.179	41	18	1.000
64 ≥ duration < 128	0.004	0.011	37	0.369	95	37	1.000
128 ≥ duration < 256	0.005	0.015	27	0.269	178	27	1.000
256 ≥ duration < 512	1.839	5.628	5,336	53.163	345	5,336	1.000
512 ≥ duration < 1024	0.088	0.268	137	1.365	640	137	1.000
1024 ≥ duration < 2048	0.243	0.742	139	1.385	1,745	139	1.000
2048 ≥ duration < 4096	1.123	3.436	397	3.955	2,828	397	1.000
4096 ≥ duration < 8192	10.457	31.996	1,585	15.792	6,597	1,585	1.000
8192 ≥ duration < 16384	13.467	41.208	1,327	13.221	10,149	1,327	1.000
16384 ≥ duration < 32768	3.818	11.682	165	1.644	23,138	165	1.000
32768 ≥ duration < 65536	1.472	4.505	38	0.379	38,748	38	1.000
65536 ≥ duration < 131072	0.149	0.456	2	0.020	74,520	2	1.000

Total	32.681	100.000	10,037	100.000	3,256	10,037	1.000

File	Total Duration [s]	%	Number of Events	%	Blocks [b]	%	Duration per Event [μs]

239	4.794	14.670	560	5.579	560	5.579	8,561
238	4.515	13.814	587	5.848	587	5.848	7,691
237	4.140	12.668	467	4.653	467	4.653	8,866
268	3.816	11.678	1,770	17.635	1,770	17.635	2,156
266	3.813	11.667	1,612	16.061	1,612	16.061	2,365
267	3.645	11.153	1,838	18.312	1,838	18.312	1,983
269	3.397	10.395	1,385	13.799	1,385	13.799	2,453
270	1.553	4.751	421	4.194	421	4.194	3,688
311	0.706	2.159	420	4.185	420	4.185	1,680
241	0.559	1.709	104	1.036	104	1.036	5,371
4	0.318	0.972	71	0.707	71	0.707	4,475
240	0.300	0.917	48	0.478	48	0.478	6,244
310	0.277	0.848	61	0.608	61	0.608	4,546
273	0.238	0.728	78	0.777	78	0.777	3,052
272	0.191	0.584	408	4.065	408	4.065	468
271	0.163	0.500	74	0.737	74	0.737	2,208
370	0.089	0.273	47	0.468	47	0.468	1,900
274	0.085	0.259	61	0.608	61	0.608	1,386
293	0.035	0.106	4	0.040	4	0.040	8,669
349	0.015	0.045	2	0.020	2	0.020	7,310
291	0.010	0.030	1	0.010	1	0.010	9,737
292	0.009	0.029	1	0.010	1	0.010	9,358
294	0.007	0.022	1	0.010	1	0.010	7,163
295	0.007	0.022	16	0.159	16	0.159	441

Total	32.681	100.000	10,037	100.000	10,037	100.000	3,256

SQL*Net message from client overall current

Range [μs]		Total Duration [s]		%		Number of Events		%		Duration per Event [μs]

4194304 ≥ duration < 8388608		5.408		100.000		1		100.000		5,408,174

db file scattered read overall current

Range [μs]	Total Duration [s]	%	Number of Events	%	Duration per Event [μs]	Blocks [b]	Blocks per Event [b]

16384 ≥ duration < 32768	0.476	39.765	20	57.143	23,824	1,207	60.350
32768 ≥ duration < 65536	0.650	54.250	14	40.000	46,431	880	62.857
65536 ≥ duration < 131072	0.072	5.986	1	2.857	71,724	63	63.000

Total	1.198	100.000	35	100.000	34,235	2,150	61.429

File	Total Duration [s]	%	Number of Events	%	Blocks [b]	%	Duration per Event [μs]

294	0.519	43.340	14	40.000	880	40.930	37,094
295	0.459	38.311	14	40.000	880	40.930	32,790
293	0.193	16.103	6	17.143	329	15.302	32,159
292	0.027	2.245	1	2.857	61	2.837	26,904

Total	1.198	100.000	35	100.000	2,150	100.000	34,235

SQL*Net message to client overall current

Range [μs]		Total Duration [s]		%		Number of Events		%		Duration per Event [μs]

2 ≥ duration < 4		0.000		100.000		1		100.000		3

Statement 2 overall

Parsing User		unknown
Text		unknown

Resource Usage Profile overall current

Component	Total Duration [s]	%	Number of Events	Duration per Event [s]

SQL*Net message from client	11.693	100.000	1	11.693
SQL*Net message to client	0.000	0.000	1	0.000

Total	11.693	100.000

SQL*Net message from client overall current

Range [μs]		Total Duration [s]		%		Number of Events		%		Duration per Event [μs]

8388608 ≥ duration < 16777216		11.693		100.000		1		100.000		11,692,698

SQL*Net message to client overall current

Range [μs]		Total Duration [s]		%		Number of Events		%		Duration per Event [μs]

0 ≥ duration < 2		0.000		100.000		1		100.000		1

Statement 3 overall

Parsing User		unknown
Text		COMMIT (synthetic)

Database Call Statistics

Call	Count	Misses	CPU [s]	Elapsed [s]	PIO [b]	LIO [b]	Consistent [b]	Current [b]	Rows

Parse	0	0	0.000	0.000	0	0	0	0	0
Execute	1	0	0.000	0.000	0	0	0	0	0
Fetch	0	0	0.000	0.000	0	0	0	0	0

Total	1	0	0.000	0.000	0	0	0	0	0

Resource Usage Profile overall current

Component		Total Duration [s]		%		Number of Events		Duration per Event [s]

CPU		0.000		n/a		n/a		n/a

Units of Measure overall

[s] = seconds
[μs] = microseconds
[b] = database blocks

TriVaDis eXtended Tracefile Analysis Tool 4.0 Beta 9 (9 April 2009 08:13)

Overall Information

Database Version

Analyzed Trace File

Interval

Transactions

Resource Usage Profile overall current

CPU overall

db file sequential read overall

SQL*Net message from client overall

db file scattered read overall

SQL*Net message to client overall

Statement 1 overall

Execution Plans

Database Call Statistics with Recursive Statements

Database Call Statistics without Recursive Statements

Resource Usage Profile overall current

db file sequential read overall current

SQL*Net message from client overall current

db file scattered read overall current

SQL*Net message to client overall current

Statement 2 overall

Resource Usage Profile overall current

SQL*Net message from client overall current

SQL*Net message to client overall current

Statement 3 overall

Database Call Statistics

Resource Usage Profile overall current

Units of Measure overall