PL/SQL Reference IX - Performance Tuning and Design

===========================================
Index
===========================================
Package memory allocation in SGA
Pragma SERIALLY_REUSABLE
NOCOPY hint
Global Temporary Tables

===========================================
Package memory allocation in SGA
===========================================
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
This prevents large, or less frequently used, objects from being aged out.
Default value is 5% of the value of SHARED_POOL_SIZE.
Maximum: one half of the value of SHARED_POOL_SIZE.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request, and avoid these errors:
ORA-04031: unable to allocate 2048 bytes of shared memory

DBMS_SHARED_POOL PACKAGE
Use DBMS_SHARED_POOL PACKAGE to manage objects in shared pool.
DBMS_SHARED_POOL.keep(object_name,flag)
DBMS_SHARED_POOL.unkeep(object_name,flag)

object_name
- Mandatory
- Is the Object name

flag
- Optional
- Can have any of the following values:
'P' or 'p' - Package. This us the default
'T' or 't' - Type
'R' or 'r' - Trigger
'Q' or 'q' - Sequence

===========================================
Pragma SERIALLY_REUSABLE
===========================================
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the 
duration of one call to the server. 

After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.


If a package has a spec and body, you must mark both. You cannot mark only the body.

The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. 

Each time the package is reused, its public variables are initialized to their default values or to NULL.


Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. 

===========================================
NOCOPY hint
===========================================
Oracle passes parameters by value, by default, when calling to functions and procedures.

NOCOPY hint instructs Oracle to pass parameters as a reference, rater than the default pass by value.
This is ONLY for OUT and IN OUT parameters.

Example
CREATE OR REPLACE PACKAGE get_emp_pkg IS

  TYPE v_emp_tab_type IS TABLE OF employee%ROWTYPE;

  CREATE OR REPLACE PROCEDURE get_emp_by_dept_proc 
       (p_dept_id IN NUMBER, 
        p_emp_tab OUT NOCOPY v_emp_tab_type);
END;
/

===========================================
Global Temporary Tables
===========================================
GTT - Global Temporary Tables

GTT are very useful for storing large result sets.
GTT are is a better alternative to program variables of large record sets, which consume a lot of memory.
GTT scope is limited to session .
Each session sees only it own separate set of rows.
No DML locks on data.
Indexes, Views and Triggers can be created on GTT.

Example
CREATE GLOBAL TEMPORARY TABLE emp_temp
AS SELECT * FROM employees;