DB2 主要表级锁模拟

发表于 2012 年 4 月 29 日 由 惜分飞

模拟X锁

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
T_03XFF                         DB2INST1        T     2012-04-11-21.33.12.479480

  3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in exclusive mode"
DB20000I  The SQL command completed successfully.

[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:32 -- Date 2012-04-29-01.08.18.056347

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43210 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B42F30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B43240 7          53514C4445464C5428DD630641 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          02000400000000000000000054 Table      ..X  G   7          255 0          0x00 0x40000000 
--还有db2内部P锁和CatCache锁

[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:28:44 -- Date 2012-04-29-01.08.30.152903

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟S锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:23 -- Date 2012-04-29-01.09.09.610865

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B43030 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          1   0          0x00 0x40000000 
0x99B431B0 7          02000400000000000000000054 Table      ..S  G   7          255 0          0x00 0x40000000 
[db2inst1@xifenfei ~]$ db2 commit
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:29:41 -- Date 2012-04-29-01.09.27.402678

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟Z锁

[db2inst1@xifenfei ~]$ db2 +c drop table t_02xff
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:14 -- Date 2012-04-29-01.11.00.399066

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B41270 7          000006000E006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42870 7          00000600010071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42CF0 7          0100000000000000B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B43240 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B418D0 7          00000600000070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B424B0 7          00000600060071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B415D0 7          00000600050070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42090 7          000006000B0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41AB0 7          0000060004006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B412D0 7          000006000A0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42930 7          00000600100071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42ED0 7          00000C0107004C030000000052 Row        ..S  G   7          2   0          0x10 0x40000000 
0x99B41A50 7          0000060003006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425D0 7          0000060009006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B420C0 7          000006000F0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42750 7          0000060008006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42630 7          000006000E006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B425A0 7          00000600010072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F30 7          000006000D006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B70 7          00000600000071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40CF0 7          0200000000000000000000004F ObjTab     .IN  G   7          255 0          0x00 0x40000000 
0x99B41C60 7          00000600050071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41630 7          00000600040070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42390 7          000006000A0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42030 7          0000060003006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B416F0 7          00000600090070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41690 7          000006000F0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41990 7          0000060008006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B429F0 7          000006000E0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B426F0 7          0000060007006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E10 7          000006000D006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B428D0 7          00000600000072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42E40 7          000006000C006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42B10 7          00001101100057120000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41CC0 7          00000600040071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B417B0 7          000006000F00CE1A0000000052 Row        ..X  G   7          1   0          0x00 0x40000000 
0x99B42960 7          00000600030070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B422D0 7          00000600090071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41570 7          000006000F0072360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41B10 7          0000060002006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41510 7          00000600080070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B413F0 7          000006000E0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40E70 7          00000E006E003B010000000052 Row        ..S  G   7          1   0          0x10 0x40000000 
0x99B41150 7          0000060001006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41BD0 7          0000060007006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42540 7          000006000D0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41210 7          0000060006006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41E70 7          000006000C006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B43090 7          00000500076E0D00B0FA119F43 CatCache   ..X  G   7          255 0          0x00 0x40000000 
0x99B421B0 7          000006000B006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C30 7          000013000A00C81A0000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B427B0 7          00000600030071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42D50 7          00000A00080063000000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41870 7          00000600020070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B423F0 7          00000600080071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42690 7          0000060001006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F30 7          00000600070070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41DB0 7          000006000D0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B410F0 7          0000060000006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41F90 7          0000060006006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42A50 7          000006000C0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40F90 7          0000060005006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41ED0 7          000006000B006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41750 7          00000600110070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B414B0 7          000006000A006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B411B0 7          0000060010006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42450 7          000006000F006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B40DB0 7          02000000000000000000000070 Pool       .IX  G   7          255 0          0x00 0x40000000 
0x99B42810 7          00000600020071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42F90 7          0000050007006E0D0000000052 Row        ..X  G   7          7   0          0x20 0x40000000 
0x99B41CF0 7          00000600010070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42330 7          00000600070071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41030 7          0000060000006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41D50 7          00000600060070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41810 7          000006000C0071360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41090 7          0000060005006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42270 7          000006000B0070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B419F0 7          0000060004006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41330 7          000006000A006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42210 7          00000600100070360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B41450 7          0000060009006E360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42150 7          000006000F006F360000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B42C90 7          00001300000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B43210 7          00000C01000000000000000054 Table      .IS  G   7          2   0          0x10 0x40000000 
0x99B42BD0 7          00000E01000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B430F0 7          00000500000000000000000054 Table      .IX  G   7          7   0          0x00 0x40000000 
0x99B42AB0 7          00000600000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42B70 7          00001101000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B42E70 7          00000800000000000000000054 Table      .IX  G   7          2   0          0x00 0x40000000 
0x99B42DE0 7          00000A00000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
0x99B40D50 7          02000500000000000000000054 Table      ..Z  G   7          255 0          0x00 0x40000000 
0x99B40E10 7          00000C00000000000000000054 Table      .IS  G   7          1   0          0x00 0x40000000 
0x99B40ED0 7          00000E00000000000000000054 Table      .IS  G   7          1   0          0x10 0x40000000 
0x99B413C0 7          00000801000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 
--除了Z锁之外,因为DDL操作会修改系统表,因此还出现很多在系统表上表锁和行锁

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:31:41 -- Date 2012-04-29-01.11.27.194147

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟IX锁

[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_02XFF where tabname LIKE 'T_%XFF'"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:43:47 -- Date 2012-04-29-01.23.33.163605

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B3A510 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          0200050008006F010000000052 Row        ..X  G   7          1   0          0x20 0x40000000 
0x99B3A4B0 7          02000500000000000000000054 Table      .IX  G   7          1   0          0x00 0x40000000 

[db2inst1@xifenfei ~]$ db2 rollback
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:45:06 -- Date 2012-04-29-01.24.52.429166

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg

模拟SIX锁

[db2inst1@xifenfei ~]$ db2 +c "lock table t_01xff in share mode"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 +c "DELETE FROM T_01XFF"
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000
[db2inst1@xifenfei ~]$ db2pd -d xifenfei -locks

Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:39:47 -- Date 2012-04-29-01.19.33.620920

Locks:
Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att  ReleaseFlg
0x99B40E10 7          53514C4332473137315992A241 Internal P ..S  G   7          1   0          0x00 0x40000000 
0x99B40C30 7          00000500076D0D0010FB3F9F43 CatCache   ..S  G   7          5   0          0x00 0x40000000 
0x99B40D50 7          02000400FFFF01000000000052 Row        ..S  G   7          1   0          0x10 0x00000001 
0x99B431B0 7          02000400000000000000000054 Table      SIX  G   7          255 0          0x10 0x40000001

因为IS锁不太好模拟,在本实验中没有体现出来,其本质就是数据库在select查询数据库时给表加的一个表级锁.

发表在 DB2 | 评论关闭

DB2 runstats和reorg操作

发表于 2012 年 4 月 29 日 由 惜分飞

db2收集统计信息(runstats)

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326
T_03XFF                         DB2INST1        T     2012-04-11-21.33.12.479480

  3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF','T_03XFF')"

STATS_TIME                
--------------------------
2012-04-12-04.35.07.539790
2012-04-11-19.55.12.023748
2012-04-11-22.20.07.016905

  3 record(s) selected.

--收集表和索引统计信息,包括数据分布
[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_01xff on all columns
with distribution and detailed indexes all"
DB20000I  The RUNSTATS command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF')"

STATS_TIME                
--------------------------
2012-04-28-23.43.23.904759

  1 record(s) selected.

--收集索引统计信息,如果表没有被收集,也会同时对表收集统计信息,对不会收集数据分布信息
[db2inst1@xifenfei ~]$ db2 "runstats on table db2inst1.t_02xff for  indexes all"
DB20000I  The RUNSTATS command completed successfully.


[db2inst1@xifenfei ~]$ db2 "select STATS_TIME from syscat.tables where tabname in('T_01XFF','T_02XFF')"

STATS_TIME                
--------------------------
2012-04-28-23.43.23.904759
2012-04-28-23.44.39.762858

  2 record(s) selected.

db2 reorg操作

--删除部分表数据
[db2inst1@xifenfei ~]$ db2 "delete from t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "delete from t_03xff"
DB20000I  The SQL command completed successfully.

--reorgchk检查是否需要进行reorg
[db2inst1@xifenfei ~]$ db2 reorgchk on schema db2inst1

Doing RUNSTATS ....


Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                     CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
                                   0      0      0     42      -        0   0   0   0 -** 
Table: DB2INST1.T_02XFF
                                 371      0     42     42      -   152110   0 100 100 --- 
Table: DB2INST1.T_03XFF
                                   0      0      0     83      -        0   0   0   0 -** 
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  F4  F5  F6  F7  F8 REORG  
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: DB2INST1.I_T_01XFF
                                  0     3     3    2     0       0            2             2                822                 822 100   0   -   0 100 ----* 
--------------------------------------------------------------------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary 
for indexes that are not in the same sequence as the base table. When multiple 
indexes are defined on a table, one or more indexes may be flagged as needing 
REORG.  Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension 
indexes have a '*' suffix to their names. The cardinality of a dimension index 
is equal to the Active blocks statistic of the table.

--离线reorg index
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff index DB2INST1.I_T_01XFF allow read access
DB20000I  The REORG command completed successfully.

--在线reorg table
[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_01xff inplace allow write access
DB20000I  The REORG command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

[db2inst1@xifenfei ~]$ db2 reorg table db2inst1.t_03xff inplace allow write access
DB20000I  The REORG command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

--证明异步操作完成
[db2inst1@xifenfei ~]$ ps -ef|grep db2reo
db2inst1  1496  1311  0 00:24 pts/1    00:00:00 grep db2reo

--检查reorg操作结果
[db2inst1@xifenfei ~]$ db2 reorgchk on schema db2inst1

Doing RUNSTATS ....


Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                     CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
                                   0      0      0      1      -        0   0   -   0 --- 
Table: DB2INST1.T_02XFF
                                 371      0     42     42      -   152110   0 100 100 --- 
Table: DB2INST1.T_03XFF
                                   0      0      0      1      -        0   0   -   0 --- 
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  F4  F5  F6  F7  F8 REORG  
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.T_01XFF
Index: DB2INST1.I_T_01XFF
                                  0     1     0    1     0       0            2             2                822                 822 100   -   -   0   0 ----- 
--------------------------------------------------------------------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary 
for indexes that are not in the same sequence as the base table. When multiple 
indexes are defined on a table, one or more indexes may be flagged as needing 
REORG.  Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension 
indexes have a '*' suffix to their names. The cardinality of a dimension index 
is equal to the Active blocks statistic of the table.
发表在 DB2 | 评论关闭

关于linux中oracle用户进程占用内存猜测

发表于 2012 年 4 月 28 日 由 惜分飞

本文是针对linux下面显示oracle用户进程占用大量内存的一个猜想性说明,希望各位专家和我一起继续探讨该问题
ORACLE用户进程占用私有内存分析
top命令结果

[oracle@ora02 31500]$ top -c

top - 12:13:16 up 254 days, 12:14,  2 users,  load average: 1.53, 1.62, 1.33
Tasks: 293 total,   3 running, 290 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.4% us,  0.8% sy,  0.0% ni, 94.7% id,  1.1% wa,  0.0% hi,  0.0% si
Mem:   4147172k total,  4129724k used,    17448k free,    20348k buffers
Swap:  4192956k total,   217772k used,  3975184k free,  2575320k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                   
12505 oracle    17   0 1561m 972m 970m R  100 24.0   3:45.89 oracletxzldb (LOCAL=NO)                                                                                   
12475 oracle    16   0 1561m 931m 929m S    4 23.0   4:08.65 oracletxzldb (LOCAL=NO)                                                                                   
12477 oracle    16   0 1561m 945m 942m S    2 23.3   3:36.92 oracletxzldb (LOCAL=NO)                                                                                   
12479 oracle    16   0 1561m 944m 941m S    2 23.3   4:27.29 oracletxzldb (LOCAL=NO)                                                                                   
12483 oracle    16   0 1561m 939m 937m R    2 23.2   3:49.00 oracletxzldb (LOCAL=NO)                                                                                   
12493 oracle    16   0 1561m 958m 955m S    2 23.7   3:35.35 oracletxzldb (LOCAL=NO)

补充说明:

VIRT	进程使用的虚拟内存总量
RES	进程使用的、未被换出的物理内存大小
SHR	共享内存大小

通过这里可以得出几个信息
12505进程实际使用内存情况:972-970=2M
12505进程%MEM:972*1024/4147172=0.24000162
12505进程在数据库中占用内存

SQL> select PGA_ALLOC_MEM/1024/1024 MEM from v$process where spid=12505;

       MEM
----------
1.90997028

通过这里可以看出12505进程实际上是占用了970M的共享内存,占用2M的PGA内存

ORACLE用户进程占用共享内存分析
分析12505进程的当前进程状态

[oracle@ora02 31500]$ cd /proc/12505
[oracle@ora02 12505]$ cat status
Name:   oracle
State:  S (sleeping)
SleepAVG:       92%
Tgid:   12505
Pid:    12505
PPid:   1
TracerPid:      0
Uid:    501     501     501     501
Gid:    502     502     502     502
FDSize: 32
Groups: 501 502 
VmSize:  1599004 kB    <--使用内存(包括虚拟内存)总量1599004/1024=1561.52734和top中VIRT基本吻合
VmLck:         0 kB
VmRSS:    996132 kB    <--实际使用内存996132/1024 =972.785156和top看到RES基本吻合
VmData:      832 kB
VmStk:       120 kB
VmExe:     37307 kB
VmLib:      4641 kB
StaBrk: 0ad6e000 kB
Brk:    0adf2000 kB
StaStk: bffff850 kB
ExecLim:        ffffffff
Threads:        1
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000000000
SigIgn: 0000000006005203
SigCgt: 00000001c9802cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000

pmap命令分析

[oracle@ora02 12505]$ pmap -d 12505
12505:   oracletxzldb (LOCAL=NO)
Address   Kbytes Mode  Offset           Device    Mapping
0013f000      88 r-x-- 0000000000000000 008:00002 ld-2.3.4.so
00155000       4 r-x-- 0000000000015000 008:00002 ld-2.3.4.so
00156000       4 rwx-- 0000000000016000 008:00002 ld-2.3.4.so
00159000    1176 r-x-- 0000000000000000 008:00002 libc-2.3.4.so
0027f000       8 r-x-- 0000000000125000 008:00002 libc-2.3.4.so
00281000       8 rwx-- 0000000000127000 008:00002 libc-2.3.4.so
00283000       8 rwx-- 0000000000283000 000:00000   [ anon ]
00287000     132 r-x-- 0000000000000000 008:00002 libm-2.3.4.so
002a8000       4 r-x-- 0000000000020000 008:00002 libm-2.3.4.so
002a9000       4 rwx-- 0000000000021000 008:00002 libm-2.3.4.so
002ac000       8 r-x-- 0000000000000000 008:00002 libdl-2.3.4.so
002ae000       4 r-x-- 0000000000001000 008:00002 libdl-2.3.4.so
002af000       4 rwx-- 0000000000002000 008:00002 libdl-2.3.4.so
003b5000      56 r-x-- 0000000000000000 008:00002 libpthread-2.3.4.so
003c3000       4 r-x-- 000000000000d000 008:00002 libpthread-2.3.4.so
003c4000       4 rwx-- 000000000000e000 008:00002 libpthread-2.3.4.so
003c5000       8 rwx-- 00000000003c5000 000:00000   [ anon ]
00ba4000      72 r-x-- 0000000000000000 008:00002 libnsl-2.3.4.so
00bb6000       4 r-x-- 0000000000011000 008:00002 libnsl-2.3.4.so
00bb7000       4 rwx-- 0000000000012000 008:00002 libnsl-2.3.4.so
00bb8000       8 rwx-- 0000000000bb8000 000:00000   [ anon ]
08048000   37308 r-x-- 0000000000000000 0fd:00001 oracle
0a4b7000    8804 rwx-- 000000000246f000 0fd:00001 oracle
0ad50000     648 rwx-- 000000000ad50000 000:00000   [ anon ]
50000000 1540096 rwxs- 0000000000000000 000:00006   [ shmid=0x9000e ]
ae000000       4 r-xs- 000000005e000000 000:00006   [ shmid=0x9000e ]
ae001000    1156 rwxs- 000000005e001000 000:00006   [ shmid=0x9000e ]
ae122000       4 r-xs- 000000005e122000 000:00006   [ shmid=0x9000e ]
ae123000    2932 rwxs- 000000005e123000 000:00006   [ shmid=0x9000e ]
b79d4000    1024 rwx-- 00000000000f4000 000:0000d zero
b7ad4000     512 rwx-- 0000000000074000 000:0000d zero
b7b54000     512 rwx-- 0000000000000000 000:0000d zero
b7bd4000      36 r-x-- 0000000000000000 008:00002 libnss_files-2.3.4.so
b7bdd000       4 r-x-- 0000000000008000 008:00002 libnss_files-2.3.4.so
b7bde000       4 rwx-- 0000000000009000 008:00002 libnss_files-2.3.4.so
b7bdf000     148 rwx-- 00000000b7bdf000 000:00000   [ anon ]
b7c04000    2940 r-x-- 0000000000000000 0fd:00001 libjox9.so
b7ee3000    1088 rwx-- 00000000002de000 0fd:00001 libjox9.so
b7ff3000       8 rwx-- 00000000b7ff3000 000:00000   [ anon ]
b7ff5000       4 r-x-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff6000       8 rwx-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff8000       4 r-x-- 0000000000000000 0fd:00001 libskgxp9.so
b7ff9000       4 --x-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffa000       4 rwx-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffb000       4 r-x-- 0000000000000000 0fd:00001 libodmd9.so
b7ffc000       4 rwx-- 0000000000000000 0fd:00001 libodmd9.so
b7ffd000       4 r-x-- 0000000000000000 008:00002 libcwait.so
b7ffe000       4 rwx-- 0000000000000000 008:00002 libcwait.so
b7fff000       4 rwx-- 00000000b7fff000 000:00000   [ anon ]
bffe2000     120 rwx-- 00000000bffe2000 000:00000   [ stack ]
ffffe000       4 ----- 0000000000000000 000:00000   [ anon ]
mapped: 1599008K    writeable/private: 12944K    shared: 1544192K

补充说明:

mapped ：映射到文件的内存数量
writable/private ：进程所占用的私有地址空间数量
shared ：与其它进程共享的地址空间数量

ipcs 命令

[oracle@ora02 12505]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x0000cace 65536      root      666        2          0                       
0x4d4e5251 98305      root      644        330752     0                       
0x55315352 131074     root      666        4096       0                       
0x44525354 163843     root      644        632832     0                       
0x53494152 196612     root      644        1024       0                       
0x00005643 229381     root      666        1024       1                       
0x00005654 262150     root      666        1024       1                       
0x992ad3dc 589838     oracle    640        1581252608 595

结合pmap和ipcs分析(shmid=0x9000e)

SQL> select to_number('9000e','xxxxxxxx') from dual;

TO_NUMBER('9000E','XXXXXXXX')
-----------------------------
                       589838

SQL> select 1540096+4+1156+4+2932 from dual;

1540096+4+1156+4+2932
---------------------
              1544192

SQL> select 1581252608/1024 from dual;

1581252608/1024
---------------
        1544192

通过这里可以得出12505进程中的共享内存,主要是数据库SGA中的共享内存

补充猜测

SQL> show sga;

Total System Global Area 1561926292 bytes
Fixed Size                   453268 bytes
Variable Size             603979776 bytes
Database Buffers          956301312 bytes
Redo Buffers                1191936 bytes
SQL> select 1561926292/1024 from dual;

1561926292/1024
---------------
     1525318.64

这里显示数据库配置的sga比ipcs中配置共享内存段小,但是进程在分配总的共享内存时候,使用的是ipcs设定的内存段大小,实际使用的内存可能是sga设置大小(未得到权威资料)

发表在 Linux, Oracle | 3 条评论