Home -> Mailing Lists -> Oracle-L -> Query Tuning

Query Tuning

From: Stalin <stalinsk_at_gmail.com>
Date: Mon, 25 Aug 2008 17:11:00 -0700
Message-ID: <c5363d3a0808251711h44e78fd0hbc9328d16e1f9378@mail.gmail.com> 





Hi All,



We have a query that runs in sub seconds if i comment out the order by
section in the sub query. Otherwise it takes a minute and half to
complete. Vendor column is indexed with zero nulls though the column
is defined as nullable.


Environment : 10.2.0.2, Solaris 10



Any input to troubleshoot this is appreciated.



Query :



SELECT vendor, devid FROM ( SELECT tmp.*, rownum rnum


                FROM ( SELECT /*+ gather_plan_statistics */ d.devId, d.vendor,
d.model, d.productClass, d.oui, d.parentDeviceId, d.lastModified AS
physDevLastMod, d.subscriberId,


                        d.userKey1, d.userKey2, d.firmwareFamily, d.softwareVer,
d.serialNum, d.ip, d.mac, d.userKey3, ifaces.id, ifaces.type AS
ifaceType,


                        ifaces.lastModified AS ifaceLastMod, ifaces.username1,
ifaces.password1, ifaces.username2, ifaces.password2,
ifaces.connReqUrl,


                        ifaces.connReqScheme, ifaces.srvNonce, ifaces.deviceNonce,
ifaces.phoneNumber,ifaces.bootstrapSecMethod,
ifaces.srvAuthentication,


                        ifaces.deviceAuthentication, ifaces.userPIN, ifaces.networkID,
ifaces.omaSessionID, ifaces.portNum, ifaces.mgtIp, ifaces.cmtsIp,


                        ifaces.mgtReadCommunity, ifaces.mgtWriteCommunity,
ifaces.cmtsReadCommunity, ifaces.cmtsWriteCommunity, devto.name AS
devtoName,


                        devto.rebootTimeout, devto.sessionInitiationTimeout,
devto.requestTimeout, devto.lastModified AS devtoLastMod


                       FROM Device d LEFT JOIN ManagementInterfaces ifaces ON
d.devId=ifaces.id

		       		     LEFT JOIN DeviceTimeouts devto ON ifaces.timeoutName=devto.name
		        ORDER by vendor ) tmp
	        WHERE rownum <= 30)




WHERE rnum >= 1



Plan hash value: 3625007619




| Id  | Operation                 | Name                 | Starts |


E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
Used-Tmp|




|*  1 |  VIEW                     |                      |      1 |


 30 |     30 |00:00:10.36 |   81036 |       |       |          |
  |


|*  2 |   COUNT STOPKEY           |                      |      1 |


    |     30 |00:00:10.36 |   81036 |       |       |          |
  |

|   3 |    VIEW                   |                      |      1 |
2000K|     30 |00:00:10.36 |   81036 |       |       |          |
   |


|*  4 |     SORT ORDER BY STOPKEY |                      |      1 |

2000K|     30 |00:00:10.36 |   81036 |  9216 |  9216 | 8192  (0)|
   |


|*  5 |      HASH JOIN RIGHT OUTER|                      |      1 |



2000K|   2000K|00:00:11.49 |   81036 |  1155K|  1155K|  470K (0)|


   |

|   6 |       TABLE ACCESS FULL   | DEVICETIMEOUTS       |      1 |
  2 |      2 |00:00:00.01 |      21 |       |       |          |
  |


|*  7 |       HASH JOIN OUTER     |                      |      1 |



2000K|   2000K|00:00:07.49 |   81015 |    72M|  5936K|  107M (0)|


   |

|   8 |        TABLE ACCESS FULL  | DEVICE               |      1 |
2000K|   2000K|00:00:00.01 |   22924 |       |       |          |
   |
|   9 |        TABLE ACCESS FULL  | MANAGEMENTINTERFACES |      1 |
2016K|   2000K|00:00:00.01 |   58091 |       |       |          |




   |





Predicate Information (identified by operation id):




   1 - filter("RNUM">=1)
   2 - filter(ROWNUM<=30)
   4 - filter(ROWNUM<=30)
   5 - access("IFACES"."TIMEOUTNAME"="DEVTO"."NAME")
   7 - access("D"."DEVID"="IFACES"."ID")





Plan with order by commented.



Plan hash value: 1457834706




| Id  | Operation                       | Name                       |


Starts | E-Rows | A-Rows |   A-Time   | Buffers |




|*  1 |  VIEW                           |                            |



     1 |     30 |     30 |00:00:00.01 |     102 |


|*  2 |   COUNT STOPKEY                 |                            |

     1 |        |     30 |00:00:00.01 |     102 |
|   3 |    NESTED LOOPS OUTER           |                            |
     1 |     30 |     30 |00:00:00.01 |     102 |
|   4 |     NESTED LOOPS OUTER          |                            |
     1 |     30 |     30 |00:00:00.01 |      99 |
|   5 |      TABLE ACCESS FULL          | DEVICE                     |
     1 |     30 |     30 |00:00:00.01 |       6 |
|   6 |      TABLE ACCESS BY INDEX ROWID| MANAGEMENTINTERFACES       |
    30 |      1 |     30 |00:00:00.01 |      93 |


|*  7 |       INDEX UNIQUE SCAN         | CT_MANAGEMENTINTERFACES_PK |

    30 |      1 |     30 |00:00:00.01 |      63 |


|*  8 |     INDEX UNIQUE SCAN           | CT_DEVICETIMEOUTS_PK       |

    30 |      1 |     30 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):




   1 - filter("RNUM">=1)
   2 - filter(ROWNUM<=30)
   7 - access("D"."DEVID"="IFACES"."ID")
   8 - access("IFACES"."TIMEOUTNAME"="DEVTO"."NAME")



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


Received on Mon Aug 25 2008 - 19:11:00 CDT












Original text of this message