Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
rajarajeswari_kaliyaperum
Active Participant
0 Kudos

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!rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!
Labels in this area