SYBASE - SAP IQ Performance Tuning

Monitor last time any object was accessed in SAP SYBASE IQ There is a way to find out the last time an object (table/view/st proc) was accessed by an application in IQ. You enable it, then can run reports against the data over time on things like table and index use, tables and indexes not used, etc.   sp_iqworkmon Procedure   It Controls collection of workload monitor usage information, and reports monitoring collection status. sp_iqworkmon collects information for all SQL statements.   Note: Usage is collected only for SQL statements containing a FROM clause; for example, SELECT , UPDATE , and DELETE .   See also * sp_iqcolumnuse * sp_iqindexadvice * sp_iqindexuse * sp_iqtableuse * sp_iqunusedcolumn * sp_iqunusedindex * sp_iqunusedtable

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     ...

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.    ...