DTreeACL Problems

Hi,Does anyone know how to avoid major performance problems cused by a very large DTreeACL (app. 600.000 rows)?It gets scanned "from head to toe" every time a normal user views or fetches a document. Admin doesn't own any docs and doesn't have the problem.

Find more posts tagged with

Comments

eLink User

Message from Jonathan Coombes <<A HREF="mailto:jcoombes@opentext.com">jcoombes@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi,

This is uncanny. I have a similar if not same problem at a customer in the UK. We are running ORacle 8i on NT with DTreeACL at around 2.7 million rows.

We experienced full table scans during search permission application, project browsing and a number of other operations. (Sometimes taking around 35 secs ++ to complete a query).

We looked a throwing an index on the RightID column, which in our test environment brought the query time right down (to about 1.5 secs fastest but suspect we could do better). Problem is, when we roll this onto a production database, Oracle starts breaking because it doesn't like the extremely large inlists generated by LL.

The error we hit is : ORA-1037 maximum cursor memory, which according to Oracle, is bad news since you've hit a hard-coded limit within the Oracle database server. (Yikes) They suggest changing the SQL generated by the application. I've attached a typical SQL query so you can see what's causing the problems. We are currently working on a solution, when we find one I'll let you know.

I'm sure someone must have seen this or something similar before - any takers?

Cheers

Jonny

-----Original Message-----
From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of eLink Discussion: Development Discussion
Sent: 30 October 2001 15:43
To: eLink Recipient
Subject: DTreeACL Problems

DTreeACL Problems Posted by SiemensICNUser31 (Petrovic, Borko) on 10/30/2001 10:39 AM

Hi,

Does anyone know how to avoid major performance problems cused by a very large DTreeACL (app. 600.000 rows)?
It gets scanned "from head to toe" every time a normal user views or fetches a document. Admin doesn't own any docs and doesn't have the problem.

Dylan_Wright_(xisdylan_-_(deleted))

May be unrelated, but I have seen an error much like yours ( ORACLE error: too many cached cursors ). As far as i can tell, it only happens when sql logging is enabled ( WANTLOGS=TRUE in opentext.ini )

jamespate

The Projects tab and News tab in Personal Workspace takes over three minutes. This chokes the Livelink threads when a user tries to view his projects or news.As it is not a problem for Admin I can only assume it is permissions checking. If someone knows of a suitable index or other ways of improving this, please, please post.

John W. Simon, Jr.

We engaged an Opentext consultant and they suggested that we creat two indexes to help with the projects tab. The first was on dtree(subtype, userid, dataid). The second was on dtreeacl(rightid, dataid, see, permission).Our big problem is with tasks in the personal workspace. Anyone else have this problem (we think it is related to the number of projects a person is a participant in).Regards,John Simon