itpub坛友问题--基于普通表或分区表创建索引,会占用临时表空间及何时回收临时表空间

简介: 个人简介: 8年oracle从业经验,具备丰富的oracle技能,目前在国内北京某专业oracle服务公司从事高级技术顾问。        服务过的客户:           中国电信      ...
个人简介:
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),链接:
                       创建索引是要排序的,而排序操作是由所属会话发起的,即服务器会话驱动,所以这个排序会优先在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

Memory Area Dedicated Server Shared Server

Nature of session memory

Private

Shared

Location of the persistent area

PGA

SGA

Location of part of the runtime area for SELECT statements

PGA

PGA

Location of the runtime area for DML/DDL statements

PGA

PGA


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

问题测试:

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

v$sort_segment的官方注解

V$SORT_SEGMENT

This view contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.

Column Datatype Description
TABLESPACE_NAME VARCHAR2(31) Name of tablespace
SEGMENT_FILE NUMBER File number of the first extent
SEGMENT_BLOCK NUMBER Block number of the first extent
EXTENT_SIZE NUMBER Extent size
CURRENT_USERS NUMBER Number of active users of the segment
TOTAL_EXTENTS NUMBER Total number of extents in the segment
TOTAL_BLOCKS NUMBER Total number of blocks in the segment
USED_EXTENTS NUMBER Extents allocated to active sorts
USED_BLOCKS NUMBER Blocks allocated to active sorts
FREE_EXTENTS NUMBER Extents not allocated to any sort
FREE_BLOCKS NUMBER Blocks not allocated to any sort
ADDED_EXTENTS NUMBER Number of extent allocations
EXTENT_HITS NUMBER Number of times an unused extent was found in the pool
FREED_EXTENTS NUMBER Number of deallocated extents
FREE_REQUESTS NUMBER Number of requests to deallocate
MAX_SIZE NUMBER Maximum number of extents ever used
MAX_BLOCKS NUMBER Maximum number of blocks ever used
MAX_USED_SIZE NUMBER Maximum number of extents used by all sorts
MAX_USED_BLOCKS NUMBER Maximum number of blocks used by all sorts
MAX_SORT_SIZE NUMBER Maximum number of extents used by an individual sort
MAX_SORT_BLOCKS NUMBER Maximum number of blocks used by an individual sort
RELATIVE_FNO NUMBER Relative file number of the sort segment header 


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

V$TEMPSEG_USAGE

This view describes temporary segment usage.

Column Datatype Description
USERNAME VARCHAR2(30) User who requested temporary space
USER VARCHAR2(30) This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in USERNAME.
SESSION_ADDR RAW(4 | 8) Address of shared SQL cursor
SESSION_NUM NUMBER Serial number of session
SQLADDR RAW(4 | 8) Address of SQL statement
SQLHASH NUMBER Hash value of SQL statement
SQL_ID VARCHAR2(13) SQL identifier of SQL statement
TABLESPACE VARCHAR2(31) Tablespace in which space is allocated
CONTENTS VARCHAR2(9) Indicates whether tablespace is TEMPORARY or PERMANENT
SEGTYPE VARCHAR2(9) Type of sort segment:
  • SORT

  • HASH

  • DATA

  • INDEX

  • LOB_DATA

  • LOB_INDEX

SEGFILE# NUMBER File number of initial extent
SEGBLK# NUMBER Block number of the initial extent
EXTENTS NUMBER Extents allocated to the sort
BLOCKS NUMBER Extents in blocks allocated to the sort
SEGRFNO# NUMBER Relative file number of initial extent 


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官方注解

CREATE_INDEX_COST Procedure

This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Syntax

DBMS_SPACE.CREATE_INDEX_COST ( ddl IN VARCHAR2, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER, plan_table IN VARCHAR2 DEFAULT NULL);

Pragmas

pragma restrict_references(create_index_cost,WNDS);

Parameters

Table 98-4 CREATE_INDEX_COST Procedure Parameters

Parameter Description

ddl

The create index DDL statement

used_bytes

The number of bytes representing the actual index data

alloc_bytes

Size of the index when created in the tablespace

plan_table

Which plan table to use, default NULL


Usage Notes

  • The table on which the index is created must already exist.

  • The computation of the index size depends on statistics gathered on the segment.

  • It is imperative that the table must have been analyzed recently.

  • In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors. 


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评估创建索引的大小


目录
相关文章
|
数据库
【教程】truncate清空表数据,为什么数据库的空间还是和原来一样并没有释放|数据库释放表空间教程
【教程】truncate清空表数据,为什么数据库的空间还是和原来一样并没有释放|数据库释放表空间教程
|
存储 索引
清空表时优先使用truncate
清空表时优先使用truncate
|
存储 索引
十、清空表时优先使用truncate
十、清空表时优先使用truncate
119 0
|
Oracle 关系型数据库 数据库