PL/SQL native compilation is not something that is real new but something that became real now in Oracle 11g.
How does Oracle world look without PL/SQL native compilation
- PL/SQL is compiled to intermediate form “mcode” or machine readable code.
- mcode is stored in database and interpreted at run time.
- This is the default mode set via init.ora parameter
PL/SQL native compilation in 9i/10g
- Convert PL/SQL code to C , then compile using C compiler and dynamically link into Oracle processes.
- Configure initialization parameters PLSQL_CODE_TYPE, PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT. One more reason to hire Oracle DBA.
PL/SQL native compilation in 11g
- No need of C compiler ; PL/SQL is compiled to machine code(DLL) and stored in the SYSTEM tablespace instead of file system
- Just set init.ora parameter PLSQL_CODE_TYPE to NATIVE instead of default value of INTERPRETED. All Other init.ora parameters have been deprecated
How to set it
At Session Level
- At session level before creating the PL/SQL procedure
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE
At Object Level
- Recompile the stored procedure
……………….ALTER PACKAGE mypackage COMPILE PLSQL_CODE_TYPE = NATIVE.
At Database Level
- Start the database in UPGRADE mode.
- Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
- Shutdown immediate and recompile all objects.
- Improved performance for computation intensive code.
- PL/SQL procedures with SQL will not see any significant benefits
- Data type SIMPLE_INTEGER provides significant performance improvements with native compilation vs. interpreted.
What is DBMS_PARALLEL_EXECUTE?
DBMS_PARALLEL_EXECUTE permits updating a large set of data incrementally. Basically you create smaller chunks of data by segregating the rows based on Rowid OR numeric column OR user-defined SQL and then update the chunks in parallel. Other major advantages are that the chunks are committed individually and better performance when chunks are based on ROWID.
Why is DBMS_PARALLEL_EXECUTE important ?
- Large single transaction can be broken into multiple chunks (transactions)
- Less impact to dependent systems like Golden Gate or streams
- Better performance compared to parallel DML when chunks are based on ROWID and blocks.
- Less undo space required and minimizing the chance of ORA-1555
- Impact of Rollback due to error is not severe.
- Duration of lock is reduced.
- Restart failed chunks (transactions)
- Unlike other parallel features , DBMS_PARALLEL_EXECUTE does not require Enterprise Edition (at least for now)
- Eliminates much of manual coding and better uniform distribution across parallel processes
1.Create a task with CREATE_TASK.
2.Create chunks (size) using one of the following.
3.Define the Update statement.
4.Execute the task in parallel with RUN_TASK.
5.Perform error handling and run failed chunks with RESUME_TASK.