个人简介：

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

   服务过的客户：

          中国电信

          中国移动

          中国联通

          中国电通

          国家电网

          四川达州商业银行

          湖南老百姓大药房

          山西省公安厅

          中国邮政

          北京302医院     

          河北廊坊新奥集团公司

 项目经验:

           中国电信3G项目AAA系统数据库部署及优化

           中国联通CRM数据库性能优化

           中国移动10086电商平台数据库部署及优化

           湖南老百姓大药房ERR数据库sql优化项目

           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

           北京高铁信号监控系统RAC数据库部署及优化

           河南宇通客车数据库性能优化

           中国电信电商平台核心采购模块表模型设计及优化

           中国邮政储蓄系统数据库性能优化及sql优化

           北京302医院数据库迁移实施

           河北廊坊新奥data guard部署及优化

           山西公安厅身份证审计数据库系统故障评估

联系方式：

          手机：18201115468

           qq   :   305076427

           qq微博: wisdomone1

           新浪微博：wisdomone9

           qq群：275813900    

           itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/

坛友问题
问题链接

http://www.itpub.net/thread-1884192-1-1.html

问题描述

1:一个3T的表，有分区，创建local索引需要多大的临时表空间，创建完后会释放吗？
2:如果我一个分区一个分区的创建索引，创建完一个索引后，会释放临时表空间吗？
3:还有普通表，创建索引后，把表删除后，会释放临时表空间吗 

问题分析：

    1,创建普通表索引时，需要临时表空间吗，如果使用临时表空间，创建完如何释放

    2,基于分区表创建索引分区，使用临时表空间吗，创建完如何释放

    3,如果基于普通表，创建索引后，把表删除，会释放临时表空间吗 

要回答上述的几个问题，先要搞明白临时表空间及索引几个重要概念：

     1,临时表空间存储什么内容
     2, 临时表空间存储内容保存的期限
     3,创建索引会使用临时表空间吗
     4,在什么条件下会使用临时表空间

     5,如果使用使用临时表空间，会使用多大的临时表空间，即创建索引与临时表空间之间的关联因素
   

好，我们依次梳理上述问题

经查阅oracle官方文档 Oracle? Database Administrator's Guide 10g Release 2 (10.2)

关于临时表空间相关的知识点如下：

            1, 临时表空间只会存储在某个会话期间产生的临时数据

            2,使用临时表空间可以极大增强多个排序操作的并行度，减少不必要的成本消耗，

               避免数据库空间管理的操作成本

            3,针对特定数据库实例或表空间的所有排序操作，它们共享使用同一个排序段即

               sort segment

            4, sort segment由首次进行排序操作的会话语句在临时表空间中创建，只有在数据

                库实例关闭才会释放sort segment

            5,sort segment不能多个事务同时使用，即sort segment每次只有有一个事务使用

            6,不能在临时表空间中显式创建对象

            7,使用v$sort_segment查看临时表空间的sort segment的分配及释放信息

               v$tempseg_usage查看使用上述临时表空间sort segment的用户会话信息

            8,如果创建一个临时表，它的记录是默认存储在缺省临时表空间

            所以，综上所述：

                      1,临时表空间只会存储会话期间产生的临时数据

                      2,如果会话期间需要排序操作，会通过在临时表空间分配sort segment实现排序操作

                      3,临时表空间中一旦分配了sort segment，只会在数据库关闭才会释放

                      4,可通过v$sort_segment及v$tempseg_usage获取与临时表空间相关的信息

     回到上面的问题

                            1,问题：临时表空间存储什么内容？

                               答：存储会话期间因为排序操作产生的临时数据

                            2,问题：临时表空间存储内容保存的期限

                               答：一直存储到数据库关闭

        细心的朋友，就马上会想到一个问题，如果数据库系统排序特别多，

  到时临时表空间不是撑爆了吗？关于这个问题，暂且不表，先集中精力回答上述提出的问题。

       我们再来看开头坛友的问题：

              创建索引会使用临时表空间吗？

   关于此问题，要分解成几个子问题。

                   1,创建索引数据库具体要作什么工作

                   2,创建索引是在pga中进行，还是在sga中进行

                   3,在什么情况下，创建索引在临时表空间中进行，这个因素或条件是什么

同样的思路，我们继续查阅oracle官方文档，

我们整理了重要的知识点：

         1,普通表的索引只会创建一个index segment

         2,分区表的分区索引，会创建多个index segment（关于分区表的分区索引，同理，下述不再测试）

         3,temporary segments的一些重要概念：

               1,运行SQL查询时，数据库经常需要一些临时性的表空间用于存储SQL在中间解析及执行

                  产生的结果

               2,一般而言，数据库采用temporary segment完成排序操作，当然，如果排序完全可在内存排序完成，

                   或者可通过其它方式完成比如索引，也可以不用temporary segment

               3,需要temporary segment的一些操作：

                      create index

                      select order by 

                      select distinct

                      select group by

                      select union

                      select intersect

                      select minus

                 4,如果连接列没有创建索引，或者关联子查询需要temporary segment.

                     比如，select distinct,group by,or order by ,这样数据库需要2个temporary segments

                 5,如果没有显式创建临时表空间，temporary segment会创建在system表空间,默认temporary segment是存储在临时表空间中

                 6,对于临时表及其索引，分配temporary segment机制是不同的

                 综上所述：

                       1,创建索引是需要排序操作的，排序操作的中间结果存储在temporary segment

                       2,temporary segment可能在内存中，也可能在系统表空间中，也可能在临时表空间

                       3,如果排序数据量很小，temporary segment在内存中，否则，如果创建了临时表空间，

                          则在临时表空间，最坏是存储在系统表空间

    又引申出新的问题：

                                  1,如果排序在内存操作，这个内存是指定在PGA，还是在SGA，如果是SGA，是SGA哪个子组件

                                  2,如果是在pga，是什么参数控制排序的效率，通过什么视图或字典可以监控排序操作的资源

                                     占用情况

                                  3,如果是在SGA中进行，又是如何进行排序工作，具体是什么参数控制呢

     经过查阅官方文档：

                   Oracle? Database Concepts 10g Release 2 (10.2),链接：

                   file:///Volumes/新加卷/官方手册/oracle10gr2官方文档/B19306_01/server.102/b14220/memory.htm#sthref1416

                       创建索引是要排序的，而排序操作是由所属会话发起的，即服务器会话驱动，所以这个排序会优先在PGA中进行

     列举关于PGA一些知识点： 

 1，pga构成图例

2， pga是私有的服务器会话属的进程专属内存区域

                      1,pga存储服务器会话的用户数据及控制数据

                      2,根据数据库连接方式不同pga中存储的内容也有所区别，但一般来说，存储内容如下：

                               （注：数据库连接方式即：专用连接或共享连接）

                                            1, private sql area

                                                        概念：

                                                                 包含SQL绑定变量及服务器会话运行时产生的内存结构的相关数据

                                                                 每个SQL所属会话都有一个private sql area

                                                                 多个服务器会话可能执行同一个SQL，所以多个private sql area会对应相同的shared sql area(位于共享池）

                                                                 每个private sql area会根据sql游标分成2个生命周期的区哉：

                                                                        persistent area:包括绑定变量的信息，此区域只要在游标关闭才会释放

                                                                        run-time area:SQL执行完就会马上释放

                                                                        每次SQL执行时会创建一个run-time area,比如：DML语句；而对于SQL查询，只要查询获取所有记录后

                                                                        才会关闭run-time area，或者中途停止了SQL查询

                                                                 private sql area到底存储在PGA还是SGA，还是取决于数据库连接方式，如果是专有数据库连接，其存储在pga

                                                                 否则存储在SGA中

                                             2,cursor和sql area

                                                        概念：数据库的开发工具OCI会显式打开一个CURSOR或句柄处理private sql area

                                                                  如果对于相同SQL隐式执行多次游标操作，可以考虑使用shared sql area

                                                                  服务器会话管理private sql area, 即private sql area分配及回收取决于应用的工具

                                                                  数据库到底可以同时打开多少个private sql area即游标由open_cursors决定，默认值为50

                                                                  private sql area在SQL游标打开情况下会一直存在，直至游标关闭

                                                                  建议开发者如果有些游标不再使用，马上关闭，这样可以释放persist area,减少不必要的内存占用

                                             3,session memory

                                                        概念：这部分PGA内存主要包含服务器会话的变量，即用户登陆信息及其它相关的会话信息

                                                                  对于共享服务器连接，session memory是共用的，而非私有

                                             4,sql work area

                                                         概念：对于一些大型的排序操作，private sql area中的run time area会分配其中一大部分内存用于这个极为消耗

                                                                   内存的操作

                                                                   这些消耗内存的操作类型如下：

                                                                            sort-based operators(order by,group by ,rollup,window function)

                                                                            hash join

                                                                            bitmap merge

                                                                            bitmap create

                                                        如果sort operator使用sql work area,也叫sort area,即排序操作在PGA中进行，

                                                        同理，hash join operator也使用sql work area（也叫hash area）基于SQL左输入数据集构建hash table

                                                        如果上述2个operator要处理的数据量远大于sql work area,数据库会把要处理的数据量分割为多个小数据片

                                                        这样就先在PGA中处理一些小数据片，其它的数据片先存放在临时表空间，以便以后处理，这样sql性能就大为下降

                                                        因为产生了大量的IO操作,所以尽量配置充足的pga，以防内存不足

                                                        sql work area大小是可以调整和优化的

                                                        （在早期版本，通过手工调节 sort_area_size,hash_area_size,bitmap_merge_area_size,

                                                           create_bitmap_area_size来控制，但难度很大，因为sql work area是有变化的，不好控制），现在简单了，

                                                           只要配置pga_aggregate_target即可

                                                       如果配置了pga_aggregate_target,会忽略＊_area_size参数，由数据库动态分配sql work area,

                                                       sql work area是由pga_aggregate_target-分配给每个服务器会话的内存，然后根据每个会话的内存需求，分配特定的

                                                       sql work area

                                                      与sql work area相关的参数,workarea_size_policy(值为：manual or auto),可以会话或系统动态调整

                                                      与pga相关的一些视图：

                                                            v$systat,v$sesstat,v$pgastat,v$sql_workarea,v$sql_workarea_active

                                                            如下视图的列可以显示PGA内存分配及使用的信息：

                                                                select pga_used_mem,pga_allocated_mem,pga_max_mem from v$process; 

3,如下是基于不同数据库连接方式，PGA内存组件的区别

Table 8-1 Differences in Memory Allocation Between Dedicated and Shared Servers

      综上所述，如果表大小不大，在pga_aggregate_target以下，即可在内存排序，否则，就要在临时表空间排序，占用临时表空间

问题测试：

1,获取临时表空间中sort segment的使用情况

v$sort_segment的官方注解

SQL> set linesize 300

SQL> col tablespace_name for a10

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,added_extents,extent_hits,freed_extents,free_requests,max_blocks,max_used_blocks,max_sort_blocks from v$sort_segment;

TABLESPACE CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_BLOCKS MAX_USED_BLOCKS MAX_SORT_BLOCKS

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

TEMP                   0        23680           0       23680             0           3             0             0      23680             384             128

2,获取使用临时表空间的会话信息

v$tempseg_usage

SQL> select username,session_num,sql_id,tablespace,contents,segtype,segfile#,segblk#,extents,blocks from  v$tempseg_usage;

no rows selected

3，连接测试用户

SQL> show user

USER is "TBS_11204"

SQL> select sid from v$mystat where rownum=1;

       SID

----------

       149 

4，创建测试表并插入数据

SQL> create table t_temp(a int,b int);

Table created.

SQL> insert into t_temp select level,level from dual connect by level

1000000 rows created.

SQL> commit;

Commit complete.

5，创建索引

SQL> create index idx_t_temp on t_temp(a);

Index created.

6，获取临时表空间中sort segment的使用情况

发现没有变化

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,added_extents,extent_hits,freed_extents,free_requests,max_blocks,max_used_blocks,max_sort_blocks from v$sort_segment;

TABLESPACE CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_BLOCKS MAX_USED_BLOCKS MAX_SORT_BLOCKS

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

TEMP                   0        23680           0       23680             0           3             0             0      23680             384             128

7，获取使用临时表空间的会话信息

没有变化

SQL> select username,session_num,sql_id,tablespace,contents,segtype,segfile#,segblk#,extents,blocks from  v$tempseg_usage;

no rows selected

小结：可见为表创建索引时，如果足以在PGA内存排序，不会占用临时表空间

8，删除上述测试索引

SQL> drop index idx_t_temp;

Index dropped.

9，查看pga_aggregate_target

SQL> show parameter pga_

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 500M

10，评估创建索引的大小

dbms_space.create_index_cost官方注解

11，收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_temp',cascade=>true);

PL/SQL procedure successfully completed.

12，评估创建索引的大小

SQL> r

  1  declare

  2  used_bytes number;

  3  alloc_bytes number;

  4  begin

  5  dbms_space.create_index_cost('create index idx_t_temp on t_temp(a)',used_bytes,alloc_bytes);

  6  dbms_output.put_line('index used bytes '||used_bytes||'   '||'index alloc bytes'||alloc_bytes);

  7* end;

index used bytes 5023905   index alloc bytes24117248

PL/SQL procedure successfully completed.

SQL> select 5023905/1024/1024 used_mb,24117248/1024/1024 alloc_mb from dual;

   USED_MB   ALLOC_MB

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

4.79116917         23

13，根据上述索引的大小，调节pga_aggregate_target

SQL> alter system set pga_aggregate_target=20m;

System altered.

SQL> show parameter pga_aggregate_target

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 20M

14，创建测试索引

SQL> create index idx_t_temp on t_temp(a);

Index created.

15，获取临时表空间中sort segment的使用情况

这下有变化了吧，已经从临时表空间分配了sort segment

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,added_extents,extent_hits,freed_extents,free_requests,max_blocks,max_used_blocks,max_sort_blocks from v$sort_segment;

TABLESPACE CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_BLOCKS MAX_USED_BLOCKS MAX_SORT_BLOCKS

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

TEMP                   4        23680         512       23168             0          31             0             0      23680            3584            3072

16，获取使用临时表空间的会话信息

SQL> select username,session_num,sql_id,tablespace,contents,segtype,segfile#,segblk#,extents,blocks from  v$tempseg_usage;

USERNAME                       SESSION_NUM SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS

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

TBS_11204                               29 9babjv8yq8ru3 TEMP                            TEMPORARY DATA             201      23561          1        128

TBS_11204                               29 9babjv8yq8ru3 TEMP                            TEMPORARY LOB_DATA         201      23433          1        128

TBS_11204                               29 9babjv8yq8ru3 TEMP                            TEMPORARY INDEX            201      23305          1        128

TBS_11204                               29 9babjv8yq8ru3 TEMP                            TEMPORARY LOB_DATA         201      23177          1        128

小结：如果排序操作不能全部在PGA内存中进行，会放在临时表空间进行

          索引创建完，临时表空间占用的sort segment也不会回收

17，重启数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  2097696 bytes

Variable Size            1526730208 bytes

Database Buffers          603979776 bytes

Redo Buffers               14675968 bytes

Database mounted.

Database opened.

18，查看v$sort_segment and v$tempseg_usage
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,added_extents,extent_hits,freed_extents,free_requests,max_blocks,max_used_blocks,max_sort_blocks from v$sort_segment;

TABLESPACE CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_BLOCKS MAX_USED_BLOCKS MAX_SORT_BLOCKS

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

TEMP                   0        23680           0       23680             0           0             0             0      23680               0               0

SQL> select username,session_num,sql_id,tablespace,contents,segtype,segfile#,segblk#,extents,blocks from  v$tempseg_usage;

no rows selected

重启数据库后，创建索引所占用的临时表空间才会回收

测试结论：

          不管是普通表，还是分区表，创建索引，如果PGA够大，不会占用临时表空间

          否则索引相当大，会占用临时表空间，且仅在重启数据库后方可回收临时表空间

          可以通过dbms_space.create_index_cost评估创建索引的大小