2

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.

BradleyDotNET
  • 60,462
  • 10
  • 96
  • 117
tousinn
  • 73
  • 2
  • 3
  • 11
  • MINUS might not be a very efficient method -- if you're looking for rows in one table that do not exist in another then an equijoin leading to a hash join would be possibly more efficient. – David Aldridge Mar 26 '13 at 09:59
  • hi,@David Aldridge.Thank you for your comment.Actually one table is a backup of the other one.The data in the original table could be inserted,updated or deleted.I don't know any other efficient way to do this compare. – tousinn Mar 27 '13 at 00:20

2 Answers2

3

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • 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. – tousinn Mar 27 '13 at 00:23
  • 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? – David Aldridge Mar 27 '13 at 10:04
  • 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. – tousinn Mar 28 '13 at 00:08
  • 1
    If you want to know as much as possible about the usage (where, what, how much and why), then use V$SQL_WORKAREA. – David Aldridge Mar 28 '13 at 00:12
0

V$SORT_SEGMENT view can be used to get used/free extents, used/free blocks information for TEMPORARY tablespaces.

V$TEMP_SPACE_HEADER and V$TEMP_EXTEND_POOL views are almost the same which provides used bytes information. However, V$TEMP_EXTEND_POOL is reliable because former is updated only when DB is restarted or tablespace is recreated.

Note: From Oracle 11g, DBA_TEMP_FREE_SPACE view can be used to get TEMPORARY tablespace information.

Srini Karthikeyan
  • 701
  • 8
  • 14