[opencms-dev] Oracle performance issues

[Tobias Herrmann]

Hi Jordi,

that's an interesting issue.

It seems you are right, and the mentioned index is redundant.

We will do some load testing and check if dropping that index causes any other issue.

I will let you know of our findings.

Greetings, Tobias


--

Alkacon Software GmbH - The OpenCms Experts

http://www.alkacon.com
http://www.opencms.org

Am 25.06.2013 20:58, schrieb 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
>
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/cgi-bin/mailman/listinfo/opencms-dev
>
>
>