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.

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;

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.