SAP Netweaver Business Intelligence BI / BW

Symptom
You can use the procedure described in this note to run parallel update statistics as of MaxDB Version 7.6.05 patch level 8 and as of Version 7.7.04.
Other terms
UPDATE STATISTICS AS PER SYSTEM TABLE, parallel update statistics, PTS 1159867
Reason and Prerequisites
You use at least MaxDB Version 7.6.05 patch level 8.
This function is also available in MaxDB Version 7.7 as of Support Package 04.
Solution
As of MaxDB Version 7.6.05 patch level 8 or Version 7.7.04, a new system table ‘SYSCHECKSTATISTICSLOG’ and a database (DB) procedure ‘SYSCHECKSTATISTICS’ is created in the MaxDB database instance when you load the system tables.
The DB procedure ‘SYSCHECKSTATISTICS’ is incorrect in Max DB versions 7.6.05 and 7.7.0. The procedure does not select all tables relevant for statistics for an Update Statistics.
You can find the corrected version of the DB procedure in the attachment for this note.
Proceed as follows to install the DB procedure:
Download the zip file on a host and unpack the file.Log on to the SQLSTudio as an SYSDBA user, for example SUPERDBA.Copy the entire text contents from the attached file to the SQLStudio window and use <F8> to execute the command sequence.
This command sequence deletes the old procedure and creates the new procedure.
1. DB procedure: SYSCHECKSTATISTICS
The DB procedure determines the change in size of the tables in the database by comparing the current size of the table in the system table ‘FILES’ with the size at the time of the last statistics creation in the system table ‘OPTIMIZERSTATISTICS’.
If a change in size, which makes it necessary to execute another update statistics run, is determined, these tables are entered in the system table ‘SYSUPDSTATWANTED’.
This response corresponds to the action UPD_CHECK known in DB13. However, this new call has not yet been implemented in DB13.2. Executing the DB procedure:
To carry out this check using the DB procedure ‘SYSCHECKSTATISTICS’, you must log on to the database (for example, with SQLSTUDIO or SQLCLI) as the DBA user for whose tables you want to update the statistics (for example, SAP<SID>). For the logon, SQLCLI can use Xuser entries so that you can also use CRON (for example) to schedule the execution of the DB procedure.
The DB procedure expects an input parameter that specifies how big the change in size (in %) must be for a table to be entered as relevant for statistics in the system table ‘SYSUPDSTATWANTED’. The required value is specified in parentheses when you call the DB procedure.
We recommend that you enter a deviation of 40%.
Start the DB procedure as follows:
CALL SUPERDBA.SYSCHECKSTATISTICS(40)
We recommend that you execute the DB procedure in autocommit mode. Otherwise, you must execute a COMMIT after executing the DB procedure.3. System table SYSCHECKSTATISTICSLOG
After calling the DB procedure, the system table ‘SYSCHECKSTATISTICSLOG’ shows how many tables were entered in the system table ‘SYSUPDSTATWANTED’.4. Update statistics
You can run the update statistics in SQLSTUDIO or using SQLCLI.
To update the statistics for the tables that were entered in the system table ‘SYSUPDSTATWANTED’, you must log on to the database as SYSDBA (for example, SUPERDBA).
Execute the following SQL statement:
UPDATE STATISTICS AS PER SYSTEM TABLE [ESTIMATE]
The keyword ‘ESTIMATE’ causes the database to estimate the statistics using the sample value that is defined in the system table domain.tables.
If you do NOT specify the keyword ‘ESTIMATE’ for ‘UPDATE STATISTICS AS PER SYSTEM TABLE’, the database determines the statistics with an accuracy of 100%. This procedure leads to a higher read load and a longer runtime.
Until further notice, we recommend that you do not use the option ‘ESTIMATE’ (reason: PTS 1150235).
If you use the procedure described in this note, the statistics are created using several parallel processes (server tasks).