Tuesday, 15 September 2020 03:42

How to find the list of SQL statements running on a given table

Written by Rajarajeswari Kaliyaperuumal
Rate this item
(0 votes)
“© 2020. SAP SE or an SAP affiliate company. All rights reserved.” “Used with permission of SAP SE”

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%’ orderby total_execution_time desc

or

select statement_string, execution_count, total_execution_time from m_sql_plan_cache where statement_string like ‘%BSEG%’ orderby 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!

Read 54 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.