Reorg过程中，数据库对表的访问

最新推荐文章于 2021-04-12 15:55:38 发布

weixin_40658897

最新推荐文章于 2021-04-12 15:55:38 发布

阅读量535

点赞数 1

分类专栏： DB2 Reorg 文章标签： DB2

本文链接：https://blog.csdn.net/weixin_40658897/article/details/87082918

版权

DB2 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

Reorg

1 篇文章 0 订阅

订阅专栏

验证一下，reorg会不会执行一次全表的扫描：

查询下8K的这个表空间

[db2inst1@db2_training ~]$ db2 list tablespaces

       Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 4
Name = IBMDB2SAMPLEXML
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 6
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 7
Name = TSTRG00
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 8
Name = TB_8K
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal

查询这个表空间下面有那些表：

[db2inst1@db2_training ~]$ db2 "select tabname from syscat.tables where t

bspaceid=8"

TABNAME

TEST02
TEST03

2 record(s) selected.

统计表里面的记录数
[db2inst1@db2_training ~]$ db2 “Select count(*) from test02”

1

1 record(s) selected.
查看测试前bufferpool的命中率：
[db2inst1@db2_training ~]$ db2pd -d sample -bufferpools

Database Partition 0 – Database SAMPLE – Active – Up 2 days 10:53:43 – Date 02/12/2019 10:55:05

Bufferpools:
First Active Pool ID 1
Max Bufferpool ID 2
Max Bufferpool ID on Disk 2
Num Bufferpools 6

Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x00007F9B12B0B740 1 IBMDEFAULTBP 8192 20000 0 0 8 0 20000 20000 0 False
0x00007F9B12B0C940 2 BUF8K 8192 10000 0 0 1 0 10000 10000 0 False
0x00007F9B1169E680 4096 IBMSYSTEMBP4K 4096 16 0 0 0 0 16 16 0 False
0x00007F9B12B08140 4097 IBMSYSTEMBP8K 8192 16 0 0 0 0 16 16 0 False
0x00007F9B12B09340 4098 IBMSYSTEMBP16K 16384 16 0 0 0 0 16 16 0 False
0x00007F9B12B0A540 4099 IBMSYSTEMBP32K 32768 16 0 0 0 0 16 16 0 False

Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):

BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
1 16072 502 96.88% 12 0 100.00% 9801 580 94.08% 0 0 00.00%
2 129 39 69.77% 0 0 00.00% 4434 3210 27.60% 0 0 00.00%
4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%

BPID DataWrts IdxWrts DirRds DirRdReqs DirRdTime DirWrts DirWrtReqs DirWrtTime
1 6 2 595512 603 15316 18 3 4
2 0 0 324096 72 4748 0 0 0
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0

BPID AsDatRds AsDatRdReq AsIdxRds AsIdxRdReq AsRdTime AsDatWrts AsIdxWrts AsWrtTime
1 47 16 6 6 22 3 2 0
2 0 0 3166 202 211 0 0 0
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0

BPID TotRdTime TotWrtTime VectIORds VectIOReq BlockIORds BlockIOReq FilesClose NoVictAvl UnRdPFetch
1 805 14 53 22 0 0 0 0 0
2 256 0 3166 202 0 0 0 0 0
4096 0 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0 0

reorg table

查看重组完成以后，bufferpool的读写情况。

[db2inst1@db2_training ~]$ db2 reorg table db2inst1.test02
DB20000I The REORG command completed successfully.
[db2inst1@db2_training ~]$ db2pd -d sample -bufferpools

Database Partition 0 – Database SAMPLE – Active – Up 2 days 10:55:02 – Date 02/12/2019 10:56:24

Bufferpools:
First Active Pool ID 1
Max Bufferpool ID 2
Max Bufferpool ID on Disk 2
Num Bufferpools 6

Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):

BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
1 16606 502 96.98% 11532 0 100.00% 10322 583 94.35% 0 0 00.00%
2 29825 13798 53.74% 0 0 00.00% 16940 3274 80.67% 0 0 00.00%
4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%

BPID DataWrts IdxWrts DirRds DirRdReqs DirRdTime DirWrts DirWrtReqs DirWrtTime
1 6 2 595612 617 15331 24 4 4
2 72 2093 324576 102 4758 218272 246 194
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0

BPID AsDatRds AsDatRdReq AsIdxRds AsIdxRdReq AsRdTime AsDatWrts AsIdxWrts AsWrtTime
1 47 16 8 7 23 3 2 0
2 13525 426 3166 202 759 0 2061 182
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0

BPID TotRdTime TotWrtTime VectIORds VectIOReq BlockIORds BlockIOReq FilesClose NoVictAvl UnRdPFetch
1 806 14 55 23 0 0 0 0 0
2 1268 210 16691 628 0 0 0 14 45
4096 0 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0 0

观察下表里面的page页数。
db2 “select npages,tabname ,fpages from syscat.tables where tabname=‘TEST02’”

[db2inst1@db2_training ~]$ db2 “select npages,tabname ,fpages from syscat.tables where tabname=‘TEST02’”

NPAGES TABNAME FPAGES

            6805 TEST02                                                                                                                                           6805

1 record(s) selected.

推测下reorg过程中，对表的读写情况。

物理读的增加： 13798-39=13759 13759近似等于源表的两倍。13759/6805=2.01
逻辑度的增加：29825-129=29696 29696