[opencms-dev] Oracle performance issues

[Jordi Pedrerol]

Hello,

We are using an OpenCms 8.5.0 connected to an Oracle database and we are
facing some Oracle performance issues.

The issue is a bit weird because we made several tests installing the
OpenCms in different schemas and we found performance issues only in some of
them while the performance was right in the others.

After further investigation we noticed that the problem comes from the
following query (the main query for retrieving resource information. In the
example the OFFLINE tables are used, but the issue is also produced in the
ONLINE tables):


SELECT
CMS_OFFLINE_STRUCTURE.STRUCTURE_ID,CMS_OFFLINE_STRUCTURE.RESOURCE_ID,CMS_OFF
LINE_STRUCTURE.RESOURCE_PATH,CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE,CMS_OFFLI
NE_STRUCTURE.DATE_RELEASED,CMS_OFFLINE_STRUCTURE.DATE_EXPIRED,CMS_OFFLINE_ST
RUCTURE.STRUCTURE_VERSION,CMS_OFFLINE_RESOURCES.RESOURCE_ID,CMS_OFFLINE_RESO
URCES.RESOURCE_TYPE,CMS_OFFLINE_RESOURCES.RESOURCE_FLAGS,CMS_OFFLINE_RESOURC
ES.RESOURCE_STATE,CMS_OFFLINE_RESOURCES.DATE_CREATED,CMS_OFFLINE_RESOURCES.D
ATE_LASTMODIFIED,CMS_OFFLINE_RESOURCES.USER_CREATED,CMS_OFFLINE_RESOURCES.US
ER_LASTMODIFIED,CMS_OFFLINE_RESOURCES.PROJECT_LASTMODIFIED AS
LOCKED_IN_PROJECT,CMS_OFFLINE_RESOURCES.RESOURCE_SIZE,CMS_OFFLINE_RESOURCES.
DATE_CONTENT,CMS_OFFLINE_RESOURCES.SIBLING_COUNT,CMS_OFFLINE_RESOURCES.RESOU
RCE_VERSION,CMS_OFFLINE_RESOURCES.PROJECT_LASTMODIFIED FROM
CMS_OFFLINE_STRUCTURE,CMS_OFFLINE_RESOURCES WHERE
CMS_OFFLINE_STRUCTURE.RESOURCE_PATH=? AND
CMS_OFFLINE_STRUCTURE.RESOURCE_ID=CMS_OFFLINE_RESOURCES.RESOURCE_ID ORDER BY
CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE ASC


The table CMS_OFFLINE_STRUCTURE has 7 indexes defined.

In the problematic schemas the index used by Oracle when executing the above
query is the index CMS_OFFLINE_STRUCTURE_07_IDX.

On the other hand, the schemas where the performance is good are using the
index CMS_OFFLINE_STRUCTURE_02_IDX when executing the above query.

An Oracle expert told us that CMS_OFFLINE_STRUCTURE_07_IDX index is
redundant and recommended us to drop it because redundant indexes may
confuse Oracle when choosing the proper index to use.

Effectively, after dropping the CMS_OFFLINE_STRUCTURE_07_IDX index in a
problematic schema, the query was executed with a good performance.


Does anybody know if dropping the CMS_OFFLINE_STRUCTURE_07_IDX index may
affect the system negatively?

Has anybody found similar issues and found different approaches to solve the
problem?


Thanks in advance.




Jordi Pedrerol

Adequa Software 
C/ Còrsega 267, Principal 2a B 
Barcelona 
telf: 93.528.56.00 
http://www.adequa.cat