PL/SQL Profiling in Amazon AWS

In my online classes, I do prefer, when students have a chance to test in action what they have learned. My class databases run in Amazon AWS cloud using “license included” model (Oracle 12c SE One).

This solution does have advantages and also some disadvantages:

• Limitation by license – Oracle SE One (so no bitmap indexes, no partitioning, no result cache, etc. :( )
• Limitation in administration
• Technical support limitation (my case only, ‘cos I do not pay for support)

I wanted my students to be able to try at least part of process for PL/SQL Profiling. This has few challenges:

• You cannot directly access file system to create directories and files
• You cannot connect as sysdba or create objects in sys schema
• You cannot ask technical support to do that for you, ‘cos you don’t pay for the support

So let’s get through steps to get at least some profiling done.

Directory

First, you need a directory where you are able to generate your profile files. But you can’t access your file system to create one (plus to grant appropriate privileges to oracle). Luckily for us, Amazon creates his databases with defined directory DATA_PUMP_DIR where you have read/write privilege. You can check its contents by using following SQL:

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'));

So all you need to do is to grant required privileges to database user who is going to run profiling:

grant execute on DBMS_HPROF to user;
grant read, write on directory DATA_PUMP_DIR to user;

Tables

Now, under normal circumstances, you would probably log in as sysdba and run script dbmshptab.sql from $ORACLE_HOME/rdbms/admin directory …. which you can’t do.

Fortunately, DBMS_HPROF does use object names without schema, so it can be used in line with following instructions:

The tables and sequence can be created in the schema for each user who wants to gather profiler data. Alternately these tables can be created in a central schema. In the latter case the user creating   these objects is responsible for granting appropriate privileges (insert, update on the tables and select on the sequence) to all users who want to store data in the tables. Appropriate synonyms must also be created so the tables are visible from other user schemas.

So basically, you can copy/paste script and use it. I ran it in main database master schema named oracle (you define it when you are creating instance in AWS). And after that I used following commands:

create or replace public synonym dbmshp_runs for oracle.dbmshp_runs;
create or replace public synonym dbmshp_function_info for oracle.dbmshp_function_info;
create or replace public synonym dbmshp_parent_child_info for oracle.dbmshp_parent_child_info;
create or replace public synonym dbmshp_runnumber for oracle.dbmshp_runnumber;

grant select, insert, update, delete on oracle.dbmshp_runs to user;
grant select, insert, update, delete on oracle.dbmshp_function_info to user;
grant select, insert, update, delete on oracle.dbmshp_parent_child_info to user;
grant select on oracle.dbmshp_runnumber to user;

Profiling

Now we can start profiling as usual:

exec DBMS_HPROF.START_PROFILING(' DATA_PUMP_DIR', 'prof.txt')
…
exec DBMS_HPROF.STOP_PROFILING;

To view contents of file, you can use following SQL:

select * from table(RDSADMIN.RDS_FILE_UTIL.READ_TEXT_FILE('DATA_PUMP_DIR',' prof.txt '));

Ok. We can run analyze now …

SET SERVEROUTPUT ON;
DECLARE
 v_runid INTEGER;
BEGIN
 v_runid := DBMS_HPROF.ANALYZE( location => 'DATA_PUMP_DIR',
                                                      filename => 'prof.txt');
 DBMS_OUTPUT.PUT_LINE('RUNID: '||v_runid);
END;

/

… and select the contents of tables DBMSHP_RUNS, DBMSHP_FUNCTION_INFO, etc.

Unfortunately this is as far as you can go in these conditions and setup. You cannot run plshprof go create HTML report. I’m kind of baffled that Oracle did not provide database version of this utility.

I'm Amazon AWS noob, so feel free to correct me if I'm wrong. :)