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

Card Puncher Data Processing

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

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

The sort test

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 :

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.

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.

Reference





Discover More
Card Puncher Data Processing
Oracle Database - Physical Read

The physical read statistic from the autotrace statistics is a measure of how much real I/O, or physical i/o, your query performed. A physical read of table or index data places the block into the buffer...
Card Puncher Data Processing
Oracle Database - Read

single block reads = sequential reads in Oracle Term



Share this page:
Follow us:
Task Runner