How to find the list of SQL statements running on a given table
TABLE and SQLs running on this table
If you wanted to know how to find out the list of statements that got executed on any table within a week or so,and who executed it,when it got executed etc you can use the below SQL.
select statement_string, execution_count, total_execution_time from m_sql_plan_cache where statement_string like ‘%<TABLE_NAME>%’ order by total_execution_time desc;
=>Replace TABLE_NAME with the table name you want to check.
A.Find the list of SQLs that got executed in the system for a specific table
select statement_string, execution_count, total_execution_time from m_sql_plan_cache where statement_string like ‘%BSEG%’ order by total_execution_time desc
select statement_string, execution_count, total_execution_time from m_sql_plan_cache where statement_string like ‘%BSEG%’ order by execution_count desc
B.To find more details on User id, Time stamp,server etc.
NOTE: All these utput can als be got from querying M_EXPENSIVE_STATEMENTS like below .
select * from M_EXPENSIVE_STATEMENTS where STATEMENT_STRING like ‘%BSEG%’ ;
However, M_EXPENSIVE_STATEMENTS does not hold DDL language related data. This data can be found in another table called M_EXECUTED_STATEMENTS
C.Do you also want to find out list of dependent views active on any table ?
select * from SYS.OBJECT_DEPENDENCIES where BASE_OBJECT_NAME=’BSEG’
Or we can you the below statements to check the entire table present in HANA DB and their dependencies.
select SCHEMA_NAME,TABLE_NAME,BASE_SCHEMA_NAME,BASE_OBJECT_NAME,case when BASE_OBJECT_NAME is null then ‘No’ else ‘Yes’ end Dependancy_exists
from (select distinct BASE_SCHEMA_NAME,BASE_OBJECT_NAME
where DEPENDENT_OBJECT_TYPE in (‘VIEW’,’TRIGGER’,’PROCEDURE’) and BASE_SCHEMA_NAME like ‘SAP%’) D
(select * from SYS.TABLES where SCHEMA_NAME like ‘SAP%’) T
on D.BASE_SCHEMA_NAME=T.SCHEMA_NAME and D.BASE_OBJECT_NAME=T.TABLE_NAME
order by SCHEMA_NAME,table_name
Thanks for reading!
Follow for more such posts!http://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!