Re: Query Tuning

Stalin
 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)
  5 - access("IFACES"."TIMEOUTNAME"="DEVTO"."NAME")
  7 - access("D"."DEVID"="IFACES"."ID")

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

Cheers
Riyaj Shamsudeen
The Pythian Group www.pythian.com
Personal blog: http://orainternals.wordpress.com

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

... Snipped..

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