Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ?

Step followed :

• disable automatic PGA management by the server
• set the hash area size.
• and play with the sort area size

In this example, the hash area size is low in order to have the optimizer consistently choose a sort join to exercise the sort area size.

On 9i, if workarea_size_policy = AUTO, the setting hash area size and sort area size are ignored.

Made with Oracle 10gR2 release 4.

gerardnico@orcl>create TABLE t AS SELECT * FROM all_objects;
 
TABLE created.
 
gerardnico@orcl>analyze TABLE t compute statistics FOR TABLE FOR columns object_id;
 
TABLE analyzed.
 
gerardnico@orcl>alter session SET workarea_size_policy =  manual;
 
Session altered.
 
gerardnico@orcl>alter session SET hash_area_size =  1024;
gerardnico@orcl>set autotrace traceonly

gerardnico@orcl>alter session SET sort_area_size =  102400;
 
Session altered.
 
gerardnico@orcl>select *
  2  FROM t t1, t t2
  3  WHERE t1.object_id = t2.object_id;
 
66650 rows selected.
 
gerardnico@orcl>/
 
66650 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3087213741
 
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 66650 |    12M|       |  8268   (1)| 00:01:40 |
|   1 |  MERGE JOIN         |      | 66650 |    12M|       |  8268   (1)| 00:01:40 |
|   2 |   SORT JOIN         |      | 66650 |  6313K|    16M|  4134   (1)| 00:00:50 |
|   3 |    TABLE ACCESS FULL| T    | 66650 |  6313K|       |   214   (3)| 00:00:03 |
|*  4 |   SORT JOIN         |      | 66650 |  6313K|    16M|  4134   (1)| 00:00:50 |
|   5 |    TABLE ACCESS FULL| T    | 66650 |  6313K|       |   214   (3)| 00:00:03 |
------------------------------------------------------------------------------------
 
Predicate Information (identified BY operation id):
---------------------------------------------------
 
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 
 
Statistics
----------------------------------------------------------
         16  recursive calls
         14  db block gets
       1846  consistent gets
       2883  physical reads
          0  redo size
    5621896  bytes sent via SQL*Net TO client
        539  bytes received via SQL*Net FROM client
         15  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          2  sorts (disk)
      66650  rows processed

Oracle Database - Physical Read exceed Oracle Database - consistent gets statistic which is an indicator that Oracle swap to the disk. The 2 Oracle Database - sorts (Disk) help to valid that. But don't rely on it because a lot of operation use the Oracle Database - Temporary Tablespace such as Oracle Database - Hash joins but don't report sorts to disk because they didn't sort.

You can suspect that the allocated memory was exceeded if :

• the Oracle Database - Physical Read do not disappear on the second run, even though there are only hundreds of Oracle Database - Buffer IO (Logical IO) (We expect that data to be in the cache)
• or the Oracle Database - Physical Read outnumber the Oracle Database - Buffer IO (Logical IO)

gerardnico@orcl>set autotrace traceonly statistics
gerardnico@orcl>alter session SET sort_area_size =  1024000;
 
Session altered.
 
gerardnico@orcl>select *
  2  FROM t t1, t t2
  3  WHERE t1.object_id = t2.object_id;
 
66650 rows selected.
 
 
Statistics
----------------------------------------------------------
         18  recursive calls
        186  db block gets
       1846  consistent gets
       6940  physical reads
          0  redo size
    5621896  bytes sent via SQL*Net TO client
        539  bytes received via SQL*Net FROM client
         15  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          2  sorts (disk)
      66650  rows processed

We wait that the Oracle Database - Physical Read go down by increasing the Oracle Database - SORT_AREA_SIZE parameter but it's not the case.

gerardnico@orcl>alter session SET sort_area_size =  1024000;
 
Session altered.
 
gerardnico@orcl>select *
  2  FROM t t1, t t2
  3  WHERE t1.object_id = t2.object_id;
 
66650 rows selected.
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1846  consistent gets
          0  physical reads
          0  redo size
    5621896  bytes sent via SQL*Net TO client
        539  bytes received via SQL*Net FROM client
         15  SQL*Net roundtrips TO/FROM client
          2  sorts (memory)
          0  sorts (disk)
      66650  rows processed

The need for Oracle Database - Physical Read went away entirely.

• Effective Oracle by Design by Tom Kyte