How to refresh Statistics at the DB level

[Former Member]

Hi,

I have come across this but I dont know how to refresh statistics at the DB level. Mind you I am not talking about Refresh DB stats for infocube in the infocube performance tab.

There seems to be a way of refreshing stats at the DB(Oracle DB) level for the whole BW system and this improves query performance greatly.

Does anybody know how this is done?

Thanks,

Reddy

[Former Member · Accepted solution]

Hi ,

Schedule program "SAP_ANALYZE_ALL_INFOCUBES" in background once in a week at least ..

Hope it works !

[former_member93896 · Accepted solution]

Hello,

SAP_ANALYZE_ALL_INFOCUBES is outdated. Please use the BRCONNECT tool. It's typically scheduled as a nightly job using transaction DB13. See also SAP notes 129252 and 428212.

Regards,

Marc

SAP NetWeaver RIG, BI US

[Former Member · Accepted solution]

Thank You Chalapathi and Marc.

Regards,

Reddy

[Former Member · Accepted solution]

Hi Marc,

The BRCONNECT is usful for Oracle DB. The program is for all Databse platform.

Jason

[former_member93896 · Accepted solution]

Jason,

you are correct. The question had explicitly mentioned Oracle so BRCONNECT is the better solution.

For other database platforms, you can use the SAP_ANALYZE_ALL_INFOCUBES program or database specific tools. Please check SAP notes for the database component (BW-SYS-DB-*).

Regards,

Marc

SAP NetWeaver RIG, US BI

[Former Member]

Can the use of BRCONNECT once every night, replace InfoCube-specific DB statistics runs after loading (in process chains)?

We suffer from long DB statistics generation times (SAPKW30B17, ORACLE 9.2.0.4.0). We have noted that statistics are generated for (for example) /BI0/PBARTNER for all cubes that we run statistics (that include this object). This table has got 1.6M rows and it takes approx 2.5h per infocube to run the statement below:

2004-11-02 01:21:34 SQL: 2004-11-02 01:21:34 ALEREMOTE

2004-11-02 01:21:34 BEGIN DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME 2004-11-02 01:21:34 =>'SAPP25', TABNAME => '"/BI0/PBPARTNER"',

2004-11-02 01:21:34 METHOD_OPT => 'FOR ALL COLUMNS SIZE 75', DEGREE

2004-11-02 01:21:34 => 1 , GRANULARITY => 'ALL', CASCADE => TRUE );

2004-11-02 01:21:34 END;

2004-11-02 03:53:17 SQL-END: 2004-11-02 03:53:17 02:31:43

2004-11-02 03:53:17 SQL: 2004-11-02 03:53:17 ALEREMOTE

Note 129252 ("Oracle DB Statistics for BW tables") references note 555030 ("Deactivating BW-initiated DB statistics"), but I can't understand if 129252 recommends the deactivation or just informs that it is possible.

Any input in this matter would be very much appreciated!

Regards

--Andreas

[former_member93896]

Hello Andreas,

yes, use BRCONNECT with the default settings instead of updating DB stats after each load. BRCONNECT will only update stats when the table content has changed significantly and each table is updated only once. On top of that, you can run it with several processes in parallel.

Regards,

Marc

SAP NetWeaver RIG, US BI

[Former Member]

Thank you Marc for your valuable advice! We will try your suggestion.

Best Regards

--Andreas