Invoker Rights - Shared pool Utilization

When using Invoker Rights we have a single procedure access data in different schema, depending on who is running the query at run time. Hence there will be multiple copies of SQL statements available in shared pool one per each user. But when using definer rights procedure, there is at most once copy of SQL statement in shared pool that will be reused by multiple users.

I, created Ten users via this script:

rajesh@10GR2> begin
  2     for x in 1..10
  3     loop
  4             execute immediate ' create user u'||x||' identified by u'||x ;
  5             execute immediate ' grant connect,resource to u'||x;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
rajesh@10GR2>

rajesh@10GR2> create table t(x number);

Table created.

Elapsed: 00:00:00.04
rajesh@10GR2> insert into t values (1);

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> create or replace procedure dr_proc
  2  as
  3     l_count number;
  4  begin
  5     select count(*) into l_count from t dr_proc;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> create or replace procedure ir_proc
  2     authid current_user
  3  as
  4     l_count number;
  5  begin
  6     select count(*) into l_count from t ir_proc;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> grant execute on dr_proc to public;

Grant succeeded.

Elapsed: 00:00:00.04
rajesh@10GR2> grant execute on ir_proc to public;

Grant succeeded.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>

Now, executing this script in all the Ten users that we created above.

create table t(x number);
insert into t values (1);
commit;
exec rajesh.ir_proc;
exec rajesh.dr_proc;

We can investigate our shared pool (v$sqlarea) to see what happened.


rajesh@10GR2>
rajesh@10GR2> EXEC print_table(' select sql_text, sharable_mem, version_count,loaded_versions, parse_calls, optimizer_mode from v$sqlarea where UPPER(sql_text) like ''%PROC%'' and lower(sql_text) not like ''%v$sqlarea%'' ');

     SQL_TEXT------------ SELECT COUNT(*) FROM T DR_PROC
     SHARABLE_MEM-------- 8643
     VERSION_COUNT------- 1
     LOADED_VERSIONS----- 1
     PARSE_CALLS--------- 10
     OPTIMIZER_MODE------ ALL_ROWS
--------------------------------------------
     SQL_TEXT------------ SELECT COUNT(*) FROM T IR_PROC
     SHARABLE_MEM-------- 76971
     VERSION_COUNT------- 10
     LOADED_VERSIONS----- 10
     PARSE_CALLS--------- 10
     OPTIMIZER_MODE------ ALL_ROWS
--------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.51
rajesh@10GR2>

This shows clearly that Invoker rights routine has made ten different copies of SQL's available in shared pool but definer rights had made only a single copy as expected.