High Physical Access On LOGICALDEVICE_CHAR (Doc ID 1472546.1)

Last updated on MARCH 20, 2019

Applies to:

Symptoms

There is a performance issue in the LOGICALDEVICE_CHAR table.
There is a high physical access on this table.
An index on the VALUE column is recommended to improve its performance.

The query that shows the problem is:

SELECT DISTINCT t0.jdoid, t0.jdoclass, t0.jdoversion, t0.activity, t0.adminstate, t0.description, t0.enddate, t0.id, t0.lastmodifieddate,
 t0.lastmodifieduser, t0.name, t0.nospec, t0.objectstate, t0.owner,
 t0.PARTITION, t0.permissions, t3.jdoid, t3.jdoclass, t3.jdoversion,
 t3.description, t3.enddate, t3.enteridmanually, t3.instancetype,
 t3.lastmodifieddate, t3.lastmodifieduser, t3.name, t3.owner,
 t3.PARTITION, t3.permissions, t3.startdate, t3.type,
 t4.lastmodifieddate, t4.lastmodifieduser, t2.consumablemaybeshared,
 t2.consumerallowsharedconsumption, t2.lastmodifieddate,
 t2.lastmodifieduser, t2.modelnumber, t2.partnumber, t2.vendorname,
 t0.startdate
FROM uim.logicaldevice t0, uim.logicaldevicespecification t2,
 uim.logicaldevice_char t1, uim.resourcespecification t4,
 uim.specification t3
WHERE(t1.name = :1
AND t1.VALUE = :2
AND(t0.objectstate = :3 OR t0.objectstate = :4 OR t0.objectstate IS NULL))
AND t0.jdoclass = :5
AND t0.jdoid = t1.logicaldevice
AND t0.specification = t2.jdoid(+)
AND t2.jdoid = t4.jdoid(+)
AND t4.jdoid = t3.jdoid(+)
ORDER BY t0.id ASC

which is probably used by this UIM API call:

LogicalDeviceManager ldManager = InventoryHelper.makeLogicalDeviceManager();
LogicalDeviceSearchCriteria criteria = ldManager.makeLogicalDeviceSearchCriteria();
CriteriaItem characData = criteria.makeCriteriaItem();
characData.setName("ICCID");
characData.setValue(iccid);
characData.setCriteriaClass(LogicalDeviceCharacteristic.class);
criteria.setCharacteristicData(characData);
ld = (ArrayList) ldManager.findLogicalDevice(criteria);

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.