oracle临时表空间组,证明临时表空间组在并发session时的作用

本帖最后由 zcs0237 于 2013-7-16 20:26 编辑

a.感谢对本帖补充、建议、错误更正

b.为节省篇幅，部分输出结果做了精简

c.可按本文先后顺序复制文中代码进程调试

d.测试环境:Ora10.2.0.1.0(Ora11.2.0.1.0暂时还没测)

****************************证明不同session会调用临时组中不同的临时表空间********************************************

说明：oracle查询时优先使用内存排序，内存(sort_area_size)不足时才会调用临时表空间。

一、改为手动管理

SQL> show parameter workarea_size_policy

NAME                   TYPE        VALUE

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

workarea_size_policy   string      AUTO

SQL> alter system set workarea_size_policy=manual;

System altered.

二、减小sort_area_size

SQL> show parameter sort_area_size

NAME               TYPE        VALUE

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

sort_area_size     integer     65536

SQL> alter system set sort_area_size=20480 scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> show parameter sort_area_size

NAME               TYPE        VALUE

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

sort_area_size     integer     20480

三、将临时表空间组指定为用户级默认临时表空间

临时表空间组中可以包含无数个临时表空间，防止一个临时表空间出现空间不足的问题。

同一个用户同时连接多个session时可以使用临时表空间组中不同的临时表空间，提高系统的性能。

SQL> create temporary tablespace tmp1 tempfile '/tmp/tmp1.dbf' size 33M autoextend on tablespace group tmpgrp1;        //建表空间同时加入tgroup1

SQL> create temporary tablespace tmp2 tempfile '/tmp/tmp2.dbf' size 33m autoextend on tablespace group tmpgrp1;        //reuse=文件若存在则重用

SQL> select * from dba_tablespace_groups;      //列出临时表空间组及表空间

GROUP_NAME                     TABLESPACE_NAME

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

TGROUP1                        TEMP1

TGROUP1                        TEMP2

SQL> create user zcs2 identified by zcs2;   设置用户级默认表空间

SQL> alter user zcs2 temporary tablespace tmpgrp1;

SQL> grant connect,resource to zcs2;

SQL> select username,temporary_tablespace from dba_users where username = 'ZCS2';

USERNAME TEMPORARY_TABLESPACE

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

ZCS      TGROUP2

SQL> select username, session_num, tablespace from v$sort_usage;

no rows selected                   //现在没有磁盘排序

四、验证同一用户不同session调用不同临时表空间

$ sqlplus zcs2/zcs2       //secureCRT会话2

SQL> select a.table_name, b.table_name from dict A, dict B order by a.table_name;

$ sqlplus zcs2/zcs2       //secureCRT会话3

SQL> select a.table_name, b.table_name from dict A, dict B order by a.table_name;

$ sqlplus zcs2/zcs2       //secureCRT会话4

SQL> select a.table_name, b.table_name from dict A, dict B order by a.table_name;

$ sqlplus /   as sysdba  //secureCRT会话1

SQL> select a.table_name, b.table_name from dict A, dict B order by a.table_name;

//证明结果：zcs3的3个session把临时表空间组中的两个会话都调用了

SQL>select username, session_num, tablespace from v$sort_usage;

USERNAME      SESSION_NUM TABLESPACE

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

ZCS2            14            TMP2

ZCS2            13            TMP1

ZCS2            6             TMP2

***************************************************************************************************

补充：退出临时表空间组

SQL> select * from dba_tablespace_groups;      //列出临时表空间组及表空间

GROUP_NAME                     TABLESPACE_NAME

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

TGROUP1                        TEMP1

TGROUP1                        TEMP2

SQL> drop tablespace tmp2 INCLUDING CONTENTS and datafiles;

Tablespace dropped.

SQL> alter tablespace tmp1 tablespace group '';

Tablespace dropped.         //将组中的临时表空间从组中移出

SQL> select * from dba_tablespace_groups;

no rows selected           //组员没组自动没

SQL> drop tablespace tmp1 INCLUDING CONTENTS and datafiles;

Tablespace dropped.

***************************************************************************************************

最近itpub发帖：

无GUI智能部署oracle脚本及视频http://www.itpub.net/thread-1778735-1-1.html