TN Search by Assignment State is Very Slow With Query Behavior 5

(Doc ID 2321120.1)

Last updated on OCTOBER 27, 2017

Applies to:

Oracle Communications Unified Inventory Management - Version 7.3.4.0.0 and later
Information in this document applies to any platform.

Symptoms

TN search by Assignment State is very slow with query behavior 5.

TN search with Assignmentstate criteria query is taking much time.
Steps to reproduce the issue:
1) Set specification and AssignmentState criteria.
Ex: Specification='TelephoneNumber' and AssignmentState='ASSIGNED'
2)Enable finest logs and Search for telephonenumbers.
3) Below query will be observed. This can be improved by adding direct join between TelephoneNumber, TNConsumer tables.

Bad SQL:

SELECT * FROM (SELECT /*+ FIRST_ROWS(500) */ a.*, ROWNUM rnum FROM (SELECT t0.ENTITYID AS a1, t0.ENTITYCLASS AS a2, t0.ACTIVITY AS a3, t0.ADMINSTATE AS a4, t0.CREATEDDATE AS a5, t0.CREATEDUSER AS a6, t0.DESCRIPTION AS a7, t0.ENDDATE AS a8, t0.ENTITYVERSION AS a9, t0.ID AS a10, t0.ISAVAILABLE AS a11, t0.LASTMODIFIEDDATE AS a12, t0.LASTMODIFIEDUSER AS a13, t0.NAME AS a14,
t0.NATIVEEMSADMINSERVICESTATE AS a15, t0.NATIVEEMSNAME AS a16, t0.NATIVEEMSSERVICESTATE AS a17, t0.NOSPEC AS a18, t0.OBJECTSTATE AS a19, t0.OWNER AS a20, t0.PARTITION AS a21, t0.PERMISSIONS AS a22,
t0.PHYSICALLOCATION AS a23, t0.STARTDATE AS a24, t0.CURRENTASSIGNMENT AS a25, t0.SPECIFICATION AS a26 FROM TelephoneNumber t0 WHERE ((((t0.ENTITYID IN
(SELECT t1.ENTITYID FROM TNConsumer t2, TelephoneNumber t1 WHERE ((((t2.ENDDATE IS NULL) OR (t2.ENDDATE >= timestamp '2017-09-01 00:00:00.0')) AND (t2.ADMINSTATE = 'ASSIGNED')) AND (t1.ENTITYID = t2.TELEPHONENUMBER))) AND (t0.SPECIFICATION = 2250101)) AND (((t0.OBJECTSTATE = 'ACTIVE') OR (t0.OBJECTSTATE = 'INACTIVE')) OR (t0.OBJECTSTATE = 'QUEUED'))) AND
(t0.ENTITYCLASS = 'TelephoneNumberDAO')) AND (ROWNUM < 10001)) ORDER BY to_number(t0.NAME) ASC) a WHERE ROWNUM <= 500) WHERE rnum > 0;


Expecting SQL after tuning:

SELECT * FROM (SELECT /*+ FIRST_ROWS(500) */ a.*, ROWNUM rnum FROM (SELECT t1.ENTITYID AS a1, t1.ENTITYCLASS AS a2, t1.ACTIVITY AS a3, t1.ADMINSTATE AS a4, t1.CREATEDDATE AS a5, t1.CREATEDUSER AS a6, t1.DESCRIPTION AS a7, t1.ENDDATE AS a8, t1.ENTITYVERSION AS a9, t1.ID AS a10, t1.ISAVAILABLE AS a11, t1.LASTMODIFIEDDATE AS a12, t1.LASTMODIFIEDUSER AS a13, t1.NAME AS a14,
t1.NATIVEEMSADMINSERVICESTATE AS a15, t1.NATIVEEMSNAME AS a16, t1.NATIVEEMSSERVICESTATE AS a17, t1.NOSPEC AS a18, t1.OBJECTSTATE AS a19, t1.OWNER AS a20, t1.PARTITION AS a21, t1.PERMISSIONS AS a22,
t1.PHYSICALLOCATION AS a23, t1.STARTDATE AS a24, t1.CURRENTASSIGNMENT AS a25, t1.SPECIFICATION AS a26 FROM TNConsumer t0, TelephoneNumber t1 WHERE (((((((t0.TELEPHONENUMBER = t1.ENTITYID) AND ((t0.ENDDATE IS NULL) OR (t0.ENDDATE >= TIMESTAMP '2017-09-01 00:00:00.0'))) AND (t0.ADMINSTATE = 'ASSIGNED')) AND (t1.SPECIFICATION = '2250101')) AND (((t1.OBJECTSTATE = 'ACTIVE') OR (t1.OBJECTSTATE = 'INACTIVE')) OR (t1.OBJECTSTATE = 'QUEUED'))) AND (t1.ENTITYCLASS = 'TelephoneNumberDAO')) AND (ROWNUM < 10001)) ORDER BY to_number(t1.ID) ASC) a WHERE ROWNUM <= 500) WHERE rnum > 0;

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms