Oracle Global Temporary Table is hereinafter referred to as GTT (Global Temporary Table), and Private Temporary Table is hereinafter referred to as PTT (Private Temporary Table).
The main differences between GTT and PTT are as follows:
- GTT is earlier. GTT is a feature of 8i and PTT is a feature of 18c.
- The data of GTT and PTT are session private, and there is no data after the session ends, but the definition of GTT is global.
- When a transaction is committed, GTT can choose whether to keep the data, and PTT can choose whether to keep the definition.
- The table definition of GTT is shared by all sessions; The PTT table definition is session private.
For GTT and PTT experiments, you can refer to Oracle Base This article and This article article.
Examples of GTT and PTT table creation syntax can be referred to here.
Experiment of GTT
-- The default is ON COMMIT DELETE ROWS
SQL> create GLOBAL TEMPORARY table gtt_test(a int);
Table created.
SQL> insert into gtt_test values(1);
1 row created.
SQL> commit;
Commit complete.
-- Therefore, there is no data after submission
SQL> select count(*) from gtt_test;
COUNT(*)
----------
0
-- Create a to keep data on submission GTT
SQL> drop table gtt_test;
Table dropped.
SQL> create GLOBAL TEMPORARY table gtt_test(a int) ON COMMIT PRESERVE ROWS;
Table created.
SQL> insert into gtt_test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from gtt_test;
COUNT(*)
----------
1
-- Because someone is using it, so GTT Cannot delete. See details MOS Doc ID 800506.1
SQL> drop table gtt_test;
drop table gtt_test
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use
-- Switching sessions, no data
SQL> connect ssb/Welcome1@orclpdb1
Connected.
SQL> select count(*) from gtt_test;
COUNT(*)
----------
0
PTT experiment
PTT table names have certain naming rules:
SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ private_temp_table_prefix string ORA$PTT_
Establish PTT for default configuration (ON COMMIT DROP DEFINITION):
SQL> create PRIVATE TEMPORARY table ora$ptt_test(a int); Table created. SQL> insert into ora$ptt_test values(1); 1 row created. SQL> commit; Commit complete. -- After the transaction is committed, the table definition does not exist SQL> desc ora$ptt_test; ERROR: ORA-04043: object ora$ptt_test does not exist
Delete the table definition after the establishment session, but retain the PTT of the table definition after submission:
SQL> create PRIVATE TEMPORARY table ora$ptt_test(a int) ON COMMIT PRESERVE DEFINITION; Table created. SQL> insert into ora$ptt_test values(1); 1 row created. SQL> commit; Commit complete. SQL> desc ora$ptt_test; Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(38) SQL> connect ssb/Welcome1@orclpdb1 Connected. SQL> desc ora$ptt_test; ERROR: ORA-04043: object ora$ptt_test does not exist
undo and redo of temporary tables
This experiment refers to the article of Oracle BASE: Global Temporary Tables
Let's first look at the undo of the temporary table:
SET AUTOTRACE ON STATISTICS;
create GLOBAL TEMPORARY table gtt_test(a int);
insert into gtt_test select 1 from dual connect by level < 10000;
9999 rows created.
Statistics
----------------------------------------------------------
13 recursive calls
138 db block gets
27 consistent gets
0 physical reads
28528 redo size
195 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9999 rows processed
SQL>
SELECT t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE s.saddr = t.ses_addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
USED_UBLK USED_UREC
---------- ----------
4 55
For the description of V$TRANSACTION, see here For help with V$SESSION, see here . USED_UBLK and USED_UREC represents the number of undo block s and undo record s respectively.
If you replace the temporary table with the permanent table and repeat the above experiment, the results are the same.
Although the data in GTT is written to the temporary table space, the associated Undo is still written to the normal undo table space. The table space itself is protected by redo. Therefore, using GTT will not reduce the undo and redo associated with protecting the undo table space.
In 12c, undo can be written to the temporary table space. See here . This article also lists the benefits of doing so:
- Writing to the undo tablespace requires the database to be opened in read-write mode, so global temporary tables cannot be used in read-only databases and physical standby databases.
- The global temporary table contains temporary data, which is not needed in the recovery scenario. Therefore, using redo to protect them means unnecessary additional load on the system.
- The undo associated with the global temporary table increases the total space required to meet the undo retention period.
This behavior is controlled by parameters and is not enabled by default:
SQL> show parameter TEMP_UNDO_ENABLED NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
SET AUTOTRACE ON STATISTICS;
create GLOBAL TEMPORARY table gtt_test(a int);
insert into gtt_test select 1 from dual connect by level < 10000;
9999 rows created.
Statistics
----------------------------------------------------------
11 recursive calls
143 db block gets
25 consistent gets
0 physical reads
280 redo size
195 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9999 rows processed
SQL>
SELECT t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE s.saddr = t.ses_addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
USED_UBLK USED_UREC
---------- ----------
1 1
undo and redo are much lower than before the parameter is not enabled.
Temporary table and parallel execution
This experiment refers to Oracle Blogs: Parallel PL/SQL Functions and Global Temporary Tables... and Wrong Results
The author's example is cleverly designed, and I have modified it on this basis. Change the number of rows to 1000 to reduce the waiting time. The sleep function has also been modified.
-- In the test table rpad Functions can be removed, but the test results may not be so perfect
create table s as select rownum id,rpad('X',1000) pad
from dual connect by level<=1000;
create or replace function f_wait(id in number) return number is
begin
dbms_session.sleep(0.01);
return(id);
end;
/
The test results are similar whether parallelism is enabled in SQL or not. It can be seen from the execution plan that parallelism is not actually enabled:
SQL> select count(*) from s where id=f_wait(id);
COUNT(*)
----------
1000
Elapsed: 00:00:13.78
SQL> select /*+ parallel(4) */ count(*) from s where id=f_wait(id);
COUNT(*)
----------
1000
Elapsed: 00:00:13.45
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| S | 1 | 4 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
This is because PL/SQL functions need to be explicitly defined to support parallelism.
create or replace function f_wait(id in number) return number parallel_enable is begin dbms_session.sleep(0.01); return(id); end; /
Now, parallelism works, although not a perfect 2.5 seconds:
SQL> select /*+ parallel(4) */ count(*) from s where id=f_wait(id);
COUNT(*)
----------
1000
Elapsed: 00:00:03.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2247559131
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 12 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 4 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 4 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1 | 4 | 12 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| S | 1 | 4 | 12 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Now let's get to the point and talk about enabling parallel PL/SQL functions and global temporary tables.
create GLOBAL TEMPORARY table gtt_test(a int);
insert into gtt_test select 1 from dual connect by level < 10000;
select /*+ parallel(2) */ count(*) from gtt_test;
COUNT(*)
----------
9999
In this case, we have two PX servers scanning the temporary table and the reported count is correct. This indicates that a single PX server can see the data populated before the user session. The difference between parallel queries is that parallel sessions working on temporary tables can see the data previously populated by QC. When querying the temporary table, QC knows the temporary table and sends the segmentation information to the PX server so that they can read the data.
The PL/SQL function of querying the temporary table changes this behavior, and the result is incorrect:
create table t1 (id number);
insert into t1 values (1000);
commit;
create global temporary table tempstage (col1 number) on commit preserve rows;
create or replace function f_test return number parallel_enable is
v_var number;
begin
select col1 into v_var from tempstage;
return v_var;
end;
/
insert into tempstage values (100);
commit;
-- Wrong result
SQL> select /*+ parallel(2) */ * from t1 where id>f_test;
-- Correct results
SQL> select * from t1 where id>f_test;
ID
----------
1000
It returns the wrong result to the user. This is because the function is declared to be safe for execution by individual PX servers. Each PX server uses its own session, so they cannot see the data populated by the user session. This is different from the previous example of running a query against a temporary table. In this case, QC knows that the temporary table is involved. Here, it only sees that parallel function calls are enabled.
Therefore, be careful when declaring a function to enable parallelism. Note that the function will be executed by the PX server, which may lead to some unexpected behavior. Consider how functions behave when executed by multiple sessions and processes. Declare it to enable parallelism only if you are sure it is secure.
reference resources
- https://blogs.oracle.com/optimizer/post/global-temporary-tables-and-upgrading-to-oracle-database-12c-dont-get-caught-out