Optimizer question after upgrade
- From: Jack van Zanen <jack@xxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Mar 2009 11:05:37 +1100
We have just upgraded our development environment from 9.2 to 10.2
Can someone can help us identify why 10.2.0.4 optimizer taking double the
time to execute the same statement for exact same number of rows ( 3803685 )
We can see, it is picking up a different plan, NL is 9.2 against HJ in 10.2.
We ran statistics the same way we ran them in Oracle9
!! We already had a look at note 295819.1 which mentions the two parameters
"_optimizer_cost_based_transformation" and "_gby_hash_aggregation_enabled"
This SQL is part of a long running batch job which ran about 20 minutes
faster than on 9i, but this step doubled in time so we could potentially
gain some more.
SQL Statement :
INSERT INTO INSTALL_DEVICE ( INSTALL_ID, DEVICE, MATERIAL_NO, DEVICE_NO,
EQUIPMENT_NO, REGISTER_GROUP, INSTALL_DATE, DEVICE_LOCATION,
DEVICE_VALID_FROM, DEVICE_VALID_TO, EQUIP_VALID_FROM, EQUIP_VALID_TO,
MODIFIED_DATE )
SELECT /*+ ALL_ROWS */ ET.INSTALL_ID, LTRIM(EQ.SERIAL_NO2,
'0') DEVICE, EQ.MATERIAL_NO, EH.DEVICE_NO, EH.EQUIPMENT_NO,
EH.REGISTER_GROUP, EH.INSTALL_DATE, EH.DEVICE_LOCATION, ET.VALID_FROM
VALID_FROM, ET.VALID_TO VALID_TO, EH.VALID_FROM EQUIP_VALID_FROM,
EH.VALID_TO EQUIP_VALID_TO, ET.MODIFIED_DATE
FROM CCS.CCS_EASTL ET,
CCS.CCS_EGERH EH,
CCS.CCS_EQUI EQ
WHERE 1=1
AND ET.INSTALL_ID BETWEEN :B2
AND :B1 AND EH.DEVICE_NO = ET.DEVICE_NO AND EH.VALID_TO = ET.VALID_TO
AND EQ.EQUIPMENT_NO = EH.EQUIPMENT_NO
Oracle 9.2 (10046 Trace Details)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.19 0 0
0 0
Execute 22 342.15 647.50 641661 8338678 14429392
3803685
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 23 342.15 647.69 641661 8338678 14429392
3803685
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 640 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
3803685 FILTER
3803685 NESTED LOOPS
3803685 HASH JOIN
3804760 TABLE ACCESS FULL CCS_EASTL
183904820 TABLE ACCESS FULL CCS_EGERH
3803685 TABLE ACCESS BY INDEX ROWID CCS_EQUI
3803685 INDEX UNIQUE SCAN CCS_EQUI_KEY (object id 278281)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
db file sequential read 612 0.15
3.30
db file scattered read 20428 0.57
220.18
log file switch completion 30 0.05
0.82
log buffer space 75 0.50
4.63
latch free 8 0.01
0.03
log file sync 14 0.03
0.25
********************************************************************************
Oracle 10.2 (10046 Trace Details)
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.02 0 0
0 0
Execute 22 694.85 1242.63 1419950 1495846 14933397
3803685
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 23 694.85 1242.66 1419950 1495846 14933397
3803685
Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 640 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
3592526 FILTER (cr=1356558 pr=1355369 pw=0 time=955288705 us)
3592526 HASH JOIN (cr=1356558 pr=1355369 pw=0 time=901159201 us)
3592526 HASH JOIN (cr=635607 pr=634978 pw=0 time=548271804 us)
3593551 TABLE ACCESS FULL CCS_EASTL (cr=182049 pr=181793 pw=0
time=187608037 us)
175545510 TABLE ACCESS FULL CCS_EGERH (cr=453558 pr=453185 pw=0
time=707111 us)
104295387 TABLE ACCESS FULL CCS_EQUI (cr=720951 pr=720391 pw=0
time=327816867 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
db file sequential read 59 0.01
0.09
db file scattered read 44944 0.90
445.14
latch: cache buffers lru chain 23 0.00
0.02
log file switch completion 33 0.08
0.58
latch: object queue header operation 7 0.00
0.00
latch: cache buffers chains 3 0.00
0.00
latch: shared pool 5 0.01
0.01
log buffer space 2 0.06
0.07
********************************************************************************
Brgds
Jack
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
Other related posts:
