Monthly Archives: July 2011

11g PL/SQL native compilation

Oracle July 29, 2011

.

.
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  PLSQL_CODE_TYPE=INTERPRETED
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

At Database Level
  • Start the database in UPGRADE mode.
  • Execute $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
  • Shutdown immediate and recompile all objects.
Benefits?
  • 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.

DBMS_PARALLEL_EXECUTE summarized

Oracle July 10, 2011 1 Comment

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 ?

  1. Large single transaction can be broken into multiple chunks (transactions)
  2. Less impact to dependent systems like Golden Gate or streams
  3. Better performance compared to parallel DML when chunks are based on ROWID and blocks.
  4. Less undo space required  and minimizing the chance of ORA-1555
  5. Impact of Rollback due to error is not severe.
  6. Duration of lock is reduced.
  7. Restart  failed chunks (transactions)
  8. Unlike other parallel features , DBMS_PARALLEL_EXECUTE  does not require Enterprise Edition (at least for now)
  9. Eliminates much of manual coding and better uniform distribution across parallel processes

Steps Involved

1.Create a task with CREATE_TASK.

2.Create chunks (size) using one of the following.

  • CREATE_CHUNKS_BY_ROWID
  • CREATE_CHUNKS_BY_NUMBER_COL
  • CREATE_CHUNKS_BY_SQL

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.