Article Original Creation Date: 2010-11-12
Overview
Searching any parameter (Unique ID String, IP) yields results in less than a second. However, when Inventory is searched by First Name, it takes about 30 seconds to find the device.
The problem came with 4.0.8 or 4.0.9 versions and was not experienced in the 4.0.7 SG version.
Environment
Solaris 10
Oracle 10
Weblogic 9.2 sp1
Tomcat 5.25
Root Cause
An agent tested the following search queries and found that in certain circumstances it takes longer than average.
1) Query for Unique ID search:
SELECT COUNT(*)
FROM sprt_ec_device
LEFT OUTER JOIN SPRT_EC_SUBSCRIBER_DEVICE_XREF ON sprt_ec_device.ID=SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id
LEFT OUTER JOIN SPRT_EC_SUBSCRIBER ON SPRT_EC_SUBSCRIBER_DEVICE_XREF.EC_SUBSCRIBER_ID=SPRT_EC_SUBSCRIBER.ID
WHERE LOWER(UNIQUE_ID_STRING) LIKE LOWER('001BBF-Router-LK09321DP190432');
SELECT /*+ USE_NL(sprt_ec_subscriber sprt_ec_subscriber_device_xref sprt_ec_device) INDEX(sprt_ec_subscriber_device_xref sprt_ec_subscriber_dev_xr_idx1) */
SPRT_EC_SUBSCRIBER.ID AS EC_SUBSCRIBER_ID,SPRT_EC_SUBSCRIBER.LAST_NAME,SPRT_EC_SUBSCRIBER.MIDDLE_NAME,SPRT_EC_SUBSCRIBER.FIRST_NAME,SPRT_EC_SUBSCRIBER.PHONE_NUMBER,SPRT_EC_SUBSCRIBER.ADDRESS,SPRT_EC_SUBSCRIBER.ADDRESS2,SPRT_EC_SUBSCRIBER.CITY,SPRT_EC_SUBSCRIBER.STATE,SPRT_EC_SUBSCRIBER.EMAIL_ADDRESS,SPRT_EC_SUBSCRIBER.BILLING_NUMBER,
sprt_ec_device.ID AS ec_device_id,sprt_ec_device.mac_address,sprt_ec_device.ip_address,sprt_ec_device.UNIQUE_ID_STRING,sprt_ec_device.EC_REALM_GUID,sprt_ec_device.last_successful_comm_date,sprt_ec_device.last_unsuccessful_comm_date,sprt_ec_device.ec_ad_id,sprt_ec_device.hardware_guid,sprt_ec_device.firmware_guid
FROM sprt_ec_device
LEFT OUTER JOIN SPRT_EC_SUBSCRIBER_DEVICE_XREF ON sprt_ec_device.ID=SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id
LEFT OUTER JOIN SPRT_EC_SUBSCRIBER ON SPRT_EC_SUBSCRIBER_DEVICE_XREF.EC_SUBSCRIBER_ID=SPRT_EC_SUBSCRIBER.ID
WHERE LOWER(UNIQUE_ID_STRING) LIKE LOWER('001BBF-Router-LK09321DP190432') ORDER BY last_name ASC, first_name ASC;
2) Query for FIRST_NAME search
SELECT COUNT(*)
FROM SPRT_EC_SUBSCRIBER
LEFT OUTER JOIN SPRT_EC_SUBSCRIBER_DEVICE_XREF ON SPRT_EC_SUBSCRIBER.ID=SPRT_EC_SUBSCRIBER_DEVICE_XREF.EC_SUBSCRIBER_ID
LEFT OUTER JOIN sprt_ec_device ON SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id=sprt_ec_device.ID
WHERE ( UPPER(REPLACE(SPRT_EC_SUBSCRIBER.FIRST_NAME, ' ','')) LIKE UPPER(REPLACE('EM722426',' ','')) OR SPRT_EC_SUBSCRIBER.FIRST_NAME IS NULL)
AND SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id=sprt_ec_device.ID;
SELECT /*+ USE_NL(sprt_ec_subscriber sprt_ec_subscriber_device_xref sprt_ec_device) INDEX(sprt_ec_subscriber_device_xref sprt_ec_subscriber_dev_xr_idx1) */
SPRT_EC_SUBSCRIBER.ID AS EC_SUBSCRIBER_ID,SPRT_EC_SUBSCRIBER.LAST_NAME,SPRT_EC_SUBSCRIBER.MIDDLE_NAME,SPRT_EC_SUBSCRIBER.FIRST_NAME,SPRT_EC_SUBSCRIBER.PHONE_NUMBER,SPRT_EC_SUBSCRIBER.ADDRESS,SPRT_EC_SUBSCRIBER.ADDRESS2,SPRT_EC_SUBSCRIBER.CITY,SPRT_EC_SUBSCRIBER.STATE,SPRT_EC_SUBSCRIBER.EMAIL_ADDRESS,SPRT_EC_SUBSCRIBER.BILLING_NUMBER,
sprt_ec_device.ID AS ec_device_id,sprt_ec_device.mac_address,sprt_ec_device.ip_address,sprt_ec_device.UNIQUE_ID_STRING,sprt_ec_device.EC_REALM_GUID,sprt_ec_device.last_successful_comm_date,sprt_ec_device.last_unsuccessful_comm_date,sprt_ec_device.ec_ad_id,sprt_ec_device.hardware_guid,sprt_ec_device.firmware_guid FROM SPRT_EC_SUBSCRIBER LEFT OUTER JOIN SPRT_EC_SUBSCRIBER_DEVICE_XREF ON SPRT_EC_SUBSCRIBER.ID=SPRT_EC_SUBSCRIBER_DEVICE_XREF.EC_SUBSCRIBER_ID
LEFT OUTER JOIN sprt_ec_device ON SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id=sprt_ec_device.ID WHERE ( UPPER(REPLACE(SPRT_EC_SUBSCRIBER.FIRST_NAME, ' ','')) LIKE UPPER(REPLACE('EM516270',' ','')) OR SPRT_EC_SUBSCRIBER.FIRST_NAME IS NULL)
AND SPRT_EC_SUBSCRIBER_DEVICE_XREF.ec_device_id=sprt_ec_device.ID
ORDER BY last_name ASC, first_name ASC;
The argument highlighted in Green above is the standard query which took an average 16 seconds to return. However, when the green part is replaced by:
SPRT_EC_SUBSCRIBER.FIRST_NAME LIKE 'EM516270'
It takes average 3,5 seconds and the first count query from 1,5 is reduced to 0,5 seconds.
It takes average 3,5 seconds and the first count query from 1,5 is reduced to 0,5 seconds.
Resolution
Version SG4.0.13 has changed search queries, a query hint is removed allowing Oracle to choose a better plan for these search queries. We advise you to upgrade to version SG4.0.13.
Below an extra remark from Engineering:
After the upgrade to 4.0.13, we recommend that the DBA (database administrator) consider using Oracle SQL Advisor to analyze the query and put a profile in place to optimize the plan. When we did this on our database for phone number queries, the cost of the query was reduced by 99.8%, by improving the join with the device table.
Below an extra remark from Engineering:
After the upgrade to 4.0.13, we recommend that the DBA (database administrator) consider using Oracle SQL Advisor to analyze the query and put a profile in place to optimize the plan. When we did this on our database for phone number queries, the cost of the query was reduced by 99.8%, by improving the join with the device table.