AIX环境DB2性能诊断

$ db2 connect to officedb

   Database Connection Information

 Database server        = DB2/AIX64 9.5.5
 SQL authorization ID   = DB2ADMIN
 Local database alias   = OFFICEDB

$ db2 "select bpname,bufferpoolid,npages,pagesize from syscat.bufferpools"

BPNAME                                                                                                                           BUFFERPOOLID NPAGES      PAGESIZE   
-------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- -----------
IBMDEFAULTBP                                                                                                                                1       65536        4096
BP32K                                                                                                                                       4       32768       32768
BP8K                                                                                                                                        8      262144        8192
BP16K                                                                                                                                       2      131072       16384
BP4K                                                                                                                                        6     1048576        4096

  5 record(s) selected.

$ db2pd -dbptnmem

Database Partition 0 -- Active -- Up 4 days 17:51:52

Database Partition Memory Controller Statistics

Controller Automatic: Y
Memory Limit:         28669080 KB
Current usage:        15999040 KB
HWM usage:            25673536 KB
Cached memory:        2346304 KB

Individual Memory Consumers:

Name             Mem Used (KB) HWM Used (KB) Cached (KB)
========================================================
APPL-OFFICEDB            18752        160000        8448
DBMS-db2admin            98048         98048       34112
FMP_RESOURCES            39424         39424           0
PRIVATE                  97216        284544       17344
DB-OFFICEDB           15743488      25304128     2286400
LCL-p409880               2112          2112           0

$ db2 get db cfg for officedb | grep -i sort
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(138489)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(13849)
 Index sort flag                             (INDEXSORT) = YES


$ db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.
$


$ db2 get snapshot for all applications > app.out


$ lsvg rootvg
VOLUME GROUP:       rootvg                   VG IDENTIFIER:  00cbef3500004c0000000129dac1969d
VG STATE:           active                   PP SIZE:        256 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      1092 (279552 megabytes)
MAX LVs:            256                      FREE PPs:       672 (172032 megabytes)
LVs:                14                       USED PPs:       420 (107520 megabytes)
OPEN LVs:           13                       QUORUM:         1 (Disabled)
TOTAL PVs:          2                        VG DESCRIPTORS: 3
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         2                        AUTO ON:        no
MAX PPs per VG:     32512                                     
MAX PPs per PV:     1016                     MAX PVs:        32
LTG size (Dynamic): 1024 kilobyte(s)         AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable

$ lsvg datavg
VOLUME GROUP:       datavg                   VG IDENTIFIER:  00cbefe500004c000000012a7f9345b2
VG STATE:           active                   PP SIZE:        512 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      3996 (2045952 megabytes)
MAX LVs:            512                      FREE PPs:       1 (512 megabytes)
LVs:                3                        USED PPs:       3995 (2045440 megabytes)
OPEN LVs:           3                        QUORUM:         3 (Enabled)
TOTAL PVs:          4                        VG DESCRIPTORS: 4
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         4                        AUTO ON:        no
MAX PPs per VG:     130048                                    
MAX PPs per PV:     1016                     MAX PVs:        128
LTG size (Dynamic): 256 kilobyte(s)          AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable

[root@oadba:/]#lspv
hdisk0          00cbef759454a27c                    rootvg          active
hdisk1          00cbefe5a1fd48ed                    rootvg          active
hdisk2          00cbefe57f9342ed                    datavg          active
hdisk3          00cbefe57f9343e9                    datavg          active
hdisk4          00cbefe57f9344d9                    datavg          active
hdisk5          00cbefe57f934575                    datavg          active
[root@oadba:/]#lsdev -Cc disk
hdisk0 Available 00-08-00 SAS Disk Drive
hdisk1 Available 00-08-00 SAS Disk Drive
hdisk2 Available 04-00-02 MPIO DS5020 Disk
hdisk3 Available 04-00-02 MPIO DS5020 Disk
hdisk4 Available 04-00-02 MPIO DS5020 Disk
hdisk5 Available 04-00-02 MPIO DS5020 Disk
[root@oadba:/]#mpio_get_config -Av
Frame id 0:
    Storage Subsystem worldwide name: 608e50017ff4600004c3a827
    Controller count: 2
    Partition count: 1
    Partition 0:
    Storage Subsystem Name = 'OA'
        hdisk      LUN #   Ownership          User Label
        hdisk2         0   A (preferred)      oadb1
        hdisk3         1   B (preferred)      oadb2
        hdisk4         2   A (preferred)      oadb3
        hdisk5         3   B (preferred)      oadb4
[root@oadba:/]#mount
  node       mounted        mounted over    vfs       date        options      
-------- ---------------  ---------------  ------ ------------ ---------------
         /dev/hd4         /                jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/hd2         /usr             jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/hd9var      /var             jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/hd3         /tmp             jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/hd1         /home            jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/hd11admin   /admin           jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /proc            /proc            procfs Feb 21 17:53 rw              
         /dev/hd10opt     /opt             jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/livedump    /var/adm/ras/livedump jfs2   Feb 21 17:53 rw,log=/dev/hd8
         /dev/lv00        /var/adm/csd     jfs    Feb 21 17:53 rw,log=/dev/loglv00
         /dev/datalv      /data            jfs2   Feb 21 18:00 rw,log=/dev/loglv01
         /dev/mqmfv       /var/mqm         jfs2   Feb 21 18:00 rw,log=/dev/loglv01
[root@oadba:/]#lsvg datavg
VOLUME GROUP:       datavg                   VG IDENTIFIER:  00cbefe500004c000000012a7f9345b2
VG STATE:           active                   PP SIZE:        512 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      3996 (2045952 megabytes)
MAX LVs:            512                      FREE PPs:       1 (512 megabytes)
LVs:                3                        USED PPs:       3995 (2045440 megabytes)
OPEN LVs:           3                        QUORUM:         3 (Enabled)
TOTAL PVs:          4                        VG DESCRIPTORS: 4
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         4                        AUTO ON:        no
MAX PPs per VG:     130048                                    
MAX PPs per PV:     1016                     MAX PVs:        128
LTG size (Dynamic): 256 kilobyte(s)          AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable
[root@oadba:/]#lsps -a
Page Space      Physical Volume   Volume Group Size %Used Active  Auto  Type Chksum
hd6             hdisk0            rootvg        8192MB     1   yes   yes    lv     0
[root@oadba:/]#mpio_get_config -Av
Frame id 0:
    Storage Subsystem worldwide name: 608e50017ff4600004c3a827
    Controller count: 2
    Partition count: 1
    Partition 0:
    Storage Subsystem Name = 'OA'
        hdisk      LUN #   Ownership          User Label
        hdisk2         0   A (preferred)      oadb1
        hdisk3         1   B (preferred)      oadb2
        hdisk4         2   A (preferred)      oadb3
        hdisk5         3   B (preferred)      oadb4
[root@oadba:/]#lsvg -l datavg
datavg:
LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT
datalv              jfs2       3894    3894    4    open/syncd    /data
loglv01             jfs2log    1       1       1    open/syncd    N/A
mqmfv               jfs2       100     100     1    open/syncd    /var/mqm
[root@oadba:/]#lspv
hdisk0          00cbef759454a27c                    rootvg          active
hdisk1          00cbefe5a1fd48ed                    rootvg          active
hdisk2          00cbefe57f9342ed                    datavg          active
hdisk3          00cbefe57f9343e9                    datavg          active
hdisk4          00cbefe57f9344d9                    datavg          active
hdisk5          00cbefe57f934575                    datavg          active
[root@oadba:/]#lsstr -El hdisk2
ksh: lsstr:  not found.
[root@oadba:/]#lspv hdisk2
PHYSICAL VOLUME:    hdisk2                   VOLUME GROUP:     datavg
PV IDENTIFIER:      00cbefe57f9342ed VG IDENTIFIER     00cbefe500004c000000012a7f9345b2
PV STATE:           active                                     
STALE PARTITIONS:   0                        ALLOCATABLE:      yes
PP SIZE:            512 megabyte(s)          LOGICAL VOLUMES:  1
TOTAL PPs:          999 (511488 megabytes)   VG DESCRIPTORS:   1
FREE PPs:           0 (0 megabytes)          HOT SPARE:        no
USED PPs:           999 (511488 megabytes)   MAX REQUEST:      256 kilobytes
FREE DISTRIBUTION:  00..00..00..00..00                         
USED DISTRIBUTION:  200..200..199..200..200                    
MIRROR POOL:        None                                       
[root@oadba:/]#lspv hdisk3
PHYSICAL VOLUME:    hdisk3                   VOLUME GROUP:     datavg
PV IDENTIFIER:      00cbefe57f9343e9 VG IDENTIFIER     00cbefe500004c000000012a7f9345b2
PV STATE:           active                                     
STALE PARTITIONS:   0                        ALLOCATABLE:      yes
PP SIZE:            512 megabyte(s)          LOGICAL VOLUMES:  1
TOTAL PPs:          999 (511488 megabytes)   VG DESCRIPTORS:   1
FREE PPs:           0 (0 megabytes)          HOT SPARE:        no
USED PPs:           999 (511488 megabytes)   MAX REQUEST:      256 kilobytes
FREE DISTRIBUTION:  00..00..00..00..00                         
USED DISTRIBUTION:  200..200..199..200..200                    
MIRROR POOL:        None                                       
[root@oadba:/]#lspv hdisk0
PHYSICAL VOLUME:    hdisk0                   VOLUME GROUP:     rootvg
PV IDENTIFIER:      00cbef759454a27c VG IDENTIFIER     00cbef3500004c0000000129dac1969d
PV STATE:           active                                     
STALE PARTITIONS:   0                        ALLOCATABLE:      yes
PP SIZE:            256 megabyte(s)          LOGICAL VOLUMES:  14
TOTAL PPs:          546 (139776 megabytes)   VG DESCRIPTORS:   2
FREE PPs:           332 (84992 megabytes)    HOT SPARE:        no
USED PPs:           214 (54784 megabytes)    MAX REQUEST:      1 megabyte
FREE DISTRIBUTION:  109..05..00..109..109                      
USED DISTRIBUTION:  01..104..109..00..00                       
MIRROR POOL:        None                                       
[root@oadba:/]#