Too many fragmentation in LMT?

这周和同事讨论技术问题时,他告诉我客户的一套11.1.0.6的数据库中某个本地管理表空间上存在大量的Extents Fragment区间碎片,这些连续的Extents没有正常合并为一个大的Extent,他怀疑这是由于11.1.0.6上的bug造成了LMT上存在大量碎片。

同事判断该表空间上有碎片的依据是从dba_free_space视图中查询到大量连续的Free Extents:

SQL> select tablespace_name,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name='FRAGMENT';

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
FRAGMENT                       LOCAL      SYSTEM

SQL> select block_id,blocks from dba_free_space where tablespace_name='FRAGMENT' and rownum<10;
BLOCK_ID     BLOCKS
---------- ----------
40009     222136
25          8
9           8
17          8
33          8
41          8
49          8
57          8
65          8
.............. 

SQL> select count(*),blocks from dba_free_space where tablespace_name='FRAGMENT' and blocks=8 group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
      5000          8

以上可以看到FRAGMENT表空间使用autoallocate的Local Extent Management,的确存在大量的连续Extents没有合并。在DMT即字典管理表空间模式下需要SMON进程定期维护FET$基表将tablespace上的连续空闲Extents合并为更大的一个Extents。而在LMT模式下因为采用数据文件头上(datafile header 3-8 blocks in 10g)的位图管理区间,所以无需某个后台进程特意去合并区间。

为什么LMT下连续空闲Extents没有合并而造成碎片呢?因为这套库采用11gr1较不稳定的11.1.0.6版本,所以把问题归咎为某个bug似乎可以讲得通。一开始我较为认同同事的bug论,且和同事一起查询了Metalink上11gr1上一些已知的bug,但并没有发现症状匹配的bug note。

这让我反思这个问题,过早的将cause定位到bug过于主观了,并不是所有我们预期外的情况(unexpected)都属于bug。

实际上dba_free_space所显示的信息可能并不”真实”,这种幻象往往由10g以后出现的flashback table特性引起:

SQL> select text from dba_views where view_name='DBA_FREE_SPACE';

TEXT
--------------------------------------------------------------------------------
======DMT REAL FREE EXTENTS=============

select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0

union all

======LMT REAL FREE EXTENTS=============

select /*+ ordered use_nl(f) use_nl(fi) */
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

======LMT RECYCLEBIN FREE EXTENTS=============

select /*+ ordered use_nl(u) use_nl(fi) */
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

======DMT RECYCLEBIN FREE EXTENTS=============

select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

以上我们通过解析10g中的dba_free_space视图可以了解到该视图所显示的Free Extents由以下四个部分组成:

  1. LMT表空间上真正空闲的Extents
  2. DMT表空间上真正空闲的Extents
  3. LMT表空间上被RECYCLEBIN中对象占用的Extents
  4. DMT表空间上被RECYCLEBIN中对象占用的Extents

而在10g以前的版本中因为没有recyclebin特性的”干扰”,所以dba_free_space所显示的Free Extents由前2个部分组成,因此我们可以在10g中创建一个兼容视图以实现对真正空闲空间的查询:

create view dba_free_space_pre10g as
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       f.block# BLOCK_ID,
       f.length * ts.blocksize BYTES,
       f.length BLOCKS,
       f.file# RELATIVE_FNO
  from sys.ts$ ts, sys.fet$ f, sys.file$ fi
 where ts.ts# = f.ts#
   and f.ts# = fi.ts#
   and f.file# = fi.relfile#
   and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 f.ktfbfebno BLOCK_ID,
 f.ktfbfeblks * ts.blocksize BYTES,
 f.ktfbfeblks BLOCKS,
 f.ktfbfefno RELATIVE_FNO
  from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
 where ts.ts# = f.ktfbfetsn
   and f.ktfbfetsn = fi.ts#
   and f.ktfbfefno = fi.relfile#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
 /

create view dba_free_space_recyclebin as
select /*+ ordered use_nl(u) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 u.ktfbuebno BLOCK_ID,
 u.ktfbueblks * ts.blocksize BYTES,
 u.ktfbueblks BLOCKS,
 u.ktfbuefno RELATIVE_FNO
  from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
 where ts.ts# = rb.ts#
   and rb.ts# = fi.ts#
   and u.ktfbuefno = fi.relfile#
   and u.ktfbuesegtsn = rb.ts#
   and u.ktfbuesegfno = rb.file#
   and u.ktfbuesegbno = rb.block#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
union all
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       u.block# BLOCK_ID,
       u.length * ts.blocksize BYTES,
       u.length BLOCKS,
       u.file# RELATIVE_FNO
  from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
 where ts.ts# = u.ts#
   and u.ts# = fi.ts#
   and u.segfile# = fi.relfile#
   and u.ts# = rb.ts#
   and u.segfile# = rb.file#
   and u.segblock# = rb.block#
   and ts.bitmapped = 0
/

通过以上创建的dba_free_space_pre10g和dba_free_space_recyclebin视图,我们可以很明确地区分表空间上空闲Extents。

针对本例中的LMT上存在大量连续的空闲Extent碎片,可以直接从上述视图中得到答案:

SQL> select * from dba_free_space_pre10g where tablespace_name='FRAGMENT';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
FRAGMENT                               13      40009 1819738112     222136           13

SQL> select count(*),blocks from dba_free_space_recyclebin where tablespace_name='FRAGMENT' group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
      5000          8

显然是RECYCLEBIN中存在大量的小"对象"从而造成了LMT上出现大量碎片的假象

SQL> select space,count(*) from dba_recyclebin where ts_name='FRAGMENT' group by space;

     SPACE   COUNT(*)
---------- ----------
         8       5000

我们可以通过purge recyclebin来"合并"这些Extents碎片

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>  select count(*),blocks from dba_free_space where tablespace_name='FRAGMENT' group by blocks;

  COUNT(*)     BLOCKS
---------- ----------
         1     262136

如果应用程序创建大量的小型堆(heap)表来存放临时数据,在不再需要这些数据时将这些堆表drop掉,那么就可能造成上述LMT”碎片”问题。我们在实际处理10g以后的这类空间问题时一定搞清楚,哪些是真正的Free Extents,而哪些是来自RECYCLEBIN的Extents。

另一方面这个case还告诉我们不要一遇到预料外的行为方式(unexpected behavior)就将问题定位到bug,这样会过早僵化我们的诊断预期。为了尽可能地发散思维,我们有必要如围棋中所提倡的”保留变化”那样来安排诊断步骤。

ORA-01652 even though there is sufficient space in RECYCLE BIN

There is a bug 6977045 which may cause ORA-1652 raised even though there is sufficient space in RECYCLE BIN. Version under 11.2 believed to be affected

[oracle@rh2 ~]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.
Bug 6977045  ORA-1652 even though there is sufficient space in RECYCLE BIN
This note gives a brief overview bug 6977045.
The content was last updated on: 06-DEC-2010
Click here for details of each of the sections below.
Affects:
Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected 	Versions BELOW 11.2
Versions confirmed as being affected
11.1.0.7
Platforms affected	Generic (all / most platforms affected)
Fixed:
This issue is fixed in
11.2.0.1 (Base Release)
11.1.0.7 Patch 32 on Windows Platforms
Symptoms:
Related To:
Error May Occur
Storage Space Usage Affected
ORA-1652
Recycle Bin
Description
Under space pressure an ORA-1652 may be signalled even if there is sufficient
space in the recyclebin.
Rediscovery Notes:
Under space pressure, space allocation fails, even though there
is sufficient free space in recycle bin.
Workaround
Turn off the recycle bin.
OR
Purge the recyclebin.
Hdr: 12582291 11.1.0.7 RDBMS 11.1.0.7 SPACE PRODID-5 PORTID-59
Abstract: UPDATING A LOB FAILS WHILE CLEARING RECYCLE BIN EVEN WHEN ENOUGH FREE SPACE IS A
BUG TYPE CHOSEN
===============
Code
SubComponent: Recovery
======================
DETAILED PROBLEM DESCRIPTION
============================
An OCI application module tried to update a LOB object, and this operation
internally & recursively tried to clear off a few segments from the recycle
bin. As ct. had enabled triggers preventing uncontrolled droppings of
segments, this apparently prevented the application module from succeeding.
Further, since this error did not show up on the application module that
failed, this customer-facing critical application of this large enterprise
was down for considerable time.
DIAGNOSTIC ANALYSIS
===================
None. This bug is raised mainly as a Q/A to get clarifications for customer,
who is demanding an answer and possible action plan so that they can prevent
such disastrous situation in future.
WORKAROUND?
===========
Yes
WORKAROUND INFORMATION
======================
Disable the trigger or not using the recycle bin (Though neither operation
is acceptable to ct. because of their business reasons).
TECHNICAL IMPACT
================
Critical application module fails.
RELATED ISSUES (bugs, forums, RFAs)
===================================
None (MOS Note 978045.1 was referenced by ct.)
Hdr: 6977045 10.2 RDBMS 10.2 RAM DATA PRODID-5 PORTID-23 ORA-1652
Abstract: ORA-1652  LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE
*** 04/16/08 12:57 pm ***
TAR:
----
6880393.992
PROBLEM:
--------
ORA-12801: error signaled in parallel query server P038
ORA-1652: unable to extend temp segment by 320 in tablespace ERROR_TS
After dropping a table in a LMT the space is not properly returned to the
tablespace datafiles .
Only after purge tablespace error_ts; do we see the space returned correctly.
Subsequently the test plan is successful and the table is created.
DIAGNOSTIC ANALYSIS:
--------------------
See attached test case. test_output.log
WORKAROUND:
-----------
none
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
TEST CASE:
----------
See attached test case. test_output.log
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 04/16/08 01:29 pm ***
*** 04/16/08 02:04 pm ***
the problem here is that even though the objects are occupying the same space
when they were created, dba_free_space shows one datafile to contain all the
free space reclaimed by the drop table command.
*** 04/16/08 02:35 pm ***
Please confirm this is a duplicate of bug 5083393.
*** 04/17/08 10:56 am ***
*** 04/17/08 05:09 pm ***
*** 04/17/08 05:14 pm *** (CHG: Sta->10)
*** 04/17/08 05:14 pm ***
*** 04/21/08 11:06 am *** (CHG: Sta->16)
*** 04/21/08 11:06 am ***
please review uploaded file ora_test1.log.
Patch 5083393 has been applied to this instance and the test was ran against
this patch.
Notice the query immedatly following the ORA_1652 error.  The temporary
segments seem to be causing the failure and specifically segment 1199.88012  .
*** 04/22/08 01:55 pm ***
Current SQL statement for this session:
create table seckle.my_test2_tb
nologging tablespace error_ts
parallel (degree 6)
as
select * from ecm.E08401AH_GEMINI_CMF_WIDE_TB
ERROR parallelizer slave or internal
qbas:54482
pgakid:2 pgadep:0
qerpx: error stack: OER(12805)
qbas_qerpxs: 54482
dfo_qerpxs: 0x4b7ba89e0 dfo1_qerpxs: 0x4b7ba9178
ntq_qerpxs: 1 ntqi_qerpxs: 0
nbfs_qerpxs: 0
nobj_qerpxs: 2  ngdef_qerpxs: 1
mflg_qerpxs: 0x2c
slave set 1 DFO dump:
kkfdo: (0x4b7ba9178)
kkfdo->kkfdochi: (0x0)
kkfdo->kkfdopar: (0x0)
kkfdo->kkfdonxt: (0x0)
kkfdo->kkfdotqi: 0
kkfdo->kkfdontbl: 2
kkfdo->kkfdongra: 1
kkfdo->kkfdofigra: 0
kkfdo->kkfdoflg: 0x2818
kkfdo->kkfdooct: 1
kkfdo->kkfdonumeopn: 0
Output table queue: (0x4b7fab1b8)
kxfqd     : 0x4b7fa5728
kxfqdtqi  : 0            TQ id
kxfqdcc   : 0x14         TQ: from slave set 1 to QC
kxfqdpty  : 4
kxfqdsmp  : 0            number of samples
kxfqdflg  : 0x4
kxfqdfmt  :              TQ format
kxfqfnco  : 5            number of TQ columns
kxfqfnky  : 0            number of key columns
TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
kxfqfcol[   0]:  4          23         0x0          4
kxfqfcol[   1]:  32720      23         0x80         32720
kxfqfcol[   2]:  1          23         0x0          1
kxfqfcol[   3]:  76         23         0x0          76
kxfqfcol[   4]:  32720      23         0x0          32720
slave set 2 DFO dump:
np_qerpxm: 6 mflg_qerpxm: 0xa7
cdfo_qerpxm: 0x4b7ba9178 (tqid 0) sdfo_qerpxm: 0x0 (tqid -1)
ctqh_qerpxm: 0xffffffff79378ac8 dump:
kxfqh     : 0xffffffff79378ac8
kxfqhflg  : 0x15         TQ handle open
kxfqhmkr  : 0x4          QC
kxfqhpc   : 2            1:producer 2:consumer 3:ranger
kxfqepty  : 4
kxfqhnsam : 6
kxfqhnth  : 6
kxfqhdsc  :              TQ descriptor
kxfqd     : 0x4b7fa5728
kxfqdtqi  : 0            TQ id
kxfqdcc   : 0x14         TQ: from slave set 1 to QC
kxfqdpty  : 4
kxfqdsmp  : 0            number of samples
kxfqdflg  : 0x4
kxfqdfmt  :              TQ format
kxfqfnco  : 5            number of TQ columns
kxfqfnky  : 0            number of key columns
TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
kxfqfcol[   0]:  4          23         0x0          4
kxfqfcol[   1]:  32720      23         0x80         32720
kxfqfcol[   2]:  1          23         0x0          1
kxfqfcol[   3]:  76         23         0x0          76
kxfqfcol[   4]:  32720      23         0x0          32720
dnst_qerpxm[cur,par]: 6,0 dcnt_qerpxm[cur,par]: 0,0
ppxv_qerpxm[0]: 0xffffffff79377f50 count[np..1]:1 1 1 1 1 1
pqv1_qerpxm: 0xffffffff79377f38 bits[np..1]: 111111
pqv2_qerpxm: 0xffffffff79377f40 bits[np..1]: 000000

If you have enabled recyclebin ,then you should check tablespace free space with dba_free_space and recyclebin space also like:

create view dba_free_space_pre10g as
select ts.name TABLESPACE_NAME,
fi.file# FILE_ID,
f.block# BLOCK_ID,
f.length * ts.blocksize BYTES,
f.length BLOCKS,
f.file# RELATIVE_FNO
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name TABLESPACE_NAME,
fi.file# FILE_ID,
f.ktfbfebno BLOCK_ID,
f.ktfbfeblks * ts.blocksize BYTES,
f.ktfbfeblks BLOCKS,
f.ktfbfefno RELATIVE_FNO
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0
and ts.online$ in (1, 4)
and ts.contents$ = 0
/
create view dba_free_space_recyclebin as
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name TABLESPACE_NAME,
fi.file# FILE_ID,
u.ktfbuebno BLOCK_ID,
u.ktfbueblks * ts.blocksize BYTES,
u.ktfbueblks BLOCKS,
u.ktfbuefno RELATIVE_FNO
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0
and ts.online$ in (1, 4)
and ts.contents$ = 0
union all
select ts.name TABLESPACE_NAME,
fi.file# FILE_ID,
u.block# BLOCK_ID,
u.length * ts.blocksize BYTES,
u.length BLOCKS,
u.file# RELATIVE_FNO
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
/

dba_free_space_pre10g which shows the real free space like 9i behavior , dba_free_space_recyclebin shows free space resided in recyclebin.

沪ICP备14014813号

沪公网安备 31010802001379号