SAP Sybase IQ Sysmon - System Performance Analysis


IQ Utility

Sybase IQ's sysmon procedure produces a log file
 

declare local temporary table dummy_monitor_debug (dummy_column integer);
declare local temporary table dummy_monitor1_debug (dummy_column integer);
set temporary option Monitor_Output_Directory = "/gpfs/jio/ingest/LOAD_STATISTICS_14092014";
iq utilities main into dummy_monitor_debug start monitor '-debug -interval 10 -file_suffix main';
iq utilities private into dummy_monitor1_debug start monitor '-debug -interval 10 -file_suffix temp';

 
OR


set option Monitor_Output_Directory = "/gpfs/jio/ingest/LOAD_STATISTICS_14092014"
 go
 sp_iqsysmon start_monitor, filemode,'-interval 120 -file_suffix sysmon.2017_May6'
 go
 commit
 go
 waitfor delay '00:59:00'
 commit
 go
 sp_iqsysmon stop_monitor
 go

 

 

Below is a handy script for processing the sysmon output. It filters the data, and shows recommended levels/benchmarks.

 

#!/bin/bash

#========================================================

# sysmon log file analyzer

#

#

#========================================================

 

fname=$1

 

if ! test -f $fname; then

  echo "Error: file $fname not found."

  exit

fi

 

date

 

echo "============================================================================="

echo "IQ Sysmon Log Analyzer"

echo "============================================================================="

sleep 2

 

 

echo "Threads: Free vs Reserved               Benchmark: ThrNumFree > ThrReserved"

echo " "; echo " "; sleep 2

 

egrep 'Thr'  $fname  | egrep 'Free|Reserved'  | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

echo "Pinned Buffers                          Benchmark: Pinned < 90%"

echo " "; echo " "; sleep 2

 

egrep 'Pool|Pin'  $fname | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

 

 

echo "Buffers in Use %                        Benchmark: ~= 100%"

echo " "; echo " "; sleep 2

 

egrep 'Pool|InUse'  $fname  | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

echo "CPU Time                                Benchmark: CPU Sys < 20%"

echo " "; echo " "; sleep 2

 

grep 'CPU Sys'  $fname | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

echo "LRU Waits                   Benchmark: < 20%"

echo " "; echo " "; sleep 2

 

grep 'LRUNumTime'  $fname  | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

cho "Buffer Busy Waits               Benchmark: ~= 0"

echo " "; echo " "; sleep 2

 

egrep 'Manager|BusyWaits'  $fname | egrep -v 'Memory|Transaction|Thread|fetch' | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

echo "Grabbed Dirty                       Benchmark: ~=0"

echo " "; echo " "; sleep 2

 

egrep -i 'grabbeddirty'  $fname | head -25

 

echo "============================================================================="; sleep 3

echo "Press enter to continue."

read sel1

 

 

echo "HR% - Cache Hit Rate                Benchmark: Hit% > 90%"

echo " "; echo " "; sleep 2

 

egrep -i 'hit%'  $fname | head -25

 

echo "============================================================================="

 

Comments

Post a Comment

Popular posts from this blog

SAP SYBASE IQ Partitioned Tables This section help find list of Partitioned tables in IQ   List of all the Partitioned tables in IQ SQL1> select b.table_name,b.table_type,a.create_time,a.update_time,z.dbspace_name from sysiqtable a, systable b, sysfile z where z.file_id=b.file_id and a.table_id=b.table_id and a.table_id in (select distinct table_id from syspartitions)   Details of particular table which is partitioned SQL2> select partition_id,partition_values,partition_name from syspartitions where table_id in (select table_id from systable where table_name=' <table_name> ')
Read more
SAP Sybase IQ Index Fragmentation This section show the fragmentation on indexes of a table in IQ: (SQL: sp_iqindexfragmentation ('table <owner.table>') )” , this Stored procedure can be used to check the index fragmentation. Stored procedure reports information about the percentage of page space taken up within the B-trees, garrays, and bitmap structures in Sybase IQ indexes.   Note: All percentages are truncated to the nearest percentage point. HG indexes also display the value of option GARRAY_FILL_FACTOR_PERCENT . Index types that use a B-tree also display the number of node (nonleaf) pages. These are HG , LF , WD , DATE , and DTTM .   For garrays, the fill percentage calculation does not take into account the reserved space within the garray groups, which is controlled by the GARRAY_FILL_FACTOR_PERCENT option. It specifies the percent of space on each HG garray pages to reserve for future incremental inserts into existing groups. The garray t...
Read more
How to find Invalid views on SAP Sybase IQ database This section shows list of invalid view in IQ (SQL: “select U.user_name , T.table_name as object_name from sysobject O, systab T, sysuser U where T.object_id=O.object_id and U.user_id=T.creator and O.status=2 ”). select U . user_name + '.' + T . table_name from sysobject O , systab T , sysuser U where T . object_id = O . object_id and U . user_id = T . creator and O . status = 2 /* Invalid */ and O . object_type = 2 /* views */   SYSOBJECT status TINYINT The status of the object. Values include: 1 (valid) - The object is available for use by the database server. This status is synonymous with ENABLED. That is, if you ENABLE an object, the status changes to VALID. 2 (invalid) - An attempt to recompile the object after an internal operation has failed, for example, after a schema-altering modification to an object on which it depen...
Read more