Tuning a SQL statement with expensive function calls

From: Dirk Gomez <usenet_at_dirkgomez.de>
Date: 23 Jan 2002 16:52:17 +0100
Message-ID: <m34rldm5e6.fsf@colorado.arsdigita.de>

(Version 8.1.7, Solaris)
I have this SQL statement

select g.group_id, g.group_name,
  nvl(cr.group_id,0) as parent_id,
  decode(acs_permission.permission_p(g.group_id, :user_id, 'admin'), 'f',0,1)   as admin_p,
  decode(acs_permission.permission_p(g.group_id, :user_id, 'group_read_name'),   'f',0,1) as visible_p
  from groups g, orga_group_ext og, composition_rels cr   where og.group_id = g.group_id
  and cr.component_id(+) = g.group_id
  order by lower(g.group_name)

The call to acs_permission.permission_p is pretty, pretty expensive. The relation between groups and orga_group_ext is 1:n.

The Execution Plan looks like this:

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=120 Bytes=504
          0)
   1    0   SORT (ORDER BY) (Cost=6 Card=120 Bytes=5040)
   2    1     HASH JOIN (OUTER) (Cost=4 Card=120 Bytes=5040)
   3    2       NESTED LOOPS (Cost=2 Card=74 Bytes=2516)
   4    3         TABLE ACCESS (FULL) OF 'GROUPS' (Cost=1 Card=514 Byt
          es=15420)
   5    3         INDEX (UNIQUE SCAN) OF 'ORGA_GROUP_EXT_GROUP_ID_PK'
          (UNIQUE)
   6    2       INDEX (FULL SCAN) OF 'COMPOSITION_RELS_COMP_GR_IDX' (U
          NIQUE) (Cost=3 Card=605 Bytes=4840)

I have been trying to push the acs_permission.permission_p call further down. Something like this:

select nvl(cr.group_id,0) as parent_id, g.group_id, g.group_name, g.admin_p, g.visible_p from
(select * from (select g.group_id, g.group_name,   decode(acs_permission.permission_p(g.group_id, :user_id, 'admin'), 'f',0,1)   as admin_p,
  decode(acs_permission.permission_p(g.group_id, :user_id, 'group_read_name'),   'f',0,1) as visible_p

     from groups g, orga_group_ext og
     where og.group_id = g.group_id
      order by lower(g.group_name)) ) g, composition_rels cr

where cr.component_id(+) = g.group_id;

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=133 Bytes=105
          07)
   1    0   HASH JOIN (OUTER) (Cost=6 Card=133 Bytes=10507)
   2    1     VIEW (Cost=4 Card=63 Bytes=4473)
   3    2       SORT (ORDER BY) (Cost=4 Card=63 Bytes=2142)
   4    3         NESTED LOOPS (Cost=2 Card=63 Bytes=2142)
   5    4           TABLE ACCESS (FULL) OF 'GROUPS' (Cost=1 Card=514 B
          ytes=15420)
   6    4           INDEX (UNIQUE SCAN) OF 'ORGA_GROUP_EXT_GROUP_ID_PK
          ' (UNIQUE)
   7    1     INDEX (FULL SCAN) OF 'COMPOSITION_RELS_COMP_GR_IDX' (UNI
          QUE) (Cost=3 Card=605 Bytes=4840)

Consistent gets go down from ~29.300 to ~5.600. Is there a way to squeeze out more?

The function permission_p looks like this:   function permission_p (
    object_id acs_objects.object_id%TYPE,     party_id parties.party_id%TYPE,
    privilege acs_privileges.privilege%TYPE   ) return char
  as
    exists_p char(1);
  begin
--

• Check direct permissions select decode( count(*), 0,'f', 't') into exists_p from dual where exists ( select /*+ FIRST_ROWS*/ 'x' from acs_object_grantee_priv_map where object_id = permission_p.object_id and grantee_id = permission_p.party_id and privilege = permission_p.privilege); if exists_p = 't' then return 't'; end if;
  --
  
• Check public permissions select decode( count(*), 0,'f', 't') into exists_p from dual where exists ( select /*+ FIRST_ROWS*/ 'x' from acs_object_grantee_priv_map m, users u where object_id = permission_p.object_id and u.user_id = permission_p.party_id and privilege = permission_p.privilege and m.grantee_id = -1); if exists_p = 't' then return 't'; end if;
  --
  
• Check group permmissions select decode( count(*), 0,'f', 't') into exists_p from dual where exists ( select /*+ ORDERED*/ 'x' from acs_object_grantee_priv_map ogpm, group_approved_member_map gmm where object_id = permission_p.object_id and gmm.member_id = permission_p.party_id and privilege = permission_p.privilege and ogpm.grantee_id = gmm.group_id); if exists_p = 't' then return 't'; end if;
  --
  
• relational segment approved group select decode( count(*), 0,'f', 't') into exists_p from dual where exists ( select /*+ FIRST_ROWS*/ 'x' from acs_object_grantee_priv_map ogpm, rel_seg_approved_member_map rsmm where object_id = permission_p.object_id and rsmm.member_id = permission_p.party_id and privilege = permission_p.privilege and ogpm.grantee_id = rsmm.segment_id); if exists_p = 't' then return 't'; end if;
  --
  
• Check public-like permissions select decode( count(*), 0,'f', 't') into exists_p from dual where exists ( select /*+ FIRST_ROWS*/ 'x' from acs_object_grantee_priv_map where object_id = permission_p.object_id and 0 = permission_p.party_id and privilege = permission_p.privilege and grantee_id = -1);
  --
  return exists_p; end;

So basically it's a chain of 5 if statements and the only thing I'm interested in is whether there's at least one permissioning record. Hence the explicit FIRST_ROWS hint.

regards Dirk Received on Wed Jan 23 2002 - 09:52:17 CST