Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tuning execution plans for global temporary table and WITH clause materializations

Oracle Database Tips by Donald BurlesonMay 26, 2015

Question:  I have a performance tuning problem with global temporary tables (GTT) and the WITH clauses in a busy vendor application.  Once created, I cannot see the temporary table rows for a specific user session and I cannot see how to manipulate the SQL against the temporary tables to force my best explain plan.  What are the ways to index, monitor and tune global temporary tables?

Answer:  Oracle SQL provides GTT (global temporary tables) and the SQL-99 WITH clause to improve the performance of complex queries that want to use permanent storage to hold intermediate results for a problem. 

See here for a global temporary table example.

12c note:  Starting in 12c, Oracle will allows private optimizer statistics for global temporary tables, allowing you to invoke session-level dbms_stats to gather statistics specific to your own global temporary table.  Prior to 12c, statistics were shared from a master copy of the CBO statistics.

Before go into details, see these notes on the general properties on temporary tables (GTT and WITH clause):

  • Great for SQL-only queries:  One issue with SQL analytic functions is their poor performance, and decomposing a complex SQL problem into finite pieces is a proven divide and conquer method that makes the code faster and easier to maintain.

  • Not great for procedural language programs:  The preferred method is not to use temporary table storage because it cannot be re-used if the application aborts in mid-calculation because the temporary table is unique to the session.  It's a programming best practice to have the application hold intermediate rowsets within a private array in RAM, but there are cases where temporary tables are appropriate.

  • You can create an index on a global temporary table but not an ad-hoc materialization using the WITH clause:

create global temporary table tbl_temp (id number) on commit preserve rows;

create index idx_tbl_temp on tbl_temp (id);

exec dbms_stats.gather_index_stats('myowner, 'idx_tbl_temp');

  • Data in a GTT disappears when a session ends, and indexes on temporary tables are also temporary.  See here for a example of indexes on global temporary tables.

  • You can analyze a temporary table using dbms_stats, but every single session will share those statistics.  If there exists one and only one optimal set of optimizer statistics you can keep them with dbms_stats, and make sure to never re-analyze the global temporary table:

If each global temporary table is different enough to warrant a different execution plans, you can lock-out the CBO statistics and force Oracle to use dynamic sampling:

exec dbms_stats.delete_table_stats('myowner', 'tbl_temp');
exec dbms_stats.lock_table_stats('myowner', 'tbl_temp);
select /*+ dynamic_sampling (tbl_temp 4) */ stuff from tbl_temp,  . . . ;

How to see the execution plan for SQL using temporary tables?

You can use TKPROF to see the execution plans used by queries that involve global temporary tables.  You should also be able to see the execution plan for queries involving a global temporary tables using the SQL*Plus autotrace command.

How to modify the execution plans for SQL using temporary tables?

Once created, a global temporary tables behaves just like a "real" table, and you can embed hints into the SQL to change the ay that the global temporary table processes data.  This is especially helpful for forcing an index.

I have different sessions that need different execution plans due to the size and distribution of columns in my temporary table.  How do I adjust the CBO statistics for the temporary tables as they are generated by the users?

The Oracle documentation does suggest that an individual session can analyze statistics for a temporary table after creating it:

create global temporary table tbl_temp (id number) as . . . ;
create index idx_tbl_temp on tbl_temp (id);

exec dbms_stats.gather_index_stats('myowner, 'idx_tbl_temp');

exec dbms_stats.gather_table_stats('myowner, 'tbl_temp');

Internally, CBO statistics on a temporary table created by one session can also be used by other sessions.  The problem arises when individual sessions create temporary tables with different characteristics to the level where it would change the execution plan. 

For example, a small rowset might want a nested loops join while a large rowset might have a "better" execution plan with a hash join.  If you still get "bad" statistics you will need to reply on dynamic sampling.  To do this, you can also "lock out" bad statistics and force the optimizer to use dynamic sampling on the global temporary table:

exec dbms_stats.delete_table_stats('myowner', 'tbl_temp');
exec dbms_stats.lock_table_stats('myowner', 'tbl_temp);

This will force the CBO to use dynamic sampling to get information about the global temporary tables (or a with clause subquery factoring table).

How to see the intermediate rows from a temporary table inside the PGA and/or TEMP tablespace

Both subquery factoring (WITH clause) and global temporary tables will write the intermediate results to the TEMP tablespace.  Oracle stores GTT rows in the users temporary tablespace of the user, but you can change this to a "real" tablespace by using the tablespace clause of the create global temporary table syntax.

You can also query the v$sort_usage view to see how temp tablespace objects map to sessions.

   v$statname    a,
   v$sesstat     b,
   v$session     s,
   v$sort_usage su

Oracle12c temporary table enhancements

Prior to Oracle12c, Oracle transactions used UNDO for temporary tables (WITH Clause materializations, global temporary tables) within the standard UNDO tablespace.  Now, you can specify "alter session set temp_undo_enabled=true" to force the UNDO to be managed within the TEMP tablespace instead of within the UNDO tablespace.  This reduced the content of "regular" UNDO allowing for faster flashback operations.  Oracle has also allowed "private optimizer statistics" for global temporary tables, instead of the Oracle 11g method in which everybody shared a single set of statistics.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster