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.
Articles Related
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 :
- 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)
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.