PL/SQL Enhancements in Oracle 11g

Real Native Compilation

Reference: http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf

• PL/SQL is an interpreted language.

• But it is not just like other interpreted languages, e.g. BASIC, the PL/SQL code is compiled into machine code called M-Code with a target virtual machine called PVM (PL/SQL Virtual Machine) (similar to JVM).

• The PVM is implemented as a set of subroutines in Oracle executables, and at run time, the M-Code is scanned by another such subroutines.

• The scanning detects each successive OPCODE and its OPERANDS, then calls the subroutine that implements this OPCODE with actual arguments. This run time scanning of the M-Code takes some resources.' This is where NATIVE compilation help with improvement.

• Compilations of both follow the same path.
  • Interpreted mode => M-Code is produced
  • Native mode => a platform-specific DLL (similar to .dll in Windows or .so in Unix) is produced.
  • This DLL = at run time, calls exactly the same PVM subroutines with same arguments as would have been called by scanning the M-Code.

• In short, the difference is that the scanning effort has been moved from runtime (when in interpreted mode) to compile time (when in native mode).

• Since exactly the same PVM subroutines are called with exactly the same arguments in both interpreted and native modes, the native mode is guaranteed to have exactly the same semantics as interpreted mode.

SQL Query Result Cache

• The result of a query or query block is cached for future reuse in a dedicated “SQL Result Cache”

• Cache is used when the exact or subset of query is used unless it is stale (underlying table changes).

• The Result Cache saves repetitive computation time during query execution.

• Good candidate statements
  • Access many rows, return few rows, e.g., lookup
  • Inline or subquery view with lot of computations
  • Deterministic function output

• By default, the default of RESULT_CACHE_MODE is MANUAL which means explicit “result_cache” hint must be used to make it stored in cache.

• Sample http://www.oracle-base.com/articles/11g/QueryResultCache_11gR1.php

PL/SQL Function Result Cache

• Easy way for performance boost by saving the results of the function with its parameters in SGA.

• The results can be reused by ANY session if calling the same function with same parameters.

• For data integrity, use of RELIES_ON to invalidate cache if table is modified.

• Sample - http://www.oracle-base.com/articles/11g/CrossSessionPlsqlFunctionResultCache_11gR1.php

Access Control for UTL_TCP

• http://oraexplorer.com/2009/10/the-access-control-lists-to-network-services-e-g-utl_http-utl_smtp-utl_tcp-etc-in-oracle-11g/
• http://oraexplorer.com/2010/02/oracle-11g-network-access-denied-by-access-control-list-acl-when-using-utl_inaddr/

Create disabled trigger

• Use the disable keyword in the create to avoid the risk that if the trigger fails to compile.

Specify trigger firing order

• Use follows keyword in the create statement to control the manual firing order for triggers of the same timing point (e.g., multiple before each row triggers).

Compound trigger

See Compound Trigger.

Call sequence

• Use myseq.nextval directly without select myseq.nextval into v from dual.
• This applies to myseq.currval.