Re: Query Tuning

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


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..
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2008