A common practice when using global temporary tables (GTT) is to simply rely on dynamic sampling to give the cost optimizer the relevant statistics when it comes to using a populated GTT in subsequent queries. Here’s a simple example of that in action. I’ll put 50 rows into a GTT and the subsequent query uses dynamic sampling to get the correct cardinality estimate for the full scan.
SQL> create global temporary table gtt
2 ( x int, y int )
3 on commit preserve rows;
Table created.
SQL>
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL>
SQL> set feedback only
SQL> select * from gtt;
50 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 50 | 1300 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
For many requirements, this is perfectly adequate because a common workflow with GTTs is a “one-and-done” operation. We load the GTT, consume the rows within it in some way, and then discard the GTT or its contents.
However, if your workflow is different to this and you expect to modify the GTT contents throughout a session, you can end up with some poor optimizer plans because the dynamic sampling is done at parse time not for each execution. For example, I’ll add another 50 rows to my table.
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL> commit;
Commit complete.
Will a fresh execution now discover the 100 rows?
SQL> set feedback only
SQL> select * from gtt;
100 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 50 | 1300 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
No it does not. I can repeat the process ad nauseam and the reality will drift ever further from the initial sampled statistics.
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> set feedback only
SQL> select * from gtt;
150 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 50 | 1300 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
It is only when the cursor is aged out of the shared pool (which I’ll force below) that we’ll pick up better statistics via fresh sampling
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set feedback only
SQL> select * from gtt;
150 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 150 | 3900 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
If this is the style of workflow you have, I recommend gathering stats each time you change the data in the GTT. This won’t impact any other session using the GTT, because from 12c onwards, we now have private statistics for GTTs, namely, the statistics apply at the session level to a GTT, not globally.
More details on that here if you’re interested
I’ll repeat the above example but include a GATHER_TABLE_STATS call after each change to the GTT and you can see that we pick up fresh stats for each query.
SQL> create global temporary table gtt
2 ( x int, y int )
3 on commit preserve rows;
Table created.
SQL>
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','GTT');
PL/SQL procedure successfully completed.
SQL>
SQL> set feedback only
SQL> select * from gtt;
50 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 50 | 300 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- Global temporary table session private statistics used
17 rows selected.
SQL>
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','GTT');
PL/SQL procedure successfully completed.
SQL>
SQL> set feedback only
SQL> select * from gtt;
100 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 100 | 600 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- Global temporary table session private statistics used
17 rows selected.
SQL>
SQL> insert into gtt
2 select rownum ,rownum
3 from dual
4 connect by level <= 50;
50 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','GTT',no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL>
SQL> set feedback only
SQL> select * from gtt;
150 rows selected.
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 44htrrgd4s45m, child number 0
-------------------------------------
select * from gtt
Plan hash value: 917624683
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| GTT | 150 | 900 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- Global temporary table session private statistics used
17 rows selected.
SQL>
Learning is not a spectator sport 
Got some thoughts? Leave a comment