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

Copyright 2000-2009 by Christian AntogniniTrivadis 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

Beginning7 Aug 2012 17:29:04
End7 Aug 2012 17:30:34
Duration90.555 [s]

Transactions

Committed1
Rollbacked0

Resource Usage Profile  overall current

ComponentTotal Duration [s]%Number of EventsDuration per Event [s]
CPU40.73044.978 n/a n/a
db file sequential read32.68136.09010,0370.003
SQL*Net message from client17.10118.88528.550
db file scattered read1.1981.323350.034
SQL*Net message to client0.0000.00020.000
unaccounted-for-1.156-1.276 n/a n/a
Total90.555100.000

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

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

Statement IDTypeTotal Duration [s]%Number of ExecutionsDuration per Execution [s]
1UPDATE80.01888.364180.018
2unknown11.69312.9120 n/a
3COMMIT0.0000.00010.000
Total91.710101.276

CPU   overall

2 statements contributed to this event.

Statement IDTypeTotal Duration [s]%
1UPDATE40.730100.000
4SELECT (SYS recursive) 0.0000.000
Total40.730100.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 < 160.0000.001190.18916191.000
16 ≥ duration < 320.0170.0518108.070208101.000
32 ≥ duration < 640.0010.002180.17941181.000
64 ≥ duration < 1280.0040.011370.36995371.000
128 ≥ duration < 2560.0050.015270.269178271.000
256 ≥ duration < 5121.8395.6285,33653.1633455,3361.000
512 ≥ duration < 10240.0880.2681371.3656401371.000
1024 ≥ duration < 20480.2430.7421391.3851,7451391.000
2048 ≥ duration < 40961.1233.4363973.9552,8283971.000
4096 ≥ duration < 819210.45731.9961,58515.7926,5971,5851.000
8192 ≥ duration < 1638413.46741.2081,32713.22110,1491,3271.000
16384 ≥ duration < 327683.81811.6821651.64423,1381651.000
32768 ≥ duration < 655361.4724.505380.37938,748381.000
65536 ≥ duration < 1310720.1490.45620.02074,52021.000
Total32.681100.00010,037100.0003,25610,0371.000

FileTotal Duration [s]%Number of Events%Blocks [b]%Duration per Event [μs]
2394.79414.6705605.5795605.5798,561
2384.51513.8145875.8485875.8487,691
2374.14012.6684674.6534674.6538,866
2683.81611.6781,77017.6351,77017.6352,156
2663.81311.6671,61216.0611,61216.0612,365
2673.64511.1531,83818.3121,83818.3121,983
2693.39710.3951,38513.7991,38513.7992,453
2701.5534.7514214.1944214.1943,688
3110.7062.1594204.1854204.1851,680
2410.5591.7091041.0361041.0365,371
40.3180.972710.707710.7074,475
2400.3000.917480.478480.4786,244
3100.2770.848610.608610.6084,546
2730.2380.728780.777780.7773,052
2720.1910.5844084.0654084.065468
2710.1630.500740.737740.7372,208
3700.0890.273470.468470.4681,900
2740.0850.259610.608610.6081,386
2930.0350.10640.04040.0408,669
3490.0150.04520.02020.0207,310
2910.0100.03010.01010.0109,737
2920.0090.02910.01010.0109,358
2940.0070.02210.01010.0107,163
2950.0070.022160.159160.159441
Total32.681100.00010,037100.00010,037100.0003,256

1 statement contributed to this event.

Statement IDTypeTotal Duration [s]%
1UPDATE32.681100.000

SQL*Net message from client   overall

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
4194304 ≥ duration < 83886085.40831.625150.0005,408,174
8388608 ≥ duration < 1677721611.69368.375150.00011,692,698
Total17.101100.0002100.0008,550,436

2 statements contributed to this event.

Statement IDTypeTotal Duration [s]%
2unknown11.69368.375
1UPDATE5.40831.625
Total17.101100.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 < 327680.47639.7652057.14323,8241,20760.350
32768 ≥ duration < 655360.65054.2501440.00046,43188062.857
65536 ≥ duration < 1310720.0725.98612.85771,7246363.000
Total1.198100.00035100.00034,2352,15061.429

FileTotal Duration [s]%Number of Events%Blocks [b]%Duration per Event [μs]
2940.51943.3401440.00088040.93037,094
2950.45938.3111440.00088040.93032,790
2930.19316.103617.14332915.30232,159
2920.0272.24512.857612.83726,904
Total1.198100.00035100.0002,150100.00034,235

1 statement contributed to this event.

Statement IDTypeTotal Duration [s]%
1UPDATE1.198100.000

SQL*Net message to client   overall

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
0 ≥ duration < 20.00025.000150.0001
2 ≥ duration < 40.00075.000150.0003
Total0.000100.0002100.0002

2 statements contributed to this event.

Statement IDTypeTotal Duration [s]%
1UPDATE0.00075.000
2unknown0.00025.000
Total0.000100.000



Statement 1  overall

Session ID199.52650
Service NameSYS$USERS
Module Namesqlplus@server1 (TNS V1-V3)
Parsing User0
Hash Value3733909562
Textupdate 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 ModeCHOOSE

RowsOperation
0UPDATE 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

CallCountMissesCPU [s]Elapsed [s]PIO [b]LIO [b]Consistent [b]Current [b]Rows
Parse110.0100.17303300
Execute1040.72073.28012,1872,072,8172,068,3454,4721,725
Fetch000.0000.00000000
Total2140.73073.45312,1872,072,8202,068,3484,4721,725
Average (per row)000.0240.04371,2011,19921

Database Call Statistics without Recursive Statements

CallCountMissesCPU [s]Elapsed [s]PIO [b]LIO [b]Consistent [b]Current [b]Rows
Parse110.0100.07100000
Execute1040.72073.28012,1872,072,8172,068,3454,4721,725
Fetch000.0000.00000000
Total2140.73073.35112,1872,072,8172,068,3454,4721,725
Average (per row)000.0240.04371,2011,19921

Resource Usage Profile  overall current

ComponentTotal Duration [s]%Number of EventsDuration per Event [s]
CPU40.73050.901 n/a n/a
db file sequential read32.68140.84310,0370.003
SQL*Net message from client5.4086.75915.408
db file scattered read1.1981.497350.034
SQL*Net message to client0.0000.00010.000
recursive statements0.0000.000 n/a n/a
Total80.018100.000

1 recursive statement was executed.

Statement IDTypeTotal Duration [s]%
4SELECT (SYS recursive) 0.0000.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 < 160.0000.001190.18916191.000
16 ≥ duration < 320.0170.0518108.070208101.000
32 ≥ duration < 640.0010.002180.17941181.000
64 ≥ duration < 1280.0040.011370.36995371.000
128 ≥ duration < 2560.0050.015270.269178271.000
256 ≥ duration < 5121.8395.6285,33653.1633455,3361.000
512 ≥ duration < 10240.0880.2681371.3656401371.000
1024 ≥ duration < 20480.2430.7421391.3851,7451391.000
2048 ≥ duration < 40961.1233.4363973.9552,8283971.000
4096 ≥ duration < 819210.45731.9961,58515.7926,5971,5851.000
8192 ≥ duration < 1638413.46741.2081,32713.22110,1491,3271.000
16384 ≥ duration < 327683.81811.6821651.64423,1381651.000
32768 ≥ duration < 655361.4724.505380.37938,748381.000
65536 ≥ duration < 1310720.1490.45620.02074,52021.000
Total32.681100.00010,037100.0003,25610,0371.000

FileTotal Duration [s]%Number of Events%Blocks [b]%Duration per Event [μs]
2394.79414.6705605.5795605.5798,561
2384.51513.8145875.8485875.8487,691
2374.14012.6684674.6534674.6538,866
2683.81611.6781,77017.6351,77017.6352,156
2663.81311.6671,61216.0611,61216.0612,365
2673.64511.1531,83818.3121,83818.3121,983
2693.39710.3951,38513.7991,38513.7992,453
2701.5534.7514214.1944214.1943,688
3110.7062.1594204.1854204.1851,680
2410.5591.7091041.0361041.0365,371
40.3180.972710.707710.7074,475
2400.3000.917480.478480.4786,244
3100.2770.848610.608610.6084,546
2730.2380.728780.777780.7773,052
2720.1910.5844084.0654084.065468
2710.1630.500740.737740.7372,208
3700.0890.273470.468470.4681,900
2740.0850.259610.608610.6081,386
2930.0350.10640.04040.0408,669
3490.0150.04520.02020.0207,310
2910.0100.03010.01010.0109,737
2920.0090.02910.01010.0109,358
2940.0070.02210.01010.0107,163
2950.0070.022160.159160.159441
Total32.681100.00010,037100.00010,037100.0003,256

SQL*Net message from client   overall current

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
4194304 ≥ duration < 83886085.408100.0001100.0005,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 < 327680.47639.7652057.14323,8241,20760.350
32768 ≥ duration < 655360.65054.2501440.00046,43188062.857
65536 ≥ duration < 1310720.0725.98612.85771,7246363.000
Total1.198100.00035100.00034,2352,15061.429

FileTotal Duration [s]%Number of Events%Blocks [b]%Duration per Event [μs]
2940.51943.3401440.00088040.93037,094
2950.45938.3111440.00088040.93032,790
2930.19316.103617.14332915.30232,159
2920.0272.24512.857612.83726,904
Total1.198100.00035100.0002,150100.00034,235

SQL*Net message to client   overall current

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
2 ≥ duration < 40.000100.0001100.0003



Statement 2  overall

Parsing Userunknown
Textunknown

Resource Usage Profile  overall current

ComponentTotal Duration [s]%Number of EventsDuration per Event [s]
SQL*Net message from client11.693100.000111.693
SQL*Net message to client0.0000.00010.000
Total11.693100.000

SQL*Net message from client   overall current

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
8388608 ≥ duration < 1677721611.693100.0001100.00011,692,698

SQL*Net message to client   overall current

Range [μs]Total Duration [s]%Number of Events%Duration per Event [μs]
0 ≥ duration < 20.000100.0001100.0001



Statement 3  overall

Parsing Userunknown
TextCOMMIT (synthetic)

Database Call Statistics

CallCountMissesCPU [s]Elapsed [s]PIO [b]LIO [b]Consistent [b]Current [b]Rows
Parse000.0000.00000000
Execute100.0000.00000000
Fetch000.0000.00000000
Total100.0000.00000000

Resource Usage Profile  overall current

ComponentTotal Duration [s]%Number of EventsDuration per Event [s]
CPU0.000 n/a n/a n/a



Units of Measure  overall

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