Partially Documented Parameters - Secrets of the Oracle Database - page 9

When creating a database with the database configuration assistant (DBCA), there is a
memory configuration page for customizing most of the aforementioned pools as well as the
PGA. On this page, DBCA adds the sizes of all pools as well as the PGA and reports the resulting
figure as Total Memory for Oracle (see Figure 1-2). This leads some people to believe that this
amount of memory (4956 MB in the screenshot) will be allocated when the ORACLE instance is
started. Knowing that the SGA is allocated on instance startup, they assume the same must be
true for the PGA. However, this is not the case. PGA memory is allocated on demand. Even the
*_AREA_SIZE parameters do not cause a memory allocation of the designated size. These too are
allocated on an as-needed basis.
Since the documentation does not address the details of work area sizing, many database
administrators assume that the entire memory set aside with PGA_AGGREAGTE_TARGET is available to
a single session as long as it does not have to compete for the memory with other sessions. In
case you're curious what the real deal is, please read on.

Researching PGA_AGGREGATE_TARGET
The research presented in this section was done with Oracle10 g Release 2. Results show that
the algorithms used by Oracle9 i and Oracle10 g are different. Due to space constraints, no
example or evidence concerning Oracle9 i is included. 6

Creating a Large Table with a Pipelined Table Function
For starters, we need a table that is large enough to cause disk spilling during sort operations.
The next few paragraphs show how to code a pipelined table function that returns an arbitrary
number of rows (see file row_factory.sql in the source code depot). This function may then be
used in conjunction with the package DBMS_RANDOM to create arbitrarily sized tables with random
data. Since pipelined table functions return a collection type, we start by creating an object
type for holding a row number.

SQL> CREATE OR REPLACE TYPE row_nr_type AS OBJECT (row_nr number);
/

The pipelined table function will return a collection type made up of individual row_nr_type s.

SQL> CREATE OR REPLACE TYPE row_nr_type_tab AS TABLE OF row_nr_type;
/

The function row_factory returns any number of rows—within the limits of the ORACLE
NUMBER data type, of course. It has the two parameters first_nr and last_nr , which control how
many rows will be returned.

CREATE OR REPLACE FUNCTION row_factory(first_nr number, last_nr number)
RETURN row_nr_type_tab PIPELINED
AS
row_nr row_nr_type:=NEW row_nr_type(0);

For research on Oracle9 i , see Jonathan Lewis' article at http://www.jlcomp.demon.co.uk/untested.html .

6.