Optimizer statistics for all tables

[Former Member]

Dear ALL,

Currently we are running on ECC 6.0 and BI 7.0 on AIX / Oracle 10G platform.

We have scheduled Check and update Optimizer statistics through DB13 , not made any changes till to

table DBSTATC , but there are many tables in both ECC and BI systems where we can see in DBSTATTORA

table ' Last Analysis date of 2008 ' , these tables are important from daily reporting purpose.

We have already reviewed notes 588668 and 129252 and found that DB13 will always run below mentioned

command.

brconnect -u / -c -f stats -t all

This will update Stats only for those tables for which Internal rules for Brconnect are met and

based on entries in DBSTATC.

We would like to know would be possible for us to run command

brconnect -u / -c -f stats -t all -f collect ( which takes slightly longer time than DB13 )

and collect statistics for all tables irrespective of any condictions ?

and then start running DB13 again as normal operation ?

what could be the side effects for creating new statistics for all tables and any

note which clearly indicates adverse effects of the same ?

Regards,

RR

[Former Member · Accepted solution]

Ganesh,

brconnect -u / -c -f stats -t all -f collect

It is SAP tool & this can be used to create new statistics for all tables

DBMS_STATS package (see Oracle documentation and Note 448380)

This is Oracle tool.You can use the stats_dbms_stats parameter to specify the use of DBMS_STATS for some or all tables or for ANALYZE (see Note 424239).

The Oracle package DBMS_STATS provides numerous options for automatically updating obsolete statistics

Yes you can run brconnect -u / -c -f stats -t all -f collect any time ( may be every 1 or 3 months along with daily DB13 job )

run brconnect -u / -c -f stats -t all on Daily basis

run brconnect -u / -c -f stats -t all -f collect on monthly basis or instead of monthly you can run this after SP upgrade, Kernel Upgrade, system refresh etc.

Hope this clears your confusion.

Thanks,

Sushil

[fidel_vales · Accepted solution]

Hi,

Only one comment.

You are running into the "common" misconception that the Optimizer needs "new" statistics and that is not true.

What if the date of the last statistics is from last year?

what is the problem with that?

did you had any performance problem due to that or you only like to increase the workload of your database because you "think" with newer statistics you will be <fill here>?

Do the tables have changed much?

do the data distribution in the table have changed much?

I strongly recommend to read the SAP note 825653 "Oracle: Common misconceptions"

It is also interesting the other extreme explained on the note 1057511 "Collecting statistis - An alternative approach"

[Former Member]

Hi Ganesh,

Please find my comments.

Does brconnect -u / -c -f stats -t all -f collect would produce adverse effects?

Ans:- NO

What is the recommended method ANALYZE or DBMS_STATS ?

Ans:-stats_dbms_stats this parameter defines how the DBMS_STATS package is used to update statistics with BRCONNECT. DBMS_STATS is the default method used in Oracle 10g.

ANALYZE statement is used to update statistics. This is the default method in Oracle 9i.

Check Below Link for the details.

http://help.sap.com/erp2005_ehp_04/helpdata/DE/ee/24063c8717e141e10000000a11402f/frameset.htm

Thanks

Sushil

Edited by: Sushil Suryawanshi on May 22, 2009 2:01 PM

[Former Member]

Sushil,

Thank you very much. Please answer my one last question.

To my understanding brconnect -u / -c -f stats -t all -f collect and DBMS_STATS are 2 sperate

entities and we can run brconnect -u / -c -f stats -t all -f collect ( may be every 3 months along with

daily DB13 job ) even if using ANALYZE method for Oracle 10G.

Correct me if i am wrong

My idea is to perform schedule as below :

Use default ANALYZE method in my ECC and BW system Oracle 10G

run brconnect -u / -c -f stats -t all on Daily basis

run brconnect -u / -c -f stats -t all -f collect on monthly basis

I dont want to switch to DBMS_STATS

Regards,

RR

Edited by: ganesh rr on May 22, 2009 10:57 AM

[Former Member]

Hi,

Try with brtools, it will generate statistic for all the tables.

Regards,

Rakesh.R

[Former Member]

Hi Ganesh,

If you have switched the collection of statistics for all tables to the DBMS_STATS package (for example, stats_dbms_stats = ALL:R:1), SAP recommend to compile statistics for all table and indexes once. You can do so using the following BRCONNECT command:

brconnect -u / -c -f stats -t all -f collect -p 4

& then run brconnect -u / -c -f stats -t all daily basis

Check below link & SAP Notes for further details

http://help.sap.com/erp2005_ehp_04/helpdata/DE/11/53e43a744ebe14e10000000a11402f/frameset.htm

Note 588668 - FAQ: Database statistics

Note 1013912 - FAQ: Oracle BW performance

Note 863811 - Using BRCONNECT to collect statistics for Oracle dictionary

Note 723894 - Brconnect: Create the statistics for all columns.

Note 1057511 - Collecting statistis - An alternative approach

Note 424239 - New BRCONNECT parameter: stats_dbms_stats

Hope this answers your query.

Thanks,

Sushil

[Former Member]

Thanks Sushil,

Currently INIT.sap shows parameter commented ( Not in use )

1. stats_dbms_stats = ([ALL:R:1,][<owner>.]<table>:R|B:<degree>,...)

I understand i have not done any switching , and my database follws ANALYZE method

now does brconnect -u / -c -f stats -t all -f collect

would produce adverse effects ?

What is the recommended method ANALYZE or DBMS_STATS ?

As per note 588668 i underastnd advantage / diadvantage of DBMS_STATS method ... but

does not understand whethere it is recommended to implement ?

If yes , which note clearly states so and methodology for switching method from ANALYZE to DBMS_STATS . ?

Regards,

RR

Edited by: ganesh rr on May 22, 2009 10:03 AM