Re: Query Tuning

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: stalinsk@xxxxxxxxx
  • Date: Tue, 26 Aug 2008 09:19:04 -0500

You are not providing any filter conditions to reduce # of rows.
Only join conditions are provided.
If you specify order by vendor, then all rows from result set must be derived, sorted and first 30 rows from the result set returned due to rownum<=30 clause. If you do not specify order by clause, then only first 30 rows from the result set returned and no need to derive whole result set. So, there is a big difference between these two query versions and performance will be much different too.

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

Can you specify any predicate ? If not, try tuning hash joins or order of these tables.

Riyaj Shamsudeen
The Pythian Group
Personal blog:

Stalin wrote:
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 :, 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.type AS
                        ifaces.lastModified AS ifaceLastMod, ifaces.username1,
ifaces.password1, ifaces.username2, ifaces.password2,
                        ifaces.connReqScheme, ifaces.srvNonce, 
                        ifaces.deviceAuthentication, ifaces.userPIN, 
ifaces.omaSessionID, ifaces.portNum, ifaces.mgtIp, ifaces.cmtsIp,
                        ifaces.mgtReadCommunity, ifaces.mgtWriteCommunity,
ifaces.cmtsReadCommunity, ifaces.cmtsWriteCommunity, AS
                        devto.rebootTimeout, devto.sessionInitiationTimeout,
devto.requestTimeout, devto.lastModified AS devtoLastMod
                       FROM Device d LEFT JOIN ManagementInterfaces ifaces ON
                                     LEFT JOIN DeviceTimeouts devto ON
                        ORDER by vendor ) tmp
                WHERE rownum <= 30)
WHERE rnum >= 1

Plan hash value: 3625007619
... Snipped..

Other related posts: