Re: Optimizer question after upgrade

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Fri, 20 Mar 2009 13:41:51 +0900
Message-ID: <43c2e3d60903192141w5ca69b57le1ce43893ca9395a_at_mail.gmail.com>

Well, 10g acutally outperforms 9i. You have lower logical reads in 10g(8,338,678 --> 1,495,846)

In 10g, logical reads == physical reads, which means that you have no cached blocks.

With some blocks cached, the performance would be enhanced.

But one problem is that Oracle denies to cache the blocks read by multi block I/O on the big table when the buffer cache is full. In this case, you might want to load the big tables into keep buffer pool.

Or you might just like to use 9i version execution plan.

---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)

---

On Fri, Mar 20, 2009 at 9:05 AM, Jack van Zanen <jack_at_vanzanen.com> wrote:

> 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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 19 2009 - 23:41:51 CDT