PL/SQL Enhancements in Oracle 11g - Revision history

Ittichai: /* PL/SQL Function Result Cache */

2011-11-16T21:40:33Z

PL/SQL Function Result Cache

Ittichai at 21:38, 16 November 2011

2011-11-16T21:38:52Z

Ittichai at 01:46, 15 March 2011

2011-03-15T01:46:14Z

Ittichai at 01:45, 15 March 2011

2011-03-15T01:45:41Z

Ittichai at 01:45, 15 March 2011

2011-03-15T01:45:12Z

Ittichai at 01:37, 15 March 2011

2011-03-15T01:37:40Z

Ittichai at 01:36, 15 March 2011

2011-03-15T01:36:04Z

Ittichai at 01:30, 15 March 2011

2011-03-15T01:30:28Z

Ittichai: Created page with "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 othe..."

2011-03-15T01:30:10Z

Created page with "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 othe..."

New page

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.

@@ Line 36: / Line 36: @@
 == PL/SQL Function Result Cache ==
-Coming soon
+* Easy way for performance boost by saving the results of the function with its parameters in SGA.
 == Access Control for UTL_TCP ==

← Older revision		Revision as of 21:38, 16 November 2011
Line 18:		Line 18:

	* 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.		* 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 ==		== PL/SQL Function Result Cache ==

@@ Line 41: / Line 41: @@
 == The Continue statement ==
-== ''when others then null'' and other compile-time warnings ==
+== "when others then null" and other compile-time warnings ==
 [[Category:Oracle_11g]]

@@ Line 33: / Line 33: @@
 == Compound trigger ==
-[[Compound Trigger]]
+See [[Compound Trigger]].
 == Call sequence ==

@@ Line 31: / Line 31: @@
 == 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).
 [[Category:Oracle_11g]]

@@ Line 1: / Line 1: @@
 Reference: http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf
@@ Line 18: / Line 19: @@
 * 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.
 [[Category:Oracle_11g]

← Older revision		Revision as of 01:30, 15 March 2011
Line 17:		Line 17:

	* 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.		* 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.
		+
		+	[[Category:Oracle_11g]