Home » RDBMS Server » Performance Tuning » Oracle Query Tuning
Oracle Query Tuning [message #333852] Mon, 14 July 2008 10:45 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Could you please help me tune this query?
 SELECT *
   FROM (SELECT ROWNUM AS RX, T.*
           FROM (SELECT PVS.POB_VENDOR_SHIPMENT_ID,
                        PVS.POB_VENDOR_SHIPMENT_NUM,
                        PVS.SHIPPING_COMPANY,
                        PVS.DOCK_DOOR_NUM,
                        PVS.TRAILER_NUM,
                        PVS.SHIP_DATE,
                        PVS.PRO_NUM,
                        PVS.POB_SHIP_TERM_ID,
                        PST.SHIP_TERM_NAME,
                        RS.RMA_STAT_NAME,
                        1 AS NO_OF_RECORDS
                   FROM POB_VENDOR_SHIPMENT PVS,
                        POB_VENDOR_SHIPMENT_DTL PVSD,
                        POB_SHIP_TERM PST,
                        POB_SHIP_ADDRESS PSA,
                        RMA_STAT RS,
                        (SELECT POB.POB_IDEN, PL.CONTAINER_LPN
                           FROM POB, POB_LPN PL, POB_VENDOR_SHIPMENT_DTL PVSD
                          WHERE PVSD.CONTAINER_LPN = PL.CONTAINER_LPN
                            AND PL.VENDOR_POB_ID = POB.POB_ID) VENDORNAME
                  WHERE PVSD.POB_VENDOR_SHIPMENT_ID(+) = PVS.POB_VENDOR_SHIPMENT_ID
                    AND PVS.POB_SHIP_TERM_ID = PST.POB_SHIP_TERM_ID(+)
                    AND PSA.POB_SHIP_ADDRESS_ID = PVS.PROCESS_POB_SHIP_ADDRESS_ID
                    AND PSA.POB_ID = 39
                    AND PVS.POB_ID = 39
                    AND RS.RMA_STAT_ID = PVS.SHIPMENT_STATUS
                    AND UPPER(PVS.POB_VENDOR_SHIPMENT_NUM) LIKE UPPER('ARTDS10620%')
                  GROUP BY PVS.POB_VENDOR_SHIPMENT_ID,
                           PVS.POB_VENDOR_SHIPMENT_NUM,
                           PVS.SHIPPING_COMPANY,
                           PVS.DOCK_DOOR_NUM,
                           PVS.TRAILER_NUM,
                           PVS.SHIP_DATE,
                           PVS.PRO_NUM,
                           PVS.POB_SHIP_TERM_ID,
                           PST.SHIP_TERM_NAME,
                           RS.RMA_STAT_NAME
                  ORDER BY PVS.SHIP_DATE) T)
  WHERE rx <= 20
    AND rx > 0


This the explain plan.
SELECT STATEMENT, GOAL = ALL_ROWS			Cost=569	Cardinality=2241	Bytes=479574
 VIEW	Object owner=MASYS		Cost=569	Cardinality=2241	Bytes=479574
  COUNT					
   VIEW	Object owner=MASYS		Cost=569	Cardinality=2241	Bytes=450441
    SORT GROUP BY			Cost=569	Cardinality=2241	Bytes=331668
     NESTED LOOPS OUTER			Cost=568	Cardinality=2241	Bytes=331668
      NESTED LOOPS OUTER			Cost=567	Cardinality=1391	Bytes=198913
       NESTED LOOPS			Cost=566	Cardinality=1391	Bytes=172484
        HASH JOIN			Cost=564	Cardinality=1391	Bytes=139100
         TABLE ACCESS FULL	Object owner=MASYS	Object name=POB_VENDOR_SHIPMENT	Cost=3	Cardinality=8	Bytes=448
         MERGE JOIN CARTESIAN			Cost=560	Cardinality=3178	Bytes=139832
          NESTED LOOPS			Cost=511	Cardinality=177	Bytes=6195
           NESTED LOOPS			Cost=511	Cardinality=177	Bytes=5487
            TABLE ACCESS FULL	Object owner=MASYS	Object name=POB_VENDOR_SHIPMENT_DTL	Cost=15	Cardinality=165	Bytes=1320
            TABLE ACCESS BY INDEX ROWID	Object owner=MASYS	Object name=POB_LPN	Cost=3	Cardinality=1	Bytes=23
             INDEX RANGE SCAN	Object owner=MASYS	Object name=PL_CLPN_IDX	Cost=2	Cardinality=1	
           INDEX UNIQUE SCAN	Object owner=MASYS	Object name=PKPOB	Cost=0	Cardinality=1	Bytes=4
          BUFFER SORT			Cost=560	Cardinality=18	Bytes=162
           TABLE ACCESS BY INDEX ROWID	Object owner=MASYS	Object name=POB_SHIP_ADDRESS	Cost=9	Cardinality=18	Bytes=162
            INDEX RANGE SCAN	Object owner=MASYS	Object name=XAK1POB_SHIP_ADDRESS	Cost=1	Cardinality=18	
        TABLE ACCESS BY INDEX ROWID	Object owner=MASYS	Object name=RMA_STAT	Cost=1	Cardinality=1	Bytes=24
         INDEX UNIQUE SCAN	Object owner=MASYS	Object name=XPKRMA_STAT	Cost=0	Cardinality=1	
       TABLE ACCESS BY INDEX ROWID	Object owner=MASYS	Object name=POB_SHIP_TERM	Cost=1	Cardinality=1	Bytes=19
        INDEX UNIQUE SCAN	Object owner=MASYS	Object name=XPKPOB_SHIP_TERM	Cost=0	Cardinality=1	
      INDEX RANGE SCAN	Object owner=MASYS	Object name=PVSD_PVSID_IDX	Cost=0	Cardinality=2	Bytes=10


From the above, I see that buffer sort is the reason for such high cost, is there some way I could avoid it?

Thanks,

Sharath
Re: Oracle Query Tuning [message #333854 is a reply to message #333852] Mon, 14 July 2008 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Performance might improve if you removed out of FROM clause those tables which contribute no data to the SELECT clause.
Re: Oracle Query Tuning [message #333886 is a reply to message #333852] Mon, 14 July 2008 14:08 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
kill all nested loops Wink
(with hints or by rewriting query)
nested loops are evil thing, trust me...
Re: Oracle Query Tuning [message #333924 is a reply to message #333886] Mon, 14 July 2008 21:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no join condition to the VENDORNAME inline view. This is causing a cartesian join.

Might be worth looking at.

Also make sure your statistics are up to date on all tables by gathering them with DBMS_STATS.GATHER_TABLE_STATS()

Ross Leishman
Previous Topic: Nasty query re-write
Next Topic: pls help to reduce some loop
Goto Forum:
  


Current Time: Sat Dec 03 06:06:01 CST 2016

Total time taken to generate the page: 0.09958 seconds