Role of DBSTATC

[sanjeev_kumar18]

Hi all,

I'm still in the learning mode and have some doubts. And i think, this is the best learning resource.

My ques is : If DBSTATC contains the result of the check (DB13) i.e it contain certain degree of changes during a specified period.

Would it be sufficient to just update the DBSTATC table to update the statistics ?

[Former Member]

hi sanjeev,

see the below information. u can get clarity of ur query.

reward me points if this helps u.

Maintaining the Control Table DBSTATC

For the MaxDB database system, you do not need to make changes to the DBSTATC table.

See also: Optimizer in the CCMS

The following information is intended as background knowledge for the database administrator. All the required entries in the DBSTATC table are made by SAP and shipped with the SAP system. The entries in the DBSTATC table are updated by actions in the CCMS.

Use

The DBSTATC control table contains a list of selected SAP database tables. These tables were selected because their settings for updating the cost–based optimizer statistics deviate from the default values. When the DBSTATC control table is shipped, it contains several hundred entries for SAP Basis and application tables.

For all other SAP tables, the default settings are used for the UPDATE STATISTICS runs.

If you create or change an existing entry for a table in the DBSTATC control table, all UPDATE STATISTICS runs use these new values (in the CCMS, including the DBA Planning Calendar).

Prerequisites

Prerequisites

Procedure

Choose the transaction Control the Database Optimizer (transaction DB21)

Changing an Existing Entry

...

1. Select the relevant entry.

2. Choose Goto ® Change Detail.

3. Enter the necessary values. Use the default values.

4. Save the entry.

Adding an Entry

...

1. Choose Edit ® New Entries.

2. Enter the necessary values. Use the default values.

3. Save the entry.

Delete Entry

If you want to use the application monitor, do not delete any entries provided by SAP for the application monitor.

...

1. Select the relevant entry.

2. Choose Edit ®Delete.

3. Save this action.

The system will now use the default settings for statistics updates for the table whose entries you deleted from DBSTATC.

Result

Statistic updates for tables that are contained in DBSTATC are executed according to the entries in DBSTATC. Statistic values are determined based on estimated values.

thanks

karthikeya

[sanjeev_kumar18]

SO it means that updating the statistics of the tables is a 2 phase process.

First it looks in the table DBSTATC for the tables for which statistics have to be updated and then tools like BRCONNECT, DB20, ANALYZE TABLE etc .. are used ( in oracle ) to update the statistics.

Regards,

Sanjeev Kumar

[Former Member]

Hi Sanjeev,

I wouldn't say updating statistics in general is a 2-phase process because the database itself doesn't care about the table DBSTATC at all when you update statistics directly on database level. This table is only used by the SAP-sided Statistic Update-procedures. So if you plan an Update Statistics Job in DB13, then this is carried out by brconnect in the end. BRConnect then reads all the entries from table DBSTATC, interprets them and creates the statistics accordingly then with the corresponding database-command.

So the entries in DBSTATC define special handling rules: assume we have a table ZTEST with 50.000 rows - as you can read in note #588668 point 21, brconnect then by default would read 30% of the whole table to estimate the statistics. But assume you want to have exact computed statistics, then you need to make brconnect read 100% of the table instead of reading 50%. To achieve this, you can enter a new row in the DBSTATC where you set the method "compute" instead of "estimate".

So if you DO NOT have an entry in DBSTATC for a specific table, brconnect will use the default sample size depending on the number of rows in the table (#588668 , point 21) and the default method. If you do specify an entry in DBSTATC for a specific table, the settings will be taken into account by brconnect.

But this applies for Statistics Updates with BRConnect and DB20 only!!!! An ANALYZE TABLE statement which is executed directly on DB-level will not touch the DBSTATC table at all, it doesn't even know that this table is existing!! DBSTATC is a logical extension for the statistics update by SAP - it's not Oracle standard.

Regards,

Beate