on 09-26-2012 10:08 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.