on 10-20-2014 3:41 PM
Is there a way to flush temporary and/or main cache without restarting the database server? (in using SAP IQ 16.0 SP8 LP3)
Thanks a lot!
Take a look of how, over the time, the temporary cache get filled up. The peaks are during the loading periods, we can clearly see that for the loads, the temporary cache is freed, but seems that over the regular daily querying of the database the temporary cache get full. I'm wondering if eventually this will affect loading performance?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
sp_iqsysmon '00:01:00'
Stat f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27
------------------------------------------- ----------------------------------- ------------------------- ------------------ ---------------------------- --------------------------- ------------------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---------- ------- ------- ------- ------- ------- ------- ------- ------- -------
==============================
Buffer Manager (Main)
==============================
STATS-NAME TOTAL NONE TXTPOS TXTDOC CMPACT BTREEV BTREEF BV VDO DBEXT DBID SORT STORE GARRAY BARRAY BLKMAP HASH CKPT BM TEST CMID RIDCA LOB LVCRID FILE RIDMAP RVLOG
Finds 667 0 0 0 0 29 148 0 124 0 0 0 0 44 35 279 0 0 8 0 0 0 0 0 0 0 0
Hits 667 0 0 0 0 29 148 0 124 0 0 0 0 44 35 279 0 0 8 0 0 0 0 0 0 0 0
Hit% 100.0 0.0 0.0 0.0 0.0 100.0 100.0 0.0 100.0 0.0 0.0 0.0 0.0 100.0 100.0 100.0 0.0 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
FalseMiss 326 0 0 0 0 0 2 0 0 0 0 0 0 12 33 279 0 0 0 0 0 0 0 0 0 0 0
UnOwnRR 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Cloned 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Creates 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Destroys 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Dirties 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RealDirties 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchReqs 190 0 0 0 0 0 0 0 0 0 0 0 0 79 84 0 0 0 27 0 0 0 0 0 0 0 0
PrefetchNotInMem 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchInMem 30 0 0 0 0 0 0 0 27 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0
Reads 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PReadBlks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PReadKB 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
ReReads 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Writes 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PWriteBlks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PWriteKB 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
GrabbedDirty 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ReadRemoteRpc 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ReadRemotePhyIO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheHit 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMiss 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMatch 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMismatch 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheReadTotal 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheReadInError 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheWriteTotal 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheWriteInError 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
STATS-NAME VALUE
BusyWaits 0
LRUNumLocks 2541
LRUNumSpinsWoTO 0 ( 0.0 %)
LRUNumSpinLoops 0
LRUNumTimeOuts 88 ( 3.5 %)
BmapHTNumLocks 115
BmapHTNumWaits 0 ( 0.0 %)
CacheTeamTimesWoken 411
CacheTeamNumAsleep 55
BmapHTMaxEntries 8192
BmapHTNEntries 1027
BmapHTNInserts 37
BmapHTNCollisn 0
BmapHTNFinds 37
BmapHTNHits 0
BmapHTNHits1 0
BmapHTNHits2 0
BmapHTNClears 37
BmapHTNLChain 48
BmapHTNRehash 1
BlockmapMutexsNLocks 685
BlockmapMutexsNWaits 24
BlockmapUID 52888550
BlockmapUIDnallocs 0
BlockmapRegEver 10731756
BlockmapRegisters 37
BufHTNBuckets 132096
BufHTNEntries 97950
BufHTNw2orMore 8770
BufHTMaxBucketSize 2899
BufHTNFoiledOps 0
IONumLocks 8365835
IONumWaits 14502 ( 0.2 %)
==============================
Buffer Manager (Temporary)
==============================
STATS-NAME TOTAL NONE TXTPOS TXTDOC CMPACT BTREEV BTREEF BV VDO DBEXT DBID SORT STORE GARRAY BARRAY BLKMAP HASH CKPT BM TEST CMID RIDCA LOB LVCRID FILE RIDMAP RVLOG
Finds 623 0 0 0 0 0 0 0 0 0 0 0 0 0 0 182 0 0 441 0 0 0 0 0 0 0 0
Hits 623 0 0 0 0 0 0 0 0 0 0 0 0 0 0 182 0 0 441 0 0 0 0 0 0 0 0
Hit% 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 100.0 0.0 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
FalseMiss 222 0 0 0 0 0 0 0 0 0 0 0 0 0 0 182 0 0 40 0 0 0 0 0 0 0 0
UnOwnRR 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Cloned 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Creates 117 0 0 0 0 0 0 0 0 0 0 0 32 0 0 10 0 0 75 0 0 0 0 0 0 0 0
Destroys 117 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10 0 0 33 0 0 0 0 0 0 0 0
Dirties 229 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 229 0 0 0 0 0 0 0 0
RealDirties 117 0 0 0 0 0 0 0 0 0 0 0 32 0 0 10 0 0 75 0 0 0 0 0 0 0 0
PrefetchReqs 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchNotInMem 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchInMem 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Reads 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PReadBlks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PReadKB 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
ReReads 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Writes 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PWriteBlks 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PWriteKB 0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
GrabbedDirty 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ReadRemoteRpc 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ReadRemotePhyIO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheHit 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMiss 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMatch 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheMismatch 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheReadTotal 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheReadInError 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheWriteTotal 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MainCacheWriteInError 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
STATS-NAME VALUE
BusyWaits 0
LRUNumLocks 2821
LRUNumSpinsWoTO 0 ( 0.0 %)
LRUNumSpinLoops 0
LRUNumTimeOuts 99 ( 3.5 %)
BmapHTNumLocks 251
BmapHTNumWaits 0 ( 0.0 %)
CacheTeamTimesWoken 0
CacheTeamNumAsleep 55
BmapHTMaxEntries 4096
BmapHTNEntries 15
BmapHTNInserts 121
BmapHTNCollisn 0
BmapHTNFinds 0
BmapHTNHits 0
BmapHTNHits1 0
BmapHTNHits2 0
BmapHTNClears 121
BmapHTNLChain 12
BmapHTNRehash 0
BlockmapMutexsNLocks 638
BlockmapMutexsNWaits 2
BlockmapUID 546140543
BlockmapUIDnallocs 121
BlockmapRegEver 5216615
BlockmapRegisters 121
BufHTNBuckets 132096
BufHTNEntries 74849
BufHTNw2orMore 11430
BufHTMaxBucketSize 73
BufHTNFoiledOps 0
IONumLocks 590702
IONumWaits 372 ( 0.1 %)
==============================
Buffer Pool (Main)
==============================
STATS-NAME TOTAL NONE TXTPOS TXTDOC CMPACT BTREEV BTREEF BV VDO DBEXT DBID SORT STORE GARRAY BARRAY BLKMAP HASH CKPT BM TEST CMID RIDCA LOB LVCRID FILE RIDMAP RVLOG
MovedToMRU 585 0 0 0 0 29 61 0 124 0 0 0 0 14 72 279 0 0 6 0 0 0 0 0 0 0 0
MovedToWash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedFromLRU 585 0 0 0 0 29 61 0 124 0 0 0 0 14 72 279 0 0 6 0 0 0 0 0 0 0 0
RemovedFromWash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedInScanMode 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MovedToPSList 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedFromPSList 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
STATS-NAME VALUE
Pages 98974
InUse 97950 ( 99.0 %)
Dirty 42 ( 0.0 %)
Pinned 0 ( 0.0 %)
Flushes 0
FlushedBufferCount 0
GetPageFrame 0
GetPageFrameFailure 0
GotEmptyFrame 0
Washed 0
TimesSweepersWoken 0
PriorityWashed 0
NPrioritySweepersWoken 0
washTeamSize 0
WashMaxSize 19590 ( 19.8 %)
washNBuffers 19590 ( 19.8 %)
washNDirtyBuffers 0 ( 0.0 %)
washNCachePendingBuffers 0 ( 0.0 %)
washSignalThreshold 1960 ( 2.0 %)
washNActiveSweepers 0
NPriorityWashBuffers 0
NActivePrioritySweepers 0
washIntensity 0
FlushAndEmpties 0
EmptiedBufferCount 0
EmptiedSkippedCount 0
EmptiedWriteCount 0
EmptiedErrorCount 0
nAffinityTotal 0 ( 0.0 %)
nAffinityArea 0 ( 0.0 %)
==============================
Buffer Pool (Temporary)
==============================
STATS-NAME TOTAL NONE TXTPOS TXTDOC CMPACT BTREEV BTREEF BV VDO DBEXT DBID SORT STORE GARRAY BARRAY BLKMAP HASH CKPT BM TEST CMID RIDCA LOB LVCRID FILE RIDMAP RVLOG
MovedToMRU 456 0 0 0 0 0 0 0 0 0 0 0 2 0 0 182 0 0 272 0 0 0 0 0 0 0 0
MovedToWash 30 0 0 0 0 0 0 0 0 0 0 0 30 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedFromLRU 486 0 0 0 0 0 0 0 0 0 0 0 32 0 0 182 0 0 272 0 0 0 0 0 0 0 0
RemovedFromWash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedInScanMode 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
MovedToPSList 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RemovedFromPSList 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
STATS-NAME VALUE
Pages 98974
InUse 74849 ( 75.6 %)
Dirty 6 ( 0.0 %)
Pinned 0 ( 0.0 %)
Flushes 0
FlushedBufferCount 0
GetPageFrame 117
GetPageFrameFailure 0
GotEmptyFrame 117
Washed 0
TimesSweepersWoken 0
PriorityWashed 0
NPrioritySweepersWoken 0
washTeamSize 0
WashMaxSize 19590 ( 19.8 %)
washNBuffers 19590 ( 19.8 %)
washNDirtyBuffers 0 ( 0.0 %)
washNCachePendingBuffers 0 ( 0.0 %)
washSignalThreshold 1960 ( 2.0 %)
washNActiveSweepers 0
NPriorityWashBuffers 0
NActivePrioritySweepers 0
washIntensity 0
FlushAndEmpties 0
EmptiedBufferCount 0
EmptiedSkippedCount 0
EmptiedWriteCount 0
EmptiedErrorCount 0
nAffinityTotal 0 ( 0.0 %)
nAffinityArea 0 ( 0.0 %)
==============================
Prefetch Manager (Main)
==============================
STATS-NAME VALUE
PFMgrNThreads 55
PFMgrNSubmitted 190
PFMgrNDropped 0
PFMgrNValid 0
PFMgrNRead 0
PFMgrNReading 0
PFMgrCondVar Locks 929 Lock-Waits 39 ( 4.2 %) Signals 0 Broadcasts 0 Waits 0
==============================
Prefetch Manager (Temporary)
==============================
STATS-NAME VALUE
PFMgrNThreads 55
PFMgrNSubmitted 0
PFMgrNDropped 0
PFMgrNValid 0
PFMgrNRead 0
PFMgrNReading 0
PFMgrCondVar Locks 1 Lock-Waits 0 ( 0.0 %) Signals 0 Broadcasts 0 Waits 0
==============================
IQ Store (Main) Free List
==============================
STATS-NAME VALUE
FLBitCount 30344918
FLIsOutOfSpace NO
FLMutexLocks 1
FLMutexWaits 0 ( 0.0 %)
==============================
IQ Store (Temporary) Free List
==============================
STATS-NAME VALUE
FLBitCount 5489083
FLIsOutOfSpace NO
FLMutexLocks 21
FLMutexWaits 0 ( 0.0 %)
==============================
Buffer Allocator (Main)
==============================
STATS-NAME VALUE
NActiveCommands 0
BufAllocMaxBufs 97951 ( 99.0 %)
BufAllocAvailBufs 97951 ( 100.0 %)
BufAllocReserved 0 ( 0.0 %)
BufAllocAvailPF 39180 ( 40.0 %)
BufAllocSlots 4700
BufAllocNPinUsers 0
BufAllocNPFUsers 0
BufAllocNPostedUsrs 0
BufAllocNUnpostUsrs 0
BufAllocPinQuota 0
BufAllocNPostEst 0
BufAllocNUnPostEst 0
BufAllocMutexLocks 127
BufAllocMutexWaits 0 ( 0.0 %)
STATS-NAME TOTAL UNKNWN HASH CSORT ROW ROWCOL FP GARRAY LOB BTREE BM BV STORE TEST DBCC BLKMAP IQUTIL
NumClients 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PinUserQuota 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchUserQuota 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PinUserRegisters 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PfUserRegisters 51 0 0 0 1 29 0 8 0 0 2 0 0 0 0 0 0 0 0 0 0 0 11 0
ClientCountOfPinners 0 1 3 6 10 33 66 100 333 666 1000 3333 6666 10000 33333 66666 100000 4294967295
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Hash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Sort 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Row 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RowColumn 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
FP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Garray 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
LOB 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BTree 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BV 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Store 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Test 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
DBCC 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Run 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
QCPRun 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
TextDoc 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
VDO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Load Pass 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
==============================
Buffer Allocator (Temporary)
==============================
STATS-NAME VALUE
NActiveCommands 0
BufAllocMaxBufs 97951 ( 99.0 %)
BufAllocAvailBufs 97951 ( 100.0 %)
BufAllocReserved 0 ( 0.0 %)
BufAllocAvailPF 39180 ( 40.0 %)
BufAllocSlots 600
BufAllocNPinUsers 0
BufAllocNPFUsers 0
BufAllocNPostedUsrs 0
BufAllocNUnpostUsrs 0
BufAllocPinQuota 19590
BufAllocNPostEst 0
BufAllocNUnPostEst 0
BufAllocMutexLocks 1
BufAllocMutexWaits 0 ( 0.0 %)
STATS-NAME TOTAL UNKNWN HASH CSORT ROW ROWCOL FP GARRAY LOB BTREE BM BV STORE TEST DBCC BLKMAP IQUTIL
NumClients 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PinUserQuota 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PrefetchUserQuota 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PinUserRegisters 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PfUserRegisters 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
ClientCountOfPinners 0 1 3 6 10 33 66 100 333 666 1000 3333 6666 10000 33333 66666 100000 4294967295
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Hash 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Sort 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Row 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
RowColumn 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
FP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Garray 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
LOB 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BTree 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BV 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Store 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Test 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
DBCC 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Run 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
QCPRun 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
TextDoc 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Unknown 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
VDO 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Load Pass 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
==============================
Memory Manager
==============================
STATS-NAME VALUE
MemAllocated 26168157776 (25554841 KB)
MemAllocatedMax 26551471008 (25929170 KB)
MemAllocatedEver 149552008 ( 146046 KB)
MemNAllocated 174436
MemNAllocatedEver 7646
MemNTimesLocked 14767
MemNTimesWaited 19 ( 0.1 %)
==============================
Thread Manager
==============================
STATS-NAME VALUE
ThrNumOfCpus 8
ThreadLimit 548
ThrNumThreads 547 ( 99.8 %)
ThrReserved 113 ( 20.6 %)
ThrNumFree 326 ( 59.5 %)
NumThrUsed 222 ( 40.5 %)
UsedPerActiveCmd 0
ThrNTeamsInUse 4
ThrMaxTeams 81
NumTeamsAlloc 0
TeamThrAlloc 0
SingleThrAlloc 10
ThrMutexLocks 26
ThrMutexWaits 0 ( 0.0 %)
==============================
CPU time statistics
==============================
STATS-NAME VALUE
Elapsed Seconds 60.00 ( 12.5 %)
CPU User Seconds 0.89 ( 0.2 %)
CPU Sys Seconds 0.04 ( 0.0 %)
CPU Total Seconds 0.93 ( 0.2 %)
==============================
Transaction Manager
==============================
STATS-NAME VALUE
TxnMgrNPending 0
TxnMgrNBlocked 5949
TxnMgrNWaiting 0
TxnMgrPCcondvar Locks 112 Lock-Waits 0 ( 0.0 %) Signals 0 Broadcasts 0 Waits 0
TxnMgrTxnIDseq 286111675
TxnMgrtxncblock Locks 2597 Lock-Waits 0 ( 0.0 %)
TxnMgrVersionID 0
TxnMgrOAVI 0
TxnMgrVersionLock Locks 75 Lock-Waits 0 ( 0.0 %) Signals 0 Broadcasts 0 Waits 0
==============================
Context Server statistics
==============================
STATS-NAME VALUE
StCntxNumConns 37
StCntxNResource 100
StCntxNOrigResource 100
StCntxNWaiting 0
StCntxNWaited 0
StCntxNAdmitted 18
StCntxLock Locks 73 Lock-Waits 0 ( 0.0 %)
StCntxCondVar Locks 40 Lock-Waits 0 ( 0.0 %)
==============================
Catalog, DB Log, and Repository statistics
==============================
STATS-NAME VALUE
CatalogLock RdLocks 0 RdWaits 0 ( 0.0 %) RdTryFails 0 WrLocks 2370 WrWaits 0 ( 0.0 %) WrTryFails 0
DbLogMLock Locks 27 Lock-Waits 0 ( 0.0 %)
DbLogSLock Locks 1 Lock-Waits 0 ( 0.0 %)
RepositoryNList 0
RepositoryLock Locks 1 SpinsWoTO 0 ( 0.0 %) Spins 0 TimeOuts 0 ( 0.0 %)
==============================
IQ In-Memory Store
==============================
STATS-NAME VALUE
RLV Memory Limit 2048 MB
RLV Memory Used 0 MB
RLV Chunks Used 0
==============================
Large Memory Allocator
==============================
STATS-NAME VALUE
Large Memory Space 12288 MB
Large Memory Max Flexible 3072 MB
Large Memory Num Flex Allocations 0
Large Memory Flexible % 50 %
Large Memory Flexible used 0 MB
Large Memory Inflexible % 90 %
Large Memory Inflexible used 12214 MB
Large Memory Anti-Starvation % 50 %
Large Memory Num Connections 0
494 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 120ms]
[Executed: 20/10/2014 12:41:48 PM] [Execution: 1m 0s]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry Chris, I means: "cache usage is a good thing, in general"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks everyone,
As Mark said early, “Temporary is flushed when the object is no longer in use”. I guess a garbage collector process clean up the unused object from the cache, but in this case seem that all the unused object are still there, or at less the space allocated to then had not been unallocated. Shashikant seems to suggest that temporary will behave similar to permanent in the sense that the unused object will remain in the cache until it is full and the space will be unallocated only went additional space is needed for new object and there is not enough free space in temporary. If this is the case, the temporary cache will fill up near to 100% in high loaded instances, and there is no need to monitor the percentage of temporary cache in use, because as Chris said: "ache usage is a good thing, in general".
Is this correct?
Thanks a lot
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It could also be that the connections are still holding resources. Just because they are dobe running doesn't mean that they cannot hold resources. A cursor could still be open. The force no scroll cursors option could be off allowing scrolling cursors, and this take up temp space.
The point is that there are many ways for space to remain consumed. Check the output of iq connection to see if the idle connections are still holding temp space or temp workspace.
There already is a feature Request CR# 681939 - "provide tool for flushing temp cache". However, I do not know when it will be implemented.
John
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Main cache cannot get flushed. Temporary is flushed when the object is no longer in use. What reason do you have for wanting to flush them? I can't think of a good operational reason to do so.
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mark, I’m monitoring temporary cache with cockpit, several processes executed and made the temporary cache jump over 90%, this triggered the alert and cockpit started sending medium priority messages. Those transactions had finished over 20 hrs. ago; but, temporary cache remains over 90%. By the moment I disable the monitor for temporary cache.
Thanks
User | Count |
---|---|
84 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.