We have a production site running Classic ACS4 with about 100,000
acs_objects. We noticed that the main file storage query was running
pretty slowly and that a dramatic improvement could be achieved by
removing the permission clause. It appears that Oracle wasn't being
smart about running the permission check late in the game. Since the
expensive permission check should be run on as few rows as possible,
we've re-arranged the query to coax Oracle into doing the right thing.
A quick glance at the head of CVS for
packages/file-storage/www/index-oracle.xql suggests that this may be a
problem for OpenACS4 as well, so I'll post our re-arranged query:
select file_id,name,live_revision,path,type,last_modified,
content_size,ordering_key,folder_id from (
select i.item_id as file_id,
r.title as name,
i.live_revision,
content_item.get_path(i.item_id,file_storage.get_root_folder([ad_conn
package_id])) as path,
r.mime_type as type,
to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
dbms_lob.getlength(r.content) as content_size,
1 as ordering_key,
i.parent_id as folder_id
from cr_items i, cr_revisions r, acs_objects o
where i.item_id = o.object_id
and i.live_revision = r.revision_id (+)
and i.parent_id = :folder_id
and i.content_type = 'content_revision'
) where 't' = (select acs_permission.permission_p(file_id, :user_id,
'read') from dual)
UNION
select file_id,name,live_revision,path,type,last_modified,
content_size,ordering_key,folder_id from (
select i.item_id as file_id,
f.label as name,
0 as live_revision,
content_item.get_path(f.folder_id) as path,
'Folder' as type,
NULL as last_modified,
0 as content_size,
0 as ordering_key,
f.folder_id as folder_id
from cr_items i, cr_folders f
where i.item_id = f.folder_id
and i.parent_id = :folder_id
) where 't' = (select acs_permission.permission_p(folder_id, :user_id,
'read') from dual)
order by ordering_key,name
The trick here is that we push the UNION'ed queries into views on the
fly, and then do a correlated subquery for permission. You'd think we
could just do "...where acs_permission.permission_p() = 't'" instead
of the "select ... from dual" construct, but in the former case we get
no performance improvement. Presumably the Oracle optimizer is
"simplifying" the query behind the scenes. I did briefly fool around
with hints to see if I could force Oracle not to re-arrange the query,
but without success.
In any case, we did see a dramatic improvement with form above.
