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

1 - About

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.

3 - Set up the test


[email protected]>create table t as select * from all_objects;

Table created.

[email protected]>analyze table t compute statistics for table for columns object_id;

Table analyzed.

[email protected]>alter session set workarea_size_policy =  manual;

Session altered.

[email protected]>alter session set hash_area_size =  1024;
[email protected]>set autotrace traceonly

4 - The sort test

4.1 - With 100 Kb


[email protected]>alter session set sort_area_size =  102400;

Session altered.

[email protected]>select *
  2  from t t1, t t2
  3  where t1.object_id = t2.object_id;

66650 rows selected.

[email protected]>/

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 (Read get|Buffer Mode) (CR) 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 :

4.2 - With 1 MB


[email protected]>set autotrace traceonly statistics
[email protected]>alter session set sort_area_size =  1024000;

Session altered.

gerardnico[email protected]>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.

4.3 - With 10 MB


[email protected]>alter session set sort_area_size =  1024000;

Session altered.

[email protected]>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.

5 - Reference

  • Effective Oracle by Design by Tom Kyte

Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap