Repository: incubator-trafodion
Updated Branches:
refs/heads/master 52608078d -> 4b4034e7d
[TRAFODION-1454] Unable to detect the query id via RMS while running the tools like ODB
Active query concept is now introduced to 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.
cd $MY_SQROOT/export/limited-support-tools/LSO
./offender -s active
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) ;
Also fixed a bug where TDBID_DETAIL stats(instance level stats) is not
being displayed for HBase access and HDFS scan operators when the query is
not yet finished.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/4b4034e7
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/4b4034e7
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/4b4034e7
Branch: refs/heads/master
Commit: 4b4034e7d7646d63cb2b40f52b9588bbd2932791
Parents: 5260807
Author: selvaganesang <se...@esgyn.com>
Authored: Fri Aug 14 17:16:15 2015 +0000
Committer: selvaganesang <se...@esgyn.com>
Committed: Fri Aug 14 22:01:15 2015 +0000
----------------------------------------------------------------------
.../sqf/export/limited-support-tools/LSO/README | 139 +++++++++----------
.../LSO/active_queries.sql | 17 +++
.../export/limited-support-tools/LSO/offender | 8 +-
core/sql/cli/sqlcli.h | 3 +-
core/sql/executor/ExStats.cpp | 52 +++++++
5 files changed, 140 insertions(+), 79 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/4b4034e7/core/sqf/export/limited-support-tools/LSO/README
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/README b/core/sqf/export/limited-support-tools/LSO/README
index b7934fe..3b4933d 100644
--- a/core/sqf/export/limited-support-tools/LSO/README
+++ b/core/sqf/export/limited-support-tools/LSO/README
@@ -15,15 +15,17 @@ consumed memory resources exceeding over a certain threshold. These are:
a) MEM_OFFENDER
b) WM_MEM_OFFENDER
-In addition, we have 5 types of filtering mechanism based on query execution
+In addition, we have 6 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) INACTIVE_QUERIES
-d) DEAD_QUERIES
-e) UNMONITORED_QUERIES
+c) ACTIVE_QUERIES
+d) INACTIVE_QUERIES
+e) DEAD_QUERIES
+f) UNMONITORED_QUERIES
+
All dynamic SQL queries including child queries can be monitored using this
mechanism, because it uses direct access to the RMS infrastructure. RMS
@@ -52,16 +54,17 @@ Here is the help from the offender script:
cpu_offender
mem_offender
wm_mem_offender
- dead
+ active
inactive
- unmonitored
in_sql
in_client
+ unmonitored
+ dead
-t|--time Optional Default 30
values: 0 < <time> < 32768
Time span (in seconds) to search for matching offenders.
- Applies to scripts: dead,inactive,in_sql,in_client,unmonitored
+ Applies to scripts: active, dead,inactive,in_sql,in_client,unmonitored
-m|--memsize Optional Default 250
Processes exceeding the given memory threshold in MB
@@ -78,6 +81,7 @@ 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.
@@ -106,15 +110,14 @@ 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
- --- 2 row(s) selected.
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
@@ -134,15 +137,13 @@ 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
+The SQL command to list the memory offenders are avilable at
$MY_SQROOT/export/limited-support-tools/LSO/mem_offender.sql
Example output is shown below:
-
-URRENT_TIMESTAMP NODE PID EXE_MEM_HIGH_WM_MB EXE_MEM_ALLOC_MB IPC_MEM_HIGH_WM_MB IPC_MEM_ALLOC_MB
+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
@@ -152,40 +153,52 @@ URRENT_TIMESTAMP NODE PID EXE_MEM_HIGH_WM_MB EXE_MEM
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:
+longer than the given number of seconds.
+The SQL commands to list queries blocked in SQL are available at:
$MY_SQROOT/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
+
-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
---- 1 row(s) selected.
-
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:
+than the given number of seconds and haven't finished execution.
+The SQL commands to list queries blocked in the client are available at:
$MY_SQROOT/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 statement 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) ;
-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
-013-02-22 16:29:30.430629 1772 MXID11000030466212228308797313024000000000106U6553500_4_SQL_DATASOURCE_Q8 FETCH "select cast(COUNT(*) as int) from HP_SYSTEM_CATALOG.MXCS_SCHEMA.DATASOURCES FOR READ UNCOMMITTED ACCESS
---- 4 row(s) selected.
INACTIVE QUERIES
+================
This statement lists allocated, prepared queries which either have finished
executing, or are not yet executed. If the query is not yet executed, it will
@@ -195,7 +208,6 @@ 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:
-
$MY_SQROOT/export/limited-support-tools/LSO/inactive_queries.sql
Example output is below:
@@ -204,75 +216,48 @@ CURRENT_TIMESTAMP LAST_ACTIVITY_SECS QUERY_ID
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
---- 2 row(s) selected.
DEAD QUERIES
+============
This statement 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:
-
+The SQL commands to list dead queries are available at:
$MY_SQROOT/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;
---- 1 row(s) selected.
+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 statement 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:
-
$MY_SQROOT/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
+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
-013-02-22 16:28:27.094424 1385 SQL_CONTROL SQL_STMT_NA NO_STATS MXID11000031218212228308814844995000000000206U6553500_38_STMT_HIGH_PRI "CONTROL TABLE HP_SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEPOLICIES PRIORITY '9'
-013-02-22 16:28:27.094424 1385 SQL_OTHER SQL_STMT_NA NO_STATS MXID11000031218212228308814844995000000000206U6553500_46_STMT_ROLLBACK_1 "ROLLBACK WORK
-013-02-22 16:28:27.094424 1340 SQL_SET_CATALOG SQL_STMT_NA NO_STATS MXID11000031218212228308814844995000000000206U6553500_58_STMT_INTERNAL_1 "SET CATALOG 'NEO'
-013-02-22 16:28:27.094424 1326 SQL_SET_CATALOG SQL_STMT_NA NO_STATS MXID11000030566212228308799335222000000000206U6553500_58_STMT_INTERNAL_1 "SET CATALOG 'NEO'
-013-02-22 16:28:27.094424 1248 SQL_SET_TRANSACTION SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_38_STMT_TRANS_ON_1 "SET TRANSACTION AUTOCOMMIT ON
-013-02-22 16:28:27.094424 1248 SQL_SET_TRANSACTION SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_39_STMT_TRANS_OFF_1 "SET TRANSACTION AUTOCOMMIT OFF
-013-02-22 16:28:27.094424 1248 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000106U6553500_18_STMT_QRYSTS_ON_1 "COMMIT WORK
-013-02-22 16:28:27.094424 1248 SQL_CONTROL SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_35_STMT_INTERNAL_1 "CONTROL QUERY DEFAULT * RESET RESET
-013-02-22 16:28:27.094424 1248 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_37_STMT_COMMIT_1 "COMMIT WORK
-013-02-22 16:28:27.094424 1248 SQL_CONTROL SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_28_STMT_HIGH_PRI "CONTROL TABLE HP_SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEPOLICIES PRIORITY '9'
-013-02-22 16:28:27.094424 1248 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_36_STMT_ROLLBACK_1 "ROLLBACK WORK
-013-02-22 16:28:27.094424 1248 SQL_SELECT_NON_UNIQUE SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_40_STMT_INTERNAL_STATISTICS "select variable_info from table(statistics(null,_ISO88591'MERGE=2,QID=CURRENT'))
-013-02-22 16:28:27.094424 437 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_109_SQL_CUR_62 "set parserflags 1;
-013-02-22 16:28:27.094424 416 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_146_SQL_CUR_92 "set parserflags 1;
-013-02-22 16:28:27.094424 406 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_158_SQL_CUR_102 "set parserflags 1;
-013-02-22 16:28:27.094424 391 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_170_SQL_CUR_112 "set parserflags 1;
-013-02-22 16:28:27.094424 382 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_182_SQL_CUR_122 "set parserflags 1;
-013-02-22 16:28:27.094424 315 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_194_SQL_CUR_132 "set parserflags 1;
-013-02-22 16:28:27.094424 240 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_205_SQL_CUR_142 "set parserflags 1;
-013-02-22 16:28:27.094424 229 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_216_SQL_CUR_152 "set parserflags 1;
-013-02-22 16:28:27.094424 225 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_227_SQL_CUR_162 "set parserflags 1;
-013-02-22 16:28:27.094424 219 SQL_OTHER SQL_STMT_NA NO_STATS MXID11001030558212228308799185024000000000206U6553500_238_SQL_CUR_172 "set parserflags 1;
-
---- 38 row(s) selected.
+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
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:
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/4b4034e7/core/sqf/export/limited-support-tools/LSO/active_queries.sql
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/active_queries.sql b/core/sqf/export/limited-support-tools/LSO/active_queries.sql
new file mode 100644
index 0000000..2a5c539
--- /dev/null
+++ b/core/sqf/export/limited-support-tools/LSO/active_queries.sql
@@ -0,0 +1,17 @@
+-- set param ?filter 'ACTIVE_QUERIES=30'; -- 30 seconds
+set param ?lsq ' sqlSrc: ';
+
+select current_timestamp "CURRENT_TIMESTAMP" -- (1) Now
+ ,cast(tokenstr('lastActivity:', variable_info) -- (2) Last Activity
+ as NUMERIC(18) ) LAST_ACTIVITY_SECS
+ ,cast(tokenstr('Qid:', variable_info) -- (3) QID
+ as varchar(175) CHARACTER SET UTF8) QUERY_ID
+ , cast(tokenstr('State:', variable_info) -- (4) State
+ as char(30)) EXECUTE_STATE
+ , cast(substr(variable_info, -- (5) SQL Source
+ position(?lsq in variable_info) + char_length(?lsq),
+ char_length(variable_info) -
+ ( position(?lsq in variable_info) + char_length(?lsq) ))
+ as char(256) CHARACTER SET UTF8) SOURCE_TEXT
+from table (statistics(NULL, ?filter))
+order by 2 descending;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/4b4034e7/core/sqf/export/limited-support-tools/LSO/offender
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/offender b/core/sqf/export/limited-support-tools/LSO/offender
index 5cee559..98e257f 100755
--- a/core/sqf/export/limited-support-tools/LSO/offender
+++ b/core/sqf/export/limited-support-tools/LSO/offender
@@ -43,10 +43,12 @@ function usage()
mem_offender
wm_mem_offender
dead
+ active
inactive
- unmonitored
in_sql
in_client
+ dead
+ unmonitored
-t|--time Optional Default $DEFAULT_TIME
values: 0 < <time> < $MAX_TIME_P1
@@ -172,6 +174,10 @@ case $SCRIPT in
PREAMBLE="set param ?filter 'INACTIVE_QUERIES=$TIME$NODE'; "
QUERY_FILE="$LSO_DIR/inactive_queries.sql"
;;
+ active)
+ PREAMBLE="set param ?filter 'ACTIVE_QUERIES=$TIME$NODE'; "
+ QUERY_FILE="$LSO_DIR/active_queries.sql"
+ ;;
unmonitored)
PREAMBLE="set param ?filter 'UNMONITORED_QUERIES=$TIME$NODE'; "
QUERY_FILE="$LSO_DIR/uninstrumented.sql"
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/4b4034e7/core/sql/cli/sqlcli.h
----------------------------------------------------------------------
diff --git a/core/sql/cli/sqlcli.h b/core/sql/cli/sqlcli.h
index 1715d0d..8b9fba3 100644
--- a/core/sql/cli/sqlcli.h
+++ b/core/sql/cli/sqlcli.h
@@ -963,7 +963,8 @@ enum STATS_SUB_REQ_TYPE {
SQLCLI_STATS_REQ_RMS_CHECK = 11,
SQLCLI_STATS_REQ_MEM_HIGH_WM = 12,
SQLCLI_STATS_REQ_MEM_ALLOC = 13,
- SQLCLI_STATS_REQ_PFS_USE = 14
+ SQLCLI_STATS_REQ_PFS_USE = 14,
+ SQLCLI_STATS_REQ_ACTIVE_QUERIES = 15
};
enum SCRATCH_OVERFLOW_MODE {
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/4b4034e7/core/sql/executor/ExStats.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ExStats.cpp b/core/sql/executor/ExStats.cpp
index faee21b..99e865c 100644
--- a/core/sql/executor/ExStats.cpp
+++ b/core/sql/executor/ExStats.cpp
@@ -8254,6 +8254,8 @@ NABoolean ExStatisticsArea::appendCpuStats(ExStatisticsArea *other,
ExUDRBaseStats *udrBaseStats;
ExMasterStats *masterStats;
ExProcessStats *processStats;
+ ExHbaseAccessStats *hbaseAccessStats;
+ ExHdfsScanStats *hdfsScanStats;
NABoolean retcode = FALSE;
ExOperStats::StatType statType;
ExOperStats *stat1;
@@ -8408,6 +8410,26 @@ NABoolean ExStatisticsArea::appendCpuStats(ExStatisticsArea *other,
retcode = TRUE;
}
break;
+ case ExOperStats::HDFSSCAN_STATS:
+ if (detailLevel_ == stat->getTdbId())
+ {
+ hdfsScanStats = new (getHeap()) ExHdfsScanStats(getHeap());
+ hdfsScanStats->setCollectStatsType(getCollectStatsType());
+ hdfsScanStats->copyContents((ExHdfsScanStats *)stat);
+ insert(hdfsScanStats);
+ retcode = TRUE;
+ }
+ break;
+ case ExOperStats::HBASE_ACCESS_STATS:
+ if (detailLevel_ == stat->getTdbId())
+ {
+ hbaseAccessStats = new (getHeap()) ExHbaseAccessStats(getHeap());
+ hbaseAccessStats->setCollectStatsType(getCollectStatsType());
+ hbaseAccessStats->copyContents((ExHbaseAccessStats *)stat);
+ insert(hbaseAccessStats);
+ retcode = TRUE;
+ }
+ break;
default:
break;
} // StatType case
@@ -10666,6 +10688,27 @@ NABoolean ExMasterStats::filterForCpuStats(short subReqType,
}
}
else
+ if (subReqType == SQLCLI_STATS_REQ_ACTIVE_QUERIES)
+ {
+ if (stmtState_ != Statement::PROCESS_ENDED_)
+ {
+ if (exeStartTime_ != -1) {
+ if (exeEndTime_ != -1)
+ tsToCompare = exeEndTime_;
+ else
+ tsToCompare = exeStartTime_;
+ lastActivity_ = (Int32)((currTimestamp-tsToCompare) / (Int64)1000000);
+ if (exeEndTime_ == -1)
+ return TRUE;
+ else
+ if (lastActivity_ <= etTimeInSecs) {
+ lastActivity_ = -lastActivity_;
+ retcode = TRUE;
+ }
+ }
+ }
+ }
+ else
if (subReqType == SQLCLI_STATS_REQ_QUERIES_IN_SQL)
{
if (exeStartTime_ != -1 && exeEndTime_ == -1 && isBlocking_)
@@ -10906,6 +10949,15 @@ Lng32 ExStatsTcb::str_parse_stmt_name(char *string, Lng32 len, char *nodeName,
retcode = SQLCLI_STATS_REQ_ET_OFFENDER;
}
else
+ if (strncasecmp(ptr, "ACTIVE_QUERIES", 14) == 0)
+ {
+ ptr = str_tok(NULL, ',', &internal);
+ etTemp = ptr;
+ etOffender = TRUE;
+ *subReqType = (short)SQLCLI_STATS_REQ_ACTIVE_QUERIES;
+ retcode = SQLCLI_STATS_REQ_ET_OFFENDER;
+ }
+ else
if (strncasecmp(ptr, "DEAD_QUERIES", 12) == 0)
{
ptr = str_tok(NULL, ',', &internal);