temp表空间的一点总结

我一直都把oracle中的temp tbs理解成win下的虚拟内存和unix下的swap分区。不知道是否准确？
总之temp使用不当会影响oracle数据库的性能。
一点总结，欢迎大家拍砖！


1、临时表空间的用途

1）disk sort

在了解disk sort排序之前，先来看一段doc（When the WORKAREA_SIZE_POLICY parameter is set to MANUAL, the
maximum amount of memory allocated for a sort is defined by the
parameter SORT_AREA_SIZE. If the sort operation is not able to
completely fit into SORT_AREA_SIZE memory, then the sort is separated into phases.The temporary output of each phase is stored in temporary
segments on disk. The tablespace in which these sort segments are created is the users temporary tablespace.When Oracle writes sort operations to disk, it writes out partially sorted
data in sorted runs. After all the data has been received by the sort, Oracle merges the runs to produce the final sorted output. If the sort area
is not large enough to merge all the runs at once, then subsets of the runs
are merged in several merge passes. If the sort area is larger, then there
are fewer, longer runs produced. A larger sort area also means that the
sort can merge more runs in one merge pass.）

从上面doc看来，我自己的理解是排序始终是在内存里完成的，如果要排序的数据量很大，在内存里不能完成，oracle会分阶段来排序，每次先排一部分，并且把排好序的数据临时存放在用户default temporary tablespace中的temp segment上，而临时表空间对应的tempfile属于disk文件，这就是disk sort的由来。具体oracle是如何分阶段来排序的，doc说的还算清楚，只是可能不太好理解。其实上面doc说的不正是排序时常提到的3种情况：optimail，one pass sort，mutli-pass sort
a）optimal

如图1

我们知道排序最理想的方式是optimal，也就是需要排序的数据都在内存里放的下，而且内存有足够空间做排序。排序本身的原理可能是相当复杂的，但是大致的说法应该是排序时在内存需要维护一个树状的结构来完成排序，所以假如你有5M的数据需要排序，这时候你需要的内存会远大于5M。

b）one pass sort

如图2

假如需要排序的是1到20，但内存一次只能排序5个数据，这时候不得不5个数据做一个排序，每排好一组就放在tempfile上，最后在磁盘上就存在4组数据，这时候If the sort area
is large enough to merge all the runs at once，那么所做的sort就是one pass的。在这个特定的例子里，large enough应该是指有能力一次在内存里做4个数据的排序，及首先在1,2,12,11中能够选出最小的，就是1，然后在接下来的6,2,12,11里选出2, 然后在6,3,12,11中选出3，以此类推

c）mutli-pass sort
如图3

在这个例子里，如果内存小到一次只能排序3个数据，那onepass 就做不到了，按照和onepass类似的方法，先在tempfile得到7组数据，然后这时候因为the sort area
is not large enough to merge all the runs at once, then subsets of the runs
are merged in several merge passes.

因此需要把7组数据变成第二阶段的3组，然后在把这3组数据排序。因为在磁盘上存了2次数据，所以叫multi-pass，内存越小，pass的次数越多，排序需要的时间也就越长。

2）global temporary table

oracle支持两种类型的临时表，之所以称为global，是因为不论是哪个session创建的临时表，该表对所有的session都是可见的，但是数据仅仅对创建临时表的session可见。

oracle支持两种类型的临时表：

a）transaction-specific (the default)

doc中提到的transaction-specific其实就是指在创建临时表时在语法中指定的on commit delete rows，这种类型的临时表在commit之后也就是事务结束以后数据被删除了：

SQL> create global temporary table gt1 as select * from dba_tables;

表已创建。

SQL> select count(*) from gt1;

COUNT(*)
----------
0

b）session-specific

这种临时表指的就是在创建时使用on commit preseve rows子句创建的临时表，它的特点是在事务结束以后数据并没有立即被删除，而是在session结束之后数据被删除的：

SQL> create global temporary table gt3 on commit preserve rows as select * from
dba_tables;

表已创建。

SQL> select count(*) from gt3;

COUNT(*)
----------
1212

在另一个sqlplus窗口看看数据发现不可见：

SQL> select count(*) from gt3;

COUNT(*)
----------
0

SQL>

再回到创建gt3时的窗口：

SQL> insert into gt3 select *from dba_tables;

已创建1213行。

SQL> select count(*) from gt3;

COUNT(*)
----------
2425

SQL> rollback;

回退已完成。

SQL> select count(*) from gt3;

COUNT(*)
----------
1212

SQL> truncate table gt3;

表被截断。

SQL> select count(*) from gt3;

COUNT(*)
----------
0

SQL>

试验发现global temporary table支持rollback，意味着对temporary table的操作是需要占用undo的。而undo是需要保护的，因此使用临时表也会产生undo生成的少量redo。临时表也支持truncate，而doc上说的（DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table）有些莫名其妙。

我们在判断临时表到底是属于那种类型时除了通过数据验证之外，也可以查询数据字典获得：

SQL> select TABLE_NAME ,TEMPORARY , DURATION,tablespace_name,logging from dba_t
ables where table_name in ('GT1','GT2','GT3');

TABLE_NAME T DURATION TABLESPACE LOG
------------------------------ - --------------- ---------- ---
GT3 Y SYS$SESSION NO
GT2 Y SYS$TRANSACTION NO
GT1 Y SYS$TRANSACTION NO

DURATION字段说明了临时表的类型。

退出创建gt3时的session发现数据被清除了：

SQL> insert into gt3 select *from dba_tables;

已创建1213行。

SQL> commit;

提交完成。

SQL> select count(*) from gt3;

COUNT(*)
----------
1213

SQL> disconnect
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
SQL> connect sys/system@dmt as sysdba
已连接。
SQL> select count(*) from gt3;

COUNT(*)
----------
0

临时表和普通的heap表产生的日志对比：

SQL> create table t1 tablespace users as select * from dba_objects where 1=2;

表已创建。

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 3248648

SQL> insert into t1 select * from dba_objects;

已创建11260行。

SQL> commit;

提交完成。

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 4780384

SQL> select 4780384 - 3248648 from dual;

4780384-3248648
---------------
1531736

再来看看同样的操作临时表产生的reodo：

SQL> create global temporary table gt1 on commit preserve rows as select * from
dba_objects where 1=2;

表已创建。

SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 1584124

SQL> insert into gt1 select * from dba_objects;

已创建11456行。

SQL> commit;

提交完成。

SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 1773124

SQL> select 1773124 - 1584124 from dual;

1773124-1584124
---------------
189000

相差的redo：

SQL> select 1531736 - 189000 from dual;

1531736-189000
--------------
1342736

SQL>

通过上面对比我们发现同样的操作使用临时表比普通的表要少产生很多日志；而尽管临时表显示的是nologging，但是还是产生了一些日志，这些日志其实是由undo 产生的，因为临时表支持rollback，意味着对临时表执行dml操作是需要占用undo的，而undo本身也是需要保护的，因此对undo的使用产生了一部分redo。

另外在创建临时表时不能指定tablespace tablespace_name子句，临时表只能使用用户的default temporary tablespace：

SQL> create global temporary table gt2(id int) on commit preserve rows tablespac
e tmp;
create global temporary table gt2(id int) on commit preserve rows tablespace tmp

*
第 1 行出现错误:
ORA-14451: 不受支持的临时表功能


了解了临时表的种类以及临时表的特性之后就可以根据需要灵活的使用临时表。

2、临时表空间的分类

上面提到disk sort会使用临时表空间，根据这个用途，临时表空间可以分为下面3种：

1）Permanent（其实就是指system表空间）

在dmt下，如果没有为系统创建Tablespaces of Type TEMPORARY 类型的表空间，当然如果也不存在lmt管理的temporary tablespace，此时系统在disk sort时会使用system表空间，而system表空间属于Permanent（永久）表空间，使用时会产生redo。简单的做个测试：

SQL> select tablespace_name , extent_management,contents,logging from dba_tables
paces;

TABLESPACE EXTENT_MAN CONTENTS LOGGING
---------- ---------- --------- ---------
SYSTEM DICTIONARY PERMANENT LOGGING
UNDOTBS1 LOCAL UNDO LOGGING
SYSAUX LOCAL PERMANENT LOGGING
TEMP LOCAL TEMPORARY NOLOGGING
USERS LOCAL PERMANENT LOGGING

10g引入了default temporary tablespace，default temporary tablespace不能删除，除非再指定一个temporary tablespace为default的或者system也可以作为default temporary tablespace
SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
第 1 行出现错误:
ORA-12906: 不能删除默认的临时表空间

SQL> alter database default temporary tablespace system;

数据库已更改。

除了system表空间之外没有哪种Permanent表空间可以作为临时表空间用

SQL> alter database default temporary tablespace sysaux;
alter database default temporary tablespace sysaux
*
第 1 行出现错误:
ORA-12902: 默认的临时表空间必须属 SYSTEM 或 TEMPORARY 类型

SQL> drop tablespace temp including contents and datafiles;

表空间已删除。
SQL> select tablespace_name , extent_management,contents,logging from dba_tables
paces;

TABLESPACE EXTENT_MAN CONTENTS LOGGING
---------- ---------- --------- ---------
SYSTEM DICTIONARY PERMANENT LOGGING
UNDOTBS1 LOCAL UNDO LOGGING
SYSAUX LOCAL PERMANENT LOGGING
USERS LOCAL PERMANENT LOGGING

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 110271220

SQL> select group# , status , sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 130
2 CURRENT 131
3 INACTIVE 129

对t表（t是一个具有72w数据大约80m左右的表）进行排序：

select * from t order by object_id desc

SQL> select group# , status , sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 130
2 ACTIVE 131
3 CURRENT 132

SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 113859320

SQL> select (113859320 - 110271220)/1024/1024 from dual;

(113859320-110271220)/1024/1024
-------------------------------
3.42187881

我们发现日志进行了切换，而且生成了大约3.5m的redo。

查看disk sort使用sytem 表空间的情况：

SQL> select policy ,work_area_size , actual_mem_used , number_passes , tempseg_s
ize , tablespace from v$sql_workarea_active;

POLICY WORK_AREA_SIZE ACTUAL_MEM_USED NUMBER_PASSES TEMPSEG_SIZE TABLES
------------ -------------- --------------- ------------- ------------ ------
AUTO 2412544 2675712 1 86833152 SYSTEM

清楚的看到使用了system表空间进行了disk sort，使用Permanent tablespaces（就是指system表空间）进行disk sort排序的缺点是：

Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts. This is because of the following reasons:

The ST-enqueue is used for allocation and de-allocation of each extent allocated to a sort segment.
Sort-segments are not reused. Each process performing a disk sort creates then drops it's own sort segment. In addition, a single sort operation can require the allocation and deallocation of many extents, and each extent allocation requires the ST-enqueue.
简单的验证一下sort segment的分配和回收情况：

排序时system的空间使用情况：

SQL> select round((a.bytes - b.bytes)/a.bytes*100,2) used_space_pct
2 from dba_data_files a, dba_free_space b
3 where a.file_id = b.file_id
4 and a.file_id=1;

USED_SPACE_PCT
--------------
98.57

排序后system的空间使用情况：

SQL> select round((a.bytes - b.bytes)/a.bytes*100,2) used_space_pct
2 from dba_data_files a, dba_free_space b
3 where a.file_id = b.file_id
4 and a.file_id=1;

USED_SPACE_PCT
--------------
70.96

也就是说排序之后sort segment立即进行了释放，而在为sort segment分配和回收extents都会产生ST-enqueue ( Space transaction enqueue)，这一点可以在排序时观察到，一般不太容易观察到：

SQL> select * from v$lock where type='ST';

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ----
------ ----------
6D22DDE4 6D22DDF8 26 ST 0 0 6 0
0 0

另外使用system表空间进行disk sort时的temp segment是在排序时创建的，而排序之后立即进行了删除，也就是说这个排序段不能重复利用。简单做个测试：

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未选定行

--在plsql developer对表t实行排序：

select * from t order by object_id desc ;

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

SEGMENT_NA SEGMENT_TY TABLESPACE HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ---------- ---------- ----------- ------------ ---------- ----------
1.109061 TEMPORARY SYSTEM 1 109061 95136 19

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未选定行

SQL>

对比排序前后temp segment的情况，显然temp segment是在排序时创建的，排序之后立即释放了，如果下次需要排序，还需要重新分配extent，重新创建temp segment，而分配或者回收extnet都会产生ST-equence.而ST-equence正是dmt的缺陷。



2）Tablespaces of Type TEMPORARY

在没有lmt之前oracle没有tempfile的概念，因此创建temp tbs的语法是：

SQL> create tablespace temp datafile 'E:ORACLEPRODUCT10.2.0ORADATADMTTEMP0
1.DBF' SIZE 10M AUTOEXTEND ON TEMPORARY;

表空间已创建。

在10g环境通过上面语法创建的表空间temp的extent管理方式是dictionary，而segment space management 是auto，这显然是有问题的；而它的contents是TEMPORARY，说明它是临时表空间，同时也要注意是logging，意味着使用它也要产生redo

SQL> select tablespace_name,contents , logging , extent_management,segment_space
_management from dba_tablespaces;

TABLESPACE CONTENTS LOGGING EXTENT_MAN SEGMEN
---------- --------- --------- ---------- ------
SYSTEM PERMANENT LOGGING DICTIONARY MANUAL
UNDOTBS1 UNDO LOGGING LOCAL MANUAL
SYSAUX PERMANENT LOGGING LOCAL AUTO
TEMP TEMPORARY LOGGING DICTIONARY AUTO
USERS PERMANENT LOGGING LOCAL AUTO

SQL> alter database default temporary tablespace temp;

数据库已更改。

SQL> select * from t order by object_id desc ;
select * from t order by object_id desc
*
第 1 行出现错误:
ORA-10615: Invalid tablespace type for temporary tablespace


出现错误的原因在于：在dmt下不可能存在segment space management auto的tbs，这应该算是10g orace的一个漏洞吧，尽管在10g下使用dmt已经很少了，而使用这种方式创建的temp tbs就更少了，但是应该了解oracle的历史产物。

SQL> alter database default temporary tablespace system;

数据库已更改。

SQL> drop tablespace temp ;

表空间已删除。

重新创建一下临时表空间，指定为segment space management manual：
SQL> create tablespace temp datafile 'E:ORACLEPRODUCT10.2.0ORADATADMTTEMP0
1.DBF' SIZE 10M reuse AUTOEXTEND ON TEMPORARY segment space management manual;

表空间已创建。

SQL> alter database default temporary tablespace temp;

数据库已更改。

--再次在plsql developer执行如下语句发现没有错误

select * from t order by object_id desc

而在排序的过程中发现日志组进行了非常频繁的切换，意味着在排序期间产生了大量的redo，而且创建了temp segment：

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
ACTIVE 1
CURRENT 2
ACTIVE 3

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
ACTIVE 1
ACTIVE 2
CURRENT 3

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

SEGMENT_NA SEGMENT_TY TABLESPACE HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
---------- ---------- ---------- ----------- ------------ ---------- ----------
5.2 TEMPORARY TEMP 5 2 78419 15683

SQL> select status , group# from v$log;

STATUS GROUP#
---------------- ----------
CURRENT 1
ACTIVE 2
ACTIVE 3

重启实例之后发现排序使用的临时段被删除了，这种表空间的特点是临时段被重建之后可以一直被重用，直到实例重启之后临时段被删除。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 71304548 bytes
Database Buffers 92274688 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。

SQL> select segment_name , segment_type , tablespace_name , header_file,header_b
lock , blocks , extents from dba_segments where segment_type like upper('%temp%'
);

未选定行

3）Temporary Tablespaces

使用"Tablespaces of Type TEMPORARY"这种类型的临时表空间产生的temp segment的缺点是除了dmt在分配和回收extent所存在的ST-equence问题之外，另一个显而易见的缺点是在实例重启之后temp segment也被drop掉了，下次使用temp segment时还需要创建，也就是说temp segment也没有被很好的重复利用。在lmt出现之后，oracle引入了lmt管理方式的临时表空间，它的创建语法随之也发生了变化：

SQL> create temporary tablespace tmp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp01.dbf' size 10m autoextend on;

表空间已创建。

从创建语法上看首先明确指出了表空间的类型是temporary ；其次引入了tempfile，不再是datafile了：

SQL> select tablespace_name,contents , logging,extent_management,segment_space_m
anagement,allocation_type from dba_tablespaces where tablespace_name like 'T%';

TABLESPACE CONTENTS LOGGING EXTENT_MAN SEGMEN ALLOCATIO
---------- --------- --------- ---------- ------ ---------
TEMP TEMPORARY LOGGING DICTIONARY MANUAL USER
TMP TEMPORARY NOLOGGING LOCAL MANUAL UNIFORM

还有一个需要说明的地方是在lmt下创建的这种类型的临时表空间分配extent时只能是uniform而不能是我们熟悉的autoallocate，default的创建语法是uniform，而且size 是1m。而另外两个系统用的表空间system,undo只能是autoallocate。

SQL> create temporary tablespace tmp1 tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp02.dbf' size 10m autoextend on uniform size 2m;

表空间已创建。

SQL> create temporary tablespace tmp2 tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp03.dbf' size 10m autoextend on autoallocate;
create temporary tablespace tmp2 tempfile 'E:ORACLEPRODUCT10.2.0ORADATADMT
tmp03.dbf' size 10m autoextend on autoallocate

*
第 1 行出现错误:
ORA-25139: CREATE TEMPORARY TABLESPACE 的选项无效


--设置tmp为default temporary tablespace，doc上提到的temporary

tablespace一旦创建，该表空间中就被创建了一个temp segment，而且该临时段一直存在并且可以重复利用，实例重启之后也不会被删除直到临时表空间被删除它也随之被删除，但是似乎不能很好的验证doc的说法，因为在创建tmp表空间之后，通过查询dba_segments并没有发现temp segment，而且又做了一个disk sort，依然没有在dba_segments中发现任何temp segment。oracle通过使用这种临时表空间所产生的temp segment在dba_segments中似乎体现不出来了？暂时解释不清，也是我的疑问！

SQL> alter database default temporary tablespace tmp;

数据库已更改。
SQL> select segment_name, segment_type, tablespace_name , extents , header_file,
header_block from dba_segments where segment_type like upper('%temp%');

未选定行

SQL> col operation_type format a10
SQL> select operation_type,policy,tempseg_size , tablespace from v$sql_workarea_
active;

OPERATION_ POLICY TEMPSEG_SIZE TABLESPACE
---------- ------------ ------------ ------------------------------
SORT (v2) AUTO 13631488 TMP

SQL>

使用这种临时表空间的好处是消除了dmt方式下通过不断的创建、回收temp segment而需要分配或者回收extent所产生的ST-equenue,同时也减少了不必要的redo。

3、v$tempseg_usage和v$sort_usage

介绍临时段不能不提v$tempseg_usage和v$sort_usage，这两个试图它们来自相同的数据源，描速的都是临时段的使用情况，v$tempseg_usage是oracle从9.2开始引入的一个试图用来取代v$sort_usage，从试图的命名理解oracle的用意是用v$tempseg_usage来表述临时段的使用更为准确一些，因为毕竟临时段不仅仅是用做disk sort的，所以用v$sort_usage来描速临时段的使用似乎不太准确。目前oracle保留v$sort_usage仅仅是为了向前兼容，所以大家应该更多的使用v$tempseg_usage。

通过v$tempseg_usage我们可以很容易的找到那些正经历着严重disk sort的session以及sql（准确的说是使用临时段严重的sql），因为v$tempseg_usage中包含了session以及session正执行的sql的信息。看看v$tempseg_usage的结构就知道包含的这几个字段通常来说对我们非常有用：


SESSION_ADDR

RAW(4)

SESSION_NUM
NUMBER

SQLADDR
RAW(4)

SQLHASH
NUMBER

SQL_ID
VARCHAR2(13)

做个简单的测试：

在plsql developer中执行一个大的排序操作：

select * from t order by object_id desc

在sqlplus窗口中查询v$tempseg_usage：

SQL> select session_addr,sqladdr,sqlhash,sql_id,tablespace,contents , segtype ,
segfile#
from v$tempseg_usage;

SESSION_ SQLADDR
SQLHASH SQL_ID
TABL CONTENTS
SEGTYPE
SEGFILE#
-------- -------- ---------- ------------- ---- --------- --------- ----------
6D187A0C 66A7747C 4279631818 8dj7zkmzjbzya TEMP TEMPORARY SORT
201

SQL>

这里通过 session_addr很容易找到排序的session：

SQL> select sid ,serial# , sql_address,sql_hash_value,sql_id , prev_sql_addr,pre
v_hash_value,prev_sql_id from v$session where saddr='6D187A0C';


SID
SERIAL# SQL_ADDR SQL_HASH_VALUE SQL_ID
PREV_SQL
---------- ---------- -------- -------------- ------------- --------
PREV_HASH_VALUE PREV_SQL_ID
--------------- -------------

34
6 66A898C0
4162285281 3n26jhzw1fvr1 66A7B57C

356401299 9m7787camwh4m

通过v$session中提供的sql_id(10g,10g之前可以通过SQL_ADDR 和SQL_HASH_VALUE 与v$sql做关联找到正在执行的sql)可以很容易的找到正在使用临时段的sql：

SQL> select sql_text from v$sql where sql_id='3n26jhzw1fvr1';

SQL_TEXT
--------------------------------------------------------------------------------

select * from t order by object_id desc

SQL>

遗憾的是通过v$tempseg_usage中的字段SQLADDR
SQLHASH SQL_ID往往不能找到正在使用临时段的sql，因为v$tempseg_usage中的这几个字段SQLADDR
SQLHASH SQL_ID

是来自v$session中的PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID，不知道oracle这样设计v$tempseg_usage的理由是什么？为什么不直接包括v$session中的SQLADDR
SQLHASH SQL_ID？

来追溯一下v$tempseg_usage的定义发现原来是V_$SORT_USAGE的同义词：

SQL> SELECT * FROM DICT WHERE TABLE_NAME IN ('V$SORT_USAGE','V$TEMPSEG_USAGE');

TABLE_NAME
COMMENTS
------------------------------ ------------------------------
V$SORT_USAGE
Synonym for V_$SORT_USAGE
V$TEMPSEG_USAGE
Synonym for V_$SORT_USAGE

SQL>

进一步追踪看看定义就知道v$tempseg_usage中这几个字段SQLADDR
SQLHASH SQL_ID原来是来自v$session中的prev_sql_addr, prev_hash_value, prev_sql，这就是我们通过v$tempseg_usage不能找到正在使用临时段的sql的真真原因。

GV$SORT_USAGE的定义：

select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#



4、临时表空间组

临时表空间组是10g引入的概念，目的是提高同一用户并发session对临时段的请求。我们知道一个临时表空间中只存在一个临时段，当一个session在使用临时段时，其他session再请求临时段时需要等到拥有该临时段的session使用完毕之后才能使用，造成这一问题的根源在于一个用户只能使用一个临时表空间。而临时表空间组的出现大大改善了同一用户并发session对临时段的争夺，因为一个临时表空间组可以包括多了临时表空间，而用户的default temporary tablespace又可以是临时表空间组。其实更直接的说就是临时表空间组的出现使用户能够使用多个临时表空间了。下面做个简单的测试：

SQL> create temporary tablespace temp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtemp01.dbf' size 10m autoextend on;

表空间已创建。
SQL> create temporary tablespace tmp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp01.dbf' size 10m reuse autoextend on;

表空间已创建。

SQL> select * from dba_tablespace_groups;

未选定行

SQL> alter tablespace temp tablespace group gp1;

表空间已更改。

SQL> alter tablespace tmp tablespace group gp1;

表空间已更改。

SQL> alter database default temporary tablespace gp1;

数据库已更改。

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP

SQL> alter user xys temporary tablespace gp1;

用户已更改。

SQL> connect xys/manager@dmt
已连接。
SQL> create table t as select * from dba_objects;

表已创建。

SQL> insert into t select * from t;

已创建11260行。

SQL> insert into t select * from t;

已创建22520行。

SQL> insert into t select * from t;

已创建45040行。

SQL> commit;

提交完成。

SQL> create table tt as select * from t;

表已创建。

分别打开两个plsql developer以用户xys登录对表t和tt同时进行排序，之后通过如下查询监视对临时表空间的使用情况，发现来自同一用户xys的不同session同时排序时使用了同一临时表空间组内的不同临时表空间，这样大大减少了之前同一用户只能使用一个临时表空间而产生的请求临时段的等待时间：
SQL> select operation_type ,sql_id , tablespace,tempseg_size,number_passes from
v$sql_workarea_active;

OPERATION_ SQL_ID TABLESPACE TEMPSEG_SIZE NUMBER_PASSES
---------- ------------- ---------- ------------ -------------
SORT (v2) 3n26jhzw1fvr1 TEMP 10485760 1
SORT (v2) 7z5ttxyv6c604 TMP 10485760 1

SQL> select sql_text from v$sql where sql_id in (select sql_id from v$sql_workar
ea_active);

SQL_TEXT
--------------------------------------------------------------------------------

select * from t order by object_name desc
select * from t order by object_id desc

另外需要说明的是临时表空间组本身不能创建，只要把存在的临时表空间加入按照命名规则命名的临时表空间组中就可以了，临时表空间组随之被创建，就象上面操作的那样：

SQL> alter tablespace temp tablespace group gp1;

表空间已更改。

SQL> alter tablespace tmp tablespace group gp1;

表空间已更改。

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP

gp1随着temp和tmp的加入被创建随着temp和tmp的脱离而被删除：

SQL> alter tablespace temp tablespace group '';

表空间已更改。

SQL> alter tablespace tmp tablespace group '';

表空间已更改。

SQL> select * from dba_tablespace_groups;

未选定行

SQL>

2007-12-28 10:26

1.jpg (13.62 KB)
 

2.jpg

 

2007-12-28 10:26

3.jpg (31.15 KB)