一、ORACLE数据库中涉及到排序操作的一些行为：
排序是非常消耗系统资源的操作，所以应该尽量避免或者减少.以下行为会涉及到排序操作： 创建索引 GROUP BY或者ORDER BY操作 DISTINCT操作 UNION,INTERSECT,MINUS操作 Sort-Merge join操作(如果没有索引的情况下，等连接加上排序操作通常会采用该连接方式) ANALYZE操作 CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, CREATE TABLE AS SELECT二、查询临时表空间使用率
临时表空间和回滚表空间一样，分配的extents都是可以重复使用的，他们的使用率查询不能和普通表空间一样查询dba_free_space等视图，对于临时表空间的使用率 我们可以借助以下视图和方法进行查询： V$SORT_USAG V$SORT_SEGMENT V$TEMP_SPACE_HEADER V$TEMP_EXTENT_POOL SQL> select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char (sysdate,'yyyymmddhh24miss') 2 from 3 (select d.tablespace_name tablespace_name, 4 nvl(sum(used_blocks),0) tot_used_blocks, 5 sum(blocks) total_blocks 6 from v$sort_segment v ,dba_temp_files d 7 where d.tablespace_name=v.tablespace_name(+) 8 group by d.tablespace_name) s, v$database; 'THE'||NAME||'TEMPTABLESPACES'||TABLESPACE_NAME||'IDLE'||ROUND(100-(S.TOT_USED_BLOCKS/S.TOTAL_BLOCKS)*100,3)||'%AT'||TO_CHAR(S ------------------------------------------------------------------------------------------------------------------------------ the ORCL temp tablespaces TEMP idle 100% at 20101210194730 the ORCL temp tablespaces TEMP2 idle 100% at 20101210194730 the ORCL temp tablespaces TEMP3 idle 88.353% at 20101210194730 SQL> col DatafileName for a30 SQL> set lin 150 SQL> Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB", 2 round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" , 3 d.file_name "DatafileName", 4 round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB", 5 round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB", 6 round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) "Free KB", 7 round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB", 8 0 "Fragmentation Index" 9 from V$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, V$TEMP_EXTENT_POOL p 10 where f.tablespace_name(+) = d.tablespace_name 11 and f.file_id(+) = d.file_id 12 and p.file_id(+) = d.file_id; total MB Free MB DatafileName Used MB total KB Free KB Used KB Fragmentation Index ---------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ------------------- 20 20 D:\ORACLE\PRODUCT\10.2.0\ORADA 0 20480 20480 0 0 TA\ORCL\TEMP01.DBF 249 220 D:\ORACLE\PRODUCT\10.2.0\ORADA 29 254976 225280 29696 0 TA\ORCL\TEMP03.DBF 10 10 D:\ORACLE\PRODUCT\10.2.0\ORADA 0 10240 10240 0 0 TA\ORCL\TEMP02.DBF SQL> COL SQL_TEXT FOR A30 SQL> SELECT SESS.SID, SESS.SERIAL#, SEGTYPE, BLOCKS*8/1024 "MB" ,SESS.SQL_ID ,SQL_TEXT 2 FROM V$SORT_USAGE SORT, V$SESSION SESS ,V$SQL SQL 3 WHERE SORT.SESSION_ADDR = SESS.SADDR 4 AND SQL.SQL_ID = SESS.SQL_ID 5 ORDER BY BLOCKS DESC; SID SERIAL# SEGTYPE MB SQL_ID SQL_TEXT ---------- ---------- --------- ---------- ------------- ------------------------------ 3294 227 SORT 29 6r2k8sy8mtk25 select a.object_name from (sel ect * from dba_objects where r ownum<1000) a,(select * from d ba_objects where rownum<1000) b order by a.object_name SQL> COL USENAME FOR A10 SQL> COL OSUSER FOR A10 SQL> COL TABLESPACE FOR A15 SQL> COL SQL_TEXT FOR A30 SQL> SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT 2 FROM V$SESSION A, V$TEMPSEG_USAGE B, V$SQLAREA C 3 WHERE A.SADDR = B.SESSION_ADDR 4 AND C.ADDRESS= A.SQL_ADDRESS 5 AND C.HASH_VALUE = A.SQL_HASH_VALUE 6 ORDER BY B.TABLESPACE, B.BLOCKS; USERNAME SID SERIAL# OSUSER TABLESPACE BLOCKS SQL_TEXT ------------------------------ ---------- ---------- ---------- --------------- ---------- ------------------------------ TEST 3294 227 weifzhou-c TEMP3 3712 select a.object_name from (sel n\weifzhou ect * from dba_objects where r ownum<1000) a,(select * from d ba_objects where rownum<1000) b order by a.object_name三、ORA-1652触发条件及原因分析：
如果临时表空间使用的块达到临时表空间数据文件的总的块数就会导致ORA-1652错误，大部分ORA-1652错误都是该原因导致(这里不考虑各种bug的因素)。 Check the status of the sort segment utilization : select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following: Note 317441.1 - How Do You Find Who And What SQL Is Using Temp Segments 有时候我们可能会发现ORA-1652错误后面跟随的表空间名字不是某个临时表空间，而是一个permanent表空间，这其实是个正常现象，创建、删除表或者索引等对象的时候 oracle会自动为该对象先创建临时段，创建、删除对象完成后再覆盖该临时段，如果这个时候创建、删除对象所在的表空间不足，就会导致ORA-1652错误(而不是创建、删除 对象错误)，对应的表空间为permanent表空间，如果出现类似错误，确保数据库没有设置10061或者10269等event，这些event都会阻止SMON进程回收临时空间。 In some cases, you may find that the ORA-1652 is not reported for a temporary tablespace, but a permanent one. This is not an abnormal behaviour and it can occur for example while creating or dropping objects like tables and indexes in permanent tablespaces. Reference : Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s In such cases the following note will be of use : Note 100492.1 - ORA-01652: Estimate Space Needed to CREATE INDEX If the tablespace in which the TEMPORARY segment resides is of type PERMANENT, also check that the following events are not set in the initialization parameter file: event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10" If they are set, unset them and restart database.These two events prevent SMON from cleaning up. 说到这两个event，顺便提一下，如果遇到SMON清理临时段的错误而无法OPEN数据库的时候可以借助10061事件禁止SMON回收临时段来OPEN数据库，OPEN之后再做相应 的后续处理，可以考虑重建表空间或者利用包dbms_space_admin进行段修复，这些内容在这里不做过多讨论。 错误大致如下： SMON encountered 28 out of maximum 100 non-fatal internal errors. 参考文档： ORA-600 [5463] While SMON is Doing Temporary Segment Drop [ID 422039.1] ORA-600 [kcbz_check_objd_typ_3] Error from SMON Process Repeatedly [ID 577579.1] ORA-1652错误出现了，我们就需要找出到底是什么操作占用了大量的临时表空间，一般我们可以通过在第二部分中提供的查询方法去查询正在占用大量临时段的操作，但是 事实上当我们发现警告日志中的ORA-1652错误的时候，排序操作已经完成并且释放了空间，这种情况下我们可以借助以下几种方法来查找相应的SQL： 1、通过以下两个个视图来捕捉(局限：如果对应SQL被ageout就查不到了)： V$SQL_WORKAREA V$SQL_PLAN_STATISTICS_ALL SQL> SELECT SQL_ID,LAST_TEMPSEG_SIZE FROM V$SQL_WORKAREA WHERE SQL_ID='6r2k8sy8mtk25'; SQL_ID LAST_TEMPSEG_SIZE ------------- ----------------- 6r2k8sy8mtk25 30408704 2、通过V$SQL相关几个视图来捕捉(局限：直接路径读写还包含排序之外的其他操作)： SQL> SELECT SQL_ID,DIRECT_WRITES/DECODE(EXECUTIONS,0,1,EXECUTIONS) 2 FROM V$SQL WHERE SQL_ID='6r2k8sy8mtk25'; SQL_ID DIRECT_WRITES/DECODE(EXECUTIONS,0,1,EXECUTIONS) ------------- ----------------------------------------------- 6r2k8sy8mtk25 14451 这些视图同样存在SQL被ageout的问题，但是可以借助awr历史数据进行查询：DBA_HIST_SQLSTAT、DBA_HIST_SQLTEXT等视图。 3、定时job： 利用上边一节中的查询语句结合定时job来捕捉。 4、1652 errorstack(局限：捕捉到的语句可能不一定是消耗临时表空间最高的SQL语句，只是它触发了ORA-1652错误而已)： ALTER SYSTEM SET EVENTS '1652 trace name errorstack level 3'; ALTER SYSTEM SET EVENT = '1652 trace name errorstack level 3' SCOPE = SPFILE; ALTER SYSTEM SET EVENTS '1652 trace name errorstack off'; ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*';四、其他相关内容： 对于一些OLAP系统，可能仅有少数的几个人做分析统计，由于大量的临时表空间读写导致性能低下，数据库服务器配置非常高，但是不能被合理利用，这个时候我们可以 通过一些方法来让ORACLE的进程可以使用比较大的物理内存减少磁盘操作，这里提供两种方法： 1、设置比较大的pga_aggregate_target和"_pga_max_size" 2、采用工作区手工管理workarea_size_policy=manual，结合比较大的sort_area_size、hash_area_size
对于RAC数据库中，为了减少'SS enqueue'和'DFS lock handle'竞争，建议临时文件数至少要大于等于节点数，可以考虑创建临时表空间组。参考文档： Doc ID: 102339.1 Temporary Segments: What Happens When a Sort Occurs Doc ID: 177334.1 Overview of Temporary Segments Doc ID: 289894.1 Queries to monitor Temporary Tablespace usage Doc ID: 793380.1 ORA-1652 Error Troubleshooting Doc ID: 19047.1 OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s Doc ID: 317441.1 How Do You Find Who And What SQL Is Using Temp Segments Doc ID: 364417.1 How Can Temporary Segment Usage Be Monitored Over Time? Doc ID: 465840.1 Configuring Temporary Tablespaces for RAC Databases for Optimal Performance Doc ID: 459036.1 Temporary tablespace SS Contention In RAC
