PGA Memory Operation Events

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;
/

 

Update to my earlier #quicktip on setting #plsql scope and warnings.

I noticed a error in my code for setting PLSCOPE_SETTINGS and PLSQL_WARNINGS. QuicTip Logon.sql What I did was get the instance name out of v$instance to figure out if I was connecting to a production environment or one of the lower environments. The problem with this is, not everyone is going to have permissions to select on sys.v_$instance. The better way to do this is to use sys_context to get the instance name. This way, you won’t have to chase down additional privileges from your DBA.

<code>

select sys_context('userenv','instance_name')
 into sInst
 from dual;

</code>

Here is the corrected code for my logon.sql

<code>

DECLARE
 sInst varchar2(1);
 BEGIN
 -- rlockard: 2018/02/23 commented out getting instance name from v$instance.
 -- used the more apporiate sys_context('userenv','instance_name')

--select upper(SUBSTR(instance_name, 1,1))
 --INTO sInst
 --FROM SYS.V_$INSTANCE;

select sys_context('userenv','instance_name')
 into sInst
 from dual;

-- test to see if this is a production instance
 -- all production instances start with P so ...
 -- if it's not a production instance set up
 -- session properties approiate for dev / test / sandbox.
 IF sInst != 'P' THEN
 execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL''';
 execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL''';
 END IF;
 END;
 /

</code>

My upcoming Spring events @OracleACE #InfoSec

March 5 – 8: I will be speaking at the Hotsos Symposium in Dallas Texas.

https://www.hotsos.com/apex/f?p=200:61801:6152298924404 I will be showing how to secure your high performance code. We will be looking at some coding standards, what common errors we are making that makes our code less secure, and how to implement a trusted path for your data.

March 15: We have managed to wrangle Bobby Curtis, and Steven Feuerstein to come out to Oracle’s Columbia Maryland office to give a couple of presentations. Maybe it’s the Maryland Crab Cakes or could be they are really nice guys. 🙂

https://www.meetup.com/natcapoug-middleware/events/248008692/

Bobby Curtis, Oracle ACE Director Alumni and Product Manager for Oracle Golden Gate will be giving a presentation on Golden Gate Security.

Steven Feuerstein Oracle ACE Director Alumni and Oracle’s Developer Advocate for PL/SQL for Oracle DevGym. This will be a great opportunity for an Oracle DevGym Workout. Devgym.oracle.com Steven will go through the exercises with you and will be giving prizes for the best performers.

March 21 – 22: I will be speaking at Utah Oracle Users Group Training Days (and getting some Spring Skiing in.) http://utoug.org/TrainingDays I will be speaking on Holistic Database Security and Secure Coding. My Holistic Database Security presentation has come a long way over the past ten years. As new attack vectors, mistakes, mitigations come out I update this presentation. So, if you’ve seen this presentation before, don’t worry there is a lot of new material in there. My Secure Coding Presentation goes through coding standards, common errors, and how to implement a trusted path for you data.

April 18: I will be speaking at Twin Cities Oracle Users Group on Oracle Database Vault and a Hybrid Holistic Database Security presentation that will be focused at DBA’s. Many DBA’s fear or don’t like Oracle Database Vault, because it changes the paradigm of how they work. We are accustomed to being the God of our databases. We will be looking at how to make Database Vault your friend, and customizing it for your needs.

May 22 – 23: I will be speaking at Oracle Users Group Finland. http://www.ougf.fi/index.php/en/

Again I will be speaking on Holistic Database Security and Secure Coding.

And the BGOUG Spring Conference. I’m just waiting on the confirmation.

More to come. 🙂