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
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 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)