The different 'on commit' setting with Oracle Global Temp Table

Question

Would any one please advise on the mechanism behind the two different settings for the Oracle GTT?

1) on commit preserve rows

2) on commit delete rows

For now I know there 'facts':

a) the records inserted into these 2 types of GTT have different lifecycle.

b) the definition of both types of GTT remains until we drop the GTT <REF>.

However, what I would like to know i whether there is any difference between the 2 types of GTT in terms of the fact b)?

I was told that, for the 'preserve' type of GTT, the table's definition will not only remain but will accumulate by the times of usage (i.e. if there are 10 sessions using the GTT, 10 copies of the table's definition will be created and won't be disappear until we drop the GTT). And if we don't drop the 'preserve' GTT on a regular basis, the SQL statement's performance will become slower and slower.

Please could anyone demystify?

【2018.08.21】 Thanks all for answering the question. Please allow me to refine the question, it is not the table definition of the GTT is being duplicated, but the tablespace being allocated by every sessions using the same GTT that wont be released by the end of session but a dedicated drop of the GTT. Would that be the truth?

Answer 1

You can check the temporary segments associated with the GTTs in v$tempseg_usage:

create global temporary table demo_gtt_preserve (id int) on commit preserve rows;
create global temporary table demo_gtt_delete (id int) on commit delete rows;

insert into demo_gtt_preserve values (1);
insert into demo_gtt_delete values (1);

select s.sql_text, tu.tablespace, tu.contents, tu.segtype, tu.segfile#, tu.segblk#
from   v$tempseg_usage tu
       join v$sql s on s.sql_id = tu.sql_id_tempseg
where  tu.username = user
and    tu.segtype = 'DATA'
and    tu.session_num = dbms_debug_jdwp.current_session_serial;

Result:

SQL_TEXT                                 TABLESPACE CONTENTS  SEGTYP SEGFILE#    SEGBLK#
---------------------------------------- ---------- --------- ------ -------- ----------
insert into demo_gtt_delete values (1)   TEMP       TEMPORARY DATA        401     438528
insert into demo_gtt_preserve values (1) TEMP       TEMPORARY DATA        401     438400

Now if you commit and rerun the query, you only get one row:

SQL_TEXT                                 TABLESPACE CONTENTS  SEGTYPE SEGFILE#   SEGBLK#
---------------------------------------- ---------- --------- ------- -------- ---------
insert into demo_gtt_preserve values (1) TEMP       TEMPORARY DATA         401    438400

(Somewhat unhelpfully, v$tempseg_usage identifies the session by session_addr and session_num, which correspond to saddr and serial# in v$session, neither of which are exposed via sys_context. You could extend the query above by joining to v$session and filtering on sid = sys_context('userenv','sid') or audsid = sys_context('userenv','sessionid') if you want to limit it to your own session.)

The only way to clear the remaining entry is to disconnect the session, or drop or truncate the table.

Regarding the performance question, note the way this works: when your session uses a GTT, a completely new temporary segment is created just for you. If other sessions do the same thing, they each get their own separate temporary segments. As those sessions commit or disconnect, the corresponding temporary segments are dropped. There is nothing shared between sessions, because each session has its own separate instance of the temporary table. Therefore, the rumour that if we don't drop the 'preserve' GTT on a regular basis, the SQL statement's performance will become slower and slower doesn't make sense.