about temp segments(zt from asktom)

  May 07, 2002
Reviewer:  A reader

Hi Tom,

When user is doing sort operation.oracle first do sort in memory(sort_area_size)
and if the space is not enough it go to temporary tablespace(if temp tablespace
is TEMP) and create TEMP SEGMENT for sorting.Once process is done with sorting
oracle deallocate extents.

Am i right?

As far as i konw we only create TEMP tablespace ,
How Sort segment are created?.

and how many sort segment(max) can be in a database.

2) If many session are performing sort operation,will it user only one sort
segment or each sort session will have their own sort segment.

3) I have maked entry in V$SORT_USAGE is not deleted after sort is done. why?

4) What is the unit of measurment(i.e bytes or block etc..)
of the following V$SYSSTAT parameter.
sorts (memory)                                                  
sorts (disk)                                                    
sorts (rows)

5) How we konw sort segment is releasing Space or not.

I am really confused here.Please help me

Thanks


Followup:  
You were right upto the "deallocate extents".

The extents are managed internally -- once we allocate them (like RBS) we don't
give them back (there is no "shrink" for temp).  You will find the temp space
only "stays the same size or grows", it does not shrink.  The expense of
allocating the extent is removed in this fashion.

You use v$sort_usage and v$sort_segment to see what is really there and what is
used/free.

You can have as many TEMP tablespaces as you want -- one is typical, dozens can
be made -- they would each have a temp segment.  We also create temp segments
when doing some operations like CREATE TABLE AS SELECT -- these temp segments
are destined to become permanent ones when the operation is done.

2) one sort segment, many extents -- each extent used by a session.

3) sure it is.  give me an example.  Here is an example of mine -- shows that
the sort space is "freed" -- UNLESS you still need it (which is probably the
case in your situation, you weren't done with it yet!)

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size=32765;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$sort_usage;
no rows selected

Nothing being used, single user system...

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
  2    from all_objects
  3   order by object_name, owner, object_type, last_ddl_time, created,
object_id;

22925 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         14  db block gets
      74628  consistent gets
        342  physical reads
          0  redo size
    2862933  bytes sent via SQL*Net to client
     170033  bytes received via SQL*Net from client
       1530  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      22925  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$sort_usage;
no rows selected

Now, that query used sort space (in another session I ran select * from
v$sort_usage whilst this was sorting to verify that).

When I'm exhausted the result set -- it released the extent I was using....

Now:

ops$tkyte@ORA817DEV.US.ORACLE.COM> variable x refcursor
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2     l_rec all_objects%rowtype;
  3  begin
  4     open :x for
  5         select *
  6           from all_objects
  7          order by object_name, owner, object_type, last_ddl_time, created,
object_id;
  8     fetch :x into l_rec;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$sort_usage;

USER                           SESSION_ SESSION_NUM SQLADDR     SQLHASH
TABLESPACE                      CONTENTS  SEGTYPE
------------------------------ -------- ----------- -------- ----------
------------------------------- --------- ---------
  SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ---------- ---------- ----------
OPS$TKYTE                      80131A38         822 00                0
TEMPORARY                       TEMPORARY SORT
       202       1481          6        384          1

Ahh -- there is the sort space.  We have only fetched the first record, the
remaining 22,000 records are sitting there in temp waiting to be fetched in this
case!  We can see that by fetching them:

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> print :x

22924 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$sort_usage;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM>

and see that the temp extent disappears.  I believe this is what you may have
observed


4) none of the above -- it is not bytes or blocks -- it is a cardinality
representing the number of times you sorted or the number of rows.  If you look
at my example above, I have ONE sort to disk -- we sorted ONCE (v$sort_usage
shows I used 6 temp extents of some 384 blocks)

5) use v$sort_usage as I demonstrated....
 

GOTO a page to Bookmark Review | Bottom | Top

still problem with v$sort_segment  May 08, 2002
Reviewer:  Kathrin Spiller  from germany

Thanks for the explanation of v$sort_usage and v$sort_segment.

I've learned that looking at v$sort_segment shows what is allocated.
I've got one TEMP-Tbsp, consisting of 3 datafiles with 601 MB and one of 101 MB.


09:42:49 SYS@RP1G>select tablespace_name, file_id, blocks_used from
v_$temp_space_header;

TABLESPACE_NAME                   FILE_ID BLOCKS_USED
------------------------------ ---------- -----------
TEMP                                    1       76928
TEMP                                    2       76928
TEMP                                    3       76928
TEMP                                    4         128

This shows datafile 1-3 fully used, 4 empty, except for the
locally-management-bitmap. So I expected to find a total
of 3*600 MB sort segments in use.

09:48:12 SYS@RP1G>select tablespace_name, total_blocks, max_blocks from
v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS MAX_BLOCKS
------------------------------- ------------ ----------
TEMP                                   76800      76800

This are only 600 MB. Who allocated the rest?

thanks in advance
 


Followup: 
well, file 4 is not showing you the bitmap for the LMT -- that would be 64k (8
blocks), not 128 blocks (1m).  That looks more like an extent -- is your LMT
uniform size 1m-64k perhaps?


Anyway, v$temp_space_header is persistent across restarts.  V$sort_segment is
not.

v$temp_space_header shows you the usage of the temp files forever (as long as
they've been around).  As SOME POINT in the past, you used that many blocks from
each file (those blocks were touched).  You shutdown and restarted -- clearing
out v$sort_usage (but not v$temp_space_header as that one is based on the
tempfiles themselves)

Here is an example that might make it clearer:

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop tablespace test_temp;
Tablespace dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create temporary tablespace test_temp
  2  tempfile '/tmp/test_temp01' size 1m reuse,
  3           '/tmp/test_temp02' size 1m reuse,
  4           '/tmp/test_temp03' size 1m reuse,
  5           '/tmp/test_temp04' size 1m reuse
  6  extent management local
  7  uniform size 16k
  8  /
Tablespace created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user ops$tkyte temporary tablespace
test_temp;
User altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$temp_space_header where tablespace_name = ''TEST_TEMP''' )
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 1
BYTES_USED                    : 65536
BLOCKS_USED                   : 8
BYTES_FREE                    : 983040
BLOCKS_FREE                   : 120
RELATIVE_FNO                  : 1
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 3
BYTES_USED                    : 65536
BLOCKS_USED                   : 8
BYTES_FREE                    : 983040
BLOCKS_FREE                   : 120
RELATIVE_FNO                  : 2
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 4
BYTES_USED                    : 65536
BLOCKS_USED                   : 8
BYTES_FREE                    : 983040
BLOCKS_FREE                   : 120
RELATIVE_FNO                  : 3
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 5
BYTES_USED                    : 65536
BLOCKS_USED                   : 8
BYTES_FREE                    : 983040
BLOCKS_FREE                   : 120
RELATIVE_FNO                  : 4
-----------------

PL/SQL procedure successfully completed.

So, when we start - each file has 8 blocks used (8*8k = 64k = LMT bitmap)

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$sort_segment where tablespace_name = ''TEST_TEMP''' )

PL/SQL procedure successfully completed.

and v$sort_usages says "nothing here", we haven't used any of it yet.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 32765;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from all_objects order by
object_name, object_type, owner, last_ddl_time, created, object_id;

22926 rows selected.


Statistics
----------------------------------------------------------
         12  recursive calls
        370  db block gets
      74632  consistent gets
        347  physical reads
          0  redo size
    2861499  bytes sent via SQL*Net to client
     170033  bytes received via SQL*Net from client
       1530  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
      22926  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$temp_space_header where tablespace_name = ''TEST_TEMP''' )
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 1
BYTES_USED                    : 786432
BLOCKS_USED                   : 96
BYTES_FREE                    : 262144
BLOCKS_FREE                   : 32
RELATIVE_FNO                  : 1
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 3
BYTES_USED                    : 786432
BLOCKS_USED                   : 96
BYTES_FREE                    : 262144
BLOCKS_FREE                   : 32
RELATIVE_FNO                  : 2
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 4
BYTES_USED                    : 770048
BLOCKS_USED                   : 94
BYTES_FREE                    : 278528
BLOCKS_FREE                   : 34
RELATIVE_FNO                  : 3
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 5
BYTES_USED                    : 770048
BLOCKS_USED                   : 94
BYTES_FREE                    : 278528
BLOCKS_FREE                   : 34
RELATIVE_FNO                  : 4
-----------------

PL/SQL procedure successfully completed.

Now, that shows we used about 3/4 of a meg from each file for that sort we
did.    We now have 96+96+94+94 = 380 blocks "used" in the tempfiles.  That
means USED at some point in time...



ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$sort_segment where tablespace_name = ''TEST_TEMP''' )
TABLESPACE_NAME               : TEST_TEMP
SEGMENT_FILE                  : 0
SEGMENT_BLOCK                 : 0
EXTENT_SIZE                   : 2
CURRENT_USERS                 : 0
TOTAL_EXTENTS                 : 174
TOTAL_BLOCKS                  : 348
USED_EXTENTS                  : 0
USED_BLOCKS                   : 0
FREE_EXTENTS                  : 174
FREE_BLOCKS                   : 348
ADDED_EXTENTS                 : 174
EXTENT_HITS                   : 0
FREED_EXTENTS                 : 0
FREE_REQUESTS                 : 0
MAX_SIZE                      : 174
MAX_BLOCKS                    : 348
MAX_USED_SIZE                 : 174
MAX_USED_BLOCKS               : 348
MAX_SORT_SIZE                 : 174
MAX_SORT_BLOCKS               : 348
RELATIVE_FNO                  : 0
-----------------

PL/SQL procedure successfully completed.

that shows that temp segment has 348 blocks in use (348+4*8 = 380 = total
blocks used in the tempfiles.  4*8 = LMT bitmap, 348 = sort blocks)...

Now, lets do a restart of the database:


ops$tkyte@ORA817DEV.US.ORACLE.COM> connect / as sysdba
Connected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
ops$tkyte@ORA817DEV.US.ORACLE.COM> startup
ORACLE instance started.

Total System Global Area   72876192 bytes
Fixed Size                    73888 bytes
Variable Size              64430080 bytes
Database Buffers            8192000 bytes
Redo Buffers                 180224 bytes
Database mounted.
Database opened.

ops$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set serveroutput on

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$temp_space_header where tablespace_name = ''TEST_TEMP''' )
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 1
BYTES_USED                    : 786432
BLOCKS_USED                   : 96
BYTES_FREE                    : 262144
BLOCKS_FREE                   : 32
RELATIVE_FNO                  : 1
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 3
BYTES_USED                    : 786432
BLOCKS_USED                   : 96
BYTES_FREE                    : 262144
BLOCKS_FREE                   : 32
RELATIVE_FNO                  : 2
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 4
BYTES_USED                    : 770048
BLOCKS_USED                   : 94
BYTES_FREE                    : 278528
BLOCKS_FREE                   : 34
RELATIVE_FNO                  : 3
-----------------
TABLESPACE_NAME               : TEST_TEMP
FILE_ID                       : 5
BYTES_USED                    : 770048
BLOCKS_USED                   : 94
BYTES_FREE                    : 278528
BLOCKS_FREE                   : 34
RELATIVE_FNO                  : 4
-----------------

PL/SQL procedure successfully completed.

Now, v$temp_space_header "remembers" that at some point, we used 380 blocks,
there have been 380 blocks used over time.  The files are that big, that
"initialized" if you will...

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from
v$sort_segment where tablespace_name = ''TEST_TEMP''' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$sort_segment
  2  /

no rows selected

but look at that!  v$sort_segment is EMPTY..  It doesn't remember its
"extents" from last time.  It needs to reallocate them again. 

So, in short, v$temp_space_header and v$sort_segment are not supposed to
reconcile with eachother.  The blocks in temp_space_header are the total number
of "initialized" blocks in the tempfiles.  The blocks in sort_segment are the
total number of allocated and usable blocks.

You cannot really compare these views as you are trying.


So, to answer the "who allocated the rest" -- the answer is "no one, you have
not allocated them as yet!!!!"
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-794863/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-794863/