I’ve been working on putting together some performance test for my secure coding talk coming up at Hotsos and encountered something I can not quite explain. This test case does a bulk select into a type and returns the type to the calling program. When I execute the code through the API, I get 1,100+- PGA Memory Operation Events. When I don’t use the API, and just use straight pl/sql I get 600+- PGA Memory Operation Events.
First we need to build two schemas and one test user. I’m keeping these pretty simple. HR_CODE is to hold business logic. HR_API is to hold the API for the HR schema. The user executes the code in the business logic schema. Here is the basic architecture. NOTE: this is not the full Secure Architecture implantation in this test case. Code Based Access Control and accessible by has not been implemented in this test case.
create user usr1 identified by x;
create user hr_code identified by x;
create user hr_api identified by x;
--
grant create session to usr1;
grant alter session to usr1;
-- create the required roles.
create role hr_big_table_all;
create role hr_big_table_sel;
-- create a test table in the hr schema
create sequence hr.big_table_seq;
create table hr.big_table (id number not null,
c number not null,
d number not null);
-- setup permissions.
grant select on hr.big_table to hr_big_table_sel, hr_api;
grant select, update, insert, delete on hr.big_table to hr_big_table_all;
grant hr_big_table_all to
grant hr_big_table_sel to usr1;
-- populate the table with 1M rows
insert into hr.big_table (
select hr.big_table_seq.nextval,
ceil(sys.dbms_random.value(0,10)),
sys.dbms_random.value(-1,5000)
from dual
connect by rownum <= 1000000);
--
commit;
-- add in the constraints and indexes
alter table hr.big_table add constraint
big_table_pk primary key (id);
--
create index hr.big_table_idx1 on hr.big_table(c);
-- analyze the schema
exec sys.dbms_stats.gather_schema_stats(ownname => 'HR');
-- create the decls package. this is used so all schemas can
-- reference common definitions.
create or replace package hr_decls.big_tab_decl as
cursor big_tab_cur is
select id,
c,
d
from hr.big_table;
subtype st_big_tab is big_tab_cur%rowtype;
type tt_big_tab is table of st_big_tab index by pls_integer;
end;
/
-- grant execute on the decls package to expose definations
grant execute on hr_decls.big_tab_decl to hr_api, hr_code, usr1;
--
-- create the api packages
create or replace package hr_api.big_tab_api
authid current_user as
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab;
end big_tab_api;
/
-- grant execute on the api to the code schema
grant execute on hr_api.big_tab_api to hr_code;
--create the package body
--
create or replace package body hr_api.big_tab_api
as
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab is
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
select id,
c,
d
bulk collect into ltt_big_tab
from hr.big_table
where d between p_low and p_high;
return ltt_big_tab;
exception when no_data_found then
-- <fixme> insert error handler
raise_application_error(20000,'sel_hr_big_tab_range');
end sel_hr_big_tab_range;
end big_tab_api;
/
-- create the code package. this holds the business logic
-- to create a shell around the API.
-- you will notice, no business logic has been added in
-- at this point.
create or replace package hr_code.sel_perf_test
authid definer is
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab;
end sel_perf_test;
/
create or replace package body hr_code.sel_perf_test is
function sel_hr_big_tab_range(p_low number, p_high number)
return hr_decls.big_tab_decl.tt_big_tab is
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
ltt_big_tab := hr_api.big_tab_api.sel_hr_big_tab_range(p_low => p_low,
p_high => p_high);
return ltt_big_tab;
end sel_hr_big_tab_range;
end sel_perf_test;
/
-- grant execute on the business logic code to the user
-- The user only has one way to get to the data, that is
-- through the business logic.
grant execute on hr_code.sel_perf_test to usr1;
-- test the user executing the business logic code.
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_api";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
-- test it.
declare
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_pk(p_id => 100);
-- ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_value(p_value => 4);
-- for i in 0 .. 50
-- LOOP
ltt_big_tab := hr_code.sel_perf_test.sel_hr_big_tab_range(p_low => 50,
p_high => 900);
-- END LOOP;
end;
/
-- to run the next test, the user will need select on hr.big_table.
conn rlockard@demo
grant hr_big_table_sel to usr1;
conn usr1/x@demo
ALTER session SET timed_statistics=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER = "perf_test_noapi";
alter session set sql_trace=TRUE;
alter session set events='10046 trace name context forever, level 12';
declare
ltt_big_tab hr_decls.big_tab_decl.tt_big_tab;
begin
-- for i in 0 .. 50
-- LOOP
select id,
c,
d
bulk collect into ltt_big_tab
from hr.big_table
where d between 50 and 900;
-- END LOOP;
end;
/