How to know the usage of temporary tablespace in Oracle

Question

I am trying to compare two tables which are very large in my system(Oracle 10g). The way I used to compare is the "MINUS" operation. Because of the large size of tables, I want to know the usage of the temporary tablespace on the real time.

I googled someways on how to get the usage of the tempory tablespace. But I am not sure which one is right.Here are the three ways:

1.select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

2.select BYTES_USED,BYTES_CACHED from V$TEMP_EXTEND_POOL

   What is the difference of BYTES_USED and BYTES_CACHED

3.select USED_EXTENDS, USED_BLOCKS v$sort_segment

the three ways really confused me a lot and I don't know what is the difference.

Solution

Look at the dynamic perfomance views v$sql_workarea and v$sql_workarea_active -- they will tell you not only how much space is being used by the query, but how much of it is attributable to different phases in the execution plan, what sort of sort area it is (hash join etc) and how it is being used (one-pass etc). It'll be a much more effective method of performance tuning.

Comments

[tousinn]

hi,@David.Thank you for your answer.I created a temporary tablespace for the schema.I want to know whether the temporary tablespace is used and the usage of it.

[David Aldridge]

V$SQL_WORKAREA_ACTIVE would identify the tablespace while the space in being used, but so should v$sort_segment. What problem are you seeing? No records in V$SORT_SEGMENT?

[tousinn]

Hi,@David,I found three ways to query the usage of temporary tablespace in my question,but I don't know the difference between them.

[David Aldridge]

If you want to know as much as possible about the usage (where, what, how much and why), then use V$SQL_WORKAREA.