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
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
Find tables which are portioned
•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 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
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.