(2) Introduction to PL/SQL

Advantages Main features Architecture
Tight Integration with SQL Error handling PL/SQL Engine
High Performance Blocks PL/SQL Units
High Productivity vars and consts Compilation Parameters
Portability subprograms
Scalability packages
Manegeability triggers
Support OO Programming I/O
Suppport Dev Web Apps Data Abstraction
Suppport Dev Server Pages Control Stmts
Conditional compilation
Process query result one row at a time

Advantages
Integration with SQL
- You can use all SQL data manipulation, cursor control, transaction control stmts
- You can use all SQL functions, operators, and pseudocolumns.
- PL/SQL fully supports SQL data types.(i.e. VARCHAR2 <--> VARCHAR2)
- Attributes: %TYPE and %ROWTYPE (define a var/record in PL/SQL with the type of a column/row)
- run a query and process the resultset one row at a time
- supports static SQL and dynamic SQL.

High Performance
- Reduced network traffic: Blocks of SQL stmts can be send at once and individual stmts processed by the SQL Engine in the database.
- SQL statement reuse: SQL stmts can be reused by the DB (less parsing takes place) each time the same code runs. (PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind arguments)
- Subprograms stored in executable form: Cached and shared among users. Reduced parsing/compiling. One network call might lead to many SQL stmts in the server (less network traffic).
- Optimizer: rearrange code for better performance.

Scalability
- If you can expand server power, concentrate code on the server to reduce network and use shared memory.
- Oracle Connection Manager can multiplex network connections.

Manageability
- One central copy of the program, rather than one in each client.

Support for OO Programing
- Through the use of Abstract data types.

Support for Web Applications
- can generate HTML/XML from the DB with PL/SQL
- Using PL/SQL instead of CGI Perl Scripts you can use DML statements, dynamic SQL, and cursors, and you also eliminate the overhead of forking a new CGI process to handle each HTTP request.



PL/SQL Gateway enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener.
mod_plsql is one implementation of the PL/SQL Gateway.
It is a plug-in of Oracle HTTP Server. It enables Web browsers to send HTTP requests in the form of URLs that include parameter values to be passed to a stored subprogram.

mod_plsql mod_plsql (a) translates the URL; (b) invokes the subprogram and (c) returns the output (html) to the client.
Advantages over the embedded PL/SQL gateway (DBMS_EPG):
  • Can run in an Oracle HTTP Server in front of a firewall while the database remains behind the firewall. The embedded gateway (DBMS_EPG) cannot take this configuration. In which situations could this configuration be preferred?
  • The embedded gateway does not support mod_plsql features such as dynamic HTML caching, system monitoring, and logging in the Common Log Format.
Embedded PL/SQL Gateway Runs in the XML DB HTTP Listener in the DB.
Provides core features of mod_plsql.
Configured with the DBMS_EPG package in the PL/SQL Web Toolkit.
Does not require the HTTP Server powered by Apache.

For a browser to have access to a PL/SQL app: (1) a Database Access Descriptor (DAD) must be created and (2) mapped to a virtual path.
DAD: set of configuration values used for database access.
DAD: represented as a servlet in XML DB HTTP Server.
Virtual path mapping: makes the application accessible under a virtual path of the XML DB HTTP Server.

SQL Web Toolkit PL/SQL Web Toolkit is a set of PL/SQL packages that provides a generic interface to use stored subprograms invoked by mod_plsql at run time.

Web Toolkit API enables stored subprograms to perform actions such as:
  • Obtain information about an HTTP request
  • Generate HTTP headers such as content-type and mime-type
  • Set browser cookies
  • Generate HTML pages

Support for the Development of Server Pages

PL/SQL Server Pages (PSP) are server-side scripts that include dynamic content, including the results of SQL queries, inside web pages. You can author the web pages in an HTML authoring tool and insert blocks of PL/SQL code.

Simple Server Page. Example extracted from the Oracle manual.
To compile and load the server page, use the loadpsp utility:


Why use PSP?
  • Easier way to create web pages that include DB-generated content. Use your preferred tool to develop web pages and embed PL/SQL.
  • Writing out HTML content line by line: PSP is more convenient than HTP and HTF packages.
  • Client does not need to process any special script tags besides plain HTML. All processing is done on the DB server. Supports all browsers.
  • Less network traffic. Less round-trips to the DB.
  • Follow a rapid, iterative development process. Central control of the software. Client needs only a browser.


Main Features

Blocks, Subprograms and other Constructs
Oracle supports two types of PL/SQL Blocks: named and anonymous.
named
  • Can be stored in the DB (procedures, funtions, packages)
  • May be dynamically created, allocated a name and executed, but not stored in the DB.
anonymous
  • Can be dynamically created and executed, but cannot be stored in the database.
  • Are treated as single SQL statements
  • Are compiled each time they are loaded into memory.
  • Compilation stages: Syntax checking (syntax check, parse tree gen) => Semantic check (Type checking; parse tree refined) => Code generation

(a) Categories of constructs that can be created at the server side:
  • Anonymous blocks – PL/SQL blocks without a name. Not stored in the DB. Run and gone.
  • Stored Procedures and Functions – Named PL/SQL blocks also known as subprograms
  • Stored Packages – PL/SQL constructs that are used to group related objects
  • Database Triggers – PL/SQL blocks associated with a table, a view, a schema, or the database
  • Object Types - (Abstract Data Types - ADTs) – User-Defined composite data types

<< label >> (optional)
DECLARE -- Declarative part (optional)
-- Declare local types, vars, & subprograms

BEGIN -- Executable part (required)
-- At least one statement
-- You may use a NULL stmt: (BEGIN NULL; END;)

[EXCEPTION -- Exception-handling part (optional)
-- handlers for exceptions raised in exec part]
END;
A block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. It can have a label.

(b) In the client side (Forms, Developer, etc), you can create constructs:
  • Anonymous blocks
  • Application Procedures and Functions
  • Application Packages
  • Application Triggers
  • Object Types (Abstract Data Types - ADTs)


named block
CREATE OR REPLACE <block type> <block name>
IS | AS -- Mandatory
. . .
-- Declarative section
-- (Type, Name[P|F], Parameters, RETURN)
BEGIN
. . .
-- Executable section
EXCEPTION
. . . –- Exception-handling section (optional)
END;
-- Marks the end of the block

<block type> can be:
Procedure, Function, Package, Trigger

Why use Subprograms? (Benefits)
Performance

Integrity/Security

Maintenance

Clarity

(a) Improved Performance
  • Multiple statements sent to the Server in a group.=> Reduced network traffic.
  • Subprgrams (named) are compiled once (when created).: Avoid re-parsing at execution time.
  • Compiled code placed into the shared pool, thus shared by multiple sessions.

(b) Data Security and Integrity
  • Inherit the privileges of the owner by default. (Control indirect access to objects)
  • Atomicity: Either all statements complete, or none will.

(c) Easy to Maintain
  • Subprograms are easily shared by multiple apps.
  • change in the DB Objects require change on the subprogram only.

(d) Increased code clarity
  • More readability: each section of the subprogram block is defined separately.
    (i.e., all exceptions are handled in the exception-handling section).


Input/Output
Besides the use of SQL Statements, PL/SQL has also a number of I/O-processing packages:

DBMS_OUTPUT Lets PL/SQL blocks, subprograms, packages, and triggers display output.
Especially useful for displaying PL/SQL debugging information
HTF Has hypertext functions that generate HTML tags (i.e., HTF.ANCHOR => anchor tag ).
HTP Has hypertext procedures that generate HTML tags.
DBMS_PIPE Lets two or more sessions in the same instance communicate.
UTL_FILE Lets PL/SQL programs read and write OS files.
UTL_HTTP Lets PL/SQL programs make HTTP callouts, and access data on the Internet over HTTP.
UTL_SMTP Sends electronic mails (emails) over Simple Mail Transfer Protocol (SMTP) as specified by RFC821.

Data Abstraction
Allows you to access the essential property of data without getting too involved with details.
Abstraction mechanisms include:
Cursors, Composite Variables, %ROWTYPE attribute, %TYPE attribute, Abstract Data Types



Cursors
Pointer to a private SQL Area that keeps information about a specific SQL stmt or PL/SQL SELECT INTO statement.

Composite Variables
Have internal components, which can be access individually. Can be passed as parameters.
Two kinds of composite variables: collections and records.
Collections
  • The internal components are always of the same data type, and are called elements. You access each element by its unique index.
  • Lists and arrays are classic examples of collections.
Records
  • Here the internal components can be of different data types, and are called fields.
  • You access each field by its name.
  • A record variable can hold a table row, or some columns from a table row.
  • Struct (in C); RECORD (in Pascal)


%ROWTYPE attribute and %TYPE attribute
The %ROWTYPE attribute is used to declare a record that represents a FULL row of a database table.


The %ROWTYPE can also be used to represent a PARTIAL row (using a Cursor).

The record fields do not inherit the constraints or initial values of the corresponding columns


With the %TYPE attribute you can declare a data item of the same data type as a previously declared variable or column (without knowing what that type is).

If the declaration of the referenced item changes, then the declaration of the referencing item changes accordingly.

referencing item inherits from the referenced item: (a) datatype and size; (b) constraints (unless the referenced item is a column)

Referencing item does not inherits from the referenced item: (a) the initial value of the referenced item.

Abstract Data Types (ADTs) - Creating OO Classes
  • Composed of data structure (attributes) + subprograms that manipulate the data (methods).
  • Stored in the database. Instances of ADTs can be stored in tables and used as PL/SQL variables.
  • Reduce complexity: separate a large system into logical, reusable, components.
Check the static dictionary views:

DBA_OBJECTS:
OBJECT_TYPE='TYPE'
DBA_TYPES:
TYPECODE='OBJECT'

Control Statements
  • Conditional selection: IF THEN [ELSE || ELSIF]; CASE
  • Loop: LOOP, FOR, Cursor FOR, WHILE [EXIT || EXIT WHEN; CONTINUE || CONTINUE WHEN]
  • Sequential control: GOTO, NULL


Conditional Compilation
Customize functionality of a PL/SQL application. Compiler recognizes db versions and enable/disable features properly.
Recognize development/production environment: activate debugging/tracing properly.

Processing a Query Result Set One Row at a Time
You can issue a SQL query and process the rows of the result set one at a time.

You can use a basic loop, or you can control the process precisely by using a cursor to run the query, retrieve the results, and finish processing.


Architecture
PL/SQL Engine
Compilation and run-time system that is present in the Server and/or in an app dev tool.

PL/SQL Units

  • Anonymous Block
  • Function
  • Procedure
  • Trigger
  • Library
  • Package
  • Package Body
  • Type
  • Type Body

PL/SQL compilation parameters
Category of DB Init parameters.
The compile-time values of the parameters are stored with the metadata of each stored PL/SQL unit, which means that you can reuse those values when you explicitly recompile the unit.
To explicitly recompile a stored PL/SQL unit and reuse its parameter values, you must use an ALTER statement with both the COMPILE clause and the REUSE SETTINGS clause

PLSCOPE_SETTINGS Controls compile-time collection, cross-reference, and storage of PL/SQL source text identifier data.
PLSCOPE_SETTINGS = IDENTIFIERS:{ NONE | ALL }
PLSQL_CCFLAGS Enables you to control conditional compilation of each PL/SQL unit independently.
PLQL_CODE_TYPE Defines compilation mode for PL/SQL libary units.
PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
PLSQL_OPTIMIZE_LEVEL The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Range: {0,3}
PLSQL_WARNINGS enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors.
NLS_LENGTH_SEMANTICS





Oracle Memory Management Methods



Automatic SGA Management
  • You specify the total amount of SGA availabe using SGA_TARGET
  • Oracle automatically distributes the memory among the various SGA components.
  • SGA Components: Pools of memory used to satisfy different classes of memory allocation requests.
    • Shared Pool (Library cache, Data Dictionary Cache, Server Result cache, reserved pool, Private sql areas, etc)
    • Large Pool
    • Database Buffer Cache
    • Java Pool
    • Streams Pool
    • Fixed SGA
  • If you are using an SPFILE, the database remember the sizes of the various components across instance shutdowns (no need to learn workload characteristics at each startup).
  • Oracle allocates/deallocates memory in chuncks of allocation units (granules)
    • If total sga size < 1G ==> granule = 4Mb.
    • If total_sga_size > 1G ==> granule = 16Mb


1. Automatic Instance tuning
  • New on Oracle 11g
  • Set parameters:
    • MEMORY_TARGET (dynamic) and
    • MEMORY_MAX_TARGET (static and optional)
  • Database redistributes memory as needed between SGA and PGA.
  • Oracle automatically calculate the values for SGA_TARGET and PGA_TARGET.
  • MEMORY_TARGET can be dynamically modified.
  • If SGA_TARGET or PGA_AGGREGATE_TARGET are specified, then
    • MEMORY_TARGET >= SGA_TARGET + PGA_AGGREGATE_TARGET




2. Automatic SGA and PGA tuning
  • Until Oracle 10g, automatic memory management was configured for the SGA and PGA separately.
  • The amount of memory allocated to both of them was fixed and unused space in one of them could not be used by the other.
  • Set parameters:
    • SGA_TARGET and
    • SGA_MAX_TARGET (optionally) and
    • PGA_AGGREGATE_TARGET

  • You can query the V$SSGAINFO view to check the size of the various SGA components and see the allocation unit size (called granule size) that is being used by the instance.
  • granule size is platform-specific and is determined by the total SGA size
SQL> select name, bytes, resizeable from v$sgainfo;

NAME                            BYTES      RES
------------------------------- ---------- ---
Fixed SGA Size                  1336260    No
Redo Buffers                    12582912   No
Buffer Cache Size               318767104  Yes
Shared Pool Size                385875968  Yes
Large Pool Size                 16777216   Yes
Java Pool Size                  16777216   Yes
Streams Pool Size               33554432   Yes
Shared IO Pool Size             0          Yes
Granule Size                    16777216   No
Maximum SGA Size                1272213504 No
Startup overhead in Shared Pool 67108864   No
Free SGA Memory Available       486539264

12 rows selected.


3. Automatic SGA and Manual PGA tuning
  • Set parameters for SGA:
    • SGA_TARGET and
    • SGA_MAX_TARGET (optionally)
  • Set parameters for PGA:
    • SORT_AREA_SIZE
    • HASH_AREA_SIZE
    • BITMAP_MERGE_AREA_SIZE


4. Manual SGA and Automatic PGA tuning
  • Set parameters for SGA:
    • SHARED_POOL_SIZE
    • DB_CACHE_SIZE
    • LARGE_POOL_SIZE
    • JAVA_POOL_SIZE
    • STREAMS_POOL_SIZE
  • Set parameters for PGA:
    • PGA_AGGREGATE_TARGET


5. Fully manual (SGA and PGA) tuning
  • Set parameters for SGA:
    • SHARED_POOL_SIZE
    • DB_CACHE_SIZE
    • LARGE_POOL_SIZE
    • JAVA_POOL_SIZE
    • STREAMS_POOL_SIZE
  • Set parameters for PGA:
    • SORT_AREA_SIZE
    • HASH_AREA_SIZE
    • BITMAP_MERGE_AREA_SIZE

Memory Management on Oracle 11g




About the Shared Global Area (SGA)


  • SGA_MAX_SIZE : specifies the maximum size of the SGA for the lifetime of the instance.
  • If not specified, defaults to the sum of all components specified or defaulted at initialization time.
SQL> show parameter sga_max_size

NAME           TYPE        VALUE
-------------- ----------- ------
sga_max_size   big integer 1216M

  • You enable automatic shared memory management by setting SGA_TARGET to a nonzero value.
  • STATISTICS_LEVEL must be set to TYPICAL or ALL

  • The entire SGA should fit in real memory. If virtual memory needs to be used performance decreases dramatically.
  • Use V$SGA_TARGET_ADVICE to help decide on a value for SGA_TARGET

To set the value for SGA_TARGET:
(1) find out how much space the SGA is using by:
SQL> select (
  2    ( select sum(value) from v$sga) -
  3    ( select current_size from v$sga_dynamic_free_memory)
  4    ) "sga_target"
  5  from dual;

sga_target
----------
 785674240

(2) Edit the text initialization parameter file and restart the database, or issue the statements below:
(a) SQL> ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
(b) SQL> ALTER SYSTEM SET <sga_component_parameter> = 0;  -- for every component.



Database Buffer Cache
  • Optimizes physical I/O:
    • After a COMMIT: writes redo to disk
    • DBWn performs lazy writes in the background (writes cold, dirty blocks to disk)
  • Buffer states can be:
    • Unused - buffer is available for use
    • Clean - Used earlier and now contains a read-consistent version of a block as of a point in time. Does not need to be checkpointed. Can be pinned to the cache so it does not age out of memory.
    • Dirty - Contains modified data not yet written to disk. Must be checkpointed befor reusing.
  • Buffer Modes
    • Current mode get - same as db block get: read block as is in buffer, including when contains uncommitted changes
    • Consistent mode - provides a read-consistent version of a block (read undo data if needed)
  • Buffer reads
    • Cache hit - block is on buffer cache. Logical read occurs. If flash cache list is used, an optimized physical read takes place.
    • Cache miss - block is not in memory. A physical read is required.
    • Buffer caceh hit ratio - The relative frequency of logical versus physical reads
    • .
  • Buffer pools
    • Default pool - where blocks are normally cached. (only pool by default)
    • Keep pool - To keep frequently accessed blocks that would have to be aged out for lack of space. Pinned blocks stay here.
    • Recycle pool - for infrequently used blocks. Full table scan blocks go here.
    • non-default block size pools - tablespaces with different block sizes have their blocks stored on size-specific pools.








Log Buffer
  • LWGR writes redo sequentially to disk.
  • DBWn performs scattered writes of data blocks to disk.
  • LOG_BUFFER parameter specifies the amount of memory used for the Redo log Buffer.
  • This value is taken out of the the area specified in SGA_TARGET







Shared Pool
  • Stores parsed SQL, PL/SQL code, system parameters and data dictionary information
  • Library Cache - Stores executable SQL, parsed PL/SQL code, locks and library cache handles and private sql areas (in shared server environment).
    • Library cache hit - also known as soft parse: Occurs when a parsed representation of a SQL statements exists in the library cache and is reused.
    • Libray cache miss (hard parse): Parsed representation is not found in the library cache and has to be recreated.
  • Use ALTER SYSTEM FLUSH SHARED_POOL to manually remove all information in the shared pool
  • Use RESULT_CACHE hint to indicate that the results of a SQL query should be stored in the result cache.
  • RESULT_CACHE_MODE parameter specify whether the SQL query result cache is used for all queries or only for annontated queries.




Large Pool
  • Optional area intended for large memory allocations (i.e. buffers for RMAN I/O slaves)

Java Pool
  • Stores all session-sepcific Java code and data within the Java Virtual Machine (JVM).
  • It includes java objects that are migrated to the Java session space at end-of-call.
  • Includes the share part of each java class (methods an dread-only memory such as code vectors). (dedicated server connections only).

Streams Pool
  • Stores buffered queue messages and provides memory for Oracle Streams capture and apply processes