Lightweight SQL Offender (LSO)
====================================================================================================================================

Lightweight SQL Offender (LSO) is a tool to list offending queries using direct 
access to the RMS infrastructure via SQL.  In this context, "offending" means 
that the query is consuming CPU resources during the interval between two user-
selected points of time. The queries that begin and end during this interval 
are not listed as offending. There is an offender for a CPU resource:

a)	CPU_ OFFENDER

Also, there are two types of memory offenders, to list the processes that have 
consumed memory resources exceeding over a certain threshold. These are:

a)	MEM_OFFENDER
b)	WM_MEM_OFFENDER

In addition, we have several types of filtering mechanism based on query execution 
time and/or state that can help the DBA and/or support personnel in analyzing 
the live state of the Trafodion instance for problems. These are:

a)	QUERIES_IN_SQL
b)	QUERIES_IN_CLIENT
c)	ACTIVE_QUERIES
d)	INACTIVE_QUERIES
e)	DEAD_QUERIES
f)	UNMONITORED_QUERIES
g)      SE_BLOCKED_QUERIES
h)      SE_OFFENDER_QUERIES
i)      QUERIES_IN_COMPILE

All dynamic SQL queries including child queries can be monitored using this 
mechanism, because it uses direct access to the RMS infrastructure. RMS 
collects the execution statistics for all dynamic queries except for some SQL 
statements like CQD, SET which are not instrumented. The un-instrumented 
queries can also be listed via UNMONITORED_QUERIES filtering mechanism.

There is no need to merge or gather the statistics of the individual queries 
to use LSO, the RMS infrastructure manages the history. There is no latency in 
the LSO mechanism because it is based on live RMS data. The filtering 
mechanism can also be limited to a particular node.

LSO is available through the shell script offender that can be executed on 
platform.
          
Here is the help from the offender script:
    >offender
    This script runs a Lightweight SQL Offender script
    syntax:  -s|--script <script-name> [-t|--time <time>] [-n|--node <node>] 
             [-h|--help] [-m | --memsize <size in MB>]
 
    Either the short form (-x) or long form (--name) of the parameters can be 
           used

    -s|--script Manditory. <script-name> must be one of:
                          cpu_offender
                          mem_offender
                          wm_mem_offender
                          active
                          se_blocked
                          inactive
                          in_sql
                          in_client
                          unmonitored
                          dead
                          in_compile

   -t|--time   Optional Default 30
               values: 0 < <time> < 32768 

               Time span (in seconds) to search for matching offenders.  
               Applies to scripts: active, dead,inactive,in_sql,in_client,unmonitored

  -m|--memsize Optional Default 250 
                Processes exceeding the given memory threshold in MB
                Applies to mem_offender and wm_mem_offender

   -n|--node   Optional Default ALL NODES
               values: -1 < <node> < 2048
               Restricts the query to the given SQL node number 

  -h|--help   Display this text and exit

It is also possible to run the queries in these obey files directly in hpdci or 
any other SQL program.


CPU OFFENDER
============

CPU offender lists the queries that are consuming CPU resource (offending) in 
other SQL executor processes since the previous user selected point of time. 
So, you have to execute the query at least twice to get results. The time 
between executions establishes the time window.  Results, if they exist, will
 appear after the second execution. The SQL commands to list CPU offenders are 
available at:

$TRAF_HOME/export/limited-support-tools/LSO/cpu_offender.sql

To use these queries you can obey these scripts in trafci or sqlci. The first 
time the offender script is obeyed, the results should be ignored, because 
obeying the script the first time is for the purpose of establishing the 
beginning of the user-selected interval. Queries listed in the second and 
subsequent runs of the OBEY file have been 'offending' since the previous run.

Example syntax to run the queries in trafci is shown below:

obey se_offender.sql ;  -- start the interval and ignore the results.
delay 30;
obey se_offender.sql ;  -- Queries listed are offenders.

obey cpu_offender.sql;  -- start the interval and ignore the results.
delay 30;
obey cpu_offender.sql;  -- Queries listed are offenders.


 Example output is shown below:
 CURRENT_TIMESTAMP           NO_OF_PROCESSES       CPU_TIME              QUERY_ID 
 --------------------------  --------------------  --------------------  ---------------------------------------------------------------------
 2013-02-22 16:22:07.851039                     1                    72  MXID11000030566212228308799335222000000000206U6553500_105_null
 2013-02-22 16:22:07.851039                     1               1273123  MXID11000031218212228308814844995000000000206U6553500_60_SQL_CUR_2
 

Memory Offenders - MEM_OFFENDER and WM_MEM_OFFENDER
===================================================

These reports include each process identified as a memory offender. Listed for 
each process are the node and process IDs, as well as the following four 
counters, given in megabytes:

* EXE_MEM_HIGH_WM_MB  - “high water mark” for executor memory – 
                        the maximum executor memory ever allocated by the 
                          process.
• EXE_MEM_ALLOC_MB    – the amount of executor memory currently allocated.
• IPC_MEM_HIGH_WM_MB  - “high water mark” for IPC memory – the maximum IPC 
                        memory ever allocated by the process.
• IPC_MEM_ALLOC_MB    – the amount of IPC memory currently allocated.

The MEM_OFFENDER processes are those which have currently allocated memory for
executor and IPC above the given threshold, specified in megabytes. 
The WM_MEM_OFFENDER processes are those which have ever allocated memory for
executor and IPC above the given threshold, specified in megabytes. 

All of a process’s allocated queries are included in these calculations. 

The SQL command to list the memory offenders are avilable at
$TRAF_HOME/export/limited-support-tools/LSO/mem_offender.sql

Example output is shown below:
CURRENT_TIMESTAMP           NODE         PID          EXE_MEM_HIGH_WM_MB  EXE_MEM_ALLOC_MB  IPC_MEM_HIGH_WM_MB  IPC_MEM_ALLOC_MB
--------------------------  -----------  -----------  ------------------  ----------------  ------------------  ----------------
2013-08-13 23:56:57.962287            2        14692                  80                10                   4                 1
2013-08-13 23:56:57.962287            3        28636                  79                10                   4                 1
2013-08-13 23:56:57.962287            1        10419                  81                10                   4                 0
2013-08-13 23:56:57.962287            0         2734                  79                10                   4                 1
2013-08-13 23:56:57.962287            3        28634                  82                10                   4                 1
2013-08-13 23:56:57.962287            0        10392                  79                10                   4                 0


QUERIES BLOCKED IN SQL 
======================

This filtering mechanism lists the queries that are currently blocked in the 
SQL Engine (either while executing the query. or fetching the output row) 
longer than the given number of seconds.  

The SQL commands to list queries blocked in SQL are available at:
$TRAF_HOME/export/limited-support-tools/LSO/queries_in_sql.sql

Example output is shown below:
CURRENT_TIMESTAMP           TIME_IN_SECONDS       QUERY_ID                                                                SOURCE_TEXT                         
-------------------------  --------------------  ----------------------------------------------------------------------- --------------------------------------------   
013-02-22 16:29:03.842781                   623  MXID11000031218212228308814844995000000000206U6553500_60_SQL_CUR_2      "select [last 0] * from t113a A, t113a B


QUERIES BLOCKED IN CLIENT
=========================

This filtering mechanism lists the querie s which are not blocked in SQL longer
than the given number of seconds and haven't finished execution. 

The SQL commands to list queries blocked in the client are available at:
$TRAF_HOME/export/limited-support-tools/LSO/queries_in_client.sql      

Example output is below:   
CURRENT_TIMESTAMP           TIME_IN_SECONDS       QUERY_ID                                                                      EXECUTE_STATE   SOURCE_TEXT   
-------------------------  --------------------  ----------------------------------------------------------------------------  --------------- ------------------------------------------------------------------------------------------------------------------------------                                               
013-02-22 16:29:30.430629                  1772  MXID11000030466212228308797313024000000000106U6553500_5_SQL_DATASRC_Q1        FETCH           "select translate(nid.obj_name using UCS2TOUTF8), nid.obj_id, asd.default_type, asd.automation, dsn.max_srvr_cnt, dsn.avail_sr                                               
013-02-22 16:29:30.430629                  1772  MXID11000030466212228308797313024000000000106U6553500_7_SQL_DATASRC_Q7        FETCH           "select variable_sequence, variable_type, translate(variable_value using UCS2TOUTF8) from HP_SYSTEM_CATALOG.MXCS_SCHEMA.NAME2I                                               
013-02-22 16:29:30.430629                  1772  MXID11000030466212228308797313024000000000106U6553500_6_SQL_ENV_Q6            FETCH           "select cast(COUNT(*) as int) from HP_SYSTEM_CATALOG.MXCS_SCHEMA.ENVIRONMENTVALUES where env_id = cast(? as integer) FOR READ
 

ACTIVE QUERIES
================

This script lists queries which either have finished executing within the
given number of seconds or still being executed. The negative number in 
LAST_ACTIVITY_SECS means that the query has completed that many seconds ago. 

CURRENT_TIMESTAMP           LAST_ACTIVITY_SECS    QUERY_ID                                                        EXEC_STATE                SOURCE_TEXT
--------------------------  --------------------  -------------------------------------------------------------   ------------------------  ---------------------------------------------------------------------------
2015-08-14 01:25:46.040274                   -18  MXID11000008455212306275447582585000000000206U3333300_175_S1    CLOSE                     "upsert into t12 values (1,1,1) ; 


INACTIVE QUERIES
================

This script lists allocated, prepared queries which either have finished 
executing, or are not yet executed. If the query is not yet executed, it will
be selected only if it was compiled the given number of seconds ago. Similarly, 
if the query has been executed, it will be selected only if it finished 
executing the given number of seconds ago. Dead queries, explained in the next 
section, are not selected. 

The SQL commands to list inactive queries are available at:
$TRAF_HOME/export/limited-support-tools/LSO/inactive_queries.sql

Example output is below: 
CURRENT_TIMESTAMP           LAST_ACTIVITY_SECS    QUERY_ID                                                                              SOURCE_TEXT 
-------------------------  --------------------  ------------------------------------------------------------------------------------  -------------------------------------------------------------
013-02-22 16:27:46.886517                   163  MXID11001029303212228308782097044000000000106U6553500_2_STMT_QSOFFND_CANARY_QUERY     "SELECT ROW COUNT FROM MANAGEABILITY.NWMS_SCHEMA.WMS_CANARY 
013-02-22 16:27:46.886517                    25  MXID11000030566212228308799335222000000000206U6553500_131_SQL_CUR_2                   "purgedata o_table


DEAD QUERIES
============

This script lists queries that have not been de-allocated by the master 
executor process when it exited (normally or abnormally), and which have 
persisted after the process exit for a period longer than the given number of 
seconds.

The SQL commands to list dead queries are available at:
$TRAF_HOME/export/limited-support-tools/LSO/dead_queries.sql

Example output is below:
CURRENT_TIMESTAMP           LAST_ACTIVITY_SECS    BLOCKED_IN_SQL        BLOCKED_IN_CLIENT     QUERY_ID                                                      SOURCE_TEXT                
-------------------------  --------------------  --------------------  --------------------  -------------------------------------------------------------  ----------------------------
013-02-22 16:27:06.468808                    63                     0                     0  MXID11000031798212228310325451355000000000206U6553500_21_A     "select * from o_table;


UNMONITORED QUERIES
===================

This script lists the queries for which the statistics collection type is 
set to NO_STATS or ALL_STATS, either by SQL engine or by the user. 

The SQL commands to list UNMONITORED queries are available at:
$TRAF_HOME/export/limited-support-tools/LSO/uninstrumented.sql  

Example output is below:
CURRENT_TIMESTAMP           LAST_ACTIVITY_SECS    QUERY_TYPE            QUERY_SUBTYPE    QUERY_STATS_TYPE   QUERY_ID                                                                               SOURCE_TEXT
-------------------------  --------------------  --------------------- ---------------  ------------------ -------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------                                                                    
013-02-22 16:28:27.094424                  1410  SQL_OTHER             SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_47_STMT_COMMIT_1                 "COMMIT WORK
013-02-22 16:28:27.094424                  1410  SQL_CONTROL           SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_56_STMT_QRYSTS_ON_1              "control query default showcontrol_unexternalized_attrs 'OFF 
013-02-22 16:28:27.094424                  1410  SQL_SET_TRANSACTION   SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_48_STMT_TRANS_ON_1               "SET TRANSACTION AUTOCOMMIT ON
013-02-22 16:28:27.094424                  1410  SQL_OTHER             SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_46_STMT_ROLLBACK_1               "ROLLBACK WORK               
013-02-22 16:28:27.094424                  1410  SQL_CONTROL           SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_38_STMT_HIGH_PRI                 "CONTROL TABLE HP_SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEPOLICIES PRIORITY '9'
013-02-22 16:28:27.094424                  1410  SQL_SELECT_NON_UNIQUE SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_50_STMT_INTERNAL_STATISTICS      "select variable_info from table(statistics(null,_ISO88591'MERGE=2,QID=CURRENT')) 
013-02-22 16:28:27.094424                  1410  SQL_SET_TRANSACTION   SQL_STMT_NA      NO_STATS           MXID11000030566212228308799335222000000000206U6553500_49_STMT_TRANS_OFF_1              "SET TRANSACTION AUTOCOMMIT OFF
013-02-22 16:28:27.094424                  1385  SQL_SET_TRANSACTION   SQL_STMT_NA      NO_STATS           MXID11000031218212228308814844995000000000206U6553500_49_STMT_TRANS_OFF_1              "SET TRANSACTION AUTOCOMMIT OFF
013-02-22 16:28:27.094424                  1385  SQL_CONTROL           SQL_STMT_NA      NO_STATS           MXID11000031218212228308814844995000000000206U6553500_56_STMT_QRYSTS_ON_1              "control query default showcontrol_unexternalized_attrs 'OFF'
013-02-22 16:28:27.094424                  1385  SQL_SELECT_NON_UNIQUE SQL_STMT_NA      NO_STATS           MXID11000031218212228308814844995000000000206U6553500_50_STMT_INTERNAL_STATISTICS      "select variable_info from table(statistics(null,_ISO88591'MERGE=2,QID=CURRENT'))   
013-02-22 16:28:27.094424                  1385  SQL_SET_TRANSACTION   SQL_STMT_NA      NO_STATS           MXID11000031218212228308814844995000000000206U6553500_48_STMT_TRANS_ON_1               "SET TRANSACTION AUTOCOMMIT ON
013-02-22 16:28:27.094424                  1385  SQL_OTHER             SQL_STMT_NA      NO_STATS           MXID11000031218212228308814844995000000000206U6553500_47_STMT_COMMIT_1                 "COMMIT WORK

SE_BLOCKED_QUERIES
==================

This script lists queries which are blocked in the storage engine APIs 
for more than given number of seconds. The BLOCKED_FOR_SECS denotes how 
long the storage engine API is blocked.

The SQL commands to list SE_BLOCKED queries are available at:
$TRAF_HOME/export/limited-support-tools/LSO/se_blocked.sql  

Example output is below:
CURRENT_TIMESTAMP           NO_OF_PROCESSES  BLOCKED_FOR_SECS   QUERY_ID                                                      TABLE_NAME
--------------------------  ---------------  ----------------  ------------------------------------------------------------  ------------------------------

2016-12-28 10:29:43.941455                1                83  MXID11000030514212349680799580002000000000206U3333300_18_S1   TRAFODION.SCH.T022   

SE_OFFENDING_QUERIES
====================

This script lists queries which has a total IO time of any operator 
accessing the storage engine longer than the given number of seconds

The SQL commands to list SE_BLOCKED queries are available at:
$TRAF_HOME/export/limited-support-tools/LSO/se_offender.sql  

CURRENT_TIMESTAMP           TOTAL_IO_TIME_IN_SECS  PROCESS_ID   QUERY_ID                                                                             TABLE_NAME
--------------------------  ---------------------  ------------  -----------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------

2017-01-18 14:20:03.604532                 1         000, 06390  MXID11000026007212351477539057002000000000206U3333300_107___SQLCI_DML_LAST__         TRAFODION.SELVA.CUSTOMER 
2017-01-18 14:20:03.604532                 1         001, 06391  MXID11000026007212351477539057002000000000206U3333300_107___SQLCI_DML_LAST__         TRAFODION.SELVA.CUSTOMER

QUERIES_IN_COMPILE
==================

This script lists queries that are being compiled for more than the given number of seconds

CURRENT_TIMESTAMP           TIME_IN_SECONDS       NODE         PID          QUERY_ID   `                                                                  SOURCE_TEXT
--------------------------  --------------------  -----------  -----------  ----------------------------------------------------------------------------  ----------------------

2017-07-15 01:54:14.909780                  1860            0         5505  MXID11000005505212366841736965000000000000206U3333300_278___SQLCI_DML_LAST__ "select * from partsupp ;                                                                                                                                                                         

LIMITING THE OUTPUT TO A NODE
=============================

To limit any of the filter queries to a particular node, append ',CPU=<node_no>'
to the second parameter for the statistics table function. For example:

select
<rest of query>
from table (statistics(NULL,'QUERIES_IN_SQL=30,CPU=1'))
<rest of query>
