Sayantan's Blog On Oracle

PLSQL COMPILER

Before oracle 11g, the PLSQL Compiler translated our source code to system code without applying many changes to improve the performance. But now Oracle PLSQL uses a new optimizing compiler that can rearrange our code for better performance.
The default optimization level improves performance in a broad range of PLSQL operation.

The list of changes happened in PLSQL Compiler:

• Elimination of compiler-generated temporary operands
• Computation of some operations during compilation
• Reuse of expression values
• Simplification or elimination of some branches and dead code elimination
• Avoidance of library calls by direct execution in the PLSQL virtual machine of some operations
• All cursors correctly finalized upon exit from a cursor loop or a declared block
• Elimination of computations which causes an exception

PLSQL compiler optimization techniques

INITIALIZATION PARAMETERS USED IN PLSQL COMPILATION

• PLSQL_CODE_TYPE
• PLSQL_OPTIMIZE_LEVEL
• PLSQL_WARNINGS
• PLSQL_CCFLAGS

PLSQL_CODE_TYPE

It specifies the compilation mode for PLSQL library units. It has two values:

• INTERPRETED (Default)
• NATIVE

INTERPRETED

PLSQL library units will be compiled to the PLSQL bytecode format.
Such modules are executed by the PLSQL interpreter engine.
The code is parsed and changed to source code one line at a time. Also only one error occurs at a time.

NATIVE

PLSQL library units (with the possible exception of top-level anonymous PLSQL blocks) will be compiled to native (machine code).
Such modules will be executed natively without incurring any interpreter overhead.

• When the value of this parameter (PLSQL_CODE_TYPE) is changed, it has no effect on PLSQL library units that have already been compiled.
• The value of this parameter is stored persistently with each library unit.
• If a PLSQL library unit is compiled native, all subsequent automatic recompilations of that library unit will use native compilation

PLSQL_OPTIMIZE_LEVEL

PLSQL_OPTIMIZE_LEVEL specifies the optimization level to be used to optimize the PLSQL library units. The higher the value of this parameter, the greater the effort the compiler will put to compile the PLSQL library unit.

PLSQL_OPTIMIZE_LEVEL = {0 | 1 | 2 | 3}

Default value is 2.

0

For 0 value oracle doesn't do any changes or do little changes to make the performance better.

1

Applies a wide range of optimizations to PLSQL programs including the elimination of unnecessary computations and exceptions, but generally doesn't move source code out of its original source order.

2

Applies a wide range of optimization techniques beyond those of level 1 including changes which may move the source code relatively far from its original location.

3

Applies a wide range of optimization techniques beyond those of level 2, automatically including techniques which are not specifically requested.

Now we will check the plsql_optimize_level set in our database.

CONN SYS AS SYSDBA;
PASSWORD: SYS

GRANT SELECT ON V_$PARAMETER TO HR;

CONN HR/HR;

CREATE OR REPLACE SYNONYM V$PARAMETER FOR SYS.V_$PARAMETER;

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'plsql_optimize_level';

Changing the parameter value from 2 to 0.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'plsql_optimize_level';

SET SERVEROUTPUT ON;
DECLARE
  NUM1 NUMBER := 0;
  NUM2 NUMBER := 0;
  NUM3 NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('START TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  FOR I IN 1..100000000
  LOOP
    NUM1 := 1;
    NUM2 := NUM1 + 1;
    NUM3 := NUM1 + NUM2;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('END TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
END;
/

In the above example we have executed a PLSQL block to check the performance. It took 20 sec to complete.

Now changing the PLSQL optimize level to 2 to execute the same task.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'plsql_optimize_level';

DECLARE
  NUM1 NUMBER := 0;
  NUM2 NUMBER := 0;
  NUM3 NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('START TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  FOR I IN 1..100000000
  LOOP
    NUM1 := 1;
    NUM2 := NUM1 + 1;
    NUM3 := NUM1 + NUM2;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('END TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
END;
/

So in the above output we can see that under optimize level 2 the PLSQL block took almost no time to complete the operation.

Now we will change this parameter to 3.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'plsql_optimize_level';

SET SERVEROUTPUT ON;
DECLARE
  NUM1 NUMBER := 0;
  NUM2 NUMBER := 0;
  NUM3 NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('START TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  FOR I IN 1..100000000
  LOOP
    NUM1 := 1;
    NUM2 := NUM1 + 1;
    NUM3 := NUM1 + NUM2;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('END TIME : '||TO_CHAR(SYSDATE, 'HH24:MI:SS'));
END;
/

It is also taking almost no time to complete.

PLSQL_WARNINGS

Prior 10g if we compile any sub program like Procedure, Function, Trigger, Package etc. then result would be either Success or Failure.

But 10g onwards, Oracle introduced a new feature called PLSQL_WARNINGS. It could be Success with warning or Failure with warning. Warning can produce undefined result or performance issue.

PLSQL warning with Success should be treated as warning that indicates that your program might not perform optimally. Then you should rectify your code so that the warning messages are removed while compile your program. On the other hand, PLSQL warning with Failure is simply a failure. First you should correct failure and then rectify the warning part.

How PLSQL_WARNINGS work

From 10g onwards PLSQL compiler has been enhanced to produce warnings for subprograms.

PLSQL_WARNINGS enables or disables the reporting of warning messages by the PLSQL compiler and specifies which warning message to show as errors.

Syntax of PLSQL_WARNINGS:

PLSQL_WARNINGS = 'value_clause' [, 'value_clause' ] ...

Values_Clause:

• Multiple values_clause can be specified, enclosed by quotes, separated by commas.
• Each value_clause is composed of
  • a qualifier
  • colon (:)
  • a modifier

Qualifier Values:

• ENABLE
  • Enable a specific warning or a set of warnings
• DISABLE
  • Disable a specific warning or a set of warnings
• ERROR
  • Treat a specific warnings or a set of warnings as errors

Modifier Values:

• ALL
  • Apply the qualifier to all warning messages
• SEVERE
  • Apply the qualifier to only those warning messages which are in SEVERE category. Messages for condition that may cause unexpected behavior or wrong result.
• INFORMATIONAL
  • Apply the qualifier to only those warning messages which are in INFORMATIONAL category. Messages for condition that do not have an effect on performance or correctness. But you may want to change the code more maintainable, such as unreachable code that never be executed.
• PERFORMANCE
  • Apply the qualifier to only those warning messages which are in PERFORMANCE category. Messages for condition that may cause performance problem such as passing a VARCHAR2 value in a number column in an insert statement.

Default value for values_clause: 'DISABLE:ALL'

We can modify PLSQL_WARNINGS through ALTER SESSION or ALTER SYSTEM commands.

PLSQL_WARNINGS can be set through two levels:

• Declaratively
  • Using the PLSQL_WARNINGS initialization parameter
• Programmatically
  • Using DBMS_WARNING package

Enable PLSQL_WARNINGS:

Using PLSQL_WARNINGS Parameter:

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

Creating a sample procedure to check the PLSQL_WARNING:

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_EMP_DTLS(P_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
  V_EMP_DTLS VARCHAR2(4000) := NULL;
BEGIN
  SELECT EMPLOYEE_ID||' - '||FIRST_NAME||' '||LAST_NAME
  INTO V_EMP_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPNO;
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS - >'||V_EMP_DTLS);
END;
/

Warnings are identified by PLW identifier. Below is the list of modifier range.

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:SEVERE';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

When one qualifier value is enabled the others are disabled by default as we can see in the above output.

To change the default behavior we can enable multiple qualifier.

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:SEVERE', 'ENABLE:PERFORMANCE', 'DISABLE:INFORMATIONAL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

Using DBMS_WARNING Package:

We can set compiler warning messages by using DBMS_WARNING package. Following are the settings we can do by using this package.

List of DBMS_WARNINGS package subprograms:

OPERATIONS	SUBPROGRAM NAME	SUBPROGRAM TYPE	DESCRIPTION
SET WARNINGS	ADD_WARNING_SETTING_CAT	PROCEDURE	Modifies the current session or system warning setting of the warning_category previously set
	ADD_WARNING_SETTING_NUM	PROCEDURE	Modifies the current session or system warning setting of the warning_num previously set
GET WARNINGS	GET_WARNING_SETTING_CAT	FUNCTION	Returns the specific warning category in the session
	GET_WARNING_SETTING_NUM	FUNCTION	Returns the specific warning number in the session
	GET_WARNING_SETTING_STRING	FUNCTION	Returns the entire warning string in the current session
REPLACE WARNINGS	SET_WARNING_SETTING_STRING	PROCEDURE	Replace the previous settings with a new value
GET WARNING CATEGORY NAME	GET_CATEGORY	FUNCTION	Returns the category name

ADD_WARNING_SETTING_CAT:

This procedure takes three input parameters:

PARAMETER	DESCRIPTION
WARNING_CATEGORY	Name of the category. Allowed values are: ALL, INFORMATIONAL, SEVERE, PERFORMANCE
WARNING_VALUE	Value of the category. Allowed values are: ENABLE, DISABLE, ERROR
SCOPE	Specifies if the changes are being performed in the session context or the system context. Allowed values are: SESSION, SYSTEM

Syntax:

DBMS_WARNING.ADD_WARNING_SETTING_CAT 
(
 warning_category    IN    VARCHAR2,
 warning_value       IN    VARCHAR2,
 scope               IN    VARCHAR2
);

Checking the current warning setup:

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

EXEC DBMS_WARNING.ADD_WARNING_SETTING_CAT('SEVERE', 'ENABLE', 'SESSION');

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

We can also use the function DBMS_WARNING.GET_WARNING_SETTING_STRING to get the above result.

SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

ADD_WARNING_SETTING_NUM:

We can modify the current session or system setting with the value provided in this procedure.

This procedure takes three input parameters:

PARAMETER	DESCRIPTION
WARNING_CATEGORY	Name of the category. Allowed values are: ALL, INFORMATIONAL, SEVERE, PERFORMANCE
WARNING_VALUE	Value of the category. Allowed values are: ENABLE, DISABLE, ERROR
SCOPE	Specifies if the changes are being performed in the session context or the system context. Allowed values are: SESSION, SYSTEM

DBMS_WARNING.ADD_WARNING_SETTING_NUM 
(
 warning_category    IN    VARCHAR2,
 warning_value       IN    VARCHAR2,
 scope               IN    VARCHAR2
);

EXEC DBMS_WARNING.ADD_WARNING_SETTING_NUM (6019, 'ENABLE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

So we can see that the INFORMATIONAL warnings are disable except 6019. So this procedure has override the session or system settings.

To disable the above warning setting number:

EXEC DBMS_WARNING.ADD_WARNING_SETTING_NUM (6019, 'DISABLE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SSELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

GET_WARNING_SETTING_CAT:

This function takes one parameter.

PARAMETER	DESCRIPTION
WARNING_CATEGORY	Name of the category. Allowed values are: ALL, INFORMATIONAL, SEVERE, PERFORMANCE

Syntax:

DBMS_WARNING.GET_WARNING_SETTING_CAT 
(
 warning_category    IN    VARCHAR2
)
RETURN warning_value;

EXEC DBMS_WARNING.ADD_WARNING_SETTING_CAT('SEVERE', 'ENABLE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

SELECT DBMS_WARNING.GET_WARNING_SETTING_CAT('SEVERE') FROM DUAL;

SELECT DBMS_WARNING.GET_WARNING_SETTING_CAT('INFORMATIONAL') FROM DUAL;

SELECT DBMS_WARNING.GET_WARNING_SETTING_CAT('PERFORMANCE') FROM DUAL;

GET_WARNING_SETTING_NUM:

This function takes one parameter.

PARAMETER	DESCRIPTION
WARNING_NUMBER	Warning numbers. Allowed values are all valid warning numbers.

Syntax:

DBMS_WARNING.GET_WARNING_SETTING_NUM 
(
 warning_number      IN    NUMBER
)
RETURN warning_value;

EXEC DBMS_WARNING.ADD_WARNING_SETTING_NUM (6019, 'ENABLE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_NUM(6019) FROM DUAL;

EXEC DBMS_WARNING.ADD_WARNING_SETTING_NUM (6019, 'DISABLE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_NUM(6019) FROM DUAL;

GET_WARNING_SETTING_STRING:

Syntax:

DBMS_WARNING.GET_WARNING_SETTING_STRING
RETURN VARCHAR2;

SET_WARNING_SETTING_STRING:

This procedure takes two input parameters:

PARAMETER	DESCRIPTION
WARNING_VALUE	The new string that will constitute the new value
SCOPE	Specifies if the changes are being performed in the session context or the system context. Allowed values are: SESSION, SYSTEM

Syntax:

DBMS_WARNING.SET_WARNING_SETTING_STRING 
(
 warning_value   IN   VARCHAR2,
 scope           IN   VARCHAR2
);

EXEC DBMS_WARNING.SET_WARNING_SETTING_STRING ('ENABLE:ALL', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

EXEC DBMS_WARNING.SET_WARNING_SETTING_STRING ('DISABLE:INFORMATIONAL, ENABLE:SEVERE, ENABLE:PERFORMANCE', 'SESSION');

SELECT DBMS_WARNING.GET_WARNING_SETTING_STRING FROM DUAL;

GET_CATEGORY:

This function takes one parameter as input.

PARAMETER	DESCRIPTION
WARNING_NUMBER	Warning numbers. Allowed values are all valid warning numbers.

Syntax:

DBMS_WARNING.GET_CATEGORY 
(
 warning_number  IN   pls_integer
) 
RETURN VARCHAR2;

SELECT DBMS_WARNING.GET_CATEGORY(5018) FROM DUAL;

SELECT DBMS_WARNING.GET_CATEGORY(6018) FROM DUAL;

SELECT DBMS_WARNING.GET_CATEGORY(7018) FROM DUAL;

SET SERVEROUTPUT ON;
DECLARE
  WMSG VARCHAR2(1000);
BEGIN

  DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SESSION');
  WMSG := DBMS_WARNING.GET_WARNING_SETTING_STRING();
  DBMS_OUTPUT.PUT_LINE(WMSG);
END;
/

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_EMP_DTLS(P_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE,
                                        P_EMP_DTLS OUT VARCHAR2)
IS
  V_EMP_DTLS VARCHAR2(4000) := NULL;
BEGIN
  SELECT EMPLOYEE_ID||' - '||FIRST_NAME||' '||LAST_NAME
  INTO V_EMP_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPNO;
  --
  P_EMP_DTLS := V_EMP_DTLS;
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS - >'||P_EMP_DTLS);
END;
/

So in the above example, two warnings are captured by PLSQL compiler.

To check the warning messages we will query the USER_ERRORS and USER_PLSQL_OBJECT_SETTINGS data dictionary view.

SELECT * FROM USER_ERRORS
WHERE NAME = 'SP_EMP_DTLS';

SELECT * FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'SP_EMP_DTLS';

Unreachable Code:

Unreachable code are the code which are written in a sub program but they are never executed.

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE SP_EMP_DTLS(P_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE,
                                        P_EMP_DTLS OUT VARCHAR2)
IS
  V_EMP_DTLS VARCHAR2(4000) := NULL;
  NUM1 NUMBER := 0;
BEGIN
  SELECT EMPLOYEE_ID||' - '||FIRST_NAME||' '||LAST_NAME
  INTO V_EMP_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPNO;
  --
  P_EMP_DTLS := V_EMP_DTLS;
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS - >'||P_EMP_DTLS);
  RETURN;
  NUM1 := NUM1 + 1;
END;
/

In the above example, we can see that the warning PLW-06002 is an Informational warning. It has the range of 6000 – 6249. Other warnings are PLW-07203 and PLW-0518. PLW is a performance warning and its range is 7000 – 7249. PLW-05018 is a Severe warning and its range is 5000 – 5999.

Disable PLSQL_WARNINGS:

ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:ALL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

There is another way we can set plsql_warnings as disable. For that we need enable all the warnings.

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

Now we will disable the INFORMATIONAL qualifier.

ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

This is happened as we know when we enable on qualifier then rest all are in disable mode. So if we disable one qualifier then automatically all the qualifiers will be in disable mode.

Now we will check the Unreachable code example to see how disabling the INFORMATIONAL qualifier affect the compilation result. We will enable rest of the qualifier.

ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL, ENABLE:SEVERE, ENABLE:PERFORMANCE';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

CREATE OR REPLACE PROCEDURE SP_EMP_DTLS(P_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE,
                                        P_EMP_DTLS OUT VARCHAR2)
IS
  V_EMP_DTLS VARCHAR2(4000) := NULL;
  NUM1 NUMBER := 0;
BEGIN
  SELECT EMPLOYEE_ID||' - '||FIRST_NAME||' '||LAST_NAME
  INTO V_EMP_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPNO;
  --
  P_EMP_DTLS := V_EMP_DTLS;
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS - >'||P_EMP_DTLS);
  RETURN;
  NUM1 := NUM1 + 1;
END;
/

So we can see that the unreachable message is not appeared in the compilation output.

Now in the above example, we can also see that the message PLW-05018 as warning. We will convert this to an error. To do this we will do the below settings:

ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:INFORMATIONAL, ENABLE:SEVERE, ENABLE:PERFORMANCE, ERROR:05018';

SELECT NAME, VALUE 
FROM V$PARAMETER
WHERE NAME = 'plsql_warnings';

CREATE OR REPLACE PROCEDURE SP_EMP_DTLS(P_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE,
                                        P_EMP_DTLS OUT VARCHAR2)
IS
  V_EMP_DTLS VARCHAR2(4000) := NULL;
  NUM1 NUMBER := 0;
BEGIN
  SELECT EMPLOYEE_ID||' - '||FIRST_NAME||' '||LAST_NAME
  INTO V_EMP_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPNO;
  --
  P_EMP_DTLS := V_EMP_DTLS;
  --
  DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS - >'||P_EMP_DTLS);
  RETURN;
  NUM1 := NUM1 + 1;
END;
/

Now we can see that the warning PLS-05018 has been converted into an error. We will check the status of procedure SP_EMP_DTLS.

SELECT * FROM USER_ERRORS
WHERE NAME = 'SP_EMP_DTLS';

RELATED TOPICS:

• DBMS_UTILITY
• DBMS_LOCK
• UTL_FILE
• DBMS_SQL