[opencms-dev] Oracle performance issues

Wed Jun 26 12:52:32 CEST 2013

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
******************************************