Starting Point
The query optimizer uses statistics to select the best query plan. The query optimizer may be unable to choose the best query plan if certain statistics are missing. If database statistics for HANA Virtual Tables have not yet been created, query performance on BW Providers using Smart Data Access may not be at its best.
In case you are using BW Near-Line Storage (cold store) with HANA Smart Data Access and want to know which standard measures can be taken to ensure best-possible performance of your BW queries.
This ABAP report can be used in some of the following scenarios:
- In case you are using NLS for archiving cold DATA and on top running several reports on that cold data then you might need proper statistics on the virtual IQ tables.
- Incase your query plan is not optimal then likely statistics are not created for the HANA Virtual table.
- Incase you have slow query performance on archived Infocube.
- Incase you have a BW query scenario reading from an Open ODS Provider with "Virtual table using HANA Smart Data Access".
RSSDA_CREATE_TABLE_STAT
RSSDA_CREATE_TABLE_STAT is a standard Executable ABAP Report available within your SAP system. This report can be used to create database statistics for HANA Virtual Tables. HANA Virtual Tables are used in the context of HANA Smart Data Access. In order to create a suitable query execution plan, HANA should have database statistics for the virtual table. At least the number of records of the source table should be known to HANA.
The report should be executed after the Data Archiving Process has finished and the data in the NLS is visible for reporting. The update of statistics is not required after each run of the Data Archiving Process, but only if the archived data volume has changed significantly.
The ABAP Program can be embedded in a BW process chain using process type "Execute ABAP Program" or "Creation of Database Statistics for Virtual Tables".
Integration
In SAP BW/4HANA, virtual tables are used for Open ODS Views based on Smart Data Access and for cold stores or near-line storages with HANA Smart Data Access. In addition, virtual tables are used in the context of a BW DataSource of HANA Source Systems.
PREREQUISITES
There are some prerequisites to use this report aswell:
- The user must be authorized to do maintenance on the relevant InfoProvider (S_RS_ADSO or S_RS_ODSV).
The Report endorses the following InfoProviders:
- Open ODS View with HANA Smart Data Access
- InfoCubes and Data store objects (Classic & ADSO), for which a Data Archiving Process to IQ Near-Line Storage is defined. Additionally, reporting makes use of Smart Data Access.
Precautions
- Creation of database statistics can cause a significant workload on the source system, especially if tables are large.
- The most inexpensive way is create simple statistics on one field only, ideally one with low cardinality.
Options in Report
Go To > Transaction SE38
(1) Infoprovider
Select an InfoProvider that meets the above-mentioned prerequisites.
(2) DataSource:
Enter a BW Source System and DataSource (Type DB Connect or HANA DataSource) which makes use of Smart Data Access.
(3) Type of Statistics:
Choose the statistics type:
Histogram
Creates a data statistics object that helps the query optimizer estimate the data distribution in a single-column data source. If you specify multiple columns in <data_sources>, then multiple data statistics objects (HISTOGRAM) are created--one per column specified.
- To more accurately assess the costs, HANA can optionally generate histogram statistics. Please be aware that this option generates more effort than using only plain statistics.
- The HANA SQL Optimizer receives the most detailed facts via histogram statistics.
- Histogram computation typically involves more workload than simple statistics. However in some circumstances, histogram statistics generation could be quicker than plain statistic. This is dependent on the data distribution and source database, and it needs to be tested in the client environment.
- If incase the creation of statistics of type SIMPLE take longer than those of type HISTOGRAM, then refer to SAP note 2965586
- With histograms: The statistics can be accessed with HANA System View DATA_STATISTICS
SIMPLE
Creates a data statistics object that helps the query optimizer calculate basic statistics, such as min, max, null count, count, and distinct count for a single-column data source. If you specify multiple columns in <data_sources>, then multiple data statistics objects are created--one per column specified.
If you are using this option and you notice the creation of statistics of type SIMPLE take longer than those of type HISTOGRAM then try out the suggestion as per SAP note 2965586.
RECORD COUNT
Creates a data statistics object that helps the query optimizer calculate the number of records (rows) in a table data source. (As of HANA SP10, a new statistics type RECORD COUNT is available for virtual tables. RECORD COUNT specifies that only the number of records is computed).
When compared to the other types of statistics, such as SIMPLE or HISTOGRAM, this type should compute substantially faster. However, the HANA Query Optimizer has less data when it comes to query execution, which could result in less optimal query execution. This type should therefore only be used in case it's too expensive to create SIMPLE or HISTOGRAM statistics.
For more details, please feel free to check out SAP note 1990181 & 2100962 & 2198386.
Hope you find this information useful.!!
- SAP Managed Tags:
2 Comments
