open Threads and expensive jobs on hdbsql

[Former Member]

Hello,

could anyone tell me how I can find actually open threads and jobs on hdbsql?

When I do some expensive statements on HANA Studio - like "alter system reclaim log" - I can acces SAP HANA Studio no longer.

Tanks

Stefan

[lucas_oliveira]

Hi,

I guess you could look into M_SERVICE_THREADS and M_TABLE_LOCKS. Here is the SQL statement run every time you reach the Performance->Threads tab on HANA Studio:

SELECT "THREADS"."HOST",

       "THREADS"."PORT",

       "THREADS"."SERVICE_NAME",

       "THREADS"."HIERARCHY",

       "THREADS"."CONNECTION_ID",

       "THREADS"."THREAD_ID",

       "THREADS"."THREAD_TYPE",

       "THREADS"."THREAD_METHOD",

       "THREADS"."THREAD_DETAIL",

       "THREADS"."DURATION",

       "THREADS"."CALLER",

       "THREADS"."CALLING",

       "THREADS"."USER_NAME",

       "THREADS"."APPLICATION_USER_NAME",

       "CONN"."TRANSACTION_ID",

       "CONN"."START_TIME",

       "CONN"."IDLE_TIME",

       "CONN"."CONNECTION_STATUS",

       "CONN"."CLIENT_HOST",

       "CONN"."CLIENT_IP",

       "CONN"."CLIENT_PID",

       "CONN"."CONNECTION_TYPE",

       "CONN"."OWN",

       "CONN"."IS_HISTORY_SAVED",

       "CONN"."MEMORY_SIZE_PER_CONNECTION",

       "CONN"."AUTO_COMMIT",

       "CONN"."LAST_ACTION",

       "CONN"."CURRENT_STATEMENT_ID",

       "CONN"."CURRENT_OPERATOR_NAME",

       "CONN"."FETCHED_RECORD_COUNT",

       "CONN"."SENT_MESSAGE_SIZE",

       "CONN"."SENT_MESSAGE_COUNT",

       "CONN"."RECEIVED_MESSAGE_SIZE",

       "CONN"."RECEIVED_MESSAGE_COUNT",

       "CONN"."CREATOR_THREAD_ID",

       "CONN"."CREATED_BY",

       "CONN"."IS_ENCRYPTED",

       "CONN"."END_TIME",

       "BLOCKED"."BLOCKED_UPDATE_TRANSACTION_ID",

       "BLOCKED"."LOCK_OWNER_TRANSACTION_ID",

       "BLOCKED"."LOCK_OWNER_UPDATE_TRANSACTION_ID",

       "BLOCKED"."BLOCKED_TIME",

       "BLOCKED"."WAITING_RECORD_ID",

       "BLOCKED"."WAITING_SCHEMA_NAME",

       "BLOCKED"."WAITING_TABLE_NAME",

       "BLOCKED"."LOCK_TYPE",

       "BLOCKED"."LOCK_MODE"

FROM   PUBLIC.M_SERVICE_THREADS AS threads

       LEFT OUTER JOIN M_CONNECTIONS AS conn

                    ON threads.CONNECTION_ID = conn.CONNECTION_ID

       LEFT OUTER JOIN M_BLOCKED_TRANSACTIONS AS blocked

                    ON conn.TRANSACTION_ID = blocked.BLOCKED_TRANSACTION_ID

Quite informative and helpful.

Regards,

Lucas de Oliveira

[rama_shankar3]

Lucas:

Thanks for sharing. Here's a thought, we should create a DB view in HANA for this sql and query it as needed! Will try doing it.

Rama

[lucas_oliveira]

Hi Rama,

I understand that, but I guess the main point here is that administration should be done with HANA

Studio mainly. I know this situation is not the usual case so it might be good to keep the sql at hand when there's no other way to tackle the issue from Studio

Also, I believe you should look for the root cause. What could be choking the database on your landscape? I've seen many cases where SLT or DS are the ones to blame. Other than that, it's always good to keep up with the latest HANA revision to avoid issues already resolved.

Regards,

Lucas de Oliveira