SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

oracle query performance issue

ora_1978Jul 31 2018 — edited Jul 31 2018

The below query taking long time. May I Know the reason for it. I ran the query without group by and taking 1 mins 15 secs. But with group by it is taking more time.

explain plan for

SELECT ccmdev.ipaddress CCM_IP_ADDR,

ccmdev.hostname CCM_HOST_NAME,

'' CCM_CLUSTER,

COUNT (*) AS IPPHONES_COUNTS

FROM (SELECT networkresourceid, hostname, ipaddress

FROM BV_IMS_INV_CALL_MGR

WHERE inventory = 176701

AND customer = 9999002015026

AND softwaretype IN ('CallManager')) ccmdev,

(SELECT DEVICEOBJID AS DEVICE_OBJID

FROM ((SELECT /*+ USE_NL(ims,ib) */

ims.networkresourceid deviceobjid,

ims.equipmentid equipmentid,

ims.DEVICE_TYPE itemtype,

DECODE (

UPPER (ims.snasequipmenttype),

'CARD', NVL (ims.producttype, 'Uncategorized'),

ims.producttype)

category,

NVL (ims.DEVICE_TYPE, '-') devicetype,

ims.hostname hostname,

NVL (ims.stack_flag, 0) color_flag,

ims.ipaddress ipaddress,

UPPER (NVL (ims.MANUFACTURER, '-')) manufacturer,

ims.snmplocation snmplocation,

ims.applianceid applianceid,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', 'OTHER',

ib.contract_num)

contractnum,

ib.service_line_name contracttype,

'-' slatype,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

INITCAP (ib.coverage_line_status_text))

contractstatus,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

NVL (

TO_CHAR (ib.coverage_line_start_date,

'yyyy-Mon-dd'),

'-'))

contractstartdate,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

NVL (

TO_CHAR (ib.coverage_line_end_date,

'YYYY-Mon-DD'),

'-'))

contractenddate,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

ib.INSTALL_SITE_NAME)

sitename,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

ib.BILLTO_SITE_NAME)

billtoname,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

( ib.SHIPTO_ADDRESS1

|| DECODE (ib.SHIPTO_ADDRESS1, NULL, NULL, ', ')

|| ib.SHIPTO_ADDRESS2

|| DECODE (ib.SHIPTO_ADDRESS2, NULL, NULL, ', ')

|| ib.SHIPTO_ADDRESS3

|| DECODE (ib.SHIPTO_ADDRESS3, NULL, NULL, ', ')

|| ib.SHIPTO_ADDRESS4

|| DECODE (ib.SHIPTO_ADDRESS4, NULL, NULL, ', ')

|| ib.SHIPTO_CITY

|| DECODE (ib.SHIPTO_CITY, NULL, NULL, ', ')

|| ib.SHIPTO_POSTAL_CODE

|| DECODE (ib.SHIPTO_POSTAL_CODE,

NULL, NULL,

', ')

|| ib.SHIPTO_STATE

|| DECODE (ib.SHIPTO_STATE, NULL, NULL, ', ')

|| ib.SHIPTO_PROVINCE

|| DECODE (ib.SHIPTO_PROVINCE, NULL, NULL, ', ')

|| ib.SHIPTO_COUNTRY))

shiptoaddress,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

( ib.INSTALL_ADDRESS1

|| DECODE (ib.INSTALL_ADDRESS1, NULL, NULL, ', ')

|| ib.INSTALL_ADDRESS2

|| DECODE (ib.INSTALL_ADDRESS2, NULL, NULL, ', ')

|| ib.INSTALL_ADDRESS3

|| DECODE (ib.INSTALL_ADDRESS3, NULL, NULL, ', ')

|| ib.INSTALL_ADDRESS4

|| DECODE (ib.INSTALL_ADDRESS4, NULL, NULL, ', ')

|| ib.INSTALL_CITY

|| DECODE (ib.INSTALL_CITY, NULL, NULL, ', ')

|| ib.INSTALL_POSTAL_CODE

|| DECODE (ib.INSTALL_POSTAL_CODE,

NULL, NULL,

', ')

|| ib.INSTALL_STATE

|| DECODE (ib.INSTALL_STATE, NULL, NULL, ', ')

|| ib.INSTALL_PROVINCE

|| DECODE (ib.INSTALL_PROVINCE, NULL, NULL, ', ')

|| ib.INSTALL_COUNTRY))

installataddress,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

ib.NSTALL_SITE_ID)

installatsiteid,

DECODE (

IS_BILL_TO_CONTRACT_FILTER (aaa.partner,

ib.BILLTO_SITE_ID,

aaa.ROLE_NAME),

'TRUE', '',

ib.system_contact_name)

systemcontact,

DECODE (

ib.serviceable_flag,

1, 'Yes',

0, 'No',

(CASE

WHEN ib.serviceable_flag IS NULL

THEN

(DECODE (isServiceable (ims.equipmentid),

1, 'Yes',

'No'))

END))

AS serviceable,

ims.iscallhomecapable smartCallHomeCapable,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.equipmenttypedesc,

'-')

ccmProdDesc,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.productfamily,

'-')

ccmProdFamily,

DECODE (ims.DEVICE_TYPE, 'CCM', ims.productid, '-')

ccmOrginalPid,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.collectedpid,

'-')

ccmValidatedPid,

DECODE (

ims.DEVICE_TYPE,

'CCM', NVL (TO_CHAR (ib.ship_date, 'YYYY-Mon-DD'),

'-'),

'-')

ccmShipDate,

DECODE (ims.DEVICE_TYPE,

'CCM', ib.instance_number,

'-')

ccmC3InstanceId,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.serialnumber,

'-')

ccmOrigSerialNumber,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.collectedserialnumber,

'-')

ccmValidSerialNumber,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.softwareversion,

'-')

ccmSoftwareVersion,

DECODE (ims.DEVICE_TYPE, 'CCM', '-', '-')

ccmPlatformType,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.mainmemory,

TO_CHAR (ims.mainmemory), '-')

ccmMemory,

DECODE (ims.DEVICE_TYPE, 'CCM', '-', '-')

ccmDiskspace,

DECODE (ims.DEVICE_TYPE, 'CCM', ims.CPU_SPEED, '-')

ccmCPUSpeed,

DECODE (ims.DEVICE_TYPE,

'CCM', ims.softwaretype,

'-')

ccmOSType,

DECODE (ims.DEVICE_TYPE, 'CCM', ims.OSVERSION, '-')

ccmOSPatchVersion,

DECODE (ims.DEVICE_TYPE, 'CCM', '', '-') ccmClusterId,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.equipmenttypedesc,

'-')

ipPhoneProdDesc,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.productfamily,

'-')

ipPhoneProdFamily,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.productid,

'-')

ipPhoneOrginalPid,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.collectedpid,

'-')

ipPhoneValidatedPid,

DECODE (

ims.DEVICE_TYPE,

'IPPHONE', NVL (

TO_CHAR (ib.ship_date,

'YYYY-Mon-DD'),

'-'),

'-')

ipPhoneShipDate,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ib.instance_number,

'-')

ipPhoneC3InstanceId,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.serialnumber,

'-')

ipPhoneOrigSerialNumber,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.collectedserialnumber,

'-')

ipPhoneValidSerialNumber,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.EQUIPMENTTYPEDESC,

'-')

ipPhoneType,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.productname,

'-')

ipPhoneModel,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.ATTRIBUTE_ONE,

'-')

ipPhoneDirectryNumb,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.softwareversion,

'-')

ipPhoneSWVersion,

DECODE (ims.DEVICE_TYPE,

'IPPHONE', ims.macaddress,

'-')

ipPhoneMACAddress,

NVL (alert_cnt.TOTAL_HW_EOX_COUNT, 0) hwalertcount,

NVL (alert_cnt.TOTAL_SW_EOX_COUNT, 0) swalertcount,

NVL (alert_cnt.TOTAL_FIELD_NOTICES_COUNT, 0)

fnalertcount,

NVL (alert_cnt.TOTAL_PSIRT_COUNT, 0) psirtalertcount,

NVL (alert_cnt.TOTAL_INTELLSHIELD_COUNT, 0)

intellishieldAlertCount,

alert_cnt.RPT_ALERTS_COUNT_SUMMARY_OBJID

alertcountobjid

FROM v_ims_alldevices_eq_type ims,

(SELECT *

FROM v_inv_user_partner_cust_map

WHERE cco_id = 'swemohan'

AND customer_id = 9999002015026

AND inventory_id = 176701) aaa,

(SELECT *

FROM pam_rpt_alerts_count_summary

WHERE is_deleted = 0

AND party_id = 9999002015026

AND inventory_id = 176701) alert_cnt,

(SELECT *

FROM ib_contracts

WHERE is_delete = 0

AND party_id = 9999002015026

AND contract_status IN ('ACTIVE',

'OVERDUE',

'SIGNED',

'QA_HOLD')

AND coverage_line_status_text IN ('ACTIVE',

'OVERDUE',

'SIGNED',

'QA_HOLD')

AND service_line_status IN ('ACTIVE',

'OVERDUE',

'SIGNED',

'QA_HOLD')) ib

WHERE ims.equipmentid = ib.element_objid(+)

AND ims.isdeleted = 0

AND ims.equipmentid = alert_cnt.equipment_id(+)

AND ims.device_type IN ('CCM', 'IPPHONE')

AND ims.party_id = aaa.customer_id

AND ims.inventory_id = aaa.inventory_id

AND ims.inventory_id = 176701

AND aaa.cco_id = 'swemohan'

AND aaa.customer_id = 9999002015026))) alldev

WHERE ccmdev.networkresourceid = alldev.device_objid

GROUP BY ccmdev.hostname, ccmdev.ipaddress;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1321469687

---------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 236 | 2948 (1)| 00:00:01 | | |

| 1 | HASH GROUP BY | | 1 | 236 | 2948 (1)| 00:00:01 | | |

| 2 | NESTED LOOPS | | 1 | 236 | 2947 (1)| 00:00:01 | | |

| 3 | NESTED LOOPS OUTER | | 1 | 199 | 2620 (1)| 00:00:01 | | |

| 4 | NESTED LOOPS OUTER | | 1 | 170 | 2616 (1)| 00:00:01 | | |

| 5 | NESTED LOOPS | | 1 | 143 | 2616 (1)| 00:00:01 | | |

| 6 | NESTED LOOPS | | 1 | 104 | 9 (0)| 00:00:01 | | |

| 7 | NESTED LOOPS | | 1 | 82 | 6 (0)| 00:00:01 | | |

| 8 | NESTED LOOPS | | 1 | 44 | 4 (0)| 00:00:01 | | |

| 9 | NESTED LOOPS | | 1 | 31 | 3 (0)| 00:00:01 | | |

| 10 | TABLE ACCESS BY INDEX ROWID | PARTY_CA | 1 | 13 | 2 (0)| 00:00:01 | | |

|* 11 | INDEX UNIQUE SCAN | PARTY_CA_BK | 1 | | 1 (0)| 00:00:01 | | |

|* 12 | TABLE ACCESS BY INDEX ROWID | INVENTORYREPORTINGGROUP | 1 | 18 | 1 (0)| 00:00:01 | | |

|* 13 | INDEX UNIQUE SCAN | SYS_C00162815 | 1 | | 0 (0)| 00:00:01 | | |

| 14 | TABLE ACCESS BY INDEX ROWID | PARTY_CA | 1 | 13 | 1 (0)| 00:00:01 | | |

|* 15 | INDEX UNIQUE SCAN | SYS_C00162511 | 1 | | 0 (0)| 00:00:01 | | |

| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | C_USER_PARTNER_CUSTOMER_ASSOC | 1 | 38 | 2 (0)| 00:00:01 | | |

|* 17 | INDEX RANGE SCAN | C_USER_PARTNER_CUSTOMER_AS_N4 | 1 | | 1 (0)| 00:00:01 | | |

|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | AAA_PARTNER_CUSTOMER | 1 | 22 | 3 (0)| 00:00:01 | | |

|* 19 | INDEX RANGE SCAN | AAA_PARTNER_CUSTOMER_N4 | 2 | | 1 (0)| 00:00:01 | | |

| 20 | VIEW | | 2 | 78 | 2607 (1)| 00:00:01 | | |

| 21 | SORT UNIQUE | | 2 | 5350 | 2607 (1)| 00:00:01 | | |

| 22 | UNION-ALL | | | | | | | |

| 23 | NESTED LOOPS ANTI | | 1 | 1327 | 2440 (1)| 00:00:01 | | |

| 24 | NESTED LOOPS | | 1 | 1321 | 2429 (1)| 00:00:01 | | |

| 25 | PARTITION HASH SINGLE | | 1 | 1306 | 2426 (1)| 00:00:01 | 15 | 15 |

|* 26 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLDEVICES | 1 | 1306 | 2426 (1)| 00:00:01 | 15 | 15 |

|* 27 | INDEX RANGE SCAN | IDX_IMS_ALLDEVICES_13 | 4576 | | 27 (0)| 00:00:01 | 15 | 15 |

|* 28 | TABLE ACCESS BY INDEX ROWID BATCHED | NESAREININVENTORYGROUP | 1 | 15 | 3 (0)| 00:00:01 | | |

|* 29 | INDEX RANGE SCAN | NEINIRG_ASSOC_PK | 1 | | 2 (0)| 00:00:01 | | |

| 30 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 6 | 11 (0)| 00:00:01 | | |

| 31 | NESTED LOOPS SEMI | | 1 | 70 | 11 (0)| 00:00:01 | | |

| 32 | NESTED LOOPS | | 1 | 45 | 7 (0)| 00:00:01 | | |

| 33 | PARTITION HASH SINGLE | | 1 | 30 | 4 (0)| 00:00:01 | KEY | KEY |

|* 34 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| IMS_ALLDEVICES | 1 | 30 | 4 (0)| 00:00:01 | KEY | KEY |

|* 35 | INDEX RANGE SCAN | IDX_IMS_ALLDEVICES_1 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |

|* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | NESAREININVENTORYGROUP | 1 | 15 | 3 (0)| 00:00:01 | | |

|* 37 | INDEX RANGE SCAN | NEINIRG_ASSOC_PK | 1 | | 2 (0)| 00:00:01 | | |

| 38 | PARTITION HASH SINGLE | | 1 | 25 | 4 (0)| 00:00:01 |KEY(AP)|KEY(AP)|

|* 39 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 1 | 25 | 4 (0)| 00:00:01 |KEY(AP)|KEY(AP)|

|* 40 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX5 | 4 | | 2 (0)| 00:00:01 |KEY(AP)|KEY(AP)|

| 41 | NESTED LOOPS SEMI | | 1 | 1348 | 165 (0)| 00:00:01 | | |

| 42 | NESTED LOOPS | | 1 | 1333 | 162 (0)| 00:00:01 | | |

|* 43 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | IMS_ALLDEVICES | 6 | 7836 | 147 (0)| 00:00:01 | 15 | 15 |

|* 44 | INDEX RANGE SCAN | IDX_IMS_ALLDEVICES_18 | 538 | | 36 (0)| 00:00:01 | | |

| 45 | PARTITION HASH SINGLE | | 1 | 27 | 4 (0)| 00:00:01 | 15 | 15 |

|* 46 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 1 | 27 | 4 (0)| 00:00:01 | 15 | 15 |

|* 47 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX3 | 8 | | 2 (0)| 00:00:01 | 15 | 15 |

|* 48 | TABLE ACCESS BY INDEX ROWID BATCHED | NESAREININVENTORYGROUP | 319 | 4785 | 3 (0)| 00:00:01 | | |

|* 49 | INDEX RANGE SCAN | NEINIRG_ASSOC_PK | 1 | | 2 (0)| 00:00:01 | | |

| 50 | PARTITION HASH SINGLE | | 1 | 27 | 0 (0)| 00:00:01 | 15 | 15 |

|* 51 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | PAM_RPT_ALERTS_COUNT_SUMMARY | 1 | 27 | 0 (0)| 00:00:01 | 15 | 15 |

|* 52 | INDEX RANGE SCAN | IDX_PAM_RPT_ALRTS_CNT_SUMMRY_3 | 1 | | 0 (0)| 00:00:01 | 15 | 15 |

| 53 | PARTITION HASH SINGLE | | 1 | 29 | 4 (0)| 00:00:01 | 15 | 15 |

|* 54 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IB_CONTRACTS | 1 | 29 | 4 (0)| 00:00:01 | 15 | 15 |

|* 55 | INDEX RANGE SCAN | IDX_IB_CONTRACTS_13 | 1 | | 2 (0)| 00:00:01 | 15 | 15 |

| 56 | VIEW | BV_IMS_INV_CALL_MGR | 1 | 37 | 327 (2)| 00:00:01 | | |

| 57 | UNION ALL PUSHED PREDICATE | | | | | | | |

| 58 | SORT UNIQUE | | 1 | 245 | 161 (2)| 00:00:01 | | |

| 59 | NESTED LOOPS SEMI | | 1 | 245 | 160 (1)| 00:00:01 | | |

| 60 | NESTED LOOPS SEMI | | 1 | 217 | 7 (0)| 00:00:01 | | |

| 61 | PARTITION HASH SINGLE | | 1 | 205 | 5 (0)| 00:00:01 | 15 | 15 |

|* 62 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 1 | 205 | 5 (0)| 00:00:01 | 15 | 15 |

|* 63 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX3 | 8 | | 3 (0)| 00:00:01 | 15 | 15 |

|* 64 | INDEX RANGE SCAN | NEINIRG_ASSOC_PK | 1 | 12 | 2 (0)| 00:00:01 | | |

|* 65 | VIEW PUSHED PREDICATE | | 3 | 84 | 153 (1)| 00:00:01 | | |

| 66 | WINDOW SORT | | 3 | 225 | 153 (1)| 00:00:01 | | |

| 67 | NESTED LOOPS | | 3 | 225 | 152 (0)| 00:00:01 | | |

| 68 | NESTED LOOPS | | 3 | 225 | 152 (0)| 00:00:01 | | |

| 69 | PARTITION HASH SINGLE | | 2 | 126 | 144 (0)| 00:00:01 | KEY | KEY |

|* 70 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 2 | 126 | 144 (0)| 00:00:01 | KEY | KEY |

|* 71 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX3 | 326 | | 16 (0)| 00:00:01 | KEY | KEY |

|* 72 | INDEX RANGE SCAN | IDX_INVENTORYDEVICEGROUP_ID | 1 | | 2 (0)| 00:00:01 | | |

|* 73 | TABLE ACCESS BY INDEX ROWID | NESAREININVENTORYGROUP | 1 | 12 | 4 (0)| 00:00:01 | | |

| 74 | SORT UNIQUE | | 1 | 258 | 166 (2)| 00:00:01 | | |

| 75 | NESTED LOOPS SEMI | | 1 | 258 | 165 (2)| 00:00:01 | | |

| 76 | NESTED LOOPS SEMI | | 1 | 217 | 7 (0)| 00:00:01 | | |

| 77 | PARTITION HASH SINGLE | | 1 | 205 | 5 (0)| 00:00:01 | 15 | 15 |

|* 78 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 1 | 205 | 5 (0)| 00:00:01 | 15 | 15 |

|* 79 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX3 | 8 | | 3 (0)| 00:00:01 | 15 | 15 |

|* 80 | INDEX RANGE SCAN | NEINIRG_ASSOC_PK | 1 | 12 | 2 (0)| 00:00:01 | | |

|* 81 | VIEW PUSHED PREDICATE | | 3 | 123 | 158 (2)| 00:00:01 | | |

| 82 | WINDOW SORT | | 3 | 225 | 158 (2)| 00:00:01 | | |

| 83 | WINDOW SORT | | 3 | 225 | 158 (2)| 00:00:01 | | |

| 84 | NESTED LOOPS | | 3 | 225 | 156 (0)| 00:00:01 | | |

| 85 | NESTED LOOPS | | 3 | 225 | 156 (0)| 00:00:01 | | |

| 86 | PARTITION HASH SINGLE | | 3 | 189 | 144 (0)| 00:00:01 | KEY | KEY |

|* 87 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | IMS_ALLHOSTS | 3 | 189 | 144 (0)| 00:00:01 | KEY | KEY |

|* 88 | INDEX RANGE SCAN | IMS_ALLHOSTS_IDX3 | 326 | | 16 (0)| 00:00:01 | KEY | KEY |

|* 89 | INDEX RANGE SCAN | IDX_INVENTORYDEVICEGROUP_ID | 1 | | 2 (0)| 00:00:01 | | |

|* 90 | TABLE ACCESS BY INDEX ROWID | NESAREININVENTORYGROUP | 1 | 12 | 4 (0)| 00:00:01 | | |

---------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

11 - access("GROUPCUSTOMER"."PARTYID"=9999002015026)

12 - filter("IRG"."GROUPCUSTOMERPARTY_ID"="GROUPCUSTOMER"."ID")

13 - access("IRG"."INVENTORYREPORTINGGROUP_ID"=176701)

15 - access("IRG"."GROUPOWNER_ID"="GROUPOWNER"."ID")

17 - access("CP"."CCO_ID"='swemohan')

18 - filter("GROUPOWNER"."PARTYID"="PC"."PARTNER" AND "PC"."STATUS_OBJID"=53006 AND ("CP"."PARTNER"="PC"."PARTNER" AND

("CP"."CUSTOMER_ID"="PC"."CUSTOMER_ID" OR "CP"."CUSTOMER_ID"=(-999999) AND "ROLE_NAME"='PartnerAdmin') OR "ROLE_NAME"='CiscoPSSGloAppAdmin' AND

"CP"."PARTNER"=(-999999)))

19 - access("PC"."CUSTOMER_ID"=9999002015026)

26 - filter("IMS"."INVENTORYID"=176701 AND (CASE "IMS"."EQUIPMENTTYPE" WHEN 3 THEN 'Chassis' WHEN 106 THEN 'IPPHONE' ELSE 'Card' END ='CCM' OR

CASE "IMS"."EQUIPMENTTYPE" WHEN 3 THEN 'Chassis' WHEN 106 THEN 'IPPHONE' ELSE 'Card' END ='IPPHONE'))

27 - access("IMS"."PARTY_ID"=9999002015026 AND "IMS"."ISDELETED"=0)

filter("IMS"."ISDELETED"=0)

28 - filter("NES"."ISLASTCOLLECTION"=1)

29 - access("NES"."INVENTORYREPORTINGGROUP_ID"=176701 AND "IMS"."NETWORKRESOURCEID"="NES"."INVENTORYDEVICEGROUP_ID")

34 - filter("IMS"."INVENTORYID"="NES"."INVENTORYREPORTINGGROUP_ID" AND "IMS"."PARTY_ID"="IMS"."PARTY_ID" AND "IMS"."ISDELETED"=0 AND

"IMS"."EQUIPMENTTYPE"=3)

35 - access("IMS"."EQUIPMENTID"="IMS"."EQUIPMENTID")

36 - filter("NES"."ISLASTCOLLECTION"=1)

37 - access("NES"."INVENTORYREPORTINGGROUP_ID"="NES"."INVENTORYREPORTINGGROUP_ID" AND

"IMS"."NETWORKRESOURCEID"="NES"."INVENTORYDEVICEGROUP_ID")

filter("IMS"."INVENTORYID"="NES"."INVENTORYREPORTINGGROUP_ID")

39 - filter("HOS"."INV_ID"="NES"."INVENTORYREPORTINGGROUP_ID" AND "NES"."INVENTORYREPORTINGGROUP_ID"="HOS"."INV_ID" AND

"HOS"."SOFTWARETYPE"='CallManager')

40 - access("HOS"."PARTY_ID"="IMS"."PARTY_ID" AND "IMS"."NETWORKRESOURCEID"="HOS"."NETWORKRESOURCEID" AND "HOS"."IS_DELETE"=0)

filter("HOS"."IS_DELETE"=0 AND "IMS"."PARTY_ID"="HOS"."PARTY_ID")

43 - filter("IMS"."INVENTORYID"=176701 AND "IMS"."EQUIPMENTTYPE"=3)

44 - access("IMS"."PARTY_ID"=9999002015026 AND "IMS"."ISDELETED"=0)

filter("IMS"."ISDELETED"=0)

46 - filter("HOS"."INV_ID"=176701 AND "HOS"."SOFTWARETYPE"='CallManager')

47 - access("HOS"."PARTY_ID"=9999002015026 AND "IMS"."NETWORKRESOURCEID"="HOS"."NETWORKRESOURCEID" AND "HOS"."IS_DELETE"=0)

48 - filter("NES"."ISLASTCOLLECTION"=1)

49 - access("NES"."INVENTORYREPORTINGGROUP_ID"=176701 AND "IMS"."NETWORKRESOURCEID"="NES"."INVENTORYDEVICEGROUP_ID")

51 - filter("PARTY_ID"(+)=9999002015026 AND "EQUIPMENTID"="PAM_RPT_ALERTS_COUNT_SUMMARY"."EQUIPMENT_ID"(+) AND "IS_DELETED"(+)=0)

52 - access("INVENTORY_ID"(+)=176701)

54 - filter("PARTY_ID"(+)=9999002015026 AND ("SERVICE_LINE_STATUS"(+)='ACTIVE' OR "SERVICE_LINE_STATUS"(+)='OVERDUE' OR

"SERVICE_LINE_STATUS"(+)='QA_HOLD' OR "SERVICE_LINE_STATUS"(+)='SIGNED') AND ("CONTRACT_STATUS"(+)='ACTIVE' OR "CONTRACT_STATUS"(+)='OVERDUE' OR

"CONTRACT_STATUS"(+)='QA_HOLD' OR "CONTRACT_STATUS"(+)='SIGNED') AND ("COVERAGE_LINE_STATUS_TEXT"(+)='ACTIVE' OR

"COVERAGE_LINE_STATUS_TEXT"(+)='OVERDUE' OR "COVERAGE_LINE_STATUS_TEXT"(+)='QA_HOLD' OR "COVERAGE_LINE_STATUS_TEXT"(+)='SIGNED'))

55 - access("IS_DELETE"(+)=0 AND "EQUIPMENTID"="IB_CONTRACTS"."ELEMENT_OBJID"(+))

62 - filter("IMS"."SOFTWARETYPE"='CallManager' AND "IMS"."INSTALLEDVERSION"<>'UNKNOWN')

63 - access("PARTY_ID"=9999002015026 AND "NETWORKRESOURCEID"="NETWORKRESOURCEID" AND "IMS"."IS_DELETE"=0)

64 - access("NEIIG"."INVENTORYREPORTINGGROUP_ID"=176701 AND "NEIIG"."INVENTORYDEVICEGROUP_ID"="NETWORKRESOURCEID")

filter("NEIIG"."INVENTORYDEVICEGROUP_ID"="IMS"."NETWORKRESOURCEID")

65 - filter("IMS"."IMS_HOST_OBJID"="IMSHOST"."IMS_HOST_OBJID" AND "RNKORD"=1)

70 - filter("SOFTWARETYPE"='CallManager' AND "INSTALLEDVERSION"<>'UNKNOWN')

71 - access("PARTY_ID"="IMS"."PARTY_ID" AND "IS_DELETE"=0)

filter("IS_DELETE"=0)

72 - access("NESIAG"."INVENTORYDEVICEGROUP_ID"="NETWORKRESOURCEID")

73 - filter("NESIAG"."INVENTORYREPORTINGGROUP_ID" IS NOT NULL)

78 - filter("IMS"."INSTALLEDVERSION"='UNKNOWN' AND "IMS"."SOFTWARETYPE"='CallManager')

79 - access("PARTY_ID"=9999002015026 AND "NETWORKRESOURCEID"="NETWORKRESOURCEID" AND "IMS"."IS_DELETE"=0)

80 - access("NEIIG"."INVENTORYREPORTINGGROUP_ID"=176701 AND "NEIIG"."INVENTORYDEVICEGROUP_ID"="NETWORKRESOURCEID")

filter("NEIIG"."INVENTORYDEVICEGROUP_ID"="IMS"."NETWORKRESOURCEID")

81 - filter("IMS"."IMS_HOST_OBJID"="IMSHOST"."IMS_HOST_OBJID" AND "RNKORD"=1 AND "CNT"=1)

87 - filter("SOFTWARETYPE"='CallManager')

88 - access("PARTY_ID"="IMS"."PARTY_ID" AND "IS_DELETE"=0)

filter("IS_DELETE"=0)

89 - access("NESIAG"."INVENTORYDEVICEGROUP_ID"="NETWORKRESOURCEID")

90 - filter("NESIAG"."INVENTORYREPORTINGGROUP_ID" IS NOT NULL)

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 28 2018

Added on Jul 31 2018

#sql, #sql-pl-sql

12 comments

321 views