Re: temp tablespace allocaton question

From: Joseph Amalraj <joseph_at_amalrajinc.com>
Date: 2006-01-06 20:26:03
Message-id: 20060106192603.25486.qmail@web314.biz.mail.mud.yahoo.com

Jeffrey Beckstrom wrote: I am looking at v$sort_segment and notice that if take \ total_blocks
(18848) times block size (8192) you get 147.25 M. Shouldn't this equal the 1000M that was allocated?

SQL> select name,bytes/1024/1024 bytes_m from v$tempfile;

NAME

---

BYTES_M

---

F:\HASTUSTS\TEMP.DBS
1000

SQL> select * from v$sort_segment;

TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE
------------------------------- ------------ ------------- -----------
CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS
------------- ------------- ------------ ------------ -----------

---

FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE
----------- ------------- ----------- ------------- -------------

---

MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS
---------- ------------- --------------- ------------- ---------------
RELATIVE_FNO

---

TEMP 0 0 8
0 2356 18848 0 0
2356
18848 0 51930 0 0
2356
18848 2249 17992 2249 17992
0

SQL> Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority 1240 W. 6th Street
Cleveland, Ohio 44113

    I am looking at v$sort_segment and notice that if take total_blocks (18848) times \ block size (8192) you get 147.25 M. Shouldn't this equal the 1000M that was allocated?    

  SQL> select name,bytes/1024/1024 bytes_m from v$tempfile;    

  NAME

---

   BYTES_M

---

F:\HASTUSTS\TEMP.DBS
      1000       

SQL> select * from v$sort_segment;    

  TABLESPACE_NAME                 SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE


------------------------------- ------------ ------------- -----------

CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS
------------- ------------- ------------ ------------ ----------- ------------
FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE
----------- ------------- ----------- ------------- ------------- ----------
MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS
---------- ------------- --------------- ------------- ---------------
RELATIVE_FNO

---

TEMP                                       0             0           8
            0          2356        18848            0           0         2356
      18848             0       51930             0             0       2356
     18848          2249           17992          2249           17992
           0
   
  

SQL>       Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority 1240 W. 6th Street
Cleveland, Ohio 44113

The sort segment grows to a steady size in the temporary tablespace. Its size does not \ decrease unless the instance is re-started. When the instance restarts SMON deallocates \ the sort segments. The "total blocks" in v$sort_segment is the number of blocks allocated \ to the sort segment. This grows if more sort space is needed. Oracle reuses sort segment \ extents when they are not being used.    

  Regards    

  Joseph Amalraj Received on Fri Jan 06 2006 - 20:26:03 CST