Choose a Temporary Tablespace for a User

Temporary tablespaces are used for operations like a large sort that requires disk space on a short-term basis. Assigning a user to a temporary tablespace allows you to segregate this type of I/O activity, and place it on a set of disks that are not being used for other purposes. If you do not assign a user to a temporary tablespace, then sorts and similar operations will use the SYSTEM tablespace. That can have a disastrous impact of performance, because the SYSTEM tablespace contains the data dictionary objects and already is heavily used. Compare the two situations shown in the following diagram.

1. 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.
   Answer:
   For your issue with global temporary tables (GTTs) and the `WITH` clause in the vendor application, there are a few things to check and try:
   
   1. Session-Specific Visibility:
      • GTTs in Oracle are designed to be session-specific, meaning that data inserted into a GTT is only visible to the session that inserted it.
      • Ensure that the session you're using to query the table is the same session that populated it.
      • If the vendor application uses multiple sessions, you may not see the data due to session isolation.
   2. Transaction-Specific Behavior:
      • If the GTT is set up with ON COMMIT DELETE ROWS, data will be cleared after each transaction commit.
      • If the data seems to disappear, you may want to verify if the table was created with ON COMMIT PRESERVE ROWS to retain the data until the session ends.
   3. Query Optimization:
      • Materializing the GTT Data: Try forcing the optimizer to materialize the CTEs by using hints like MATERIALIZE or INLINE to influence the optimizer's behavior. Adding hints can sometimes help achieve a more efficient plan, especially if the optimizer can view the GTT data directly in the context of the main query.
      • Hinting Temporary Tables: You might be able to guide the optimizer by hinting at the GTT usage. Try using optimizer hints (e.g., USE_NL, USE_HASH, INDEX, or FULL) within your SQL statement to achieve the best plan based on the query structure.
   4. Plan Stability:
      • Use SQL Profiles or Baselines to force the explain plan.
      • By capturing the desired plan and applying it as a baseline, you can ensure that Oracle reuses that execution path.
2. Question: What are the ways to index, monitor and tune global temporary tables^[1]?
   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.

1. The Tom Kyte is experiencing plenty of I/O throughput to and from the TEMP tablespace.
   His sort is progressing rapidly.
2. Larry Ellison is experiencing limited throughput because his large sort is using the SYSTEM tablespace, which other users also are using heavily.
3. For the DBA, a storm is brewing over the DBA who can expect to receive some phone calls from irate users.
4. Larry Ellison's sort is hurting everyone's performance. Where is that DBA, anyway?

When the user named Tom Kyte issues a query requiring a sort, no other database users are affected unless they too are using the TEMP tablespace. On the other hand, when Larry Ellison requires a sort, it places a heavy I/O burden on the SYSTEM tablespace, and could have a drastic effect on the performance experienced by all database users. Most databases are constructed with at least one or more temporary tablespaces. In the case of the COIN database, the temporary tablespace is named TEMP.

Cost Based Optimizer (CBO) statistics play a crucial role in optimizing query performance in Oracle 19c. Here's a breakdown of how they are used: What are CBO statistics?

• CBO statistics are data describing the database and its objects (tables, indexes, etc.). They include information like the number of rows, data distribution, column cardinality, and index usage.
• Oracle uses these statistics to estimate the cost of different execution plans for a given SQL statement.
• The plan with the lowest estimated cost is chosen and executed.

Benefits of using CBO statistics:

• Improved query performance: By choosing the most efficient plan, CBO helps minimize resource usage and query execution time.
• Adaptive query optimization: Statistics are automatically updated over time, allowing CBO to adapt to changes in data and usage patterns, ensuring continued optimal performance.
• Self-tuning capabilities: Certain features like `DBMS_STATS.GATHER_TABLE_STATS` and `DBMS_STATS.GATHER_INDEX_STATS` allow manual gathering and updating of statistics for improved accuracy.

Key functionalities in Oracle 19c:

• Automatic statistics gathering: Oracle automatically gathers statistics during regular database maintenance tasks, keeping them up-to-date.
• Histograms: For numerical columns, CBO uses histograms to represent data distribution more accurately, leading to better cost estimates.
• Cardinality feedback: This feature automatically adjusts cardinality estimates based on actual query execution data, further improving accuracy over time.
• Optimizer Statistics Advisor: This advisor analyzes statistics and identifies potential issues like missing or outdated data, recommending actions for improvement.

Using CBO statistics effectively:

• Ensure statistics are up-to-date for relevant tables and indexes used in frequently executed queries.
• Analyze query execution plans to identify potential issues in CBO estimates.
• Utilize features like Cardinality feedback and Optimizer Statistics Advisor for continuous improvement.

Remember:

• While CBO statistics are crucial for good query performance, they are just one part of the equation. Other factors like database configuration, hardware resources, and query structure also play a role.

Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk. The temporary tablespace is where this takes place. Each database should have one temporary tablespace that is created when the database is created. You create, drop and manage tablespaces with

1. create temporary tablespace,
2. drop temporary tablespace and
3. alter temporary tablespace commands,

each of which is similar to its create tablespace counterpart.
The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles. Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command as you can see in these examples:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE ?/ora01/oracle/oradata/booktst_temp_01.dbf? SIZE 50m;
DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tempfiles are a bit different than datafiles in that you may not immediately see them grow to the size that they have been allocated (this particular functionality is platform dependent). Hence, do not panic if you see a file that looks too small.

col allocated_bytes format 999,999,999,999,999
col free_bytes format 999,999,999,999,999

select  
   a.tablespace_name,  
   a.file_name,  
   a.bytes c3,  
   b.free_bytes  
FROM 
   dba_temp_files a, 
   (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b
WHERE  
a.file_id=b.file_id
ORDER BY 
 a.tablespace_name;

Here is a script that will display the contents of the TEMP tablespace.

set pagesize 60 linesize 132 verify off  
break on file_id skip 1 
 
column file_id heading "File|Id"  
column tablespace_name for a15  
column object          for a15 
column owner           for a15 
column MBytes          for 999,999 

select tablespace_name, 
'free space' owner, /*"owner" of free space */  
' ' object,         /*blank object name */ 
file_id, /*file id for the extent header*/ 
block_id, /*block id for the extent header*/  
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_free_space 
where tablespace_name like '%TEMP%' 
 union 
select tablespace_name, 
substr(owner, 1, 20), /*owner name (first 20 chars)*/ 
 substr(segment_name, 1, 32), /*segment name */ 
file_id, /*file id for extent header */  
block_id, /*block id for extent header */ 
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_extents 
where tablespace_name like '%TEMP%'  
order by 1, 4, 5  
/ 

You can check for held TEMP segments with this query:

select  
   srt.tablespace,  
   srt.segfile#,  
   srt.segblk#,  
   srt.blocks,  
   a.sid,  
   a.serial#,  
   a.username,  
   a.osuser,  
   a.status  
from 
    see code depot for full scripts 
   v$session    a,
   v$sort_usage srt  
where  
   a.saddr = srt.session_addr  
order by  
   srt.tablespace, srt.segfile#, srt.segblk#,  
   srt.blocks;

[1] Temporary Tables: Temporary tables in Oracle serve as transient storage for data within a single database session or transaction.