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!