确定临时表空间的使用情况

最新推荐文章于 2024-05-17 14:26:01 发布

原创最新推荐文章于 2024-05-17 14:26:01 发布 · 3.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #logging #file #allocation #session

DB出现了如下问题

ORA-01114: IO error writing block to file 255 (block # 1047029)

ORA-27063: skgfospo: number of bytes read/written is incorrect

IBM AIX RISC System/6000 Error: 28: No space left on device

Additional information: -1

FILE 255不存在

查ORACLE的错误解释如下

orasbp@svodbp01:$ oerr ora 27063

27063, 00000, "skgfospo: number of bytes read/written is incorrect"

// *Cause: the number of bytes read/written as returned by aiowait

// does not match the original number, additional information

// indicates both these numbers

// *Action: check errno

orasbp@svodbp01:$ oerr ora 01114

01114, 00000, "IO error writing block to file %s (block # %s)"

// *Cause: The device on which the file resides is probably offline. If the

// file is a temporary file, then it is also possible that the device

// has run out of space. This could happen because disk space of

// temporary files is not necessarily allocated at file creation time.

// *Action: Restore access to the device or remove unnecessary files to free

// up space.

当时TEMPORARY是16G，AUTOEXTEND ON

查找原因,首先看是否真正用到这么多TEMPORARY空间

v$sort_usage将会告诉我们是谁在做什么

1* select username,session_addr,sqladdr,sqlhash from v$sort_usage

SQL> /

no rows selected

无人使用,再看v$sort_segment可以看temp的比较详细的使用情况,证实下

SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;

TABLESPACE_NAME CURRENT_USERS

-------------------------------------------------------------- -------------

TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

------------ ----------- -----------

TEMP 0

1059584 0 1059584

我的DB_BLOCK_SIZE 是16K，所以TEMP总共分配了16G，看到TEMPORARY SEGMENTS全部FREE，TEMPORARY SEGMENTS就像ROLLBACK一样，虽然占用了16G的空间，但是并没有全部使用，然而系统却提示了空间已满。

也可以多表连接来找出用TEMPORARY 的SQL和SESSION信息

SQL> select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

no rows selected

再看其他相关信息

SQL> show parameter polic

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

workarea_size_policy string

AUTO

SQL> show parameter pga

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

pga_aggregate_target big integer

1572864000

SQL> select * from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE

------------------------------------------------------------ ----------

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN

-------------- ----------- ----------- ----------- ------------ ----------

STATUS CONTENTS LOGGING FORCE_

------------------ ------------------ ------------------ ------

EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGE SEGMENT_SPAC DEF_TAB_COMPRESS

-------------------- ------------------ ------ ------------ ----------------

SYSTEM 16384

32768 16384 1 1017 50 0

ONLINE PERMANENT LOGGING NO

DICTIONARY USER NO MANUAL DISABLED

UNDO01 16384

65536 1 2147483645 65536

ONLINE UNDO LOGGING NO

LOCAL SYSTEM NO MANUAL DISABLED

TEMP 16384

4194304 4194304 1 0 4194304

ONLINE TEMPORARY NOLOGGING NO

LOCAL UNIFORM NO MANUAL DISABLED

DATA01 16384

262144 262144 1 2147483645 0 262144

ONLINE PERMANENT LOGGING NO

LOCAL UNIFORM NO AUTO DISABLED

可以得出结论, 并不是由于真正TEMPORARY 空间不足或有大应用在消耗TEMPORARY , 而是在非常空闲的情况下产生的问题.

TEMPORARY 的问题要从4个方面来考虑解决

1，考虑SQL和INDEX的合理性，如果SQL或INDEX设计的不好会导致大量使用TEMPORARY SEGMENT。

2， coalescing the temp tablespace

3， increasing the sort_area_size parameter

4， increasing the temp tablespace or recreate temp tablespace

针对以上解决方向的分析

v 第一个解决方向需要找到消耗TEMPORARY大的SQL来针对解决,但显然, 本次遇到的问题是在TEMPORARY非常空闲的情况下发生的, 不适合次方法.

v 第二个解决方向, 需要设置

1, SQL>alter tablespace temp increase 1;

等SMON 自动coalesce后再
SQL>alter tablespace temp increase 0

2, SQL>alter tablespace temp coalesce;

需要考虑SORT AREA SIZE和PGA的设置,来减少TEMPORARY 的使用,

SQL> alter tablespace temp coalesce;

alter tablespace temp coalesce

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

因为是LOCAL MANAGEMENT所以导致coalesce语法无效。

3, 可以把TEMP先改成PERMANENT然后再使其变成TEMPORARY。因为PERMANENT会由SMON来回收空间。但是这么做得时候系统会变得非常慢并运行一段时间，所以是不建议使用的。PERMANENT TABLESPACE会比TEMPORARY TABLESPACE更好地自动释放空间，但是速度会比TEMPORARY慢许多。

TEMPORARY TABLESPACE也是由EXTENTS组成，但是为了避免ALLOCATE和FREE的消耗，ORACLE会一次性完成分配，所以，ORACLE不会DEALLOCATE TEMPORARY TABLESPACE

但是TEMPORARY转变为PERMANENT与版本相关，只有create tablespace temp .... temporary才可以被ALTER为PERMANENT，而create TEMPORARY TABLESPACE不行。(见dba_tablespaces中的CONTENTS字段)

这些方法对LOCALLY MANAGEMENT无效，但是我还是建议TEMPORARY是LOCALLY MANAGED并且UNIFORM SIZE。LMT在重用空间方面效率很高而且基本无碎片。

各版本语句的比较

<Oracle 7.3

CREATE TABLESPACE temp DATAFILE ...; -

Oracle 7.3 & 8.0

CREATE TABLESPACE temp DATAFILE ... TEMPORARY;

>Oracle 8i and above

CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;

因为本系统是ORACLE 9,, 所以也不能采用这种方式.

v 第三个解决方向是increasing the sort_area_size parameter, 本次遇到的问题是在TEMPORARY非常空闲的情况下发生的, 不适合次方法.

v 第四个解决方向increasing the temp tablespace or recreate temp tablespace

如 SQL>alter tablespace temp tempfile '......' offline drop;
SQL>alter tablespace temp add tempfile '......';

试试改小如何?

SQL> alter database tempfile '/oradata/SBP/sandata2/TEMP.DBF' resize 10000M

2 /

alter database tempfile '/oradata/SBP/sandata2/TEMP.DBF' resize 10000M

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

当这四个方向都解决不了问题的时候, 我想当AUTOEXTEND ON的时候ORACLE宁愿新分配EXTEND也不去使用旧的EXTEND。因为新分配EXTEND效率更高，但是这样也导致EXTEND的时候没有边界随意扩展知道OS空间满了为止，所以，TEMPORARY和UNDO TABLESPACE最好设置一个比较大的值并且取消AUTOEXTEND。而现在TEMPORARY 16G我认为是比较符合本数据库的一个大小 , 所以设置AUTOEXTEND OFF并同时增加磁盘空间为20G (并不是给TEMPORARY使用). 此时再询问开发人员, 问题已不再发生.

SQL> select * from dba_temp_files;

FILE_NAME FILE_ID TABLESPACE_NAME

BYTES BLOCKS STATUS RELATIVE_FNO AUTOEX MAXBYTES

MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

---------- ------------ ---------- -----------

/oradata/SBP/sandata2/TEMP.DBF 1 TEMP

1.7364E+10 1059840 AVAILABLE 1 NO 0

0 0 1.7360E+10 1059584

总结: .

当登陆数据库后执行一个排序操作，在cursor open的时候就拥有了temporary extents，当close cursor的时候此temporary就会标识为可重用，如果永远没有CLOSE则次extents被永远处于使用状态。

TEMPORARY SEGMENTS就像ROLLBACK一样，虽然占用了16G的空间，但是并没有全部使用，然而系统却提示了空间已满。TEMPORARY TABLESPACE也是由EXTENTS组成，但是为了避免ALLOCATE和FREE的消耗，ORACLE会一次性完成分配，这样做可以避免RECURSIVE SQL管理TEMP SEGMENTS的消耗，这也是为什么TEMPORARY 比PERMANENT建立的时候与使用的时候都更快的原因。在取得性能的优势的同时也失去了管理方面的特性比如COALESCE与REUSE。