Update index statistics - brtools

[former_member199632]

Hello,

We have noticed that most of the index stats are obsolete in the oracle DB.

SAPSR3S COL_VALUE_SETT~0 27-AUG-09

SAPSR3S COOLOBJECTS~0 27-AUG-09

SAPSR3S COOLOBJECTST~0 27-AUG-09

SAPSR3 T5C2G~0 23-JAN-10

SAPSR3 T5C22T~0 23-JAN-10

SAPSR3 T5C1V~0 23-JAN-10

SAPSR3 T5C1X~0 23-JAN-10

Is it advisable/required to update those indexes via brtools (REBUILD indexes) , So that it will update the index database statistics.

* ALTER INDEX "SAPSR3"."T5C2G~0" REBUILD ONLINE;

BRTOOLS -> REBUILD INDEX -> INDEX NAMES ....

-Zerandib

[former_member199632]

Thx James.. Yes, i will try out this in less peak hrs , since it costs system resources.

I have run the command for VBAK table

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

But it prompts this error;

BR0886E Checking/collecting statistics failed for table SAPSR3.VBAK

---------

erptrn:oratrn 32>  brconnect -u / -c -f stats -t VBAK -f collect
BR0801I BRCONNECT 7.00 (32)
BR0805I Start of BRCONNECT processing: cevtvaci.sta 2017-05-08 11.38.20
BR0484I BRCONNECT log file: /oracle/TRN/sapcheck/cevtvaci.sta
BR0280I BRCONNECT time stamp: 2017-05-08 11.38.21
BR0813I Schema owner found in database TRN: SAPSR3*
BR0280I BRCONNECT time stamp: 2017-05-08 11.38.21
BR0807I Name of database instance: TRN
BR0808I BRCONNECT action ID: cevtvaci
BR0809I BRCONNECT function ID: sta
BR0810I BRCONNECT function: stats
BR0812I Database objects for processing: VBAK
BR0851I Number of tables with missing statistics: 0
BR0852I Number of tables to delete statistics: 0
BR0854I Number of tables to collect statistics without checking: 1
Owner SAPSR3: 1
VBAK
BR0855I Number of indexes with missing statistics: 0
BR0856I Number of indexes to delete statistics: 0
BR0857I Number of indexes to collect statistics: 0
BR0853I Number of tables to check (and collect if needed) statistics: 0
BR0862I Force option with value 'collect' set
BR0126I Unattended mode active - no operator confirmation required
BR0280I BRCONNECT time stamp: 2017-05-08 11.38.21
BR0877I Checking and collecting table and index statistics...
BR0280I BRCONNECT time stamp: 2017-05-08 11.38.21
BR0881I Collecting statistics for table SAPSR3.VBAK with method/sample E/P3 ...
BR0280I BRCONNECT time stamp: 2017-05-08 11.39.19
BR0301E SQL error -20003 at location stats_tab_collect-20, SQL statement:
'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"VBAK"', ESTIMATE_PERCENT => 3, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'
ORA-20003: Specified bug number (5099019) does not exist
ORA-06512: at "SYS.DBMS_STATS", line 14531
ORA-06512: at "SYS.DBMS_STATS", line 14552
ORA-06512: at line 1
BR0886E Checking/collecting statistics failed for table SAPSR3.VBAK
BR0280I BRCONNECT time stamp: 2017-05-08 11.39.19
BR0850I 1 of 1 object processed - 0.002 of 0.002 units done
BR0204I Percentage done: 100.00%, estimated end time: 11:39
BR0001I **************************************************
BR0280I BRCONNECT time stamp: 2017-05-08 11.39.19
BR0879I Statistics checked for 0 tables
BR0878I Number of tables selected to collect statistics after check: 0
BR0880I Statistics collected for 0/0 tables/indexes
BR1308E Collection of statistics failed for 1/0 tables/indexes
BR0806I End of BRCONNECT processing: cevtvaci.sta 2017-05-08 11.39.19
BR0280I BRCONNECT time stamp: 2017-05-08 11.39.19
BR0804I BRCONNECT terminated with errors

---------

after that , i check the index stats using this SQL

select OWNER,INDEX_NAME,LAST_ANALYZED from dba_indexes where owner not in ('SYS','SYSTEM') ORDER BY LAST_ANALYZED desc;

Index stats not updated!!. It still shows the older dates only!

SAPSR3 VBAK~AUD 15-JUN-16

SAPSR3 VBAK~0 15-JUN-16

SAPSR3 VBAK~Y01 15-JUN-16

Any idea, why its not collecting the stats correctly?

[JamesZ]

is it caused by the issue mentioned in sap note 1667086？

To find out why VBAK is not updated, please review the db13 on db statistics job. (I suppose you schedule such job, otherwise other tables' statistics won't be updated.

[JamesZ]

Hi Zerandib,

rebuild index means to recreate index, which costs system resource.

You can manually collect those index statistics by collecting its table's statistics:

brconnect -u / -c -f stats -t <table name> -f collect

Best regards,
James

[former_member199632]

Thx James.. Yes, i will try out this in less peak hrs , since it costs system resources.

I have run the command for VBAK table

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

But it prompts this error;

BR0886E Checking/collecting statistics failed for table SAPSR3.VBAK

---------

erptrn:oratrn 32>  brconnect -u /-c-f stats -t VBAK -f collect
BR0801I BRCONNECT 7.00(32)
BR0805I Startof BRCONNECT processing: cevtvaci.sta 2017-05-0811.38.20
BR0484I BRCONNECT logfile:/oracle/TRN/sapcheck/cevtvaci.sta
BR0280I BRCONNECT timestamp:2017-05-0811.38.21
BR0813I Schema owner foundindatabase TRN: SAPSR3*
BR0280I BRCONNECT timestamp:2017-05-0811.38.21
BR0807I Nameofdatabaseinstance: TRN
BR0808I BRCONNECT action ID: cevtvaci
BR0809I BRCONNECT functionID: sta
BR0810I BRCONNECT function: stats
BR0812I Databaseobjectsforprocessing: VBAK
BR0851I Numberoftableswith missing statistics:0
BR0852I Numberoftablestodelete statistics:0
BR0854I Numberoftablestocollect statistics without checking:1
Owner SAPSR3:1
VBAK
BR0855I Numberof indexes with missing statistics:0
BR0856I Numberof indexes todelete statistics:0
BR0857I Numberof indexes tocollect statistics:0
BR0853I Numberoftablestocheck(andcollectif needed) statistics:0
BR0862I Force optionwithvalue'collect'set
BR0126I Unattended mode active -no operator confirmation required
BR0280I BRCONNECT timestamp:2017-05-0811.38.21
BR0877I Checking and collecting tableandindex statistics...
BR0280I BRCONNECT timestamp:2017-05-0811.38.21
BR0881I Collecting statistics fortable SAPSR3.VBAK withmethod/sample E/P3 ...
BR0280I BRCONNECT timestamp:2017-05-0811.39.19
BR0301E SQL error -20003at location stats_tab_collect-20,SQLstatement:'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"VBAK"', ESTIMATE_PERCENT => 3, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'
ORA-20003:Specified bug number(5099019) does not exist
ORA-06512:at"SYS.DBMS_STATS", line 14531
ORA-06512:at"SYS.DBMS_STATS", line 14552
ORA-06512:atline1
BR0886E Checking/collecting statistics failed fortable SAPSR3.VBAK
BR0280I BRCONNECT timestamp:2017-05-0811.39.19
BR0850I 1of1object processed -0.002of0.002 units done
BR0204I Percentage done:100.00%, estimated endtime:11:39
BR0001I **************************************************
BR0280I BRCONNECT timestamp:2017-05-0811.39.19
BR0879I Statistics checked for0tables
BR0878I Numberoftables selected tocollect statistics aftercheck:0
BR0880I Statistics collected for0/0tables/indexes
BR1308E Collectionof statistics failed for1/0tables/indexes
BR0806I Endof BRCONNECT processing: cevtvaci.sta 2017-05-0811.39.19
BR0280I BRCONNECT timestamp:2017-05-0811.39.19
BR0804I BRCONNECT terminated witherrors

---------

after that , i check the index stats using this SQL

select OWNER,INDEX_NAME,LAST_ANALYZED from dba_indexes where owner notin('SYS','SYSTEM')ORDERBY LAST_ANALYZED desc;

Index stats not updated!!. It still shows the older dates only!

SAPSR3 VBAK~AUD 15-JUN-16

SAPSR3 VBAK~0 15-JUN-16

SAPSR3 VBAK~Y01 15-JUN-16

Any idea, why its not collecting the stats correctly?