RelationalDBDesign RelationalDBDesign


Creating Users   «Prev 

Better performance using Temporary Tablespaces in Oracle

Tuning execution plans using global temporary table and WITH clause materializations

Question: I have a performance tuning problem using (GTT) global temporary tables and the WITH clauses in a 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.
Question: 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.
Oracle 12c note: Starting in 12c, Oracle will allow 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.

Temporary versus non-temporary

  1. The MARYBETH smiley face: MARYBETH is experiencing plenty of I/O throughput to and from the TEMP tablespace.
    Her sort is progressing rapidly.
  2. The JOSEPH frowney face: JOSEPH is experiencing limited throughput because his large sort is using the SYSTEM tablespace, which other users also are using heavily.
  3. The DBA frowney face: A storm is brewing over the DBA who can expect to receive some phone calls from irate users.
  4. The other frowney faces: JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?