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: 
former_member182307
Contributor
0 Kudos
758

Data sources... Where to find the data?


Blog series starts here

Well ... It all started with the need for me to pull some performance/capacity data out of HANA. I needed to build up some HANA performance review report.

In Hana, you'll find several performance views/tables available for you to evaluate the performance of the Hana system.

All the data gets collected automatically by the HDB system ( statistics server whether embedded or not ) which is a great thing.

The main ones I'm pulling the data from are :

 






















































TABLE / VIEW RELATED INFO FROM SAP HELP REPORTING USAGE


M_SQL_PLAN_CACHE

M_SQL_PLAN_CACHE_RESET

SYS_STATISTICS.HOST_SQL_PLAN_CACHE


M_SQL_PLAN_CACHE

M_SQL_PLAN_CACHE_RESET

HOST_SQL_PLAN_CACHE
The data extracted from these views will be used to build the dataset for HANA workload and runtimes


M_EXPENSIVE_STATEMENTS

M_SQL_CLIENT_NETWORK_IO


M_EXPENSIVE_STATEMENTS

M_SQL_CLIENT_NETWORK_IO


Provided that the expensive statements trace is activated on your HDB, you'll be able to use these to build some Application usage tops based on runtimes.

If the expensive statement trace is not activated then the M_EXPENSIVE_STATEMENTS view does not get populated.


HOST_HEAP_ALLOCATORS

HOST_COLUMN_TABLES_PART_SIZE

GLOBAL_ROWSTORE_TABLES_SIZE

HOST_RS_INDEXES


HOST_HEAP_ALLOCATORS

HOST_COLUMN_TABLES_PART_SIZE

GLOBAL_ROWSTORE_TABLES_SIZE

HOST_RS_INDEXES


Memory areas usage will be pulled out from these.

 
M_CS_ALL_COLUMNS M_CS_ALL_COLUMNS Dataset for Tables tops
GLOBAL_TABLE_PERSISTENCE_STATISTICS GLOBAL_TABLE_PERSISTENCE_STATISTICS Tables Growth and shrinks ( disk usage ) can be reviewed based on these views.


M_TABLE_VIRTUAL_FILES

M_RS_TABLES

M_TABLE_LOB_FILES


M_TABLE_VIRTUAL_FILES

M_RS_TABLES

M_TABLE_LOB_FILES
Tables usage on disk


M_SERVICE_REPLICATION

HOST_SERVICE_REPLICATION


M_SERVICE_REPLICATION

HOST_SERVICE_REPLICATION
Dataset for system repllication


SYS_STATISTICS.global_disks

M_DISKS

M_DISK_USAGE


global_disks

M_DISKS

M_DISK_USAGE
Dataset for disks usage


M_BACKUP_CATALOG

M_BACKUP_CATALOG_FILES


M_BACKUP_CATALOG

M_BACKUP_CATALOG_FILES
Dataset for backup perrformance review


 

These are only a subset of the overall available system views of course. A list of all monitoring views is available in SAP help here , and SYS_STATISTICS views can be found there. I'll use some others depending on the reporting needs.

Most of the views persist the data for a defined time length. For example, views/tables managed by the statistics server are persisted for 42days as a default.

But in my case, I'll need to keep this information for a longer period of time ( like 2 years for example ) and also, I won't use the raw data extracted from these system views.

So I will create my own specific schema and set of tables in order to persist the data as long as I want and have it in the format I like.

The benefit I see here is I'll have the hand on this dataset without modifying anything in terms of system configuration, and also, I avoid the risk of deleting data from system views. Last thing: I hope it gives me room to play around with data aging!

The drawback is that I'll kind of have the data stored twice ( for the first 42 days let's say ).

Last but not least, this will allow me to learn how tables/objects get created in hana by the way :).

 

Additional objects required


As I'll have my own schema, I'll need objects in order to manage that additional metrics datamart :

  • Tables of course, in which the processed data metrics will be persisted,

  • Stored procedure to format, load the data and also perform the housekeeping tasks,

  • Jobs to perform the regular update and maintenance tasks for the data.


 

Next step


Create the required objects in HANA to build our data mart.

BOBJ & HANA for Basis (3) …

 

 

 

 

 

 
Labels in this area