Technical Articles
How to find SQL statement from SQL Hash
SQL statement Vs SQL’s Statement HASH:
In HANA, in many places we would have seen the column called STATEMENT_HASH,STATEMENT_ID etc .Hence if we want to find out the underlying SQL associated with the STATEMENT_HASH below information will help you .
Note: 1 STATEMENT_HAS can have n number of associated STATEMENT_ID. Hence always go with STATEMENT_HASH for troubleshooting purpose.
Tables involved: M_SQL_PLAN_CACHE (main preference) or M_EXPENSIVE_STATEMENTS or M_EXECUTED_STATEMENTS
Way 1:
select * from M_SQL_PLAN_CACHE where STATEMENT_HASH=’6db9403adbbea07f6e4453dcd7bd1954′;
//similar query can also be checked on m_expensive_statement and m_executed_statement
Here,the column value STATEMENT_STRING will help with the SQL associated with this statement hash. (Note:STATEMENT_STRING columns as LOB columns and hence to zoom or display we might have to change the preference in studio . Refer the oss note for the same =>2830573 – How to enable zoom of LOB columns)
Way 2:
Download and use the SQL script HANA_SQL_StatementHash_SQLText from OSS note 1969700 and modify the modification section it has provided.
Below is the sample screen shot from a RTE dump and it points to a STATEMENT_HASH.
Now we will download the specified SQL and modify the modification section .Here replace the statement hash for which we want to find the information and then run the sql in the system to get the associated SQL statement .
Output:The crash context says that the crash happened when the user was running this particular script and hence we have found the SQL involved.
Hope it was helpful!!
Click on like if you found this article useful and FOLLOW for more such articleshttps://people.sap.com/rajarajeswari_kaliyaperumal
Please leave a comment or suggestion!
Thanks Rajarajeswari for sharing the blog, this helped us to identify the query which was causing issue in production and after optimizing the query, we brought the ram utilization from 200GB to 45GB, previously due to usage of that query we suffered lot and system was in hung status most of the time and we have to do restart of production DB every time.
Hi Zameer,
Nice to hear and happy and proud that this helped 🙂
Regards
Raji K
Hi Rajeshwari,
Need your help on analyzing the issue, i f you have came through the issue in your HANA journey. Kindly help here. We are observing very high archive log generation in HANA DB and we are unable to identify the root cause for causing the high archive logs generation.
You know any standard HANA DB script is available to analyze the root cause for the issue or custom script which you have it handy or guide me to analyze this issue in proper way, in Oracle we will have Oracle log miner to analyze what is causing the issue, but don't know in HANA how to handle it.
in our environment the HANA DB is standalone DB with XSA applications running on top of it, no SAP Application layer(ECC,S/4 HANA, BW or BOBJ).
Your help will be appreciated.
Thanks and Regards
Zameer Ahamad
Hi Rajarajeswari,
Thanks for sharing this info.
I have a SQL has number, can I find the program name from this where it has been used?
Thanks and Regards,
Jaspal