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. 

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.

select
   a.name,
   b.value
from
   v$statname    a,
   v$sesstat     b,
   v$session     s,
   v$sort_usage su
where
   a.statistic#=b.statistic#
and
   b.sid=s.sid
and
   s.saddr=su.session_addr;

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.

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 -  2011 by Burleson Enterprises All rights reserved. Oracle ? is the registered trademark of Oracle Corporation.