Forum OpenACS Development: Response to ACS 4.x won't scale (I hope I am wrong)
Hm, well, the trace for you're Query 2 has a "MERGE JOIN CARTESIAN" (167707 rows) in it. That sure seems suspicious... I just tried a similar query:
on my patched ACS 4.2 Dev site, and I did not get a cartesian join. When I run tkprof on the trace file, I see only 4 query rows, vs. 335512 rows on your system.select decode( count(*), 0,'f', 't') from dual where exists ( select 'x' from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm where object_id = 5204 and rsmm.member_id = 3305 and privilege = 'admin' and ogpm.grantee_id = rsmm.segment_id );
Now, I have even less data in my Dev system then you do, I don't have BBoard installed, and my users, groups, and permissions are different from yours, so none of this is conclusive. That merge join you're getting has got to be symptomatic of some problem, though... But whether it's something weird on your site, or a problem common to OpenACS, I don't know.
You're sure your tables are analyzed? If not, try:
(A percent of 20 or so should be good enough.)
begin dbms_stats.gather_schema_stats (:db_user, :percent, cascade => true); end;
Also, what sort of performance difference are you seeing, anyway, time-wise?