Extract On Restart While In Recovery Runs Slower Using Lots Of Virtual Memory / Disk File Caching

Last updated on JANUARY 22, 2018

Applies to:

Symptoms

Extract runs slower on restart and uses lots of virtual memory (swap) / disk file caching.

-- ggsci> send <extract name>, status would indicate "In recovery"

Sending STATUS request to EXTRACT EJ1 ...
EXTRACT EJ1 (PID 8581)
  Current status: In recovery[1]: Processing data with empty data queue

-- ggsci> send <extract name>, cachemgr cst would indicate "vm current" higher than the "cache size",
"disk caching" a non-zero value, "Cumulative Stats For Cache Pool" a high value for "trans active"

eg.

GGSCI (zzipdb01.apple.com) 1> send ej1 cachemgr cst
Sending CACHEMGR request to EXTRACT EJ1 ...

CACHE MANAGER VM USAGE
vm current     =  55.99G   vm anon queues =      0
vm anon in use =  55.98G   vm file        =   5.82M
vm used max    =  55.99G   ==> CACHE BALANCED

CACHE CONFIGURATION
cache size       =  32G   cache force paging = 55.99G
buffer min       =  64K   buffer highwater   =   4M
pageout eligible size =   4M

CACHE File Caching
disk current   =      0    disk total  =      0
disk caching   =  10.32M   file cached =   4.83M
file retrieves =   3.36M

CUMULATIVE STATS FOR CACHE POOL #8
POOL INFO   group: ej1  id: p8581_ORA-LOB-MEMPOOL  instance: 0  tid: (nil)
trans active  =    2.88M  trans concurrent (max) =     0

Issue is seen when there are lots of committed transactions between "Recovery Checkpoint" and "Current Checkpoint" for tables with LOBS.

Cause