dwhqa_ora_20751.trc / Oracle Session Profiler

Session Summary

Instance Name: dwhqa
Node Name: server1
Oracle Release: 10.2.0.4.0
Session ID: 199.52650
Total SQL Statements: 2 (0 user statements, 2 internal statements)
Total Transactions: 1 (0 rollback(s), 0 read-only)

Report Shortcuts

Session Flat Profile
Top 5 Statements per Event
Session Call Graph
Events Histograms
Statements

Session Flat Profile Pie Chart

Session Flat Profile

Event Name	% Time	Seconds	Calls	- Time per Call -
Event Name	% Time	Seconds	Calls	Avg	Min	Max
EXEC calls [CPU]	45.0%	40.7200s	2	20.3600s	0.0000s	40.7200s
db file sequential read	36.1%	32.6813s	10,037	0.0033s	0.0000s	0.0811s
SQL*Net message from client [idle]	18.9%	17.1009s	2	8.5504s	5.4082s	11.6927s
db file scattered read	1.3%	1.1982s	35	0.0342s	0.0173s	0.0717s
PARSE calls [CPU]	0.0%	0.0100s	2	0.0050s	0.0000s	0.0100s
SQL*Net message to client	0.0%	0.0000s	2	0.0000s	0.0000s	0.0000s
FETCH calls [CPU]	0.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
unaccounted-for time	-1.3%	-1.1573s
Total	100.0%	90.5531s

Top 5 Statements per Event

EXEC calls [CPU]

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
3733909562	100.0%	40.7200s	1	40.7200s	40.7200s	40.7200s
1398610540	0.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	40.7200s	2	20.3600s	0.0000s	40.7200s

db file sequential read

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
3733909562	100.0%	32.6813s	10,037	0.0033s	0.0000s	0.0811s
Total	100.0%	32.6813s	10,037	0.0033s	0.0000s	0.0811s

SQL*Net message from client [idle]

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
Cursor #3	68.4%	11.6927s	1	11.6927s	11.6927s	11.6927s
3733909562	31.6%	5.4082s	1	5.4082s	5.4082s	5.4082s
Total	100.0%	17.1009s	2	8.5504s	5.4082s	11.6927s

db file scattered read

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
3733909562	100.0%	1.1982s	35	0.0342s	0.0173s	0.0717s
Total	100.0%	1.1982s	35	0.0342s	0.0173s	0.0717s

PARSE calls [CPU]

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
3733909562	100.0%	0.0100s	1	0.0100s	0.0100s	0.0100s
1398610540	0.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	0.0100s	2	0.0050s	0.0000s	0.0100s

SQL*Net message to client

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
3733909562	75.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Cursor #3	25.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	0.0000s	2	0.0000s	0.0000s	0.0000s

FETCH calls [CPU]

SQL Hash Value	% Time	Seconds	Calls	- Time per Call -
SQL Hash Value	% Time	Seconds	Calls	Avg	Min	Max
1398610540	n/a %	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	0.0000s	1	0.0000s	0.0000s	0.0000s

Session Call Graph

SQL Hash Value	Branch %	Total %	Self Seconds	Cumulative Seconds	Calls	Similar	Command Type
3733909562 1398610540	99.9% 0.1%	100.0% 0.1%	73.3513s 0.1015s	73.4528s 0.1015s	2 3	0 0	UPDATE SELECT

Events Histograms

EXEC calls [CPU]

value	- Distribution -	count
0.000000s		1
0.000001s		0
0.000002s		0
0.000004s		0
0.000008s		0
0.000016s		0
0.000032s		0
0.000064s		0
0.000128s		0
0.000256s		0
0.000512s		0
0.001024s		0
0.002048s		0
0.004096s		0
0.008192s		0
0.016384s		0
0.032768s		0
0.065536s		0
0.131072s		0
0.262144s		0
0.524288s		0
1.048576s		0
2.097152s		0
4.194304s		0
8.388608s		0
16.777216s		0
33.554432s		1
67.108864s		0

db file sequential read

value	- Distribution -	count
0.000004s		0
0.000008s		5
0.000016s		820
0.000032s		21
0.000064s		38
0.000128s		27
0.000256s		5,335
0.000512s		138
0.001024s		139
0.002048s		397
0.004096s		1,585
0.008192s		1,327
0.016384s		165
0.032768s		38
0.065536s		2
0.131072s		0

SQL*Net message from client [idle]

value	- Distribution -	count
2.097152s		0
4.194304s		1
8.388608s		1
16.777216s		0

db file scattered read

value	- Distribution -	count
0.008192s		0
0.016384s		20
0.032768s		14
0.065536s		1
0.131072s		0

PARSE calls [CPU]

value	- Distribution -	count
0.000000s		1
0.000001s		0
0.000002s		0
0.000004s		0
0.000008s		0
0.000016s		0
0.000032s		0
0.000064s		0
0.000128s		0
0.000256s		0
0.000512s		0
0.001024s		0
0.002048s		0
0.004096s		0
0.008192s		1
0.016384s		0

SQL*Net message to client

value	- Distribution -	count
0.000000s		0
0.000001s		1
0.000002s		1
0.000004s		0

FETCH calls [CPU]

value	- Distribution -	count
0.000000s		1

Session Objects Statistics

Object/Event	% Time	Seconds	Calls	- Time per Call -
Object/Event	% Time	Seconds	Calls	Avg	Min	Max
TABLE AFINISHED_GOOD_NEW [9568951]
db file sequential read	50.0%	16.9300s	7,446	0.0023s	0.0000s	0.0679s
TABLE ACUSTOMER_SHIP_TO [16075053]
db file sequential read	39.7%	13.4493s	1,614	0.0083s	0.0003s	0.0811s
INDEX ACUST_SHIP_TO_IDX_CODE [16075065]
db file sequential read	3.5%	1.1760s	223	0.0053s	0.0000s	0.0465s
INDEX AFINISHED_GOOD_NEW_PK [9568971]
db file sequential read	3.1%	1.0434s	729	0.0014s	0.0000s	0.0417s
Obj#(16040034)
db file scattered read	1.5%	0.5193s	14	0.0371s	0.0173s	0.0717s
db file sequential read	0.0%	0.0072s	1	0.0072s	0.0072s	0.0072s
Obj#(16040033)
db file scattered read	1.4%	0.4591s	14	0.0328s	0.0187s	0.0480s
db file sequential read	0.0%	0.0070s	16	0.0004s	0.0000s	0.0067s
Obj#(16040036)
db file scattered read	0.6%	0.1930s	6	0.0322s	0.0182s	0.0645s
db file sequential read	0.1%	0.0347s	4	0.0087s	0.0003s	0.0196s
Obj#(16040035)
db file scattered read	0.1%	0.0269s	1	0.0269s	0.0269s	0.0269s
db file sequential read	0.0%	0.0094s	1	0.0094s	0.0094s	0.0094s
TABLE MAN_FORE_ENTRY [2887767]
db file sequential read	0.0%	0.0146s	2	0.0073s	0.0071s	0.0075s
INDEX MAN_FORE_ENTRY_IDX_MM [10066347]
db file sequential read	0.0%	0.0097s	1	0.0097s	0.0097s	0.0097s

Session Read Statistics

Blocks Read per Call	% Time	Seconds	Calls	- Time per Call -
Blocks Read per Call	% Time	Seconds	Calls	Avg	Min	Max
1	96.5%	32.6813s	10,037	0.0033s	0.0000s	0.0811s
63	3.1%	1.0441s	30	0.0348s	0.0173s	0.0717s
61	0.4%	0.1358s	4	0.0340s	0.0242s	0.0568s
16	0.1%	0.0182s	1	0.0182s	0.0182s	0.0182s
Total	100.0%	33.8796s	10,072	0.0034s	0.0000s	0.0811s

Session Read Datafiles Statistics

Datafile #	% Time	Seconds	Calls	- Time per Call -
Datafile #	% Time	Seconds	Calls	Avg	Min	Max
239	14.2%	4.7944s	560	0.0086s	0.0003s	0.0483s
238	13.3%	4.5147s	587	0.0077s	0.0003s	0.0446s
237	12.2%	4.1402s	467	0.0089s	0.0003s	0.0811s
268	11.3%	3.8164s	1,770	0.0022s	0.0000s	0.0438s
266	11.3%	3.8129s	1,612	0.0024s	0.0000s	0.0517s
267	10.8%	3.6450s	1,838	0.0020s	0.0000s	0.0496s
269	10.0%	3.3974s	1,385	0.0025s	0.0000s	0.0587s
270	4.6%	1.5527s	421	0.0037s	0.0000s	0.0465s
311	2.1%	0.7055s	420	0.0017s	0.0000s	0.0679s
241	1.6%	0.5586s	104	0.0054s	0.0003s	0.0312s
294	1.6%	0.5265s	15	0.0351s	0.0072s	0.0717s
295	1.4%	0.4661s	30	0.0155s	0.0000s	0.0480s
4	0.9%	0.3177s	71	0.0045s	0.0000s	0.0465s
240	0.9%	0.2997s	48	0.0062s	0.0003s	0.0347s
310	0.8%	0.2773s	61	0.0045s	0.0003s	0.0326s
273	0.7%	0.2380s	78	0.0031s	0.0000s	0.0298s
293	0.7%	0.2276s	10	0.0228s	0.0003s	0.0645s
272	0.6%	0.1909s	408	0.0005s	0.0000s	0.0417s
271	0.5%	0.1634s	74	0.0022s	0.0000s	0.0227s
370	0.3%	0.0893s	47	0.0019s	0.0003s	0.0208s
274	0.2%	0.0845s	61	0.0014s	0.0000s	0.0113s
292	0.1%	0.0363s	2	0.0181s	0.0094s	0.0269s
349	0.0%	0.0146s	2	0.0073s	0.0071s	0.0075s
291	0.0%	0.0097s	1	0.0097s	0.0097s	0.0097s
Total	100.0%	33.8796s	10,072	0.0034s	0.0000s	0.0811s

SQL Statements

SQL Hash Value: 3733909562 uid: 0 depth: 0 optimizer mode: CHOOSE

Statement 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')

Statement Cumulative Statistics

Call	Cache Misses	Count	- Seconds -		Physical Reads	- Logical Reads -		Rows
Call	Cache Misses	Count	CPU	Elapsed	Physical Reads	Consistent	Current	Rows
Parse	1	1	0.0100s	0.1727s	0	3	0	0
Exec	0	1	40.7200s	73.2801s	12,187	2,068,345	4,472	1,725
Fetch		0	0.0000s	0.0000s	0	0	0	0
Total	1	2	40.7300s	73.4528s	12,187	2,068,348	4,472	1,725

Per Exe	1.0	2.0	40.7300s	73.4528s	12,187.0	2,068,348.0	4,472.0	1,725.0
Per Row	0.0	0.0	0.0236s	0.0426s	7.1	1,199.0	2.6	1.0

Statement Self Statistics

Call	Cache Misses	Count	- Seconds -		Physical Reads	- Logical Reads -		Rows
Call	Cache Misses	Count	CPU	Elapsed	Physical Reads	Consistent	Current	Rows
Parse	1	1	0.0100s	0.0712s	0	0	0	0
Exec	0	1	40.7200s	73.2801s	12,187	2,068,345	4,472	1,725
Fetch		0	0.0000s	0.0000s	0	0	0	0
Total	1	2	40.7300s	73.3513s	12,187	2,068,345	4,472	1,725

Per Exe	1.0	2.0	40.7300s	73.3513s	12,187.0	2,068,345.0	4,472.0	1,725.0
Per Row	0.0	0.0	0.0236s	0.0425s	7.1	1,199.0	2.6	1.0

Statement Flat Profile

Event Name	% Time	Seconds	Calls	- Time per Call -
Event Name	% Time	Seconds	Calls	Avg	Min	Max
EXEC calls [CPU]	50.9%	40.7200s	1	40.7200s	40.7200s	40.7200s
db file sequential read	40.8%	32.6813s	10,037	0.0033s	0.0000s	0.0811s
SQL*Net message from client [idle]	6.8%	5.4082s	1	5.4082s	5.4082s	5.4082s
db file scattered read	1.5%	1.1982s	35	0.0342s	0.0173s	0.0717s
PARSE calls [CPU]	0.0%	0.0100s	1	0.0100s	0.0100s	0.0100s
SQL*Net message to client	0.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	80.0177s

Statement Objects Statistics

Object/Event	% Time	Seconds	Calls	- Time per Call -
Object/Event	% Time	Seconds	Calls	Avg	Min	Max
TABLE AFINISHED_GOOD_NEW [9568951]
db file sequential read	50.0%	16.9300s	7,446	0.0023s	0.0000s	0.0679s
TABLE ACUSTOMER_SHIP_TO [16075053]
db file sequential read	39.7%	13.4493s	1,614	0.0083s	0.0003s	0.0811s
INDEX ACUST_SHIP_TO_IDX_CODE [16075065]
db file sequential read	3.5%	1.1760s	223	0.0053s	0.0000s	0.0465s
INDEX AFINISHED_GOOD_NEW_PK [9568971]
db file sequential read	3.1%	1.0434s	729	0.0014s	0.0000s	0.0417s
Obj#(16040034)
db file scattered read	1.5%	0.5193s	14	0.0371s	0.0173s	0.0717s
db file sequential read	0.0%	0.0072s	1	0.0072s	0.0072s	0.0072s
Obj#(16040033)
db file scattered read	1.4%	0.4591s	14	0.0328s	0.0187s	0.0480s
db file sequential read	0.0%	0.0070s	16	0.0004s	0.0000s	0.0067s
Obj#(16040036)
db file scattered read	0.6%	0.1930s	6	0.0322s	0.0182s	0.0645s
db file sequential read	0.1%	0.0347s	4	0.0087s	0.0003s	0.0196s
Obj#(16040035)
db file scattered read	0.1%	0.0269s	1	0.0269s	0.0269s	0.0269s
db file sequential read	0.0%	0.0094s	1	0.0094s	0.0094s	0.0094s
TABLE MAN_FORE_ENTRY [2887767]
db file sequential read	0.0%	0.0146s	2	0.0073s	0.0071s	0.0075s
INDEX MAN_FORE_ENTRY_IDX_MM [10066347]
db file sequential read	0.0%	0.0097s	1	0.0097s	0.0097s	0.0097s

Statement Read Statistics

Blocks Read per Call	% Time	Seconds	Calls	- Time per Call -
Blocks Read per Call	% Time	Seconds	Calls	Avg	Min	Max
1	96.5%	32.6813s	10,037	0.0033s	0.0000s	0.0811s
63	3.1%	1.0441s	30	0.0348s	0.0173s	0.0717s
61	0.4%	0.1358s	4	0.0340s	0.0242s	0.0568s
16	0.1%	0.0182s	1	0.0182s	0.0182s	0.0182s
Total	100.0%	33.8796s	10,072	0.0034s	0.0000s	0.0811s

Statement Read Datafiles Statistics

Datafile #	% Time	Seconds	Calls	- Time per Call -
Datafile #	% Time	Seconds	Calls	Avg	Min	Max
239	14.2%	4.7944s	560	0.0086s	0.0003s	0.0483s
238	13.3%	4.5147s	587	0.0077s	0.0003s	0.0446s
237	12.2%	4.1402s	467	0.0089s	0.0003s	0.0811s
268	11.3%	3.8164s	1,770	0.0022s	0.0000s	0.0438s
266	11.3%	3.8129s	1,612	0.0024s	0.0000s	0.0517s
267	10.8%	3.6450s	1,838	0.0020s	0.0000s	0.0496s
269	10.0%	3.3974s	1,385	0.0025s	0.0000s	0.0587s
270	4.6%	1.5527s	421	0.0037s	0.0000s	0.0465s
311	2.1%	0.7055s	420	0.0017s	0.0000s	0.0679s
241	1.6%	0.5586s	104	0.0054s	0.0003s	0.0312s
294	1.6%	0.5265s	15	0.0351s	0.0072s	0.0717s
295	1.4%	0.4661s	30	0.0155s	0.0000s	0.0480s
4	0.9%	0.3177s	71	0.0045s	0.0000s	0.0465s
240	0.9%	0.2997s	48	0.0062s	0.0003s	0.0347s
310	0.8%	0.2773s	61	0.0045s	0.0003s	0.0326s
273	0.7%	0.2380s	78	0.0031s	0.0000s	0.0298s
293	0.7%	0.2276s	10	0.0228s	0.0003s	0.0645s
272	0.6%	0.1909s	408	0.0005s	0.0000s	0.0417s
271	0.5%	0.1634s	74	0.0022s	0.0000s	0.0227s
370	0.3%	0.0893s	47	0.0019s	0.0003s	0.0208s
274	0.2%	0.0845s	61	0.0014s	0.0000s	0.0113s
292	0.1%	0.0363s	2	0.0181s	0.0094s	0.0269s
349	0.0%	0.0146s	2	0.0073s	0.0071s	0.0075s
291	0.0%	0.0097s	1	0.0097s	0.0097s	0.0097s
Total	100.0%	33.8796s	10,072	0.0034s	0.0000s	0.0811s

Statement Plan

met 1 time

Rows	Row Source Operation	Object Id
0 1,725 187,564 187,564 1,725 187,564 187,564 187,564 187,564 187,564 1,725 14,545,321	UPDATE FORECAST (cr=2,068,345 pr=12,187 pw=0 time=73.2759s) FILTER (cr=2,068,345 pr=12,172 pw=0 time=73.1795s) PARTITION RANGE ALL PARTITION: 1 4 (cr=2,166 pr=2,157 pw=0 time=1.7333s) TABLE ACCESS FULL FORECAST PARTITION: 1 4 (cr=2,166 pr=2,157 pw=0 time=1.5116s) NESTED LOOPS (cr=2,066,179 pr=10,015 pw=0 time=70.7052s) NESTED LOOPS (cr=1,131,343 pr=10,012 pw=0 time=37.2303s) TABLE ACCESS BY INDEX ROWID AFINISHED_GOOD_NEW (cr=568,651 pr=8,175 pw=0 time=20.2279s) INDEX UNIQUE SCAN AFINISHED_GOOD_NEW_PK (cr=375,128 pr=729 pw=0 time=2.1662s) TABLE ACCESS BY INDEX ROWID ACUSTOMER_SHIP_TO (cr=562,692 pr=1,837 pw=0 time=16.5782s) INDEX UNIQUE SCAN ACUST_SHIP_TO_IDX_CODE (cr=375,128 pr=223 pw=0 time=2.2480s) TABLE ACCESS BY INDEX ROWID MAN_FORE_ENTRY (cr=934,836 pr=3 pw=0 time=33.1180s) INDEX RANGE SCAN MAN_FORE_ENTRY_IDX_MM (cr=375,128 pr=1 pw=0 time=2.5353s)	16040032 9568951 9568971 16075053 16075065 2887767 10066347

SQL Hash Value: 1398610540 uid: 0 depth: 1 optimizer mode: CHOOSE

Statement Text

select text from view$ where rowid=:1

Statement Cumulative Statistics

Call	Cache Misses	Count	- Seconds -		Physical Reads	- Logical Reads -		Rows
Call	Cache Misses	Count	CPU	Elapsed	Physical Reads	Consistent	Current	Rows
Parse	1	1	0.0000s	0.0005s	0	0	0	0
Exec	1	1	0.0000s	0.1010s	0	0	0	0
Fetch		1	0.0000s	0.0000s	0	3	0	1
Total	2	3	0.0000s	0.1015s	0	3	0	1

Per Fch	2.0	3.0	0.0000s	0.1015s	0.0	3.0	0.0	1.0
Per Row	2.0	3.0	0.0000s	0.1015s	0.0	3.0	0.0	1.0

Statement Flat Profile

Event Name	% Time	Seconds	Calls	- Time per Call -
Event Name	% Time	Seconds	Calls	Avg	Min	Max
EXEC calls [CPU]	n/a %	0.0000s	1	0.0000s	0.0000s	0.0000s
FETCH calls [CPU]	n/a %	0.0000s	1	0.0000s	0.0000s	0.0000s
PARSE calls [CPU]	n/a %	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	0.0000s

Statement Plan

met 1 time

Rows	Row Source Operation	Object Id
1	TABLE ACCESS BY USER ROWID VIEW$ (cr=2 pr=0 pw=0 time=0.0000s)	6659

Statement Binds Statistics

total binds sets: 1 unique binds sets: 1

Sample Bind Set

met 1 time; elapsed time spent: 0.1015s

Name	Data Type	Value
1	ROWID	000129AC.0000.00D0

SQL Hash Value: Cursor #3 uid: n/a

Statement Text

  No statement text found in trace file

Statement Cumulative Statistics

  No cumulative stat found in trace file

Statement Flat Profile

Event Name	% Time	Seconds	Calls	- Time per Call -
Event Name	% Time	Seconds	Calls	Avg	Min	Max
SQL*Net message from client [idle]	100.0%	11.6927s	1	11.6927s	11.6927s	11.6927s
SQL*Net message to client	0.0%	0.0000s	1	0.0000s	0.0000s	0.0000s
Total	100.0%	11.6927s

Generated by OraSRP v4.1.5 beta