Search code examples
sqloracleplsqloracle-sqldeveloper

Oracle SQL Procedure Tracing

I have a package which several procedures

create or replace PACKAGE DAT_REPORT_V2 AUTHID CURRENT_USER AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 
    PROCEDURE LOAD_CONFIG;
    PROCEDURE AUX_TABLES; -- Data by CA
    PROCEDURE GEN_VIEWS;
    PROCEDURE PENDING_PROFORMAS;

END DAT_REPORT_V2;

What would be the best way to find the operations that are the slowest for each procedure?

So far, I've tried this profiler option (with the Gather SQL Statistics checked), but it then asks for a path to store some file and finally fails (after the steps in which I'm asked to provide user/password and I introduce mine).

enter image description here

enter image description here

enter image description here

enter image description here

Solution

  • First copy the text in warning dialog box and run as sysdba(either in sql developer or sqlplus)

    sql>sqlplus / as sysdba
sql>CREATE OR REPLACE directory PLSHPROF_DIR as '/tmp';
sql>grant READ, WRITE on directory PLSHPROF_DIR to OT;
sql>grant execute on DBMS_HPROF to OT;
sql>grant create table to OT;
sql>grant create sequence to OT;
sql>grant unlimited tablespace to OT;

    For database 18c and above login as the user and create tables with procedure

    SQL> conn ot/ot@orclpdb
Connected.
SQL> EXEC DBMS_HPROF.create_tables;

PL/SQL procedure successfully completed.

    Once necessary grants,tables and directory created run the profiler from SQL Developer which generates html and trace files in tmp folder on DB server.