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

Root Cause

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;

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;

Resolution