不要让临时表空间影响数据库性能

一般Oracle数据库（Oracle Database）可以分为两部分，即实例（Instance）和数据库（Database）。

实例：是一个非固定的、基于内存的基本进程与内存结构。当服务器关闭后，实例也就不存在了。

数据库（Database）指的是固定的、基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等。

一般情况下，Oracle数据库都是一个数据库对应一个实例。

当然可以根据需要创建多个数据库和对应的多个实例。

下面我们开始讨论临时表空间的问题。

在Oracle数据库中进行排序、分组汇总、索引等到作时，会产生很多的临时数据。如有一张员工信息表，数据库中是安装记录建立的时间来保存的。如果用户查询时，使用Order BY排序语句指定按员工编号来排序，那么排序后产生的所有记录就是临时数据。对于这些临时数据，Oracle数据库是如何处理的呢?
　　通常情况下，Oracle数据库会先将这些临时数据存放到内存的PGA(程序全局区)内。在这个程序全局区中有一个叫做排序区的地方，专门用来存放这些因为排序操作而产生的临时数据。但是这个分区的容量是有限的。当这个分区的大小不足以容纳排序后所产生的记录时，数据库系统就会将临时数据存放到临时表空间中。这就是临时表空间的来历。看起来好像这个临时表空间是个临时工，对于数据库的影响不会有多大。其实大家这是误解这个临时表空间了。在用户进行数据库操作时，排序、分组汇总、索引这些作业是少不了，其会产生大量的临时数据。为此基本上每个数据库都需要用到临时表空间。而如果这个临时表空间设置不当的话，则会给数据库性能带来很大的负面影响。为此管理员在维护这个临时表空间的时候，不能够掉以轻心。要避免因为临时表空间设置不当影响数据库的性能。具体来说，主要需要注意如下几个方面的内容。

一、创建用户时要记得为用户创建临时表空间。

最好在创建用户时为用户指定临时表空间。如可以利用语句default temporary table space语句来为数据库设置默认的临时表空间。不过在Oracle数据库中这个不是强制的。但是笔者强烈建议这么做。因为如果没有为用户指定默认临时表空间的话，那么当这个用户因为排序等操作需要使用到临时表空间的话，数据库系统就会“自作聪明”的利用系统表空间SYSTEM来创建临时段。众所周知，这是一个系统表空间。由于在这个表空间中存放着系统运行相关的数据，一般的建议是用户的数据不能够保存在这个表空间中。那么如果将用户的临时表空间防止在这个系统表空间之内，会产生什么负面影响呢?
　　由于临时表空间中的数据是临时的。为此数据库系统需要频繁的分配和释放临时段。这些频繁的操作会在系统表空间中产生大量的存储碎片。当这些存储碎片比较多时，就会影响系统读取硬盘的效率，从而影响数据库的性能。其次系统表空间的大小往往是有限制的。此时临时段也来插一脚，就会占用系统表空间的大小。
　　为此数据库管理员需要注意一点，当没有为用户指定临时表空间时，用户排序等操作仍然需要用到临时段。此时数据库系统就会将临时段放入到系统表空间中。为此就会对数据库的性能产生不利的影响。所以笔者建议各位读者与数据库管理员，在创建用户的时候同时为用户指定一个默认的表空间，以减少临时段对系统表空间的占用。

二、合理设置PGA，减少临时表空间使用的几率。

当排序操作产生临时数据时，数据库并不是马上将其存储在临时表空间中。通常情况下，会先将这些临时数据存储在内存的PGA程序全局区内。只有当这个程序全局区无法容纳全部数据时，数据库系统才会启用临时表空间中的临时段来保存这些数据。但是众所周知，操作系统从内存中读取数据要比从硬盘中读取数据块几千倍。为此比较理想的情况是，这个程序全局区足够的大，可以容纳所有的临时数据。此时数据库系统就永远用不到临时表空间了。从而可以提高数据库的性能。
　　但是这毕竟只是一个理想。由于内存大小等多方面的限制，这个PGA程序区的大小往往是有限制的。所以在进行一些大型的排序操作时，这个临时表空间仍然少不了。现在数据库管理员可以做的就是合理设置这个PGA程序全局区的大小，尽量减少临时表空间使用的几率。如在实际工作中，数据库管理员可以根据需要来设置初始化参数SORT_AREA_SIZE参数。这个参数主要控制这个PGA程序全局区内排序区的大小。通常情况下，如果这个数据库系统主要用来查询并且需要大量的排序、分组汇总、索引等操作时，那么可以适当调整这个参数，来扩大PGA分区的大小。相反，如果这个系统主要用于更新操作，或者在这个数据库服务器上还部署由其他的应用程序，那么这个PGA分区就不能够占用太多的内存，以防止对其他应用程序产生不利的影响。所以说，数据库官员不能够一刀切，需要根据实际情况来调整。在必要的情况下，可以增加系统内存来增加PGA分区的大小，从而降低临时表空间的使用几率，以提高数据库的排序、分组汇总等操作的性能。
　　总之，如果临时段被频繁使用的话，由于内存与硬盘在性能上的差异，从而会降低数据库的性能。为此在平时工作中，数据库管理员还需要监控临时表空间的使用情况，以判断是否需要采取措施来减少临时表空间的使用来提高数据库的查询性能。为了实现这个目的，笔者建议数据库管理员可以查看 v$sort_segment这张动态性能视图。通过这张动态性能视图可以查看系统排序段(临时段的一种)的使用情况。另外通过动态性能视图 v$sort_usage还可以查询使用排序段的用户与会话信息。从而为数据库管理员优化数据库性能提供数据上的支持。对于这个排序段，笔者还要说明一点。对于排序段来说，同一个例程的所有SQL语句(如果需要排序操作的话)都将共享同一个排序段。并且排序段在第一次需要用到时被创建。排序完成后这个排序段不会被释放，只有在这个历程关闭后排序段才会被释放。为此以上两张视图要综合起来分析，才能够得到数据库管理员想要的信息。

三、要为临时表空间保留足够的硬盘空间。

其他表空间对应的数据文件，在其创建时就会被完全分配和初始化，即在其创建时就会被分配存储空间。但是临时表空间对应的临时文件则不同。如在 Linux操作系统中，临时表空间创建时系统是不会分配和初始化临时文件的。也就是说，不会为临时文件分配存储空间。只有临时数据出现需要用到临时文件的时候，系统才会在硬盘上分配一块地方用来保存临时文件。此时就可能会产生一个问题，即当需要用到临时文件系统为其分配空间的时候，才会先系统分区中没有足够的存储空间了。此时就会产生一些难以预料的后果。
　　为此对于这些临时文oracle认证更多详细资料件，数据库管理员最好能够预先为其保留足够的空间。如在Linux操作系统中，可以将其防止在一个独立的分区内，不允许其他应用程序使用。如此的话，就不用担心临时文件没有地方存储了。另外由于临时表空间主要用来存放一些排序用的临时文件。为此如果能够将这个临时表空间存放在性能比较好的分区中，还可以提高数据库系统读取临时表空间中数据的速度。另外由于系统需要频繁分配临时表空间中的数据，为此临时表空间所在的分区会出现比较多的碎片。此时如果将临时表空间存放在一个独立的分区内，那么数据库管理员就可以单独对这个分区进行碎片整理，从而提高这个分区的性能。所以无论出于什么原因，将临时表空间防止在一个独立的分区内，是一个不错的想法。不仅可以保证临时文件有存储的空间，而且还可以提高数据库的性能。
　　对于临时表空间最后需要说明的是，默认情况下这个临时表空间对各个用户都是共享的。也就是说每个连接到数据库的用户都可以使用默认的临时表空间。数据库管理员可以为其指定其他的临时表空间。一般来说，只需要一个临时表空间即可。

 四、临时表空间太大怎么办。

我们知道Oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要对查询的结果进行排序，如果没有猜错的话，在磁盘空间的（内存）的分配上，Oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB，如果还有增长，那么依此类推，临时表空间始终保持在一个最大的上限。像上文提到的恐怖现象经过分析可能是以下几个方面的原因造成的。 
    
1.   没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。 
  
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。 

3.对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。 

参照以上原因尝试解决下, 如果不行就清空临时表空间,不过还是建议先查找临时表空间增长的原因才是关键 

清控临时表空间 
1.startup   --启动数据库 

2.create   temporary   tablespace   TEMP2   TEMPFILE   '/home2/oracle/oradata/sysmon/temp02.dbf '   SIZE   512M   REUSE   AUTOEXTEND   ON   NEXT   640K   MAXSIZE   UNLIMITED;   --创建中转临时表空间 

3.alter   database   default   temporary   tablespace   temp2;--改变缺省临时表空间   为刚刚创建的新临时表空间temp2 

4.drop   tablespace   temp   including   contents   and   datafiles;--删除原来临时表空间 

5.create   temporary   tablespace   TEMP   TEMPFILE   '/home2/oracle/oradata/sysmon/temp01.dbf '   SIZE   512M   REUSE   AUTOEXTEND   ON   NEXT   640K   MAXSIZE   UNLIMITED;   --重新创建临时表空间 

6.alter   database   default   temporary   tablespace   temp;--重置缺省临时表空间为新建的temp表空间 

7.drop   tablespace   temp2   including   contents   and   datafiles;--删除中转用临时表空间 

8.alter   user   roll   temporary   tablespace   temp;   --重新指定用户表空间为重建的临时表空间

9. select * from dba_users; --查看用户的临时表空间

10. select username,default_tablespace,temporary_tablespace from dba_users where username = 'SCOTT'; 查看scott用户的默认表空间、临时表空间

11. select username,privilege,admin_option from user_sys_privs where username = 'SCOTT';查看scott用户的系统权限

12. select grantee,owner, table_name, t.grantor, t.privilege, t.grantable, t.hierarchy from dba_tab_privs t where t.grantee = 'SCOTT' ; 查看赋予scott用户的对象权限(注意：查询 dba_tab_privs就是查询赋予这个用户(角色)的对象权限，用下面这种方式查询获取不到结果：select * from user_tab_privs t，user_tab_privs这个视图查询的只是通过这个用户自己赋予出去的对象权限，而不是别人赋予他的，注意跟dba_tab_privs的差别 )

13. select t.grantee,t.granted_role, t.admin_option, t.default_role from dba_role_privs t where t.grantee = 'SCOTT' 或者 select * from user_role_privs t; --查看授予了scott的角色权限 

14. select t.table_name,t.tablespace_name from dba_all_tables t where t.owner = 'SCOTT';--查看scott用户使用了哪些表空间

15. select t.privilege from session_privs t; --查看当前用户拥有的权限

16. select * from role_role_privs t where t.role = 'DBA'; -- 查看角色(DBA)被赋予的角色权限

17. select * from role_tab_privs t1 where t1.role = 'DBA'; --查看角色(DBA)被赋予的对象权限

18. select * from  table_privileges t1 where t1.grantee = 'GDYXHD'; --查看赋给用户(GDYXHD)对对象操作的一些权限

19. select d.username, t.file_name, d.temporary_tablespace from DBA_TEMP_FILES t,dba_users d where t.tablespace_name = d.temporary_tablespace;--查看所有用户的临时表空间及相应的数据文件

20. alter database tempfile '/home/oracle/oradata/trade/temp01.dbf' resize 4096m;--修改临时表空间tmp的大小

21. alter database default temporary tablespace tmp;--将系统的默认临时表空间设为tmp

22. alter user aa temporary tablespace tmp;-- 修改用户aa的默认表空间为tmp

23. drop tablespace ex_aa including contents and datafiles; -- 删除用户aa以前的临时表空间ex_aa

24. 注意dba_data_files和dba_temp_files，二者是统一级别下的不同的感念，从字面上很容易引起误解“后者是前者的子集”

25. select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text  from v$sort_usage sort, v$session sess, v$sql sql  where sort.SESSION_ADDR = sess.SADDR  and sql.ADDRESS = sess.SQL_ADDRESS  order by blocks desc; -- 查看当前临时表空间使用空间大小与正在占用临时表空间的sql语句

26. select 'the ' || name || ' temp tablespaces ' || tablespace_name ||
       ' idle ' ||
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
       '% at ' || to_char(sysdate, 'yyyymmddhh24miss')
  from (select d.tablespace_name tablespace_name,
               nvl(sum(used_blocks), 0) tot_used_blocks,
               sum(blocks) total_blocks
          from v$sort_segment v, dba_temp_files d
         where d.tablespace_name = v.tablespace_name(+)
         group by d.tablespace_name) s,
       v$database; -- 查询临时表空间的空闲程度

27. 临时表空间的主要作用，索引create或rebuild， Order by 或 group by， Distinct 操作，Union 或 intersect 或 minus，Sort-merge joins，analyze，重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。即使重建了临时表空间，过一段时间后，临时表空间的使用率就达到99%，然后，表空间就开始增长，直到耗尽硬盘空间。

28. 当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。

29. ALTERTABLESPACE TEMP0 ADDTEMPFILE '/home/u01/app/oracle/oradata/ORCL/temp02.dbf' SIZE4G AUTOEXTENDON  NEXT128M;  --执行添加临时表空间的数据文件命令

30.  select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;-- 查看临时表空间文件是否自动扩展

31. alter database datafile 'E:xxxxxxESCALADE.ORA' autoextend on; -- 修改临时表空间文件为自动可扩展性