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

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.

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

The sort test

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 :

With 1 MB

[email protected]>set autotrace traceonly statistics
[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
----------------------------------------------------------
         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.

With 10 MB

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

Session altered.

gerar[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.

Reference


Powered by ComboStrap