Query Tuning
- From: Stalin <stalinsk@xxxxxxxxx>
- To: "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Aug 2008 17:11:00 -0700
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")
--
//www.freelists.org/webpage/oracle-l
Other related posts:
