Updating DBSTATC

[Former Member]

Hi,

As part of SAP's oracle performance optimization report, we implemented the scripts dbstatc10.sql attached to the note 403704 ; Maintained customer specific settings and implemented the script statistics.txt attached to the note 1020260. I ran brconnect to gather statistics (brconnect -u / -c -f stats -t ALL). I checked the LAST_ANALYZED field of the table dba_tables to determine when table statistics were collected for a table defined in DBSTATC with the Active flag set to 'A'. Surprisingly, the LAST_ANALYZED field of dba_tables did not return the date stamp of the recent brconnect run. I tested for a single table using DB21 and saw that the LAST_ANALYZED field was updated. brconnect documentation states that it picks the tables in DBSTATC whose Active flag is set to A. Any reasons why LAST_ANALYZED was not updated by brconnect?. Thanks for your time.

brconnect version: 7.00(52)

oracle: 10.2.0.2

Regards,

[Former Member · Accepted solution]

Hi Ajay,

Execute with the "-force" option, as below;

brconnect -u / -c force  -f stats -t ALL

For the further information, you can read the thread http://scn.sap.com/thread/3154417

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Thanks Orkun. I ran brconnect with the force option but it didn't update the LAST_ANALYZED field of DBA_TABLES. I am inclined to think --as per the link you posted-- that the statistics for those tables in DBSTATC are available and hence there is no need to generate the statistics.

Regards,

[Former Member · Accepted solution]

It depends to the condition. If the changes are lower than %50, it is not necessary to update statistics for those tables. You can check the ratio on DB20. For the further information, check the Note 106047 - DB21: Customizing the DBSTATC

For your case, you can find the specific information in the note, as below;

A -> Statistics created if necessary. 

BRCONNECT determines whether statistics are necessary, taking into account the number of table entries compared to the number of the table entries during the last statistics creation. If the variance exceeds a threshold value (default: stats_change_threshold = 50%), new statistics are created.

If you would like to force the system to collect statistics for all tables, you can execute the command, below;

brconnect -u / -c -f stats -t ALL -f collect,allsel,keep,locked

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

Thanks for your time . I read note 106047 last night and saw that the threshold is 50% by default.

Regards,