联合体insert性能疑问

回复 12# zhenda


   2台机器物理配置一样的，缓冲池参数SRM_XSM_CUST_ITEM_LMT表所在表空间使用的缓冲池大小为480M，EC_CUST_CGT_LMT_TEST 表所在表空间使用的缓冲池大小为240M，B库中EC_CUST_CGT_LMT_TEST 表是我参考生产表创建的测试表，插入的时候该表是空表，该表没runstats，但是生产表runstats了，是否runstats测试好像没什么影响。

   另外SQL快照，A机器如下：
Number of executions               = 1
Number of compilations             = 1
Worst preparation time (ms)        = 3
Best preparation time (ms)         = 3
Internal rows deleted              = 0
Internal rows inserted             = 0
Rows read                          = 6308346
Internal rows updated              = 0
Rows written                       = 0
Statement sorts                    = 0
Statement sort overflows           = 0
Total sort time                    = 0
Buffer pool data logical reads     = 156042
Buffer pool data physical reads    = 10
Buffer pool temporary data logical reads   = 0
Buffer pool temporary data physical reads  = 0
Buffer pool index logical reads    = 0
Buffer pool index physical reads   = 0
Buffer pool temporary index logical reads  = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads      = 0
Buffer pool xda physical reads     = 0
Buffer pool temporary xda logical reads    = 0
Buffer pool temporary xda physical reads   = 0
Total execution time (sec.microsec)= 1041.804537
Total user cpu time (sec.microsec) = 82.227238
Total system cpu time (sec.microsec)= 59.639625
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time  (milliseconds) = 0
Statement text                     = insert INTO EC_CUST_CGT_LMT_TEST (ORG_CODE,CUST_CODE,CGT_CODE,QTY_LMT,NOTE,UP_TIME)    SELECT COM_ID,CUST_ID,ITEM_ID,LMT_QTY,NOTE,'2014-02-11 16:35:02'      FROM SRM_XSM_CUST_ITEM_LMT      WHERE COM_ID='11230101'



B机器抓快照报私有内存不足，还没找到怎么解决，报错及db2diag.log如下，麻烦看下是什么原因呢？
$ db2 get snapshot for dynamic sql on xsmec
SQL0083C  A memory allocation error has occurred.



2014-02-20-15.44.03.185970+480 E331230024A1156    LEVEL: Error (OS)
PID     : 1016682              TID  : 1           PROC : db2bp
INSTANCE: db2inst3             NODE : 000
EDUID   : 1
FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
CALLED  : OS, -, malloc
OSERR   : ENOMEM (12) "There is not enough memory available now."
MESSAGE : Private memory and/or virtual address space exhausted, or data ulimit
          exceeded
DATA #1 : Soft data resource limit, PD_TYPE_RLIM_DATA_CUR, 8 bytes
134217728
DATA #2 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 8 bytes
269815808
DATA #3 : Current set size, PD_TYPE_SET_SIZE, 8 bytes
90963968
CALLSTCK:
  [0] 0x090000000051DD50 sqloLogMemoryCondition + 0x274
  [1] 0x090000000051DA24 sqloLogMemoryCondition@glue30B + 0x78
  [2] 0x090000000062479C sqlogmblkEx + 0x11C
  [3] 0x0900000000BEA954 sqloMemBlockReallocate + 0x100
  [4] 0x00000001000F0834 clpbp_exe_sqlmonss__Fv + 0x394
  [5] 0x00000001000015F0 clp_bp_con__Fv + 0x630
  [6] 0x0000000100000A78 main + 0x1F8
  [7] 0x0000000100000320 __start + 0x98
  [8] 0x0000000000000000 ?unknown + 0x0
  [9] 0x0000000000000000 ?unknown + 0x0

2014-02-20-15.44.03.187008+480 E331231181A561     LEVEL: Warning
PID     : 1016682              TID  : 1           PROC : db2bp
INSTANCE: db2inst3             NODE : 000
EDUID   : 1
FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30
DATA #1 :
Out of memory failure for Private Heap.
Requested block size           : 269774247 bytes.
Physical heap size             : 90505216 bytes.
Configured heap size           : 1099511627776 bytes.
Unreserved memory used by heap : 0 bytes.
Unreserved memory left in set  : 0 bytes.




$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         131072
stack(kbytes)        32768
memory(kbytes)       32768
coredump(blocks)     2097151
nofiles(descriptors) 2000


是否需要增大data的limits?