SAP HANA XSA Find Invalidated Views,Functions,Procedures and Handy Queries

[narasingha]

Hello All,

I am writing this blog post for SAP HANA XSA SPS05 handy queries which are very useful when a developer is working in HANA XSA hana2sp05. I did not find a proper blog post in HANA to find invalidate Views,Procedures and how to delete them. Also I have mentioned very useful queries which will help developer to fine tune the DB and HANA objects.

These queries will be helpful to check HANA objects whether they are valid and also with the help of these queries we can analyze the performance. Here I am focusing on HANA Flowgraphs. For procedures I will write a separate BLOG on SQL plan and Visualize plan etc.

Please see the queries below

How to check Invalidated schemas, tasks, Views and procedures in HANA XSA. These issues normally occur after HANA upgrade may cause failures on production environment.Please note that these are not M tables. These are views in SYS schema. These invalidated views, procedures, functions and tasks can only be deleted with DELETE privilege's for a user on SYS Schema

Find Schema names which are invalid. These view can be found under SYS Schema as in below snapshot.

•Select distinct SCHEMA_NAME from VIEWS where IS_VALID != 'TRUE' order by SCHEMA_NAME;

• Find the Views which are invalid.

The invalidated “Data Preview” views, created by flowgraphs, remain in the containers if deployed one time with the Debug option enabled.Undeployment of the affected flowgraphs would not solve the problem.

•select * from VIEWS where IS_VALID != 'TRUE' order by SCHEMA_NAME;

Find the Procedures which are Invalid

•select * from PROCEDURES where IS_VALID != 'TRUE' order by SCHEMA_NAME;

Tasks or HANA flowgraphs which are invalid

•select * from TASKS where IS_VALID != 'TRUE order by SCHEMA_NAME;

Find HANA functions which are invalid.

• select * from FUNCTIONS where IS_VALID != 'TRUE' order by SCHEMA_NAME;

SAP OSS Comment

•Consider that containers may have invalidated views with flowgraph-specific names (namespace::flowgraph_node_CV) and ignore these objects while checking the system.

•Clean up invalidated views from the containers. This step will require specific privileges and after the clean-up the container must be deployed

Find SDI flowgraphs which are running on HANA XSA

•SELECT * from M_TASKS

Find which operation of flowgraph is consuming more memory or time. By checking this we can fine tune the HANA XSA SDI flowgraphs.

•select * from _sys_task.task_operations_executions where task_execution_id = 'TakeFromAbove' order by partition_id, start_time

Find active procedures in HANA XSA.

•select * from M_ACTIVE_PROCEDURES

• The below query will give us the exact Stored procedure which is running and Consuming How many GBs of space.

select round(s.allocated_memory_size/1024/1024/1024) as "Alloc Mem (GB)",

round(s.used_memory_size/1024/1024/1024) as "Used Mem (GB)",

s.statement_string,s.last_executed_time,

c.host, c.user_name, c.connection_status, c.transaction_id

from m_connections c, m_prepared_statements s

where s.connection_id = c.connection_id and c.connection_status != 'IDLE'

order by s.allocated_memory_size desc;

 

Find the Active Jobs which are running on HANA XSA. These jobs can be ALTER scripts, Merge Partitions or delta merge etc.

•SELECT * FROM M_JOB_PROGRESS

Find Active Threads in HANA XSA which are up and running

•select * from M_SERVICE_THREADS

How to find tables which are Partitioned 

•select * from "SYS"."PARTITIONED_TABLES"

How to check SQL plan cache for a statement

•select * from m_sql_plan_cache where "SCHEMA_NAME" = 'Put the Schema' and statement_string like '%PutValues' order by total_execution_time desc;

How to check the disk size in GB IN HANA XSA

•select "TABLE_NAME","DISK_SIZE",sum((DISK_SIZE)/1024/1021/1024) AS DISK_SIZE_IN_GB from "M_TABLE_PERSISTENCE_STATISTICS" where SCHEMA_NAME = 'PutVales' and TABLE_NAME like '%PutValues%'GROUP BY "TABLE_NAME","DISK_SIZE"order by DISK_SIZE desc

How to find total memory in HANA XSA DB

•select TABLE_NAME,sum(memory_size_in_total)/1024/1024/1024 as TotalMemGB, sum(memory_size_in_main)/1024/1024/1024 as TotalMaingb,sum(memory_size_in_delta)/1024/1024/1024 as TotalDeltaGB from M_CS_TABLES where TABLE_NAME like '%PutValues%' and SCHEMA_NAME= 'PutValues'group by TABLE_NAME

How to find expensive statements in HANA XSA DB

•SELECT * FROM M_EXPENSIVE_STATEMENTS

•select * from M_EXPENSIVE_STATEMENTS WHERE SCHEMA_NAME= PutValues AND OBJECT_NAME LIKE '%PutValues%'

How to find the persistent memory DISK size.

•select START_TIME,STATEMENT_STRING,OPERATION,round(MEMORY_SIZE/1024/1024/1024,2) as "Memory Size Gb",round(DURATION_MICROSEC/1000000,2) as "Seconds", round(CPU_TIME/1000000,2) as "CPU_TIME_IN_SECONDS" from M_EXPENSIVE_STATEMENTS

How to find record count of tables under a schema.

1st Find the Schema

SELECT * FROM "SYS"."SCHEMAS" where schema_name = 'XYZ'

Then

select "SCHEMA_NAME","TABLE_NAME","RECORD_COUNT","TABLE_SIZE",((("TABLE_SIZE"/1024)/1024)/1024) AS SIZE_IN_GB
from m_tables where schema_name = 'XYZ'

How to find the delta merge statistics.

in HANA delta tables are default compression only, then it chooses compression approach per column based on actual data sample and performs delta merge activity to move from delta memory to main memory.

SELECT * FROM M_DELTA_MERGE_STATISTICS

Check Index table for Index Name and Index Type 

Select * from INDEXES where SCHEMA_NAME='XUZ' and TABLE_NAME ='ABC';

To see How many Threads are Running on HANA DB.

select * from
M_SERVICE_THREADS  (SAP HANA Studio -> Administration -> Performance -> Threads DBACOCKPIT -> Performance -> Threads)

How to show all running sessions in sap hana

select host, port, connection_id as "c-id", connection_status as "status", connection_type as "c-type", transaction_id as "transac", idle_time, auto_commit as "autoc", client_host, user_name, current_schema_name as "schema", fetched_record_count as "rec-count" from m_connections where connection_status = 'RUNNING' order by connection_status;

How to get sap hana connection and transaction id?

Select host, port, connection_id as "connecti_id", connection_status as "status", connection_type as "c-type", transaction_id as "transact_id", idle_time, auto_commit as "autoc", client_host, user_name, current_schema_name as "schema", fetched_record_count as "rec-count" from m_connections order by connection_status;

 

Find the index serve parameter is set to what? This will help when an index server crash happens in HANA.

• select * from M_INIFILE_CONTENTS where "FILE_NAME" LIKE '%indexserver.ini%' and "VALUE" = 'true'

These queries will help basis guys or a developer to see the HANA DB's performance and will help on analysis to fine tune the Objects.

 

Thank You.

Narasingha