How to use DBMS_PROFILER package?

DBMS_PROFILER: It is the PL/SQL code tuning technique. It allows you to check the run time behavior of your PL/SQL code and helps you in identifying the areas where the performance issue is. The output of DBMS_PROFILER package is very easy to read as it gives the execution time for each line of code and from there you can easily
identify the bottleneck.

Profiling is for the developer to understand where the PL/SQL code is spending the most time, so they can detect and optimize it. DBMS_PROFILER is to PL/SQL, what tkprof and Explain Plan are to SQL.

DBMS_PROFILER package has some subprograms like:

1. FLUSH_DATA Function and Procedure: Flushes profiler data collected in the user's session.
2. GET_VERSION Procedure: Gets the version of this API.
3. INTERNAL_VERSION_CHECK Function: Verifies that this version of the DBMS_PROFILER package can work with the implementation in the database.
4. PAUSE_PROFILER Function and Procedure: Pauses profiler data collection.
5. RESUME_PROFILER Function and Procedure: Resumes profiler data collection.
6. START_PROFILER Functions and Procedures: Starts profiler data collection in the user's session.
7. STOP_PROFILER Function and Procedure: Stops profiler data collection in the user's session.
   

To execute the DBMS_PROFILER package we need to do certain settings in our oracle environment. There are two files that create the environment setting for DBMS_PROFILER:

• .proftab.sql : This file creates three tables (PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA) and sequence and must be executed first from the oracle user from which the profiling is to be done. This needs to be executed before profload.sql.
• .profload.sql : This file creates the specification and the body of DBMS_PROFILER package. This needs to be executed from sys user only. Some public synonyms also needs to be created for the tables to for the other oracle user (from which the profiling is to be done).

Following are the steps to do the settings and the execution of the DBMS_PROFILER (using a UNIX platform):

(We will consider an example, Oracle user "TRACETEST" needs profiling to be done on a sample package "TEST_PROFILER")

Step 1: Go to the admin directory under ORACLE_HOME/rdbms using the command "cd $ORACLE_HOME/rdbms/admin".

Step 2: Connect as tracetest (Please make sure that the user has CREATE SEQUENCE, CREATE TABLE and CREATE PROCEDURE privilege) the following commands:
sqlplus /nolog
connect tracetest/tracetest

Step 3: Run proftab.sql file using the command
@$ORACLE_HOME/rdbms/admin/proftab.sql

Step 4: Grant some privileges to PUBLIC for the tables created using the following commands:
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Step 5: Create a test_profiler procedure:
CREATE OR REPLACE PROCEDURE test_profiler AS
l_dummy NUMBER;
BEGIN
FOR i IN 1 .. 50 LOOP
SELECT l_dummy + 1
INTO l_dummy
FROM dual;
END LOOP;
END;
/

Step 6: Connect to SYSDBA user to run proftab.sql file using command "connect / as sysdba".

Step 7: Run profload.sql using the command
@$ORACLE_HOME/rdbms/admin/profload.sql

Step 8: Create PUBLIC SYNONYM using the following commands for "tracetest user":
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR tracetest.plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR tracetest.plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR tracetest.plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR tracetest.plsql_profiler_runnumber;

Now, we are all set to run the DBMS_PROFILER package to check the runtime execution of the procedure TEST_PROFILER.

Step 9: To run the profiler:

DECLARE
l_result BINARY_INTEGER;
BEGIN
l_result := DBMS_PROFILER.start_profiler(run_comment => 'test_profiler_execution: ' || SYSDATE);
test_profiler;
l_result := DBMS_PROFILER.stop_profiler;
END;
/

Step 10: Syntax to see which run happened: (RUNID is the unique identifier associated to each run)

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN run_comment FORMAT A50
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;



Step 11: Syntax to see the details of runid of the runid you got from the above query:

COLUMN runid FORMAT 99999
COLUMN unit_number FORMAT 99999
COLUMN unit_type FORMAT A20
COLUMN unit_owner FORMAT A20

SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = 1
ORDER BY u.unit_number, d.line#;





Here we can see that the line no. 5 executed less times then line no. 4 but took lots of time.

Step 12: To check the line numbers of the source code:

SELECT line||' : ' ||text
FROM all_source
WHERE owner = 'TRACETEST'
AND type = 'PROCEDURE'
AND name = 'TEST_PROFILER';

LINE||' : '||TEXT
---------------------------------------------------
1 : CREATE OR REPLACE PROCEDURE test_profiler AS
2 : l_dummy NUMBER;
3 : BEGIN
4 : FOR i IN 1 .. 50 LOOP
5 : SELECT l_dummy + 1
6 : INTO l_dummy
7 : FROM dual;
8 : END LOOP;
9 : END;

We can easily identify that the loop didn't took much time in execution while the SQL query itself
took more time to execute. So, we can conclude that SQL_TRACE or other facilities can be used to tune the SQL further.

PS: Syntax for other DBMS_PROFILER options are:

DBMS_PROFILER.FLUSH_DATA;
DBMS_PROFILER.PAUSE_PROFILER;
DBMS_PROFILER.RESUME_PROFILER;
DBMS_PROFILER.INTERNAL_VERSION_CHECK
RETURN BINARY_INTEGER;
DBMS_PROFILER.GET_VERSION (
major OUT BINARY_INTEGER,
minor OUT BINARY_INTEGER);

Difference Between Invoker Rights and Definer Rights

Invoker Rights Vs Definer Rights

In Oracle database, the stored PL/SQL procedures executes by default with the owner's privileges. This means that these subprograms execute only for the schema where they exists or created.

Let me explain this concept clearly using a scenario:

Let's say there are two schema schema1 and schema2. Schema1 has procedure proc1 and table emp_tab and Schema2 has table emp_tab only.

The structure of the proc1 procedure in Schema1 is as follows:

Create or replace procedure proc1 (p_empid number, p_ename varchar2, p_salary number)
as
begin
insert into emp_tab (eid, emp_name,emp_sal)
values (p_empid,p_ename,p_salary);
commit;
end;
/

Now, User Schema1 granted EXECUTE permission to User Schema2 on this procedure. If user Schema2 wants to insert data into his emp_tab table using this procedure he cannot do because when user Schema2 executes the procedure, the procedure will be executed with the privileges granted to user Schema1.

Though the user Schema2 has the same table in his schema and does not have the permissions for table emp_tab in Schema1, still the procedure will insert the new values into Schema1 table instead of his schema table.

Before Oracle 8i release, there were following two ways out to resolve this problem :

First either to copy the procedure into Schema2 as well which leads to code replication and hampers the maintenance.

Secondly use the schema references to the objects used in the procedure (under schema1) like insert into schema2.emp_tab which leads to hamper the code portability. To resolve this issue you can pass the schema name as parameter to the procedure and associate in sql.

To overcome this problem, in Oracle 8i and higher releases the AUTHID CURRENT_USER clause included. This invoker rights enables the procedure to execute with the privileges of the current user.

The syntax of the procedure in Schema1 will be like this:


Create or replace procedure proc1
(p_empid number, p_ename varchar2, p_salary number) AUTHID CURRENT_USER
as
begin
insert into emp_tab (eid, emp_name,emp_sal)
values (p_empid,p_ename,p_salary);
commit;
end;

Now, if the user Schema2 executes the procedure which is residing under Schema1 user the procedure will update Schema2 emp_tab table only. If the table is not existing in Schema2 user it will throw an error.
By default, Oracle assumes AUTHID DEFINER (definer rights) if you don't use the clause.

Difference Between Number Datatypes

The number data types in oracle database are used to store numeric values/data.

There are BINARY_INTEGER, NUMBER and PLS_INTEGER data types which have small differences w.r.t PL/SQL code performance point of view.

Let's have a look on all three and find which one is better to use and why....

NUMBER Data type: This is the very common data type used to store numeric data (fixed-point and floating-point). Its magnitude range starts from 1E-130 .. 10E125. Oracle throws error if the value exceed or under the specified range.

The syntax is NUMBER(Precision,scale).

Precision: This is the value equal to the total no. of digits.
Scale: This is the value equal to the digits after the decimal point.

e.g. If you want to store a value 1234.56 Then you need to specify NUMBER(6,2).

BINARY_INTEGER Data type: This data type is used to store signed integers. Its magnitude range is -2**31 .. 2**31. BINARY_INTEGER values require less storage space than NUMBER values. This uses library arithmetic hence BINARY_INTEGER operations are slower than PLS_INTEGER operations. If the BINARY_INTEGER calculation overflows then no error/exception is raised.

PLS_INTEGER Data type: This data type is also used to store the signed integers. Its magnitude is similar to BINARY_INTEGER only. If the PLS_INTEGER calculation overflows then an exception is raised. PLS_INTEGER uses machine arithmetic hence operations are faster then BINARY_INTEGER.

NOTE: In new applications, always try to use the PLS_INTEGER as it is faster.