Skip to Main Content
  • Questions

  • PL/SQL Table VS Global Temporary Table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ramanan.

Asked: September 24, 2015 - 5:06 pm UTC

Last updated: September 28, 2015 - 3:45 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

PL/SQL Table VS Global Temporary Table

We are performing database migration from Sybase 12 to Oracle 11g. Which one will be a good option to replace # tables (temp tables) of Sybase to Oracle? Is it PL/SQL tables or Global Temporary Table? Could you please list down pros and cons of each options?

and Connor said...

I would probably opt for global temporary tables in terms of "safety".

- Put 100 million rows into a temorary table....no problem.
- Put 100 million rows into a PLSQL table....there's a good chance your session might crash with a ora-4030 out of process memory error.

You also get transactional consistency etc.

But in doing so, I'd strongly recommend migrating to 12c not 11g. Why move to a version that is not current ? (And global temporary tables have some significant improvements in 12c)

Hope this helps


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

And global temporary tables have some significant improvements in 12c

A reader, September 25, 2015 - 1:46 am UTC

"And global temporary tables have some significant improvements in 12c"

Can you list these improvements.

Thanks
Connor McDonald
September 25, 2015 - 3:07 am UTC

Session level statistics and temporary undo

Hope this helps





Temp Undo not possible at session level.

Rajeshwaran, Jeyabal, September 25, 2015 - 4:57 pm UTC

Temp undo is not possible at session level.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8125235400346863808
Connor McDonald
September 26, 2015 - 2:38 am UTC

I beg to differ...

SQL> drop table gtt;

Table dropped.

SQL> create global temporary table gtt ( x char(100));

Table created.

SQL> conn scott/tiger
Connected.

SQL> insert into gtt
  2  select '1' from dual connect by level <= 10000;

10000 rows created.

SQL>
SQL> update gtt set x = 'z';

10000 rows updated.

SQL>
SQL> SELECT t.used_ublk,
  2         t.used_urec
  3  FROM   v$transaction t,
  4         v$session s
  5  WHERE  s.saddr = t.ses_addr
  6  AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
       227        862

SQL>
SQL> conn scott/tiger
Connected.

SQL> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

Session altered.

SQL>
SQL> insert into gtt
  2  select '1' from dual connect by level <= 10000;

10000 rows created.

SQL>
SQL> update gtt set x = 'z';

10000 rows updated.

SQL>
SQL> SELECT t.used_ublk,
  2         t.used_urec
  3  FROM   v$transaction t,
  4         v$session s
  5  WHERE  s.saddr = t.ses_addr
  6  AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 USED_UBLK  USED_UREC
---------- ----------
         1          1

Temp Undo not possible at session level.

Rajeshwaran, Jeyabal, September 26, 2015 - 10:33 am UTC

While responding to a review/followup, it would be really good if you could give use the Database version. We don't have any clue about your environment details (is that on 12.1.0.1 or 12.1.0.2) ?

Below test is from 12.1.0.2 on both Multitenant and non-CDB Architecture.

I don't see reduced undo after setting TEMP_UNDO_ENABLED=TRUE.

Do i need to re-connect the session before setting TEMP_UNDO_ENABLED=TRUE? is that mandatory ?

Non-CDB Architecture

rajesh@ORA12C> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

rajesh@ORA12C>
rajesh@ORA12C> select name,cdb,con_id
  2  from v$database ;

NAME       CDB     CON_ID
---------- --- ----------
ORA12C     NO           0

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> drop table gtt purge;
drop table gtt purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


rajesh@ORA12C> create global temporary table gtt(x char(100))
  2  on commit preserve rows;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> insert into gtt(x)
  2  select 'x' from dual
  3  connect by level <=10000;

10000 rows created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C> update gtt set x = 'y';

10000 rows updated.

rajesh@ORA12C>
rajesh@ORA12C> select used_ublk,used_urec
  2  from v$transaction t1,
  3    v$session t2
  4  where t1.ses_addr = t2.saddr
  5  and t2.audsid =  sys_context('userenv','sessionid');

 USED_UBLK  USED_UREC
---------- ----------
       303      10589

1 row selected.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C>
rajesh@ORA12C> alter session set temp_undo_enabled=true;

Session altered.

rajesh@ORA12C> update gtt set x = 'z';

10000 rows updated.

rajesh@ORA12C>
rajesh@ORA12C> select used_ublk,used_urec
  2  from v$transaction t1,
  3    v$session t2
  4  where t1.ses_addr = t2.saddr
  5  and t2.audsid =  sys_context('userenv','sessionid');

 USED_UBLK  USED_UREC
---------- ----------
       221        589

1 row selected.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C>


Multi-Tenant Architecture

rajesh@PDB1> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

rajesh@PDB1>
rajesh@PDB1> select name,cdb,con_id
  2  from v$database ;

NAME       CDB     CON_ID
---------- --- ----------
CDB1       YES          0

1 row selected.

rajesh@PDB1>
rajesh@PDB1> create global temporary table gtt(x char(100))
  2  on commit preserve rows;

Table created.

rajesh@PDB1> insert into gtt(x)
  2  select 'x' from dual
  3  connect by level <=10000;

10000 rows created.

rajesh@PDB1> commit;

Commit complete.

rajesh@PDB1>
rajesh@PDB1> update gtt set x = 'y';

10000 rows updated.

rajesh@PDB1>
rajesh@PDB1> select used_ublk,used_urec
  2  from v$transaction t1,
  3    v$session t2
  4  where t1.ses_addr = t2.saddr
  5  and t2.audsid =  sys_context('userenv','sessionid');

 USED_UBLK  USED_UREC
---------- ----------
       221        589

1 row selected.

rajesh@PDB1> commit;

Commit complete.

rajesh@PDB1>
rajesh@PDB1> alter session set temp_undo_enabled=true;

Session altered.

rajesh@PDB1>
rajesh@PDB1> update gtt set x = 'z';

10000 rows updated.

rajesh@PDB1>
rajesh@PDB1> select used_ublk,used_urec
  2  from v$transaction t1,
  3    v$session t2
  4  where t1.ses_addr = t2.saddr
  5  and t2.audsid =  sys_context('userenv','sessionid');

 USED_UBLK  USED_UREC
---------- ----------
       221        589

1 row selected.

rajesh@PDB1>

Connor McDonald
September 28, 2015 - 3:41 am UTC

http://docs.oracle.com/database/121/REFRN/GUID-E2A01A84-2D63-401F-B64E-C96B18C5DCA6.htm#REFRN10326

"Once the value of the parameter is set, it cannot be changed for the lifetime of the session. If the session has temporary objects using temporary undo, the parameter cannot be disabled for the session. Similarly, if the session already has temporary objects using regular undo, setting this parameter will have no effect."

(and obviously check your 'compatible' parameter)

Hope this helps.

PL/SQL table VS Global Temporary Table

Ramanan, September 26, 2015 - 6:10 pm UTC

We understand for million of records, GTT will be best option.
In case of populating records in thousands, Is it good to use PL/SQL table?

What is the disadvantage of using PL/SQL table? In what are the cases, PL/SQL table will be better option over global temporary table?

Connor McDonald
September 28, 2015 - 3:45 am UTC

See the previous followup. You might get some benefit from session level stats

Also dont forget transactional consistency (which may or may not be important in your case). That is, if you plan to put 10 records into a plsql table, and after the 5th one you have an error, then you still have 5 records in your table....

That *might* be what you want, it might not be. WHereas in a GTT, you can rollback etc.

PLSQL tables might be better a option in cases where the data will not be subsequenbtly used in queries etc.

Dont discount either...use each where it best works for your need.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.