Skip to Content

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) …

 

 

 

 

 

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply