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
The MARYBETH smiley face: MARYBETH is experiencing plenty of I/O throughput to and from the TEMP tablespace. Her sort is progressing rapidly.
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.
The DBA frowney face: A storm is brewing over the DBA who can expect to receive some phone calls from irate users.
The other frowney faces: JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?