About PGA

Introduction to PGA management
Almost every Oracle professional agrees that the old-fashioned sort_area_size and hash_area_size parameters imposed a cumbersome one-size-fits-all approach to sorting and hash joins. Different tasks require different RAM areas, and the trick has been to allow "enough" PGA RAM for sorting and hash joins without having any high-resource task "hog" all of the PGA, to the exclusion of other users.

Oracle9i introduced the pga_aggregate_target parameters to fix this resource issue, and by-and-large, pga_aggregate_target works very well for most systems. You can check your overall PGA usage with the v$pga_target_advice advisory utility or a STATSPACK or AWR report. High values for multi-pass executions, high disk sorts, or low hash join invocation might indicate a low resource usage for PGA regions.

Let's take a look at the issues surrounding the hidden limits of pga_aggregate_target.

Hidden parameters for Oracle PGA regions
With proper understanding (and knowing that these undocumented parameters are not supported by Oracle), you can adjust your PGA regions to allow for system-specific sorting and hash joins.

_pga_max_size ?this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.
 
_smm_px_max_size ?This parameter is used for Oracle parallel query, and defaults to 30% of the pga_aggregate_target setting, divided by degree of parallelism (as set by a PARALLEL hint, "alter table xxx parallel" command, or the parallel_automatic_tuning initialization parameter). For example, by default a DEGREE=4 parallel query would have a maximum sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. Remember, parallel full-table scans bypass the data buffers and store the incoming data rows in the PGA region and not inside the data buffers (as defined by the db_cache_size parameter).
The limits of sorting and hashing
There are important limitations of pga_aggregate_target:

The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size.
 
No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. The algorithm further reduces this to (200/2) for sorts so the actual limit for pure sorts will be 100 megabytes.
These restrictions were made to ensure that no large sorts or hash joins hog the PGA RAM area, but there are some secrets to optimize the PGA. For example, the following set of parameters may be mutually-exclusive:

sort_area_size=1048576 <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto, unless you are using a specialized feature such as the MTS. If dedicated server connections are used, the sort_area_size parameter is ignored.
 
pga_aggregate_target = 500m <-- The maximum default allowed value is 200 megabytes, this limits sorts to 25 megabytes (5% of 500m).
 
mts_servers<>0 <-- If Multi-threaded server is being used, the pga_aggregate_target setting would be ignored in all versions except Oracle10g.
(Note: there may be some cases where sort_area_size is used in Oracle utilities, but these have not been documented, even with pga_aggregate_target.)

We also see these additional undocumented parameters:
 

Parameter Name Description
_smm_advice_enabled if TRUE, enable v$pga_advice
_smm_advice_log_size overwrites default size of the PGA advice workarea history log
_smm_auto_cost_enabled if TRUE, use the AUTO size policy cost functions
_smm_auto_max_io_size Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_min_io_size\ Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_bound overwrites memory manager automatically computed bound
_smm_control provides controls on the memory manager
_smm_max_size maximum work area size in auto mode (serial)
_smm_min_size minimum work area size in auto mode
_smm_px_max_size maximum work area size in auto mode (global)
_smm_trace Turn on/off tracing for SQL memory manager

WARNING - These are unsupported parameters and they should not be used unless you have tested their behavior on your own database and you are willing to accept full responsibility for any issues.

Super-size me
For certain Oracle applications the Oracle professional will want to allow individual tasks to exceed the default limits imposed by Oracle. For example, PC-based, 64 bit Oracle servers (1 or 2 CPU's with 8 gigabytes of RAM) will often have unused RAM available. For example, a fully-cached 5 gigabyte database on an 8 gigabyte dedicated Oracle server will have approximately 1 gigabyte available for the PGA (allowing 20% for the OS and other SGA regions):

O/S - 1.6 gig
SGA - 5 gig
PGA Space - 1 gig
Total - 8 gig
The system has a pga_aggregate_target setting of 1 gigabyte and the undocumented parameters are at their default settings.  While it is unusual for an online system to require super-sized regions for sorting (because the result sets for online screens are normally small), there can be a benefit to having large RAM regions available for the Oracle optimizer.

The Oracle cost-based optimizer will determine whether a hash join would be beneficial over a nested-loop join, so making more PGA available for hash joins will not have any detrimental effect since the optimizer will only invoke a super-sized hash join if it is better than a nested-loop join. In a system like the example above, the following settings would increase the default sizes for large sorts and hash joins while limiting those for parallel sorts.

pga_aggregate_target = 4g
_pga_max_size = 400m
_smm_px_max_size = 333m
With these hidden parameters set we see significant size increase for serial sorts and a throttling effect for parallel queries and sorts. To see a reproducible, artificial test case demonstrating sort throttling, Mike Ault has prepared a 230 page artificial test case: Validation of Sort Sizes in a Linux Oracle10g Database. However, bear in mind that it only valid for a specific release of Oracle10g, on a specific hardware and OS environment, and not using any optional features such as the MTS.

A RAM sort or hash join may now have up to the full 200 megabytes (5% of pga_aggregate_target) a 400% increase over a 1 gigabyte pga_aggregate_target setting. With the default settings, only a 200% (100 megabyte size) increase would be possible.
 
Parallel queries are now limited to 333 megabytes of RAM (30% of pga_aggregate_target or _smm_px_max_size), such that a DEGREE=4 parallel query would have a maximum of 83 megabytes (333 meg/4) per slave which may actually be less due to internal sizing algorithms that set the memory increments used in setting sort areas. This throttling is to prevent one parallel query using all available memory since _smm_px_max_size would default to 1.2 gigabytes with the setting for pga_aggregate_target at 4 gigabytes.
 
You must be careful in setting the pga_aggregate_target to greater than the available memory, calculate the maximum number of users who would be sorting/hashing and multiple that times the predicted size to get your actual limitations otherwise ORA-4030 errors or swapping may occur.
In conclusion, overriding the built-in safeguards of pga_aggregate_target can make more efficient use of RAM resources in cases where large RAM regions are available on the database server. When used with care (and the blessing of Oracle Technical Support) it can often make sense to over-ride these default values to make better use of expensive RAM resources.

There is also lots of evidence that changing these parameters will have a positive effect of large, batch-oriented Oracle jobs, but you must be very careful to fully understand the limitations of the PGA parameters:

Success stories for PGA size expansion
If you have a limited number of active sessions you may wish to override the PGA governor that only allows any single task to consume 5% of the total PGA.  Laurent Schneider notes in Oracle MetaLink that overriding the PGA defaults made a large batch processes run more than 8x faster:

"I set appropriate values for pga_aggregate_target and _pga_max_size...

alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2000000000;

...and I gave the query some hints "NOREWRITE FULL USE_HASH ORDERED". As a result, it boosted my query performance from 12 hours to 1.5 hour."

Ah, if only it were that easy, just change a setting and batch jobs run six times faster.  Laurent Schneider notes some perils and reliability issues relating to this parameter and says "this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons". 
While Laurent abandoned the undocumented approach, the promise of eight times faster execution speeds are very tempting.  Once you get permission from Oracle Technical Support to set an undocumented parameter, they can work with  to resolve errors.  While they may not address bugs, they may be able to provide alternatives and workarounds.
 
References
Metalink Note:223299.1: "If pga_aggregate_target is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored." . . .

"If pga_aggregate_target is set in the init.ora, then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored. . .

If pga_aggregate_target is set in init.ora, then WORKAREA_SIZE_POLICY defaults to AUTO."
 
Metalink Note: 223730.1: "1- When we set the pga_aggregate_target and WORKAREA_SIZE_POLICY to auto then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters."
 
Metalink Note: 30918.1: "Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting pga_aggregate_target instead. SORT_AREA_SIZE is retained for backward compatibility."

From: http://www.dba-oracle.com

--
Thanks
Xiangzhen

BULK COLLECT and BULK BIND in PLSQL

 

rem -----------------------------------------------------------------------
rem Filename:   bulkbind.sql
rem Purpose:    Simple program to demonstrate BULK COLLECT and BULK BIND.
rem Notes:      Bulk operations on ROWTYPE only work from and above.
rem Date:       12-Feb-2004
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
set serveroutput on size 50000

DECLARE
  CURSOR emp_cur IS SELECT * FROM EMP;

  TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab emp_tab_t;            -- In-memory table

  rows NATURAL        := 10000;   -- Number of rows to process at a time
  i    BINARY_INTEGER := 0;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Bulk collect data into memory table - X rows at a time
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
    EXIT WHEN emp_tab.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

    FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
      -- Manipumate data in the memory table...
      dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
    END LOOP;

    -- Bulk bind of data in memory table...
    FORALL i in emp_tab.FIRST..emp_tab.LAST
      INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

  END LOOP;
  CLOSE emp_cur;
END;
/

 

 

Bulk Insert [forall] in PL/SQL

create table t_ (a number, b varchar2(30));

create type t_number as table of number;
/

create type t_varchar as table of varchar2(30);
/

set serveroutput on size 1000000 format wrapped

declare
  v_time number;
begin
  dbms_output.put_line('');

  v_time := dbms_utility.get_time;

  for r in (select rownum, object_name from dba_objects) loop
    insert into t_ values (r.rownum, r.object_name);
    exit when r.rownum = 10000;
  end loop;

  dbms_output.put_line('time used: ' || (dbms_utility.get_time - v_time) / 100 || ' secs');
  dbms_output.put_line('');
end;
/

select count(*) from t_;

truncate table t_;

declare
  v_time    number;
  a_varchar t_varchar:=t_varchar();
  a_number  t_number :=t_number();
begin
  dbms_output.put_line('');

  v_time := dbms_utility.get_time;

  for r in (select rownum, object_name from dba_objects) loop
    a_number.extend;
    a_varchar.extend;
   
    a_number (a_number.count)  := r.rownum;
    a_varchar(a_varchar.count) := r.object_name;

    if mod(r.rownum, 550) = 0 then
      forall i in 1 .. a_number.count
        insert into t_ values(a_number(i), a_varchar(i));

      a_number.delete;
      a_varchar.delete;

      exit when r.rownum = 10000;

    end if;

    if r.rownum = 10000 then
      forall i in 1 .. a_number.count
        insert into t_ values(a_number(i), a_varchar(i));

      exit;
    end if;
   
  end loop;

  dbms_output.put_line('time used: ' || (dbms_utility.get_time - v_time)
    / 100 || ' secs');
  dbms_output.put_line('');
end;
/

select count(*) from t_;

drop table t_;
drop type t_number;
drop type t_varchar;

Noted.

--
Thanks
Xiangzhen

OS： Red Hat Enterprise Linux ES relea...

OS： Red Hat Enterprise Linux ES release 4 (Nahant Update 4)

DB： Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SGA：

      Total System Global Area  880803840 bytes
      Fixed Size                  1222384 bytes
      Variable Size             167774480 bytes
      Database Buffers          704643072 bytes
      Redo Buffers                7163904 bytes
PGA：281M

 

-- 二表关联更新(200万，200万，表结构同dba_tables)
-- T01.Id 有索引
SQL> select count(*) from t01;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t02;

 COUNT(*)
----------
 2000000

SQL> @/tmp/3.sql
04:07:44
04:09:35

PL/SQL procedure successfully completed.

SQL> ! cat /tmp/3.sql
declare
 maxrows number default 1000;
 cnt number;
 type type_id is table of number index by pls_integer;
 ids type_id;
 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 -- INDEX BY PLS_INTEGER;
 cursor cur is
 select /*+ hash(t01,t02) */ t02.id
 from t01,t02
 where t01.id=t02.id;
begin
 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 cnt := cnt + 1;
 open cur;
 loop
 exit when cur%notfound;
 fetch cur bulk collect into ids limit maxrows;
 forall i in 1 .. ids.count
 update t01 set val = 'abc'
 where id = ids(i);
 cnt := cnt + 1;
 commit;
 --dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 end loop;
 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
end;
/

-- 六表关联更新(200万，200万，200万，200万，200万，200万，表结构同dba_tables)
-- T01.Id 有索引
SQL> select count(*) from t01;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t02;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t03;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t04;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t05;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t06;

 COUNT(*)
----------
 2000000

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 type type_id is table of number index by pls_integer;
 5 ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id
 16 and rownum<=500000;
 17 begin
 18 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 19 cnt := cnt + 1;
 20 open cur;
 21 loop
 22 exit when cur%notfound;
 23 fetch cur bulk collect into ids limit maxrows;
 24 forall i in 1 .. ids.count
 25 update t01 set val = 'abc'
 26 where id = ids(i);
 27 cnt := cnt + 1;
 28 commit;
 29 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30 end loop;
 31 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 32* end;
SQL> /
04:33:45
04:35:09

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 type type_id is table of number index by pls_integer;
 5 ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id;
 16 -- and rownum<=500000;
 17 begin
 18 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 19 cnt := cnt + 1;
 20 open cur;
 21 loop
 22 exit when cur%notfound;
 23 fetch cur bulk collect into ids limit maxrows;
 24 forall i in 1 .. ids.count
 25 update t01 set val = 'abc'
 26 where id = ids(i);
 27 cnt := cnt + 1;
 28 commit;
 29 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30 end loop;
 31 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 32* end;
SQL> /
04:35:38
04:38:57

PL/SQL procedure successfully completed.

-- fetch 二个字段的情况
SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 -- type type_id is table of number index by pls_integer;
 5 -- ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id,t06.table_name
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id
 16 and rownum<=100000;
 17 type type_id is table of t02.id%type index by pls_integer;
 18 type type_name is table of t06.table_name%type index by pls_integer;
 19 ids type_id;
 20 names type_name;
 21 begin
 22 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 23 cnt := cnt + 1;
 24 open cur;
 25 loop
 26 exit when cur%notfound;
 27 fetch cur bulk collect into ids,names limit maxrows;
 28 forall i in 1 .. ids.count
 29 update t01 set val = names(i)
 30 where id = ids(i);
 31 cnt := cnt + 1;
 32 commit;
 33 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 34 end loop;
 35 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 36* end;
SQL> /
05:05:38
05:06:29

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 -- type type_id is table of number index by pls_integer;
 5 -- ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id,t06.table_name
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id;
 16 -- and rownum<=100000;
 17 type type_id is table of t02.id%type index by pls_integer;
 18 type type_name is table of t06.table_name%type index by pls_integer;
 19 ids type_id;
 20 names type_name;
 21 begin
 22 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 23 cnt := cnt + 1;
 24 open cur;
 25 loop
 26 exit when cur%notfound;
 27 fetch cur bulk collect into ids,names limit maxrows;
 28 forall i in 1 .. ids.count
 29 update t01 set val = names(i)
 30 where id = ids(i);
 31 cnt := cnt + 1;
 32 commit;
 33 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 34 end loop;
 35 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 36* end;
SQL> /
05:07:06
05:11:05

PL/SQL procedure successfully completed.

SQL> select id,table_name,val from t01 where rownum<10;

        ID TABLE_NAME                     VAL
---------- ------------------------------ ------------------------------
         1 CON$                           CON$
         2 UNDO$                          UNDO$
         3 CDEF$                          CDEF$
         4 CCOL$                          CCOL$
         5 PROXY_ROLE_DATA$               PROXY_ROLE_DATA$
         6 FILE$                          FILE$
         7 FET$                           FET$
         8 TS$                            TS$
         9 PROXY_DATA$                    PROXY_DATA$

9 rows selected.

A few examples are used to handle a Oracle job

Example 1:

The following can be used to create a job using a predefined Program and Schedule:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_01`,
 program_name            => `BACKUP_PROGRAM`,
 schedule_name            => `BACKUP_SCHEDULE`);
END;
/

Example 2:

The following can be used to create a job using a predefined Program without a predefined Schedule:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_02`,
 program_name            => `BACKUP_PROGRAM`,
 start_date        => `TRUNC(SYSDATE)+23/24`,
 repeat_interval => `FREQ=WEEKLY; BYDAY=SUN` );
END;
/

Example 3:

The following can be used to create a job using a predefined Schedule without a predefined Program:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_03`,
 schedule_name            => `BACKUP_SCHEDULE`,
 job_type                      => `EXECUTABLE`,
 job_action       => `/dba/scripts/weekly_backup.sh`
);
END;
/

Example 4:

The following can be used to create a job without a predefined Program and Schedule:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
 job_name                    => `BACKUP_JOB_04`,
 job_type                      => `EXECUTABLE`,
 job_action       => `/dba/scripts/weekly_backup.sh`,
 start_date        => `TRUNC(SYSDATE)+23/24`
 repeat_interval => `FREQ=WEEKLY; BYDAY=SUN`
);
END;
/

The following is the syntax to run, stop, copy, and drop a job:

DBMS_SCHEDULER.RUN_JOB
(  job_name                  in varchar2 );
DBMS_SCHEDULER.STOP_JOB
( job_name                   in varchar2,
 force               in Boolean default false );

The copy_job procedure copies all attributes of an existing job to a new job.

DBMS_SCHEDULER.COPY_JOB
(
 old_job                       in varchar2,
 new_job                      in varchar2);

DBMS_SCHEDULER.DROP_JOB
(
 job_name                    in varchar2,
 force               in Boolean default false
);

Create Job Class

A Job Class defines a category of jobs that share common resource usage requirements.  A Job Class is associated with two attributes: the resource consumer group, which defines a set of user sessions that have common resource processing requirements; and a database service name, which defines the instance to which the job class belongs.  Each job belongs to a single job class at any given time.  By associating a Job with a Job Class, the amount of resources a Job can use during its execution can be managed.

The syntax to create a Job Class is:

DBMS_SCHEDULER.CREATE_JOB_CLASS
(
 job_class_name                       in varchar2,
 resource_consumer_group       in varchar2 default null,
 service                         in varchar2 default null,
 log_purge_policy                     in varchar2 default null,
 comments                                in varchar2 default null
);

By default, the Scheduler log table entries are not purged. The log_purge_policy defines the policy for purging the log table entries.

Data Dictionary Views

The following SQL script provides a list of data dictionary views used to monitor the Scheduler� activities:

SQL> select table_name, comments
   2 from   dict
   3 where table_name like 'DBA%SCHEDULER%'
   4 order by table_name;

 

 

These examples published by Burleson at www.dba-oracle.com. here just refer to it.