[opencms-dev] Oracle performance issues

Fri Jun 28 16:40:26 CEST 2013

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
>>
>>
>>
>
>
> ------------------------------
>
> Message: 7
> Date: Wed, 26 Jun 2013 08:21:40 +0000
> From: Sandrine Prousteau <s.prousteau at eurelis.com>
> To: The OpenCms mailing list <opencms-dev at opencms.org>
> Subject: Re: [opencms-dev] OpenCms 8.5.1 - OpenCmsDateTime and 31
> 	December
> Message-ID:
> 	
> <B9D2053F23EEBA4494A360926C314F474F9199E1 at AMSPRD0610MB374.eurprd06.prod.outl
> ook.com>
> 	
> Content-Type: text/plain; charset="iso-8859-1"
>
> Confirmed on 8.5.1
>
> -----Message d'origine-----
> De?: opencms-dev-bounces at opencms.org
> [mailto:opencms-dev-bounces at opencms.org] De la part de Cavva
> Envoy??: jeudi 20 juin 2013 14:42
> ??: opencms-dev at opencms.org
> Objet?: [opencms-dev] OpenCms 8.5.1 - OpenCmsDateTime and 31 December
>
> Hi all,
>
> I noticed some strange behaviours about the OpenCmsDateTime.
> If you pick 31 december of any year it selects 1 december of that year.
>
> somebody can confirm it?
>
> regards,
> Davide
>
>
>
> --
> View this message in context:
> http://opencms.996256.n3.nabble.com/OpenCms-8-5-1-OpenCmsDateTime-and-31-Dec
> ember-tp23380.html
> Sent from the OpenCMS mailing list archive at Nabble.com.
> _______________________________________________
> 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
>
>
>
>
>
>
>
> ------------------------------
>
> Message: 8
> Date: Wed, 26 Jun 2013 01:48:06 -0700 (PDT)
> From: Cavva <davide.cavarretta at gmail.com>
> To: opencms-dev at opencms.org
> Subject: Re: [opencms-dev] OpenCms 8.5.1 - OpenCmsDateTime and 31
> 	December
> Message-ID: <1372236486198-23395.post at n3.nabble.com>
> Content-Type: text/plain; charset=us-ascii
>
> Thank you Sandrine
>
> :-)
>
>
>
> --
> View this message in context:
> http://opencms.996256.n3.nabble.com/OpenCms-8-5-1-OpenCmsDateTime-and-31-Dec
> ember-tp23380p23395.html
> Sent from the OpenCMS mailing list archive at Nabble.com.
>
>
> ------------------------------
>
> Message: 9
> Date: Wed, 26 Jun 2013 10:58:20 +0200
> From: Tobias Herrmann <t.herrmann at alkacon.com>
> To: The OpenCms mailing list <opencms-dev at opencms.org>
> Subject: Re: [opencms-dev] OpenCms 8.5.1 - OpenCmsDateTime and 31
> 	December
> Message-ID: <51CAAD2C.70307 at alkacon.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Sandrine,
>
> we have fixed this issue on github. The fix will be included in the upcoming
> 8.5.2 release.
>
> Greetings, Tobias
>
> --
>
> Alkacon Software GmbH - The OpenCms Experts
>
> http://www.alkacon.com
> http://www.opencms.org
>
> Am 26.06.2013 10:48, schrieb Cavva:
>> Thank you Sandrine
>>
>> :-)
>>
>>
>>
>> --
>> View this message in context:
> http://opencms.996256.n3.nabble.com/OpenCms-8-5-1-OpenCmsDateTime-and-31-Dec
> ember-tp23380p23395.html
>> Sent from the OpenCMS mailing list archive at Nabble.com.
>> _______________________________________________
>> 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
>>
>>
>>
>
>
> ------------------------------
>
> _______________________________________________
> 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
>
> End of opencms-dev Digest, Vol 87, Issue 3
> ******************************************
>
> _______________________________________________
> 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
>
>
>