Using the PL/SQL Profiler in the Oracle Autonomous Database

The PL/SQL (Hierarchical) Profiler has been part of the Oracle database for quite some time but there is very little, close to none, information on how to use it in the Oracle Autonomous Database.

This blog post is about how to bypass few hurdles in the Autonomous database for we lack OS access in ADB-S.

A good starting point on how to do PL/SQL Tuning with PL/SQL Hierarchical Profiler is Ulrike Schwinn‘s blog post.

There are 2 main packages: DBMS_PROFILER and DBMS_HPROF introduced in 8i and 11gR1, respectively. Tim Hall explains in detail how to configure and use both DBMS_PROFILER and DBMS_HPROF.

For the PL/SQL Hierarchical Profiler (DBMS_HPROF), you need to specify a directory/location and a file name where the profiler output file will be placed.  Then, in order to generate formatted and readable HTML reports from the raw profiler output file, you need to run plshprof (placed in the directory $ORACLE_HOME/bin/). Now, this is rather tricky in ADB as we do not have direct OS access.

As we cannot create physical directories on the server, here is the workaround: let us use (for example) DATA_PUMP_DIR (there are few options from DBA_DIRECTORIES) and run the profiler on a procedure called MONGODB_TO_ORACLE (it is a real procedure migrating MongoDB to Oracle if you are wondering this ):

execute dbms_hprof.start_profiling(LOCATION=>'DATA_PUMP_DIR',FILENAME=>'profiler.txt');
execute MONGODB_TO_ORACLE;
execute dbms_hprof.stop_profiling;

Afterwards, when the profiling completes, we can run the ANALYZE function to analyze the raw data and place it in the (hierarchical) profiler tables. Then, we can check what files were produced (note the profiler.html file!):

DECLARE
  l_runid  NUMBER;
BEGIN
  l_runid := DBMS_HPROF.analyze (
               location    => 'DATA_PUMP_DIR',
               filename    => 'profiler.txt',
               run_comment => 'MongoDB_to_Oracle_migration');                   
  DBMS_OUTPUT.put_line('l_runid=' || l_runid);
END;
/

SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

To access the log file you need to move the log file to your Cloud Object Storage using the procedure DBMS_CLOUD.PUT_OBJECT. Note that PUT_OBJECT is overloaded. That is, in one form the procedure PUT_OBJECT copies a file from ADB to Cloud Object Storage. In another form, the procedure PUT_OBJECT copies a BLOB from Autonomous Database to the Cloud Object Storage. For example, the following PL/SQL block moves the file profiler.html to your Cloud Object Storage:

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'DEF_CRED_NAME',
    object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/juliandon/b/adbkofa/o/profiler.html',
    directory_name  => 'DATA_PUMP_DIR',
    file_name => 'profiler.html);
END;
/

Note that if you run DBMS_CLOUD.PUT_OBJECT with a user other than ADMIN you need to grant read privileges on the directory to the other user. 

In case you would prefer using the old profiler (DBMS_PROFILER) in ADB, then you can get from another Oracle installation the two scripts proftab.sql and profiler.sql and copy them locally on you computer. The proftab script creates 3 tables and some sequences while the profiler.sql creates the html report. Edit the profiler.sql script to spool the output in a local directory (search for SPO).

You can get the profiler.sql also from MOS: Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1). In case you do not have access to another installation, here are create table commands:

create table plsql_profiler_runs
(
  runid           number primary key,  -- unique run identifier,
                                       -- from plsql_profiler_runnumber
  related_run     number,              -- runid of related run (for client/
                                       --     server correlation)
  run_owner       varchar2(128),       -- user who started run
  run_date        date,                -- start time of run
  run_comment     varchar2(2047),      -- user provided comment for this run
  run_total_time  number,              -- elapsed time for this run
  run_system_info varchar2(2047),      -- currently unused
  run_comment1    varchar2(2047),      -- additional comment
  spare1          varchar2(256)        -- unused
);

comment on table plsql_profiler_runs is
        'Run-specific information for the PL/SQL profiler';

create table plsql_profiler_units
(
  runid              number references plsql_profiler_runs,
  unit_number        number,           -- internally generated library unit #
  unit_type          varchar2(128),    -- library unit type
  unit_owner         varchar2(128),    -- library unit owner name
  unit_name          varchar2(128),    -- library unit name
  -- timestamp on library unit, can be used to detect changes to
  -- unit between runs
  unit_timestamp     date,
  total_time         number DEFAULT 0 NOT NULL,
  spare1             number,           -- unused
  spare2             number,           -- unused
  --
  primary key (runid, unit_number)
);

comment on table plsql_profiler_units is
        'Information about each library unit in a run';

create table plsql_profiler_data
(
  runid           number,           -- unique (generated) run identifier
  unit_number     number,           -- internally generated library unit #
  line#           number not null,  -- line number in unit
  total_occur     number,           -- number of times line was executed
  total_time      number,           -- total time spent executing line
  min_time        number,           -- minimum execution time for this line
  max_time        number,           -- maximum execution time for this line
  spare1          number,           -- unused
  spare2          number,           -- unused
  spare3          number,           -- unused
  spare4          number,           -- unused
  --
  primary key (runid, unit_number, line#),
  foreign key (runid, unit_number) references plsql_profiler_units
);

comment on table plsql_profiler_data is
        'Accumulated data from all profiler runs';

create sequence plsql_profiler_runnumber start with 1 nocache;

That’s it:

SQL> start profiler.sql 1

 RUNID RUN_OWNER                 RUN_DATE        RUN_COMMENT                   
------ ------------------------- --------------- ------------------------------
     1 ADMIN                     07-NOV-24 10:17 07-NOV-24                     

Parameter 1:
RUNID (required)

Value passed:
~~~~~~~~~~~~
RUNID: "1"

PROFILER file has been created:
profiler_NWUWD5H8UG_POD_23.0.0.0.0_20241107_102240.html.

 RUNID RUN_OWNER                 RUN_DATE        RUN_COMMENT                   
------ ------------------------- --------------- ------------------------------
     1 ADMIN                     07-NOV-24 10:17 07-NOV-24                     

Read MoreJulian Dontcheff’s Database Blog