Troubleshoot ORA-06528 : Error executing PL/SQL profiler

Start DBMS_PROFILER - user level

Troubleshoot ORA-06528 : Error executing PL/SQL profiler

Issue Description :

I received “ORA-06528” error from HR user. Same procedure I have tested also and received above error. Same procedure I have created and tested in different schema without any issues. Then I guess there may be a issue with profiler tables for the issuing schema.

about dbms_profiler:

The dbms_profiler package is a built-in set of procedures to capture performance information from PL/SQL.   The dbms_profiler package has these procedures:

            dbms_profiler.start_profiler

      dbms_profiler.flush_data

      dbms_profiler.stop_profiler

The idea behind profiling with dbms_profiler is for the developer to understand where their code is spending the most time, so they can detect and optimize it.  The profiling utility allows Oracle to collect data in memory structures and then dumps it into tables as application code is executed.  dbms_profiler is to PL/SQL, what tkprof and Explain Plan are to SQL. 

Once you have run the profiler, Oracle will place the results inside the dbms_profiler tables. 

The dbms_profiler procedures are not a part of the base installation of Oracle.  Two tables need to be installed along with the Oracle supplied PL/SQL package.  In the $ORACLE_HOME/rdbms/admin directory, two files exist that create the environment needed for the profiler to execute. 

            proftab.sql        - Creates three tables and a sequence and must be executed before the profload.sql file.

            profload.sql      - Creates the package header and package body for DBMS_PROFILER.  This script                                              must be executed as the SYS user.

Starting a Profiling Session – Using sqlplus console window

The profiler does not begin capturing performance information until the call to start_profiler is executed.

SQL> exec dbms_profiler.start_profiler ('Test of raise procedure by Gouranga');

Flushing Data during a Profiling Session

The flush command enables the developer to dump statistics during program execution without stopping the profiling utility. The only other time Oracle saves data to the underlying tables is when the profiling session is stopped, as shown below:

SQL> exec dbms_profiler.flush_data();

PL/SQL procedure successfully completed.

OR
Data in the profiler tables must be cleaned up. This can be done by running.

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

Stopping a Profiling Session

Stopping a profiler execution using the Oracle dbms_profiler package is done after an adequate period of time of gathering performance benchmarks – determined by the developer. Once the developer stops the profiler, all the remaining (unflushed) data is loaded into the profiler tables.

SQL> exec dbms_profiler.stop_profiler();

PL/SQL procedure successfully completed.

Oracle dbms_profiler package also provides procedures that suspend and resume profiling (pause_profiler(), resume_profiler()).

Example:

[oracle@01HW155534 ~]$ sqlplus /nolog

SQL> connect hr@myDB

Enter password:

Connected.

SQL>

SQL> exec dbms_profiler.flush_data();

PL/SQL procedure successfully completed.

SQL> exec dbms_profiler.start_profiler ('Test of raise procedure by Gouranga');

PL/SQL procedure successfully completed.

SQL> exec p_test();

PL/SQL procedure successfully completed.

Query to find out statistics:

select runid, unit_number, line#, total_occur, total_time,   

       min_time, max_time

from plsql_profiler_data;

Sample Output ( console / html output)

Tables/Views associated with dbms_profiler:

      plsql_profiler_units

      plsql_profiler_runs

      plsql_profiler_data

 

Sample output – Using Plsql Developer tool:

Note : When you are using third-party tools for sql or plsql, it gives you extra benefits like executed text code, timespent marking, graphs etc.

Executed procedure body:

CREATE OR REPLACE PROCEDURE p_test AS

 cnt      NUMBER := 0;

BEGIN

 DBMS_PROFILER.START_PROFILER( 'mod' );

  FOR I IN 1..500000 LOOP

   cnt := cnt + 1;

   IF ( MOD(cnt,1000) = 0 ) THEN

     COMMIT;

   END IF;

  END LOOP;

 DBMS_PROFILER.STOP_PROFILER;

END;

/

Issues while executing dbms_profiler:

Most of cases ORA-06528 error will come while executing the profiler. Please see the below error which shows the details of the error.

Error snapshot:

  

Troubleshoot the error:

Step-1: Connect to database server by OS level and go to below location :

      $ORACLE_HOME/rdbms/admin

Step-2: Connect SQLPLUS:

      $ sqlplus /nolog

      SQL> connect hr@myDB

      Connected.

Step-3: Start the profiler package:

      SQL> @proftab.sql

Now you can test. No issues will come.

Thanks 

Please feel free to post a comment.