Alfresco, Hibernate, and ORA-01795 on Production

[jack_jin]

Hello,

We are currently using alfresco 3.0c running on a dedicated linux server.  As we load more content with alfresco's wcm system, we encountered an error didn't exist before.  While using alfresco's native UI, we now encounter an error doing the following:

1) Login to alfresco as admin (admin is a content manager in our web project)
2) GO to the web project on the left nav
3) By trying to expand modified items in a heavily used sandbox, we have to wait 20 minutes ish, then we get the transaction is rolling back error.

Looking at the logs, there's a SQL error underneath that's being caused by WCMWorkflowEvaluator.  After turning on hibernate logging, we've found the following error while alfresco's trying to expand modified items

16:54:13,615 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1795, SQLState: 42000
16:54:13,615 ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-01795: maximum number of expressions in a list is 1000‍‍

After digging deeper into logs by enabling debug statements from org.hibernate.SQL, I found the following SQL statement right before the above error

16:54:13,600 DEBUG [org.hibernate.SQL] select this_.ID_ as ID1_70_1_, this_.VERSION_ as VERSION3_70_1_, this_.NAME_ as NAME4_70_1_, this_.DESCRIPTION_ as DESCRIPT5_70_1_, this_.ACTORID_ as ACTORID6_70_1_, this_.CREATE_ as CREATE7_70_1_, this_.START_ as START8_70_1_, this_.END_ as END9_70_1_, this_.DUEDATE_ as DUEDATE10_70_1_, this_.PRIORITY_ as PRIORITY11_70_1_, this_.ISCANCELLED_ as ISCANCE12_70_1_, this_.ISSUSPENDED_ as ISSUSPE13_70_1_, this_.ISOPEN_ as ISOPEN14_70_1_, this_.ISSIGNALLING_ as ISSIGNA15_70_1_, this_.ISBLOCKING_ as ISBLOCKING16_70_1_, this_.TASK_ as TASK17_70_1_, this_.TOKEN_ as TOKEN18_70_1_, this_.PROCINST_ as PROCINST19_70_1_, this_.SWIMLANINSTANCE_ as SWIMLAN20_70_1_, this_.TASKMGMTINSTANCE_ as TASKMGM21_70_1_, this_.CLASS_ as CLASS2_70_1_, processins1_.ID_ as ID1_63_0_, processins1_.VERSION_ as VERSION2_63_0_, processins1_.KEY_ as KEY3_63_0_, processins1_.START_ as START4_63_0_, processins1_.END_ as END5_63_0_, processins1_.ISSUSPENDED_ as ISSUSPEN6_63_0_, processins1_.PROCESSDEFINITION_ as PROCESSD7_63_0_, processins1_.ROOTTOKEN_ as ROOTTOKEN8_63_0_, processins1_.SUPERPROCESSTOKEN_ as SUPERPRO9_63_0_ from JBPM_TASKINSTANCE this_ inner join JBPM_PROCESSINSTANCE processins1_ on this_.PROCINST_=processins1_.ID_ where this_.ISOPEN_=? and this_.END_ is null and processins1_.END_ is null and processins1_.ID_ in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)‍

Looking at the SQL, we have about 100 expressions, but for some reason, we have over 1000 ?s.  Is this intentional?  It seems to be directly related to ORA-01795.  Oracle thinks we're executing a SQL query with over 1000 expressions, because we have over ?s.

This is happening on our production system and our website is scheduled to go live tomorrow.  If anyone have any advice on how to avoid or solve this issue, please let me know.

Thanks for your time,
Jack

[jack_jin]

Also, would upgrading to Alfresco 3.1 would fix this?

[jack_jin]

Our site went live with the issue unfixed.  Any help would be greatly appreciated.

[jack_jin]

^

[zaizi]

By trying to expand modified items in a heavily used sandbox, we have to wait 20 minutes ish, then we get the transaction is rolling back error.

Can you qualify how heavily the sandbox is used?

Did you just trying running your site on Enterprise 3.1?

[tommorris]

Hi there,

We're experiencing similar problems. The more files you have in the modified-items list, the slower it gets. It gets even slower when those items have been submitted to a workflow that is 'in-flight'. Or, more accurately, the greater the number of *separate* workflows instances.

The problem is exacerbated with certain hardware, I believe.

We have an open JIRA request for engineers to look into this.

Tom

[whybish]

Even if there were less than 1000 anonymous parameters that sql would still perform poorly.  The IN clause is slow because it cannot use indexes effectively.  Rather than finding all process instances with given ids, this stuff would need to be refactored to be looking at some flag on process instance.

[jack_jin]

By trying to expand modified items in a heavily used sandbox, we have to wait 20 minutes ish, then we get the transaction is rolling back error.

Can you qualify how heavily the sandbox is used?

Did you just trying running your site on Enterprise 3.1?

Sorry for the late reply to the thread.

The sandbox is around 1500 versions and has about 200-300 files.  The problem still exist on production today.

After doing two more projects on alfresco 3.1, Alfresco 3.1 doesn't seem to have this issue.  The queries in Alfresco 3.1 seems to be more efficient when it comes to fetching a list of modified items.

I wonder if there's a way to patch it for 3.0, since Alfresco doesn't release oracle scripts to update alfresco 3.0 to 3.1.

[fracat71]

Hi all,
here a possible solution:
http://forums.alfresco.com/en/viewtopic.php?f=14&t=26355&p=88594#p88594

Regards