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

Enable and Disable Trace in SAP Sybase IQ database Enable Trace in IQ DB   1.   Check the table meta info in dbisql:     sp_iqobjectinfo [owner],[table_name];   2.   Enable SQL or Query Plan generator – On the load target IQ server – run these commands with DBA: create a temp folder to save trace and logs:     mkdir /tmp/log_iqload   SQL Trace: in dbisql:     call sa_server_option('request_level_logging', 'SQL');     call sa_server_option('request_level_log_file','/tmp/log_iqload/sqltrace.log');   Query plan: in dbisql:     set option query_plan='on';     set option query_detail='on';     set option query_plan_as_html = 'on';     set option dml_options10='on';     set option query_plan_after_run='on';     set option query_name ='load_plan';     set option query_plan_text_caching='on'     set option query_plan_as_html_dire
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
SAP SYBASE IQ Index Advisor sp_iqindexadvice Procedure Displays stored index advice messages. Optionally clears advice storage. SQL> select * from sa_conn_options() where OptionName like '%Index_Adv%' and number=connection_property ('Number') Enable Index Advisor in IQ DB   Option1: 1.       SET OPTION index_advisor = 'ON'; 2.       SET OPTION index_advisor_max_rows = 100; 3.       commit; 4.       <query to be analyzed>; 5.       call sp_iqindexadvice ();   Option2: 1.       set temporary option INDEX_ADVISOR = on; 2.       set temporary option INDEX_ADVISOR_MAX_ROWS = 20; 3.       commit; 4.       -- sql statements 5.       call sp_iqindexadvice (); The above code does two things for you- 1. It tells you that your option are set correctly. 2. If table structure is not optimal relative to query, the advisor should give some advice.
Read more