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

> Database > Oracle Database

1 - About

How to detect read from the temporary tablespace ?

Step followed :

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.

Advertising

3 - Set up the test

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

4 - The sort test

4.1 - With 100 Kb

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 (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

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.

4.3 - With 10 MB

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.

Advertising

5 - Reference

db/oracle/oradb_example_how_to_detect_read_from_temp.txt · Last modified: 2017/09/13 16:16 by gerardnico