There are the following four types of initialization parameters for PL/SQL compilation.
- PLSQL_CODE_TYPE
- PLSQL_OPTIMIZE_LEVEL
- PLSQL_CCFLAGS
- PLSQL_WARNINGS
In releases before Oracle Database 10g, the PL/SQL compiler translated your code to machine code without applying many changes for performance. Oracle Database 11g onwards, PL/SQL uses an optimizing compiler that can rearrange code for better performance. You do not need to do anything to get the benefits of this new optimizer; it is enabled by default.
The following table is specially describing those four number of initialization parameter of PL/SQL compilation.
+============================+=============================================================================+
|Compiler Option |Description |
+============================+=============================================================================+
|PLSQL_CODE_TYPE |Specifies the compilation mode for PL/SQL library units. |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_OPTIMIZE_LEVEL |Specifies the optimization level to be used to compile PL/SQL library units. |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_WARNINGS |Enables or disables the reporting of warning messages by the PL/SQL compiler.|
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_CCFLAGS |Controls conditional compilation of each PL/SQL library unit independently. |
+----------------------------+-----------------------------------------------------------------------------+
The new compiler improves the performance of PL/SQL code and allows it to execute approximately two times faster than an Oracle 8i database and 1.5 times to 1.75 times as fast as Oracle 9i Database Release 2.
To get the fastest performance, the compiler setting must be:
PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 2
Hence this post I focus on displaying and setting the previous two initialization parameters for PL/SQL compilation.
Taking the schema “OE” for example in Oracle Database 11.2, firstly, we use the following SQL code snippet to check the current value of both PLSQL_CODE_TYPE and PLSQL_OPTIMIZE_LEVEL based on some oracle objects.
-- running on the schema of 'OE'.
SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15
SELECT name
, type
, plsql_code_type AS CODE_TYPE
, plsql_optimize_level AS OPT_LVL
FROM user_plsql_object_settings;
NAME TYPE CODE_TYPE OPT_LVL
------------------------------ ------------ --------------- ----------
ACTIONS_T TYPE INTERPRETED 2
ACTION_T TYPE INTERPRETED 2
ACTION_V TYPE INTERPRETED 2
ADD_JOB_HISTORY PROCEDURE INTERPRETED 2
CATALOG_TYP TYPE INTERPRETED 2
CATALOG_TYP TYPE BODY INTERPRETED 2
CATEGORY_TYP TYPE INTERPRETED 2
COMPOSITE_CATEGORY_TYP TYPE INTERPRETED 2
COMPOSITE_CATEGORY_TYP TYPE BODY INTERPRETED 2
CORPORATE_CUSTOMER_TYP TYPE INTERPRETED 2
CUSTOMER_TYP TYPE INTERPRETED 2
CUST_ADDRESS_TYP TYPE INTERPRETED 2
CUST_DATA PACKAGE INTERPRETED 2
CUST_DATA PACKAGE BODY INTERPRETED 2
CUST_LIST PROCEDURE INTERPRETED 2
......
51 rows selected.
As you can see from the above vaule “INTERPRETED” based on PLSQL_CODE_TYPE, but we can set the compiler initialization parameter’s value using the ALTER SYSTEM or ALTER SESSION statements.
Secondly, we’ll use the ALTER SESSION statement to set PLSQL_CODE_TYPE to become “NATIVE” in order to get the fastest performance.
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
Thirdly, I create a procedure “add_job_history” on the schema “OE”.
CREATE OR REPLACE PROCEDURE add_job_history
(p_emp_id job_history.employee_id%TYPE,
p_start_date job_history.start_date%TYPE,
p_end_date job_history.end_date%TYPE,
p_job_id job_history.job_id%TYPE,
p_department_id job_history.department_id%TYPE
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/
Abruptly oracle gives me the following warning message.
Warning: Procedure created with compilation errors.
Using a simple statement “SHOW ERRORS” to quickly find out which lines the compilation errors are located in.
oe@ORA11GR2> SHOW ERRORS
Errors for PROCEDURE ADD_JOB_HISTORY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5 PL/SQL: SQL Statement ignored
10/17 PL/SQL: ORA-01031: insufficient privileges
As you can see from the previous error ORA-01031 that has a bit of confusion. Next, I check the object_type of “job_history”.
COLUMN object_name FORMAT a12
COLUMN object_type FORMAT a12
SELECT object_name, object_type FROM user_objects WHERE object_name = 'JOB_HISTORY';
OBJECT_NAME OBJECT_TYPE
------------ ------------
JOB_HISTORY SYNONYM
Oh, my god! As you can see from the above object_type, it’s a synonym (not a table). A real table “JOB_HISTORY” should be from the schema “HR”. By the following SQL, you can prove my description whether it is right.
COLUMN synonym_name FORMAT a12
COLUMN table_owner FORMAT a12
COLUMN table_name FORMAT a12
SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'JOB_HISTORY';
SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------ ------------ ------------
JOB_HISTORY HR JOB_HISTORY
So, switching to the schema “HR”, rerunning all the previous code step by step.
SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15
SELECT name
, type
, plsql_code_type AS CODE_TYPE
, plsql_optimize_level AS OPT_LVL
FROM user_plsql_object_settings;
NAME TYPE CODE_TYPE OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL PROCEDURE INTERPRETED 2
ADD_DEPARTMENT PROCEDURE INTERPRETED 2
ADD_DEPARTMENT_NOEX PROCEDURE INTERPRETED 2
ADD_DEPT PROCEDURE INTERPRETED 2
ADD_EMPLOYEE PROCEDURE INTERPRETED 2
ADD_ROW PROCEDURE INTERPRETED 2
......
81 rows selected.
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
CREATE OR REPLACE PROCEDURE add_job_history
(p_emp_id job_history.employee_id%TYPE,
p_start_date job_history.start_date%TYPE,
p_end_date job_history.end_date%TYPE,
p_job_id job_history.job_id%TYPE,
p_department_id job_history.department_id%TYPE
)
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/
Procedure created.
SELECT name
, type
, plsql_code_type AS CODE_TYPE
, plsql_optimize_level AS OPT_LVL
FROM user_plsql_object_settings;
NAME TYPE CODE_TYPE OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL PROCEDURE INTERPRETED 2
ADD_DEPARTMENT PROCEDURE INTERPRETED 2
ADD_DEPARTMENT_NOEX PROCEDURE INTERPRETED 2
ADD_DEPT PROCEDURE INTERPRETED 2
ADD_EMPLOYEE PROCEDURE INTERPRETED 2
ADD_JOB_HISTORY PROCEDURE NATIVE 2
ADD_ROW PROCEDURE INTERPRETED 2
......
82 rows selected.
As you can see the last SQL query result, the PLSQL_CODE_TYPE of the procedure “ADD_JOB_HISTORY” being created just now has become NATIVE.
Focusing and Concentrating Oracle Recipes, Tips and Techniques 