Skip to end of metadata
Go to start of metadata

SAP® MaxDB - Internal File Size Information (File Directory Counter)

 

This section gives detailed information about this functionality starting in SAP MaxDB version 7.6. The internal file size information (file directory counter) is used by the SQL optimizer to find the best strategy. If those file size information does not exist SAP MaxDB evaluation (single table optimizer) or optimize statistics (join)  is used to find the best strategy.

Moderator: Birgit Malik

WIKI Space Editor: Thiago Lüttig

 

 

Answers Internal File Size Questions

What's the internal file size information?

As of SAP MaxDB version 7.6. the internal file size information (file directory counter) is created automatically by the SAP MaxDB kernel. For each table, index exact size in KB and the exact number of entries is stored in the System table FILES. For tables, this system table shows the size of all LOB values referenced as well.
back to top

When is the internal file size information created?

After an upgrade from a version lower than 7.6 to version 7.6 or higher, the internal file size information (file directory counter) must be determined once for each object, which is done by a server task when the system load is low. The server task is counting the entries and sizes for all objects that are listed in the System table SYSUPDATECOUNTERWANTED. When system table SYSUPDATECOUNTERWANTED has no entries the internal file size information should be available for all objects. Future maintenance of internal file size information is performed automatically during INSERT or DELETE statements, that is, the update via extra task is required only once after the upgrade.
Please consider information in note 1335585 which describes problems in some database versions due to wrong values for index counters.
back to top

How to check whether the internal file size information of a table/index is available?

You can use the System table FILES to check if the internal file size information is available.
The important columns are: ENTRYCOUNT, TREEINDEXSIZE,TREELAEVESSIZE and LOBSIZE. If any of these columns has the NULL value set for a database object this means that this column is not relevant for the database object (e.g. indixes have never LOB values hence LOBSIZE is NULL), or that the internal file size information has not yet been determined for this object. In that case check if you find an entry in the system table SYSUPDATECOUNTERWANTED for this object. If not, add an entry in system table SYSUPDATECOUNTERWANTED for this object.
back to top

System table SYSUPDATECOUNTERWANTED

The system table SYSUPDATECOUNTERWANTED lists tables and indexes for which the internal file size information hasn't been determined yet. These checks are performed and the results added automatically by the system after an upgrade from a version lower than 7.6 to version 7.6 or higher or manually by database user if internal file size information was detected as missing. You find detailed information about the structure of SYSUPDATECOUNTERWANTED in the SAP MaxDB Documentation: SYSUPDATECOUNTERWANTED .

SCHEMANAME

TABLENAME

INDEXNAME

CANCELCOUNT

SAPR3

VBAP

 

7

SAPR3

VBAP

VBAP~Z01

0

When there is low activity on the database the database system starts server tasks to determine the internal file size information beginning with the first entry in system table SYSUPDATECOUNTERWANTED. This process is stopped at once when an application tries to access a database object that is processed. You find the information how often the determination of the internal file size information has been stopped in the column CANCELCOUNT. When CANCELCOUNT = 0 the database system has either not started a server task for this database object yet or a task is currently processing the database object.
After the internal file size information has been created sucessfully for a database object its entry in system table SYSUPDATECOUNTERWANTED is deleted.
back to top

System table FILES

The system table FILES describes all internal database files except LOB (LONG) files. Maintenance of internal file size information is performed automatically during INSERT or DELETE statements. You find detailed information about the structure of FILES in the SAP MaxDB Documentation: FILES.
back to top

Usage of the internal file size information for the SAP MaxDB SQL optimizer

The SQL optimizer uses the internal file size information - if available - for the join optimization and for the SQL statements SELECT COUNT (*) . If the internal file information is not available the SQL optimizer uses the statistics values.
back to top

Does the SQL optimizer in SAP MaxDB versions higher than 7.5 need statistics as well?

Yes, of course the most important information for the SQL optimizer are statistics created with UPDATE STATISTICS. But the SQL optimizer uses the internal file size information as well, if they are available.
When your system has up to date statistics the internal file size information is less important.
But when you have a lot of SQL statements like SELECT COUNT(*) , these SQL statements run much faster when the internal file size information is available, because then there is no further need to read the whole B*-tree of a table.
back to top

Why has the process stopped although there are still counters to be updated?

The database kernel checks the content of system table SYSUPDATECOUNTERWANTED for missing file directory counters. If no table entries can be processed because of application locks the check of system table SYSUPDATECOUNTERWANTED ends.
back to top

How to start the create process when it has been stopped before?

When the create process of internal file size information got an error or if you kill the process the creation of the internal file size information will be started again implizitly after the restart of the database.
You can also start this create process explicitly again as database system administrator with the statement CHECK TABLE AS PER SYSTEM TABLE without stopping and restarting the database. 

If there are no entries in table SYSUPDATECOUNTERWANTED the CHECK TABLE statement finishes at once.
The statement can run (sleep) days and weeks when there is high workload on the system.
back to top

Are there other explicit possibilities to create the internal file size information?

Yes, there are possibilities to start the create process explicitly. To determine the internal file size information for a single table you can execute the following statement (in SQL studio or database studio): CHECK TABLE <table_name> WITH SHARE LOCK or even better CHECK TABLE <table_name> UPDATE COUNTERS (check more information and availability in note 1315198
But take care: a share lock is set on the table while the create process is running!
A database structure check (CHECK DATA) in operational state ADMIN creates the internal file size information for all tables and indexes as well. However, in this case the counter for LOBSIZE is not determined and still is shown as NULL in the system table FILES. This is usually not a problem as the SQL optimizer does not use the value of LOBSIZE.
Please notice that after a CHECK DATA in operational state ADMIN the entries in system table SYSUPDATECOUNTERWANTED won't be updated. The create process of internal file size information starts again even if the internal file size information is already available in system table FILES. This does not matter. The counter create process of internal file size information starts only when there is low activity on the system.
back to top

How is it guaranteed that the internal file size information is current?

Once the internal file size information has been created for a database object it is automatically updated with each INSERT or DELETE operation.
Please consider information in note 1335585 which describes problems in some database versions due to wrong values for index counters.
back to top

Is it possible to avoid the implicit creation of the internal file size information?

Yes, you can use the database parameter EnableFileCounterInitialization to avoid the implicit creation of the internal file size information after restart of the database. Set the value to NO.

Warning

The internal file size information (file directory counter) is necessary for the SAP MaxDB SQL optimizer. You may not switch the database parameter EnableFileCounterInitialization to NO without a recommendation of the SAP Support.

back to top