Redo/Undo explosion from thick declared table insert

This blog presents a Redo/Undo explosion caused by thick declared table insert. Originally there is a thin table (thin_tab) consisting of two columns: a number and a 40 char varchar2. Later two new columns with 1000 char varchar2 each are required to store some seldom occurred message, so a new table (thick_tab) is created by adding these two new columns.

The test code is performed on 10gR2 and 11gR2 on a NOARCHIVELOG-mode database with
   undo_management=AUTO,
   db_block_size=8192,
   nls_characterset=AL32UTF8.

First we insert 10000 rows into the thin_tab, then we insert the same content into the thick_tab. The two new columns in thick_tab are not used at all. Table test_stats is used to store the test statistics for each step.

drop table test_stats;

create table test_stats
    (step  varchar2(10),
     name  varchar2(30),
     value number       );

drop table thin_tab;

create table thin_tab
(
  num    number,
  txt    varchar2(40 char)
)
tablespace sysaux
pctused    0
pctfree    10
initrans   1
maxtrans   255
storage    (
            initial          64k
            next             1m
            minextents       1
            maxextents       unlimited
            pctincrease      0
            buffer_pool      default
           )
logging
nocompress
nocache
noparallel
monitoring;

drop table thick_tab;

create table thick_tab
(
  num    number,
  txt    varchar2(40 char),
  txtn1  varchar2(1000 char),
  txtn2  varchar2(1000 char)
)
tablespace sysaux
pctused    0
pctfree    10
initrans   1
maxtrans   255
storage    (
            initial          64k
            next             1m
            minextents       1
            maxextents       unlimited
            pctincrease      0
            buffer_pool      default
           )
logging
nocompress
nocache
noparallel
monitoring;

---- step_1 ----
insert into test_stats
select 'step_1' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');

---- step_2 insert into thin_tab ----
insert into thin_tab(num, txt)
select level, 'abc' from dual connect by level <= 10000;

insert into test_stats
select 'step_2' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');

---- step_3 insert into thick_tab ----
insert into thick_tab(num, txt)
select level, 'abc' from dual connect by level <= 10000;

insert into test_stats
select 'step_3' step, vn.name, vs.value
from   v$sesstat  vs
     , v$statname vn
where  vs.sid = userenv('sid')
  and  vs.statistic# = vn.statistic#
  and  vn.name in ('redo size', 'undo change vector size');
 
select step, name, value,
       (value - lag(value) over (partition by name order by step)) diff
from   test_stats;

commit;

select segment_name, blocks, bytes
from   dba_segments
where  segment_name in ('THIN_TAB', 'THICK_TAB');

Output:

STEP    NAME                     VALUE       DIFF
------- ------------------------ ----------  ----------
step_1  redo size                11'592'572 
step_2  redo size                11'793'484     200'912
step_3  redo size                14'335'284   2'541'800
step_1  undo change vector size   3'011'440 
step_2  undo change vector size   3'037'820      26'380
step_3  undo change vector size   3'720'120     682'300

SEGMENT_NAME  BLOCKS  BYTES
------------- ------  -------
THICK_TAB         24  196'608
THIN_TAB          24  196'608

Above output demonstrates thick_tab insert generated 10 times redo,and 30 times undo than thin_tab even though the two new columns in thick_tab have nothing inserted. But both data segments have the similar size.

By dumping the redo logfile, it turns out that Oracle uses row array allocation for thin_tab, but single row allocation for thick_tab. Probably that is an Oracle internal optimization.

If using direct-path insert (insert /*+ append */ ), there will be no big difference for both inserts, and redo and undo will be much less (redo size = 10K, undo change vector size = 2K).

For partitioned table, when multiple sessions concurrently make the direct-path(with append hint) for each partition per session, the PARTITION clause is mandatory:

 insert /*+ append */ into test_table_1 PARTITION (part_1) select *  from test_table_2;

Otherwise there is a TM lock contention among the sessions on global table.
The reason is because without PARTITION clause, direct-path makes:

 TM lock with LMODE 6 on global table;
 TM lock with LMODE 3 on the specified partition;

however, with PARTITION clause, they are:

  TM lock with LMODE 3 on global table;
 TM lock with LMODE 6 on the specified partition;

For non direct-path, with or without PARTITION clause are the same:

 TM lock with LMODE 3 on global table;
 TM lock with LMODE 3 on the specified partition;

By the way, Oracle official document about:
   Locking Considerations with Direct-Path INSERT
states:
  During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table).
(see Oracle® Database Administrator's Guide 11g Release 2 (11.2)

This claim only holds when no PARTITION clause is specified.

ORA-04027: self-deadlock during automatic validation

Sometimes ago I hit an error:

   ORA-04027: self-deadlock during automatic validation for object

I wonder why there is a self-deadlock in a single session (no autonomous transaction involved). Then I tried to extract a short test case to reproduce it as follows.

drop table testt1;
drop package testp1;
drop procedure proc1;

create table testt1 (a number(2));
insert into testt1 values (12);
commit;

create or replace package testp1 as
  function f1 return number;
  procedure p1;
end testp1;
/

create or replace procedure proc1              
as
  procedure prt(p_name varchar2) as
  begin
      for c in (select p_name || object_name || '(' || object_type || ')' || status s
                from   dba_objects
                where  object_name in ('TESTP1', 'PROC1'))
      loop
        dbms_output.put_line(c.s);
      end loop; 
  end;
begin
  prt('Before Alter: ');
  execute immediate 'alter table testt1 modify (a number(2))';
  prt('After Alter:  ');
  update testt1 set a=testp1.f1;
end proc1;
/

create or replace package body testp1 as
  function f1 return number as
    begin
      return 10;
    end;
   
  procedure p1 is
    begin
      proc1;
    end;
end testp1;
/

Now if I run:

SQL> exec proc1;

Before Alter: TESTP1(PACKAGE BODY)VALID
Before Alter: TESTP1(PACKAGE)VALID
Before Alter: PROC1(PROCEDURE)VALID
After Alter: TESTP1(PACKAGE BODY)INVALID
After Alter: TESTP1(PACKAGE)VALID
After Alter: PROC1(PROCEDURE)INVALID
BEGIN proc1; END;

*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of TESTP1
ORA-04027: self-deadlock during automatic validation for object PROC1
ORA-06512: at "PROC1", line 16
ORA-06512: at line 1

There are two invalids: TESTP1(PACKAGE BODY) and PROC1(PROCEDURE). You can make both valid by:

alter PACKAGE TESTP1 COMPILE BODY;

but whenever you call proc1, they are again invalid.

The dependency is TESTP1(PACKAGE BODY) on PROC1, and PROC1 & TESTP1(PACKAGE BODY) on TESTP1(PACKAGE).

Here is an attempt to make some reasoning.

When running "exec proc1;", proc1 is pinned, after "alter table testt1", proc1 is invalid, which in turn, causes the invalid of testp1 body due to dependency (the pinned version of proc1 is still valid since it is the directly called unit), then proc1 runs to the update statement, which requires a valid testp1 body, thus it demands an X-lock on testp1 body, which again asks for an X-lock of proc1 (due to dependency). Since proc1 is already pinned (Share-lock) by its own, it is not possible to allocate an X-lock to itself. So a self-deadlock is generated during validation of proc1.
                       
The code was tested on 10gr2 and 11gr2. Toggling 11gr2 hidden parameters:
_disable_fast_validate(TRUE, FALSE), _ignore_fg_deps  (TABLES, PLSQL, ALL, NONE)
seems having no influence on the behavior.

Foreign Key on Nested Table

This Blog will make an attempt to define a referential constraint on a nested table column.

At first I copy and run the code from Book: Expert Oracle Database Architecture: 9i and 10g (http://www.apress.com/9781590595305).

drop table dept_and_emp cascade constraints;
drop type emp_tab_type;
drop type emp_type;

create or replace NONEDITIONABLE type emp_type  -- NONEDITIONABLE since 11.2
--create or replace type emp_type
as object
(empno       number(4),
 ename       varchar2(10),
 job         varchar2(9),
 mgr         number(4),
 hiredate    date,
 sal         number(7, 2),
 comm        number(7, 2)
)
/

create or replace NONEDITIONABLE type emp_tab_type  -- NONEDITIONABLE since 11.2
--create or replace type emp_tab_type
as table of emp_type
/

create table dept_and_emp
(deptno    number(2) primary key,
 dname     varchar2(14),
 loc       varchar2(13),
 emps      emp_tab_type
)
nested table emps store as emps_nt
/

alter table emps_nt add constraint
emps_empno_unique unique(empno)
/

insert into dept_and_emp values(1, 'Dept_1', 'Loc_1',
  new emp_tab_type(new emp_type(11, 'Emp_11', 'Job_11', null, date'1962-01-11', 1, 1),
                   new emp_type(12, 'Emp_12', 'Job_12', 11, date'1962-01-12', 2, 2)
                   ));
                  
insert into dept_and_emp values(2, 'Dept_2', 'Loc_2',
  new emp_tab_type(new emp_type(21, 'Emp_21', 'Job_21', null, date'1962-02-11', 1, 1),
                   new emp_type(22, 'Emp_22', 'Job_22', 21, date'1962-02-12', 2, 2)
                   )); 
                  
commit;

As already showed in the book, if run:

alter table emps_nt add constraint mgr_fk foreign key(mgr) references emps_nt(empno);

one will get an error:

ORA-30730: referential constraint not allowed on nested table column

Now I will try with other columns. At first, I list the columns from master and nested table (not relevant columns are removed):

SQL > select o.object_name tname, c.name, c.type# ctype
        from   dba_objects o, sys.col$ c
       where c.obj# = o.object_id
         and o.object_name in ('EMPS_NT', 'DEPT_AND_EMP')
       order by o.object_name, c.name;

DEPT_AND_EMP            SYS_NC0000400005$    23
EMPS_NT                 NESTED_TABLE_ID      23

I see both DEPT_AND_EMP.SYS_NC0000400005$ and EMPS_NT.NESTED_TABLE_ID having the same type: 23(SQLT_BIN).
   
then run:

alter table emps_nt add constraint emps_nt_fk foreign key(nested_table_id)
references dept_and_emp(sys_nc0000400005$);

it will create a foreign key constraint EMPS_NT_FK.

I can verify it by:

SQL > select constraint_name, constraint_type, table_name 

        from dba_constraints where table_name = 'EMPS_NT';

EMPS_NT_FK          R     EMPS_NT
EMPS_EMPNO_UNIQUE   U     EMPS_NT

Tested on Oracle 10gr2 and 11gr2, both works.

Now I am lost with Oracle documentation:

ORA-30730: referential constraint not allowed on nested table column
    Cause: An attempt was made to define a referential constraint on a nested table column.
    Action: Do not specify referential constraints on nested table columns.

By the way, on 11gr2, with the query:

SQL > select  index_name, table_name, column_name 

        from  dba_ind_columns 

       where table_name in ('EMPS_NT', 'DEPT_AND_EMP');

SYS_C0016818               DEPT_AND_EMP      DEPTNO
SYS_C0016819               DEPT_AND_EMP      EMPS
SYS_FK0000184243N00004$    EMPS_NT           NESTED_TABLE_ID
EMPS_EMPNO_UNIQUE          EMPS_NT           EMPNO

we can see the column NESTED_TABLE_ID in EMPS_NT is indexed by: SYS_FK0000184243N00004$. So Oracle follows its rule: "foreign keys should be indexed" (Oracle® Database Concepts 11g Release 2 (11.2) Part Number E16508-05 in http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT88886)

However, this index can not be found in 10gr2.    

We can also verify this foreign key constraint:

SQL > delete dept_and_emp where deptno = 1;
ORA-02292: integrity constraint (K.EMPS_NT_FK) violated - child record found

Now disabling this constraint, delete deptno 1 again:

SQL > alter table emps_nt disable constraint emps_nt_fk;
SQL > delete dept_and_emp where deptno = 1;
1 row deleted.

We can see that both parent and child rows are deleted:

SQL > select SYS_NC0000400005$, t.* from dept_and_emp t;

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;

(Without hint: nested_table_get_refs, it hits: "ORA-22812: cannot reference nested table column's storage table")

However, if we delete with two additional hints:

SQL > delete /*+ no_index(t SYS_FK0000184243N00004$) nested_table_get_refs */ dept_and_emp t
      where deptno = 2;
1 row deleted.

We can see that only parent row is deleted, but child rows remain:

SQL > select SYS_NC0000400005$, t.* from dept_and_emp t;
no rows selected

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;

E40DC694BEAC31C7E04478E7D1E92954 (21; Emp_21; Job_21; ; 11.02.1962; 1; 1) 21 Emp_21 Job_21  11.02.1962 1 1

E40DC694BEAC31C7E04478E7D1E92954 (22; Emp_22; Job_22; 21; 12.02.1962; 2; 2) 22 Emp_22 Job_22 21 12.02.1962 2 2

2 rows selected.

Taking the NESTED_TABLE_ID of above query, we can delete these child rows directly:

SQL > delete /*+ nested_table_get_refs */ emps_nt t
      where nested_table_id=hextoraw('E40DC694BEAC31C7E04478E7D1E92954');
2 rows deleted.

SQL > select /*+ nested_table_get_refs */ NESTED_TABLE_ID, SYS_NC_ROWINFO$, t.* from emps_nt t;
no rows selected

 

Update Restart and new Active Undo Extent

Blog:  That old restart problem again...
(http://tkyte.blogspot.com/2010/04/that-old-restart-problem-again.html) or Link: That old restart problem again...
presents a new case of update restart, which demonstrates that restart can also happen within a single session, not necessarily two sessions with one being blocked by a update from another session. Inherently single session restart is distinct from the normal two sessions' restart  in which all rows till first detected changed row are restarted, however in single session restart, only occasionally a few rows are restarted.

Referring to Oracle documentation, it is not evident to get a clue about such behavior. Sometimes I hear the warning: "Don't go searching for it in the documentation" (Blog:  NO_DATA_NEEDED - something I learned recently in http://tkyte.blogspot.com/2010/04/nodataneeded-something-i-learned.html).

So it is worth of pursuing some research on it. Following modified script shows certain coincidence of new active undo_extent and restart.

I slightly modified the code in Blog:  That old restart problem again...: by adding a helper function to count the active undo extents, and some lines to print out this count.

create or replace function get_undo_extent_cnt return number
as
  l_cnt number;
begin   
 select count(*) into l_cnt from dba_undo_extents where status = 'ACTIVE';
 return l_cnt;
end;
/

create or replace package pacepack
as
  type array is table of number index by varchar2(40);
  g_data                         array;
  g_cnt                           number;
  g_restart_cnt                number;
  g_undo_extent_cnt       number;
end pacepack;
/

drop table delete_data;

create table delete_data as
select owner, object_name, rownum id
from  dba_objects
where rownum  < 53001;
    
create or replace trigger delete_data_bt
  before delete or update on delete_data
  for each row
begin
  if ( pacepack.g_data.exists(rowidtochar(:old.rowid)))
  then
    pacepack.g_restart_cnt := pacepack.g_restart_cnt + 1;
   
    dbms_output.put_line( 'doing "' || :old.rowid ||
                           '" again was called ' || pacepack.g_cnt );
    dbms_output.put_line('-- Restart#: ' || pacepack.g_restart_cnt ||
                            ', Undo Extent Count: ' || get_undo_extent_cnt);
  else
    pacepack.g_data(rowidtochar(:old.rowid)) := 1;
  end if;
      
  pacepack.g_cnt        := pacepack.g_cnt + 1;
end;
/

Assume you are the only one on the database, then run the same script:

declare
  cursor l_delete_csr is
    select * from delete_data for update;
  l_cnt number := 0;
begin
  pacepack.g_data.delete;
  pacepack.g_cnt         := 0;
  pacepack.g_restart_cnt := 0;
         
  for l_delete_row in l_delete_csr loop
    update delete_data set owner = lower(owner) where current of l_delete_csr;
    l_cnt := l_cnt + 1;
   
    if l_cnt = 1 then
     for c in (select usn, extents, extends from v$rollstat where xacts > 0) loop
      dbms_output.put_line('---- Begin Rollstat: '||'USN='||c.usn||', EXTENTS='||c.extents||', EXTENDS='||c.extends);
     end loop;
    end if;
  end loop;
    
  dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt || ' local count = '   || l_cnt );   
  for c in (select usn, extents, extends from v$rollstat where xacts > 0) loop
    dbms_output.put_line('---- End Rollstat: '||'USN='||c.usn||', EXTENTS='||c.extents||', EXTENDS='||c.extends);
  end loop;
 end;
 /

output:

---- Begin Rollstat: USN=20, EXTENTS=24, EXTENDS=21
doing "AABEYTAAAAAC9WMAB6" again was called 7144
-- Restart#: 1, Undo Extent Count: 23
doing "AABEYTAAAAAC9XMACo" again was called 18475
-- Restart#: 2, Undo Extent Count: 24
doing "AABEYTAAAAAC9YpACA" again was called 29740
-- Restart#: 3, Undo Extent Count: 25
doing "AABEYTAAAAAC9ZsAB1" again was called 41005
-- Restart#: 4, Undo Extent Count: 26
doing "AABEYTAAAAAC9axAAa" again was called 52298
-- Restart#: 5, Undo Extent Count: 27
trigger count = 53005 local count = 53000
---- End Rollstat: USN=20, EXTENTS=29, EXTENDS=26

The output shows 5 Restarts(53005-53000) , 5 new Extents(29-24) created by 5 extending(26-21).
So for each restart, a new active Undo Extent is created. If you run it repeatedly, you will see the same behavior.

As we know, Oracle fulfils a DML by following 5 steps:
  create UNDO Change Vector
  create REDO Change Vector
  combine both Change Vectors into Redo Buffer, and then write to redo log
  write UNDO record into UNDO file
  write modified Data into DATA file

When a transaction fills rollback segment and finds that the last remaining block is not sufficient to hold the UNDO record, it will trigger SMON to perform a recursive space transaction, which will allocate a new UNDO extent. Hereafter Oracle recommences the above 5 steps, and hence an "Update Restart".

As I tested in 10g and 11g (undo_management=AUTO), this output is confirmed,  but I would be careful to have any earlier conclusion before Oracle says.

Update (2022Aug25) --------------------------------------------------

Blog: That Old Restart Problem Strikes Back: Setting the Stage (1 August 2019)
sets DML diagnostic events in session:

  alter session set events 'trace[dml]:sql_trace wait=true';

and shows that there is one ORA-01551 error for each restart.

Setting this DML event, repeat our above test.
For above 5 Restarts, we can see 5 occurrences of following text in the trace file:

updThreePhaseExe: objn=4891285 phase=NOT LOCKED
updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7ac  xid: 0x006e.011.00006316  uba: 0x00c0227c.1936.58 
updrow: objn=4891285 phase=NOT LOCKED
updrow: objn=4891285 error=1551
updrow: qecinvsub objn=4891285
updrow: setting ROW_RETRY objn=4891285
updrow: retry_this_row: ROW_RETRY set, objn= 4891285 phase=NOT LOCKED

The difference of those 5 occurrences are the second line about (scn, xid, uba),
where scn and uba are different for each restart, but for the same xid:

updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7ac  xid: 0x006e.011.00006316  uba: 0x00c0227c.1936.58 
updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7b0  xid: 0x006e.011.00006316  uba: 0x00c0517c.1937.5a 
updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7b2  xid: 0x006e.011.00006316  uba: 0x00c051fc.1938.5a 
updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7b3  xid: 0x006e.011.00006316  uba: 0x00c0527c.1939.59 
updaul: phase is NOT LOCKED snap oldsnap env: (scn: 0x00000b7302b6b7b7  xid: 0x006e.011.00006316  uba: 0x00c052fc.193a.5a 

If we made a 1551 errorstack trace:

  alter session set events='1551 trace name errorstack level 3';

the Error Stack shows:

----- Error Stack Dump -----
ORA-01551: extended rollback segment, pinned blocks released
----- Current SQL Statement for this session (sql_id=4u6b4b82z7xrp) -----
SELECT * FROM DELETE_DATA FOR UPDATE
----- PL/SQL Call Stack -----

[15] (kgeselv()+89                    
[16] (ksesecl0()+189                  
[17] (ktusmasp()+1088   --->ktusmasp - kernel transaction undo smu (system managed undo) add space              
[18] (ktuchg2()+9603                  
[19] (ktbchg2()+231                   
[20] (kddchg()+611                    
[21] (kddlok()+2350                   
[22] (kddlkr()+322                    
[23] (qerfuProcessFetchedRow()+669    
[24] (qerfuLockingRowProcedure()+68   
[25] (qersoRowP()+880                 
[26] (kdstf000110100000000km()+1014   
[27] (kdsttgr()+2154                  
[28] (qertbFetch()+1090               
[29] (qersoProcessULS()+281           
[30] (qersoFetchSimple()+2251         
[31] (qersoFetch()+210                
[32] (qerfuStart()+602                
[33] (selexe0()+3409                  
[34] (opiexe()+11896  

End of Update (2022Aug25)--------------------------------------------------



The same behavior is also observed for the single row update, in which the cursor is defined with an input parameter p_id to select a single row:

declare
   cursor l_delete_csr (p_id number) is
     select * from delete_data where id = p_id for update;
   l_cnt number := 0;
begin
  pacepack.g_data.delete;
  pacepack.g_cnt         := 0;
  pacepack.g_restart_cnt := 0;
  for i in 1..53000 loop       
    for l_delete_row in l_delete_csr(i) loop
      update delete_data set owner = lower(owner) where current of l_delete_csr;
      l_cnt := l_cnt + 1;
     
      if l_cnt = 1 then
       for c in (select usn, extents, extends from v$rollstat where xacts > 0) loop
        dbms_output.put_line('---- Begin Rollstat: '||'USN='||c.usn||', EXTENTS='||c.extents||', EXTENDS='||c.extends);
       end loop;
      end if;     
    end loop;
  end loop;
  
  dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||' local count = '   || l_cnt );
  for c in (select usn, extents, extends from v$rollstat where xacts > 0) loop
    dbms_output.put_line('---- End Rollstat: '||'USN='||c.usn||', EXTENTS='||c.extents||', EXTENDS='||c.extends);
  end loop;      
end;
/

Output:

---- Begin Rollstat: USN=34, EXTENTS=29, EXTENDS=25
doing "AABEYTAAAAAC9WhABL" again was called 11315
-- Restart#: 1, Undo Extent Count: 29
doing "AABEYTAAAAAC9XnAAB" again was called 22612
-- Restart#: 2, Undo Extent Count: 30
doing "AABEYTAAAAAC9ZCAAe" again was called 33877
-- Restart#: 3, Undo Extent Count: 31
doing "AABEYTAAAAAC9aHAAr" again was called 45142
-- Restart#: 4, Undo Extent Count: 32
trigger count = 53004 local count = 53000
---- End Rollstat: USN=34, EXTENTS=33, EXTENDS=29

The Blog: Three new "hints" (http://rwijk.blogspot.com/2009/10/three-new-hints.html) shows a new  Oracle 11.2.0.1 hint: RETRY_ON_ROW_CHANGE, which I would call it block level restart, in comparing to the traditional row level (maybe this hint already exists in 10g since it is mentioned in 10g  "Bug 6082734  Dump (kdr9igtl) accessing a row from an IOT").

One interesting code in this Blog is that it uses after trigger to catch the restart.

dbms_session package_memory_utilization

Oracle 11g Release 2 extends dbms_session Package by introducing a new Procedure:
get_package_memory_utilization to expose memory usage of instantiated packages,
functions, triggers, or types so that the memory consumption of program units is revealed and the analysis of ORA-04030 becomes pretty straightforward.

It outputs 5 PL/SQL associative arrays, but nowadays Oracle people get used to v$ like views. Here is an example how to turn them into such a view by pipelined function.

create or replace package sess_mem_usage as
  type t_rec is record (
    owner     varchar2(4000)
   ,unit      varchar2(4000)
   ,type      varchar2(40)
   ,used      number
   ,free      number
  );
  type t_rec_tab is table of t_rec;
  
  function get return t_rec_tab pipelined;  
end sess_mem_usage;
/

create or replace package body sess_mem_usage as
  function map_type2name(p_type integer) 
  return varchar2
  as
    l_v varchar2(20);
  begin
    l_v := case p_type  when 7  then '(procedure)' 
                        when 8  then '(function)' 
                        when 9  then '(package)' 
                        when 11 then '(package body)' 
                        when 12 then '(trigger)' 
                        when 13 then '(type)' 
                        when 14 then '(type body)'
                        else ''
            end;
    return rpad(to_char(p_type), 3) || l_v;
  end map_type2name;

 -- since Oracle 11.2.0.4.0

  function get return t_rec_tab pipelined
  is
    l_desired_info            dbms_session.integer_array;
    l_owner_array             dbms_session.lname_array;
    l_unit_array              dbms_session.lname_array;
    l_type_array              dbms_session.integer_array;
    l_amounts                 dbms_session.big_integer_matrix;
    l_used_array              dbms_session.big_integer_array;
    l_free_array              dbms_session.big_integer_array;
    l_rec                     t_rec;
  begin
   l_desired_info(1) := dbms_session.used_memory;
   l_desired_info(2) := dbms_session.free_memory;
    dbms_session.get_package_memory_utilization(l_desired_info, l_owner_array, l_unit_array, l_type_array, l_amounts);
    for i in 1 .. l_owner_array.count loop
      l_rec.owner := l_owner_array(i);
      l_rec.unit  := l_unit_array (i);
      l_rec.type  := map_type2name(l_type_array(i));
      l_rec.used  := l_amounts(1)(i);
      l_rec.free  := l_amounts(2)(i);
      pipe row(l_rec);
    end loop;
    return;
  end get; 
    
  function get_deprecated return t_rec_tab pipelined as
    l_owner_array  dbms_session.lname_array;
    l_unit_array   dbms_session.lname_array;
    l_type_array   dbms_session.integer_array;
    l_used_array   dbms_session.integer_array;
    l_free_array   dbms_session.integer_array;
    l_rec          t_rec;
  begin
    dbms_session.get_package_memory_utilization

    (l_owner_array, l_unit_array, l_type_array, 

     l_used_array, l_free_array);
    for i in 1..l_owner_array.count loop
      l_rec.owner := l_owner_array(i);
      l_rec.unit  := l_unit_array(i);
      l_rec.type  := map_type2name(l_type_array(i));
      l_rec.used  := l_used_array(i);
      l_rec.free := l_free_array(i);
        pipe row(l_rec);
    end loop;
    return;
  end get_deprecated;
end sess_mem_usage;
/

create or replace force view v$ora_sess_mem_usage as
  select * from table(sess_mem_usage.get);

then you can access them by:

select * from v$ora_sess_mem_usage order by used desc;

Comparing to directly calling dbms_session.get_package_memory_utilization, order by and where condition are able to be flexibly applied instead of PL/SQL code.
           
The limitation with dbms_session.get_package_memory_utilization is that one can only watch its own memory usage. Another limitation is that memory amount is expressed in dbms_session.integer_array, which is a table of BINARY_INTEGER with maximum of 2147483647 (2 ^ 31 – 1, or 2GB). So if a package consumes more than 2GB, it will output a negative value. (This is probably similar as COUNT STOPKEY limitation of  2 ^ 32 – 1 reported in blog: http://blog.tanelpoder.com/2010/10/25/count-stopkey-operation-the-where-rownum)

Addendum (2016.01.31):
From Oracle 11.2.0.4.0, a new overloaded API:
   dbms_session.get_package_memory_utilization(desired_info IN integer_array, ...)
is added, which measures memory usage in INTEGER (i.e. NUMBER(38,0)), instead of BINARY_INTEGER(sb4). That means memory can be represented till (10 ^ 38) instead of (2 ^ 32 – 1).

With the new created view, following program dumps the memory usage of current session.

create or replace package dump_mem_usage as
 l_seq number := 0;
 function get_next_seq return number;
 function round_mb(p_bytes number) return number;
  procedure run(p_name varchar2 := 'Test', p_dump_limit number := 2);
end;
/

create or replace package body dump_mem_usage as
  function get_next_seq return number as 
  begin
   l_seq := l_seq + 1;
    return l_seq;
  end;
  
  function round_mb(p_bytes number) return number as 
  begin
    return round(p_bytes/1024/1024, 2);
  end;

  procedure run(p_name varchar2, p_dump_limit number) as
    c_max_pls_integer constant pls_integer := 2147483647;
    l_v$process_mem            varchar2(4000);
    l_v$process_memory_mem     varchar2(4000);
    l_usage_list               varchar2(2000); 
    l_sum_abs_mb               number;
    l_mysid                    number;
  begin
    dbms_output.put_line('-------- '||dump_mem_usage.get_next_seq||'. '||p_name||' --------');
    select sid into l_mysid from v$mystat where rownum=1;
    select '(SID='||l_mysid||')'||
           'Used/Alloc/Freeable/Max='||
            dump_mem_usage.round_mb(pga_used_mem)    ||'/'||
            dump_mem_usage.round_mb(pga_alloc_mem)   ||'/'||
            dump_mem_usage.round_mb(pga_freeable_mem)||'/'||
            dump_mem_usage.round_mb(pga_max_mem)
      into l_v$process_mem
      from v$process where addr = 
          (select paddr from v$session where sid=l_mysid);
      
    dbms_output.put_line('v$process: ' ||l_v$process_mem);
    
    select 'Category/Alloc/Used/Max='||
            listagg(Category||'/'||
              dump_mem_usage.round_mb(allocated)||'/'||
              dump_mem_usage.round_mb(used)     ||'/'||
              dump_mem_usage.round_mb(max_allocated)||',')
            within group (order by Category desc) name_usage_list
    into l_v$process_memory_mem
    from v$process_memory where pid = 
          (select pid from v$process where addr = 
            (select paddr from v$session where sid=l_mysid));  
    
    dbms_output.put_line('v$process_memory: ' ||l_v$process_memory_mem);
      
    select listagg(owner||'; '||unit||'; '||type||'; '||
                   dump_mem_usage.round_mb(used)||'; '||
                   dump_mem_usage.round_mb(free)||'; '||
                   alloc_abs_mb, chr(10)) 
             within group (order by alloc_abs_mb desc) name_usage_list
          ,sum(alloc_abs_mb)                           sum_abs_mb
    into l_usage_list
        ,l_sum_abs_mb
    from (
          select v.*
                ,dump_mem_usage.round_mb
                         (decode(sign(used), -1, c_max_pls_integer, used)
                        + decode(sign(free), -1, c_max_pls_integer, free)) alloc_abs_mb
          from   v$ora_sess_mem_usage v
          order by alloc_abs_mb desc
         )
    where rownum <= p_dump_limit;
    dbms_output.put_line('[Owner; Unit; Type; Used; Free; sum_abs_mb(' || l_sum_abs_mb ||')]' ||  chr(10) || 
            l_usage_list);
  end;
end;
/ 

The above procedure can be simply integrated as part of memory usage monitoring.

As the name indicated, dbms_session.get_package_memory_utilization is restricted to package memory usage. It will not report memory consumption of PL/SQL functions and procedures as illustrated by the following example.

Calling "exec mem_use_kilo(100000)" will not list 100MB(at least) of l_tab memory.

create or replace procedure mem_use_kilo(p_kilo number) as
  type t_tab is table of varchar2(1000); 
  l_tab t_tab := t_tab();
begin
  select rpad('E', 1000, 'N')
  bulk collect into l_tab
  from dual connect by level <= p_kilo;
  dump_mem_usage.run('mem_use_kilo Test');
end;
/

Normally PL/SQL table memory allocation is counted as 'session pga memory' in v$sesstat, and as category 'PL/SQL' in v$process_memory.

Session PGA memory usage is exposed in v$process_memory, v$process, v$sesstat(join with v$statname), as well as v$active_session_history, dba_hist_active_sess_history. But the values reported in v$sesstat, and v$active_session_history, dba_hist_active_sess_history is limited by 4GB (data overflow of 32 bits).

4GB limitation is fixed in Oracle 12.1.0.2.0, see:
Bug 15951898 - Session Statistics for PGA Memory are Incorrect in V$ACTIVE_SESSION_HISTORY / V$SESSTAT (Doc ID 15951898.8)

The original discussion about UNIX commands to watch PGA, SGA size is moved to Blog:
PGA, SGA memory usage watching from UNIX