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