Update DB Statistics

[former_member186066]

Hello Guys,

We have observed that whenever we run the below db statistics, it makes the system stop responding. SAP System hangs until it is cancelled.

brconnect -u / -c -o summary -f stats -o SAPSR3 -t all -m +I -s P10 -f allsel,collect,method,precision,space,keep -p 4

Any clue to it? The above query comes from what SAP recommends during installation.

Thanks and Regards,
Ankit Mishra

[former_member243502]

Hello Ankit,

The option +I creates histograms for all tables and indexes, additionally Validates Indexes structures wich causes table locking and therefore updates hanging up in all the system.

It´s better to Skip and run it in times with low activity or even in the downtime of the system.

For regular statistics collection you should run it from db13 instead.

this recommendation is only for export preparation.

Best Regards

Fredy

[former_member186066]

Thank you Fredy.

[Former Member]

Hi Ankit,

If there is no a specific reason to execute brconnect with the parameters that you noted, I recommend you schedule update statistics on DB13 in daily basis. So that the statistics will be collected, automatically.

Best regards,

Orkun Gedik

[former_member186066]

Hello Guys,

Here is the adverse affect of the command shared:

1.All updates gets hanged up in SM13

2. System stops responding.

In short, SAP system is dead.

The script comes as an option in SAPINST when asking for Updating Database statistics under "Export preparation".

Had skipped the option in sapinst and had run it from Db level.

I had the query, if such is the affect, then why does SAP recommends this query in SAPINST?

Best Regards,

Ankit Mishra

[Former Member]

Hi Mate,

Please check the amount of the cpu you have and the db_writer_processes in the your system.

Sap Gives a general stats command and which runs for all the system . You have an issue because i think that you dont have correct no of CPU.

Please share the oracle alert log and the st06 before the system comes to hold.

Thanks

Rishi abrol

[former_member188883]

Hi Ankit,

Just keep it simple and regular using the command

brconnect -u / -c -o summary -f stats -o SAPSR3 -t all -p 4.

Objective is to have regular updates on the database statistics.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hi,

The command that you are trying thow things i want to ask you

Why using P10 fro all the tables. So please remove it.

How many CPU you have as you are trying to use parallel processing with 4 thread.

Try to remove P10 and -p 4.

What happens to your CPU when you run the above command i think it would be 100% and that is the reason you have issue.

Below is the example of all the things used in the above commad.

CREATING SPACE STATI

STICS WITH BRCONNECT

You often require information about how the space is used in the database by tables and indexes. For

example, this information is necessary for planning an upgrade, or when reorganizing or converting individual tables.

You can create space statistics for all database objects as follows:

brconnect -u / -c -f stats -t all -m +I -s P1 -f  allsel,collect,method,precision,space -p 4

The options used have the following meaning:



-t all

: collects statistics for all databa

se objects. You can specify subsets if required.



-m +I

: collects index space statistics by validating its structure. Without this option, only space

statistics for tables are collected.



-s P1

: collects statistics with a minimum accuracy of analysis of 1

% of the table records

-f allsel,collect,method,precision,space

: forces the functionality described above

-p 4

: accelerates update statistics by using parallel processing with four threads

The space statistics for tables are stored in the DBSTATTORA tabl

e and for indexes in the DBSTATIORA

table.

You can retrieve the space statistics from these tables for further processing using SQL.

You can find summary information on space statistics at the end of the BRCONNECT log.

For more information, see

SAP Note 554031

Thanks

Rishi Abrol