Matrix of Buffer Cache Monitor values and options

The Sybase IQ performance and Tuning guide has a section called the "Buffer cache monitor checklist". Some 12 different items are listed to watch for performance problems, along with some hints for tuning. The list does not show which option of the IQ monitor statement you should use to capture the value. Below you find this matrix.

How to monitor

Use these statements to capture IQ performance data (you need to log on first):

set option Monitor_Output_Directory = "<path where the output should be placed>"
go
declare local temporary table dummy_monitor (dummy_column integer)
go
iq utilities main into dummy_monitor start monitor '-summary'
go
/* Do not disconnect from IQ and wait until the monitoring should stop. Then type this: */
iq utilities main into dummy_monitor stop monitor
go

Now a file has been created with the captured data. In the example above the "-summary" option was used. You can use these options:

• -summary
• -cache
• -cache_by_type
• -io
• -buffalloc
• -contention
• -threads
• -debug

The following shows you which option you can choose to see a specific value.

IQ monitor matrix

Statistic	option	Look in column
HR%	-cache	HR%
	-cache_by_type	HR%
	-summary	HR%
GDirty	-cache	GDirty
	-cache_by_type	Gdirty
	-debug	GrabbedDirty
	-summary	GDirty
Bwaits	-cache	BWaits
	-cache_by_type	BWaits
	-contention	BWaits
	-debug	BusyWaits (?)
LRU Waits	-debug	LRUNumTimeOuts (percentage value)
IO Wait	-contention	IOWait
	-debug	IONumWaits (percentage value)
FL Wait	-contention	FLWait
	-debug	FLMutexWaits (percentage value)
HT Wait	-contention	HTWait
	-debug	BmapHTNumWaits
Mem Wts	-contention	MemWts
	-debug	MemNTimesWaited
PFMgrCondVarWaits	-debug	PFMgrCondVar (percentage value)
CPU Time (System time)	-debug	CPU Sys Seconds
CPU Time (Total time)	-debug	CPU Total Seconds
In use %	-summary	InUse%
Pin %	-cache	Pin%
	-cache_by_type	Pin%
	-debug	Pinned
	-summary	Pin%
Free threads	-debug	ThrNumFree and ThrReserved
	-threads	Free and Resrvd
FlOutOfSpace	-debug	FLIsOutOfSpace