为什么有大量的FreePndpgs很久都不释放呢？

为什么会有很多PndFreePages不释放呢？这可能是很多DBA尤其是BI数据库的DBA不能理解的地方。那么如何才能让这些页真正的释放掉呢？

下面我们做个测试，下面通过一个简单的例子说明一下。不过要聊起原因来，就要涉及到DB2的表空间管理架构了，囿于篇幅，这里就不做详细的解释了。

1. 创建自动存储器表空间ftest2，表sales2位于ftest2中

2. 初始状态如下：
$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 8192 8160 160 0 8000 160 0x00000000 12497015370

3. load数据到sales2中

4. 查看此时的状态：
$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 65536 65504 60544 0 4960 60544 0x00000000 12497015370

5. 窗口1： 在表空间ftest2中创建一个新表test2，插入一些数据但是不提交
$ db2 +c "insert into test2 select * from test"
DB20000I The SQL command completedsuccessfully.

6. 窗口2： 同时在一张没有位于ftest2的表sales1中，插入一些数据并且不要提交
$ db2 "select substr(tabname,1,10),substr(tbspace,1,10) fromsyscat.tables where tabname='SALES1'"

1 2
---------- ----------
SALES1 FTEST

$ db2 +c "insert into sales1 select * from sales1"
DB20000I The SQL command completedsuccessfully.

7. drop表ftest2中的表sales2
$ db2 drop table sales2
DB20000I The SQL command completedsuccessfully.

8. 查看此时的表空间状态
$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 65536 65504 160 60448 4896 60608 0x00000000 12497022920

有很多页处于PndFreepgs哦！！！9. rollback第5步中的交易，然后查看表空间的状态

$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 65536 65504 160 60448 4896 60608 0x00000000 12497022920

依然没有变化！这个时候我们使用listtablespaces show detail试一下，因为list tablespaces showdetail会触发DB2引擎将处于PndFree状态的块置成Free状态。

db2 list tablesapces show detail

TablespaceID = 5
Name = FTEST2
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Totalpages = 65536
Useablepages = 65504
Usedpages = 60608
Freepages = 4896
High watermark(pages) = 60608
Page size(bytes) = 4096
Extent size(pages) = 32
Prefetch size(pages) = 32
Number ofcontainers = 1
Minimum recoverytime = 2009-08-08-03.37.41.000000

$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 65536 65504 160 60448 4896 60608 0x00000000 12497026610
Address Id AS AR InitSize IncSize IIPMaxSize LastResize LRF
0x07000000474848205 Yes Yes0 -1 No None 08/07/2009 22:22:46.635067 No

Free的页数依然没有变化，难道是由于那个更新sales1的交易还没有提交所致吗？而sales1并没有位于ftest2表空间中。下面我们看看rollback第六步中的交易，看看结果会有什么变化 吧

10. rollback第6步的交易，然后查看表空间的状态
$ db2 list tablespaces show detail

Tablespaces for Current Database

TablespaceID = 5
Name = FTEST2
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Totalpages = 65536
Useablepages = 65504
Usedpages = 160
Freepages = 65344
High water mark(pages) = 60608
Page size(bytes) = 4096
Extent size(pages) = 32
Prefetch size(pages) = 32
Number ofcontainers = 1
Minimum recoverytime = 2009-08-08-03.37.41.000000

$ db2pd -db sample -tablespaces | egrep"0x0700000047484820|Address|PndFreePgs"
Address Id TypeContent PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrsMaxStripe LastConsecPg Name
0x07000000474848205 DMS Large 4096 32 Yes 32 1 1 Off1 0 31 FTEST2
Address Id TotalPgs UsablePgs UsedPgs PndFreePgsFreePgs HWM State MinRecTime NQuiescers
0x07000000474848205 65536 65504 160 0 65344 60608 0x00000000 12497026610

没错，当把最早的尚未提交的交易commit/rollback之后，一切问题就解决了 ^_^

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9614008/viewspace-1025053/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9614008/viewspace-1025053/