查看: 17451|回复: 9

[原创] Oracle 临时表空间总结

[复制链接]
论坛徽章:
0
跳转到指定楼层
1#
发表于 2011-9-9 15:20 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
这两天把临时表空间的知识点总结了一下。记下来分享一下。


首先从数据字典视图说起
v$dba_temp_files
v$sort_segment
v$tempseg_usage
dba_temp_free_space

上面这几个视图是我总结的在遇到Oracle临时空间问题时能用到的数据字典信息。
先看v$dba_temp_files
这个视图显示了数据库中临时文件的具体信息。要注意的有4列

SQL> desc dba_temp_files;
Name            Type          Nullable Default Comments                                            
--------------- ------------- -------- ------- ---------------------------------------------------
FILE_NAME       VARCHAR2(513) Y                Name of the database temp file                     
FILE_ID         NUMBER        Y                ID of the database temp file                        
TABLESPACE_NAME VARCHAR2(30)                   Name of the tablespace to which the file belongs   
BYTES           NUMBER        Y                Size of the file in bytes                           
BLOCKS          NUMBER        Y                Size of the file in ORACLE blocks                   
STATUS          VARCHAR2(7)   Y                File status: "AVAILABLE"                           
RELATIVE_FNO    NUMBER        Y                Tablespace-relative file number                     
AUTOEXTENSIBLE  VARCHAR2(3)   Y                Autoextensible indicator:  "YES" or "NO"            
MAXBYTES        NUMBER        Y                Maximum size of the file in bytes                  
MAXBLOCKS       NUMBER        Y                Maximum size of the file in ORACLE blocks           
INCREMENT_BY    NUMBER        Y                Default increment for autoextension                 
USER_BYTES      NUMBER        Y                Size of the useful portion of file in bytes         
USER_BLOCKS     NUMBER        Y                Size of the useful portion of file in ORACLE blocks

这四列中, BYTES , BLOCKS 显示的是临时文件有多少BYTE大小,包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此,我们可以知道临时文件中有一部分是被系统占用的,大概可以理解成文件头信息,在我的db中,这一部分大小是128个block,如下图所示,22784-22656=128.
SQL> select file_name, blocks , user_blocks from dba_temp_files;

FILE_NAME                                                                            BLOCKS          USER_BLOCKS
-------------------------------------------------------------------------------- -------------------- -----------
/u01/app/oracle/oradata/sales/temp01.dbf                                    22784                22656
/u01/app/oracle/oradata/sales/temp02.dbf                                    6400                  6272


再看v$sort_segment视图
这个视图从字面翻译虽然叫做排序段,但实际上不仅仅是排序,只要是消耗了临时表空间的操作,比如创建临时表等,都会在这个视图中显示。但如果一个操作没有占用临时表空间(比如开销很小的排序,在内存中完成,或者一个临时表在创建时通过tablespace子句指定到了一个永久表空间中),那么这个视图就不会被更新。
但有一个问题是,我们指定正常的segment是一个segment用来承载一个对象如表或者index,再或者一个对象的分区,如分区表的分区,但我们的sort_segment似乎不是这样的,一个sort_segment可以供多个对象或者说操作使用。
如图,一个sort_segment, 有两个extent被占用,同时有两个user在使用。因为这个测试是我自己进行的,所以我清楚的知道,两个user 一个在创建临时表,另一个在创建一个大表的索引。这根本就是两件不相干的事,但用的是同一个sort_segment.
不过这个问题不重要,因为我们通过这个视图主要知道多少个用户在使用临时表空间,已经使用了多少这就够了。
SQL> select tablespace_name ,TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ,CURRENT_USERS from v$sort_segment;

TABLESPACE_NAME    TOTAL_BLOCKS    USED_EXTENTS    USED_BLOCKS  CURRENT_USERS
-------------------------------  -----------------------      ----------------------     -----------                 ---------------------
TEMP                             22656                         2                               256                        2


再看v$tempseg_usage
这个视图可以详细的显示那些用户在使用临时空间,使用了多少,其session的id,SQL的ID等。但有一个很困惑的问题是其中的SQL_ID列显示的信息不准确。根据我的试验,它显示的应该是目标会话的当前SQL的SQL_ID,而非消耗临时表空间的SQL_ID。比如说你在一个会话中发出了一条SQL消耗了很大的临时空间,然后你又发出了一条不相干的SQL,这时如果我select * from v¥tempseg_usage,查到的SQL_ID对应的SQL是第二条,也就是最新的那条。
另外一条要注意的是,这个视图与 v$sort_usage是一样的,只不过后者改了个名字而已。大概oracle是这么考虑的,如果叫sort_usage会引起歧义,会让人以为这个视图针对的是排序的开销,但实际上不仅仅是排序,只要是对临时表空间的消耗都会体现在这个视图中。


dba_temp_free_space
SQL> desc dba_temp_free_space
Name                                          Null?            Type
----------------------------------------- --------            ----------------------------
TABLESPACE_NAME                NOT NULL  VARCHAR2(30)
TABLESPACE_SIZE                                        NUMBER
ALLOCATED_SPACE                                     NUMBER
FREE_SPACE                                                  NUMBER

该视图要注意的是ALLOCATED_SPACE 和 FREE_SPACE。前面两个比较简单,tablespace_name不用说了,而      tablespace_size  体现的是整个表空间的大小。如果把前面dba_temp_files 中BYTES列的值加起来,就是这个tablespace_size的值。 至于ALLOCATED_SPACE则要注意了,他体现的是分配出去的临时空间,包括分配出去但仍然可用的以及分配出去但不可用的。我们通过下面四个语句来理解这个视图。
SQL> select * from dba_temp_free_space;

TABLESPACE_NAME  TABLESPACE_SIZE             ALLOCATED_SPACE FREE_SPACE
------------------------------ -------------------------------------- ---------------------- ----------
TEMP                             239075328                            187695104             234881024

SQL> select file_name, blocks , user_blocks from dba_temp_files;

FILE_NAME                                                                            BLOCKS          USER_BLOCKS
-------------------------------------------------------------------------------- -------------------- -----------
/u01/app/oracle/oradata/sales/temp01.dbf                                    22784                22656
/u01/app/oracle/oradata/sales/temp02.dbf                                    6400                  6272

SQL> select TOTAL_BLOCKS,USED_EXTENTS,USED_BLOCKS ,CURRENT_USERS from v$sort_segment;

TOTAL_BLOCKS       USED_EXTENTS      USED_BLOCKS       CURRENT_USERS
-----------------------        ----------------------        -----------                     ---------------------
22656                             2                                256                              2

对于tablespace_name没什么好说的。

对于tablespace_size,我们理解为是临时表空间的总大小。验证一下。
通过dba_temp_files视图,我们知道了每个temp文件的数据块个数,22784和6400. 那么总的大小是数据块个数之和乘以数据块的大小(本系统中是8192)。 (22784 +   6400) * 8192 = 239075328。 验证成功。

对于ALLOCATED_SPACE我们理解为分配出去的空间。
验证一下。
v$sort_segment中查到total_block 为22656. 而我们知道temp文件每个文件都被系统占用了128个block(从dba_temp_file 视图的 block , user_block对比可以看出)。那么被占用的总的数据块个数应该是128 *2 (两个临时文件所以乘以2) 加上sort_segment中的22656.   128*2 + 22656=22912。 这是数据块的个数,如果要得到BYTE为单位的大小还要乘以8192(数据块尺寸), 结果正好为ALLOCATED_SPACE。验证成功。不过要注意的很重要的一点是,这里虽然是allocate的空间但并非是不可用的。其中包含了一部分仍然可用的。比如sort_segment这个视图中没分配的extent。

对于FREE_SPACE,也许你会认为直接用 total size 减去 allocate的space就可以了。但如果这样,那么这列存在的意义就不大了。实际上这一列显示的是 临时空间中可用的空间。可用的意思是如果有排序操作或者说临时表操作需要,我们仍然可以提供这么大的空间。这一部分不是 total 与 allocate的差,实际上它应该是 total 减去 allocate 再加上allocate中可用的那部分。
验证一下 ,total 减去 allocate 为239075328 - 187695104 = 51380224。而 allocate中可用部分的大小我们可以从v$sort_segment视图中看出。可以看到仅仅使用了256个block。那么剩余的空间应该是v$sort_segment的总大小减去256 ,为22656-256 = 22400.  这里只是数据块的个数,要得到大小还要乘以8192 得 183500800。用这个数字加上之前的51380224正好为  234881024。
论坛徽章:
1
ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26
2#
发表于 2011-9-9 15:30 | 只看该作者
谢谢分享!

使用道具 举报

回复
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
3#
发表于 2011-9-9 15:30 | 只看该作者

回复 #2 deepshrift 的帖子

oracle都忘记光了,复习一下吧!

使用道具 举报

回复
论坛徽章:
24
萤石
日期:2013-01-03 16:13:11海蓝宝石
日期:2013-04-23 17:06:35红宝石
日期:2013-05-17 17:32:35SQL大赛参与纪念
日期:2013-12-06 14:03:45马上有对象
日期:2014-02-18 16:44:08马上有车
日期:2014-02-27 15:22:45优秀写手
日期:2014-03-22 06:00:12马上有房
日期:2014-03-26 19:40:00巨蟹座
日期:2015-10-18 17:42:41
4#
发表于 2011-9-9 15:44 | 只看该作者
先顶再看 呵呵

使用道具 举报

回复
论坛徽章:
75
生肖徽章:猪
日期:2014-09-05 10:25:05指数菠菜纪念章
日期:2016-10-17 16:12:49生肖徽章:兔
日期:2015-02-05 16:49:02生肖徽章:鼠
日期:2015-02-05 16:49:02生肖徽章:鼠
日期:2015-02-05 16:49:02生肖徽章:马
日期:2015-02-05 16:49:02生肖徽章:羊
日期:2015-02-06 08:40:35生肖徽章:羊
日期:2015-02-06 08:40:34股神
日期:2015-01-05 08:27:16菲尼克斯太阳
日期:2014-12-29 13:11:51
5#
发表于 2011-9-9 15:50 | 只看该作者
LZ 还可以研究下把什么情况下会用到temp 空间。

使用道具 举报

回复
论坛徽章:
3
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:512014年新春福章
日期:2014-02-18 16:44:08马上有对象
日期:2014-02-18 16:44:08
6#
发表于 2011-9-23 16:22 | 只看该作者
谢谢LZ

使用道具 举报

回复
论坛徽章:
22
ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013数据库大会纪念章
日期:2015-03-18 10:16:212014数据库大会纪念章
日期:2015-03-18 10:16:21秀才
日期:2015-07-13 09:55:11金牛座
日期:2015-09-14 18:15:03天蝎座
日期:2015-12-03 09:15:302016猴年福章
日期:2016-02-18 09:31:30猴赛雷
日期:2016-02-18 09:33:422016猴年福章
日期:2016-02-23 09:58:34秀才
日期:2016-03-24 09:20:52
7#
发表于 2012-5-14 11:45 | 只看该作者
謝謝樓主,有個疑問,比如我是昨天發生temp過大的現象,我今天想抓相關的sql ,是否有視圖可以查詢呢 ?

使用道具 举报

回复
论坛徽章:
5
咸鸭蛋
日期:2012-12-09 22:25:322013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2013-03-05 15:00:38马自达
日期:2013-07-31 10:31:17问答徽章
日期:2013-11-20 09:59:28
8#
发表于 2013-4-12 23:05 | 只看该作者
慢慢看

使用道具 举报

回复
论坛徽章:
0
9#
发表于 2013-6-16 16:43 | 只看该作者
谢谢楼主,正好用到

使用道具 举报

回复
论坛徽章:
0
10#
发表于 2013-7-15 21:44 | 只看该作者
楼主好细心,正好有疑问这里,谢谢分享

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 未成年人举报专区 
京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表