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

or

select statement_string, execution_count, total_execution_time from m_sql_plan_cache where statement_string like ‘%BSEG%’ order by execution_count desc

or

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
from “SYS”.”OBJECT_DEPENDENCIES”
where DEPENDENT_OBJECT_TYPE in (‘VIEW’,’TRIGGER’,’PROCEDURE’) and BASE_SCHEMA_NAME like ‘SAP%’) D
right join
(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!