Skip to Content

Update index statistics - brtools

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 08, 2017 at 05:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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?

  • May 08, 2017 at 06:14 AM

    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?

    Add comment
    10|10000 characters needed characters exceeded

    • 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.