Temp表空间的分配与回收机制测试

1 Temp表空间的分配与回收机制?

贪心法分配机制. 使用完后释放Extents,但仍表示已分配.
下一个Session要用时,先从已分配的空间中,先使用,只有不够时,才又再重新申请分配新的Extents.
直到没有Extent可用,则报错:ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
这其实就是Extent的分配机制,只是在临时表空间上的应用而已.

2 哪些动作会占用到temp表空间
DISK Sort, Temporary Table, Direct write/read path. Create table xx as select * from xx;

3 临时表空间的相关视图:

3.1 临时表空间的基本信息dba_tablespaces
select tablespace_name, block_size,initial_extent, next_extent, pct_increase, MAX_EXTENTS,status,extent_management,SEGMENT_SPACE_MANAGEMENT  
from dba_tablespaces where tablespace_name='TEMP';

3.2 临时文件的信息 dba_temp_files 
col file_name format a40;
select file_name, file_id, tablespace_name, bytes/1024/1024,autoextensible, maxbytes/1024/1024, user_bytes/1024/1024 from dba_temp_files;

V$TEMPFILE
col name format a40;
select FILE#, NAME,BYTES/1024/1024,STATUS from V$TEMPFILE;

3.3 查看临时文件使用率 V$TEMP_SPACE_HEADER
This view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how
much is free as identified in the space header.

select TABLESPACE_NAME,file_id,
(bytes_used+bytes_free)/1024/1024 "size_total(mb)",
bytes_used/1024/1024,
bytes_free/1024/1024
from V$TEMP_SPACE_HEADER;

bytes_used应理解为用过的.bytes_free应理解为没用过的.

3.4 查看正在使用的sort segment V$SORT_SEGMENT

select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;

3.5 查看哪个用户的哪个语句在使用sort segment,使用了多少, V$SORT_USAGE=v$tempseg_usage

select a.username, a.SESSION_NUM,b.sql_text, a.segfile#, a.segblk# "Begin block number", a.extents , a.blocks, a.TABLESPACE
from v$tempseg_usage a, v$sqlarea b
where a.SQLHASH = b.hash_value;

3.6 测试

SQL> select tablespace_name, initial_extent, next_extent/1024/1024, pct_increase from dba_tablespaces where tablespace_name='TEMP';

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT/1024/1024 PCT_INCREASE
------------------------------ -------------- --------------------- ------------
TEMP                                  1048576                     1            0

EXTENT SIZE=1M

v$sort_segment 中记录的是当前使用的Sort Segment空间大小.
但这里记录的是已分配的.
即: 当一个SQL语句需要使用Sort Segment时,Oracle会分配相应的Extent给这个会话使用.

1) 假定 最开始执行查询语句: select count(*) from test group by name; 前:
col tablespace_name format a20;
select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS  total,USED_EXTENTS  used,FREE_EXTENTS free, FREED_EXTENTS unallocated from v$sort_segment;
TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             0          0          0          0           0

表示当前没有已分配的Extent.

2) 执行 select count(*) from test group by name; 查询语句时:
col tablespace_name format a20;
select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS  total,USED_EXTENTS  used,FREE_EXTENTS free, FREED_EXTENTS unallocated from v$sort_segment;
TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             0          0          0          0           0

还是0,表明排序过程中没有用到Sort Segment.全部在内存中就完成,通过Statspack生在报告可以发现Sorted In-Memory 100%.

在排序的过程中,分配的Extent一直在增长.用多少,分配多少.

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             1        295        295          0           0

SQL> /

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             1        312        312          0           0

3) 执行 select count(*) from test group by name; 查询语句完成后:
col tablespace_name format a20;
select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS  total,USED_EXTENTS  used,FREE_EXTENTS free, FREED_EXTENTS unallocated from v$sort_segment;

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             0        399          0        399           0
排序完成后,全部归还.used=0, Total为已分配的Extent个数.

5) 增加Sort Segment的需求量
insert into test select * from test;
commit;
执行 select count(*) from test group by object_name; 查询语句:

col tablespace_name format a20;
select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS  total,USED_EXTENTS  used,FREE_EXTENTS free, FREED_EXTENTS unallocated from v$sort_segment;

会分配更多的Extent.

SQL> /

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             2        593        593          0           0

SQL> /

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             0        798          0        798           0

6) 重启后.

SQL> select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS  total,USED_EXTENTS  used,FREE_EXTENTS free, FREED_EXTENTS unallocated from v$sort_segment;

TABLESPACE_NAME      CURRENT_USERS      TOTAL       USED       FREE UNALLOCATED
-------------------- ------------- ---------- ---------- ---------- -----------
TEMP                             0        798          0        798           0
 
已分配的Extent不会回收,已分配与使用完全不是一个概念.

4 问题记录
ASK Tom Question and answer about Temporary tablespace.

Q1: 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.

Answer: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.

As a normal behaviour all the used temporary space/segments should be released automatically upon the instance shutdown & startup, ..
this is false. once allocated - temp stays allocated forever.

Q2: 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.

A: 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.

(First Session need to disk sort.  One sort segment for one tablespace)

Q3: If many session are performing sort operation,will it user only one sort segment or each sort session will have their own sort segment.
A: one sort segment, many extents -- each extent used by a session.
(one Sort Segment,Different Extents)

Q4: What is the unit of measurment(i.e bytes or block etc..) of the following V$SYSSTAT parameter.
A: 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. 

Q5: How we konw sort segment is releasing Space or not.
A: Select * from v$sort_usage;

Q6: V$temp_space_header and V$sort_segment 在空间大小上的矛盾?
A: 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)

So, in short, v$temp_space_header and v$sort_segment are not supposed to reconcile with each other. 
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.

Q7: Do there any relationship between SORT_AREA_SIZE parameter values and TEMPORARY TABLESPACE extent size?
A: your extent size in temp should be a multiple of the sort_area_sizes you use. (8i,9以上好像没这个要求)

Q8: What are the errors ORA-1630 & ORA-1652, i could not find proper oracle
   documentation for these errors ?

A: both are documented in the Server Messages guide

http://download-east.oracle.com/docs/cd/A58617_01/server.804/a58312/newch225.htm#17574
The unix command line utility oerr would tell you:

$ oerr ora 1630
01630, 00000, "max # extents (%s) reached in temp segment in tablespace %s"

// *Cause:  A temp segment tried to extend past max extents.
// *Action: If maxextents for the tablespace is less than the
            the system
//          maximum, you can raise that. Otherwise, raise 
            pctincrease for the
//          tablespace

$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent for temp segment in
            tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add
            one or more
//          files to the tablespace indicated.

Q9: What is the concept of temp segment in a tablespace which is going to store
   tables and indexes alone? Should it not be TEMP tablespace which store temp
   segments?
A: during the creation of those objects(Index, tables) -- temporary extents were allocated to "create
them".  After their creation -- these temporary extents would be converted into real
extents and given to the table.  During the "create phase", they are temporary -- when the entire thing is built -- they will become permanent.
（创建某些对象的时候先在临时表空间上创建，最后再变永久的．）

Q10: When session are done with sorts,does values sorts (memory) sorts (disk)  sorts (rows) for this parameter goes down?
A:sort(disk) will never go down until the server is rebooted.  v$sysstat is a cumulative counter table.
sorts(rows)什么意思?排序的况行数.