[opencms-dev] Oracle performance issues

[Jordi Pedrerol]

Hi Tobias,

Thank you for the information.

Kind regards.


Jordi Pedrerol

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

Date: Fri, 28 Jun 2013 16:40:26 +0200
From: Tobias Herrmann <t.herrmann at alkacon.com>
To: The OpenCms mailing list <opencms-dev at opencms.org>
Subject: Re: [opencms-dev] Oracle performance issues
Message-ID: <51CDA05A.7010408 at alkacon.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hi Jordi,

we did some load testing regarding the redundant index issue.
It seems removing the index does improve the overall performance slightly.
So I think it's save to drop that index.

Greetings, Tobias

--

Alkacon Software GmbH - The OpenCms Experts

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

Am 26.06.2013 12:52, schrieb Jordi Pedrerol:
> Hi Tobias,
>
> Thank you for your help.
>
> If you need more information with this issue or if I can help you in some
> way do not hesitate asking me anything you need.
>
>
> Kind regards.
>
>
> Jordi Pedrerol
>
> Adequa Software
> C/ C?rsega 267, Principal 2a B
> Barcelona
> telf: 93.528.56.00
> http://www.adequa.cat
>
> -----Mensaje original-----
>
> Date: Wed, 26 Jun 2013 10:10:43 +0200
> From: Tobias Herrmann <t.herrmann at alkacon.com>
> To: The OpenCms mailing list <opencms-dev at opencms.org>
> Subject: Re: [opencms-dev] Oracle performance issues
> Message-ID: <51CAA203.3080403 at alkacon.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> 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