Skip to Content
author's profile photo Former Member
Former Member

Trying to understand why brconnect is analyzing the same table partitions every day?

In checking my analyze log, I have noticed that brconnect is collecting stats on the same table partitions every day.

I just want to know why this is happening as these partitions should not be changing.

I would also like to get an understanding of what the numbers represent at the end of the "BR0883I" lines below.
I am running the command: brconnect -u / -c -f stats -t all -p 4 which is running BRCONNECT 7.20 (25) on Oracle Enterprise Edition 11.2.0.3.0

Analyze is not collecting stats on the rest of the partitions every day (this is the normal behaviour that I would expect).


from yesterday's log...
...
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2001_CAN1_1 (0/0:566794:147029)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2001_USA1_1 (0/0:566794:2506609)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2002_CAN1_1 (2/0:5566794:1761639)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2002_USA1_1 (1179/0:566794:13240581)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2003_CAN1_1 (4/0:566794:3945735)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2003_USA1_1 (1076/0:566794:21784099)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2004_CAN1_1 (0/0:566794:4598810)
BR0280I BRCONNECT thread 4 time stamp: 2013-08-26 05.45.57
BR0881I Collecting statistics for table partition SAPSR3.BSEG.BSEG_2001_CAN1_1 with method/sample E/P.200 ...
...

from today's log...
...
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2001_CAN1_1 (0/0:573144:146968)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2001_USA1_1 (0/0:573144:2498494)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2002_CAN1_1 (2/0:573144:1763049)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2002_USA1_1 (1179/0:573144:13177883)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2003_CAN1_1 (4/0:573144:3945735)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2003_USA1_1 (1076/0:573144:21882186)
BR0883I Table partition selected to collect statistics after check: SAPSR3.BSEG.BSEG_2004_CAN1_1 (0/0:573144:4642968)
BR0280I BRCONNECT thread 3 time stamp: 2013-08-27 05.46.01
BR0881I Collecting statistics for table partition SAPSR3.BSEG.BSEG_2001_CAN1_1 with method/sample E/P.200 ...
...

Any help or knowledge would be greatly appreciated.

Thanks,
Paul

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on Aug 27, 2013 at 03:05 PM

    Hi Paul,

    it seems to be custom partitioning right or do you use the SAP partitioning engine (so i am just confused about the naming convention)? ... maybe some DDL reasons or whatever.

    The easiest way would be to enable the BR*Tools trace (BR_TRACE 15) and check for the reason .. anything else is just guessing.

    Regards

    Stefan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 27, 2013 at 05:58 PM

    Stefan - Thanks! The trace helped narrow the issue down. It appears that that my SYS.DBA_TAB_MODIFICATIONS table is not getting updated/cleared after the stats have run. I'll need to do some further digging but at least I have a direction to investigate now. And yes, we use a custom naming convention for our partitioning.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Paul,

      > It appears that that my SYS.DBA_TAB_MODIFICATIONS table is not getting updated/cleared after the stats have run.

      Can you please post the exact PL/SQL procedure call of DBMS_STATS.GATHER_TABLE_STATS for that particular table (partitions)? Have you also run "dbms_stats.flush_database_monitoring_info()" before cross-checking DBA_TAB_MODIFICATIONS?

      Have seen such issues by using particular options.

      Regards

      Stefan

  • author's profile photo Former Member
    Former Member
    Posted on Aug 28, 2013 at 12:04 PM

    Hi Stefan,

    Here is the statement generated by BRCONNECT..

    BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SAPSR3',

    TABNAME => 'BSEG',

    PARTNAME => 'BSEG.BSEG_2001_CAN1_1',

    ESTIMATE_PERCENT => .2,

    METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',

    DEGREE => NULL,

    GRANULARITY => 'PARTITION',

    CASCADE => TRUE,

    NO_INVALIDATE => FALSE);

    END;

    and afterwards I manually executed...

    EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    I also have tried...

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SAPSR3',

    TABNAME=>'BSEG',

    ESTIMATE_PERCENT=>.1,

    DEGREE=>8,

    CASCADE=>TRUE,

    NO_INVALIDATE=>FALSE);

    EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    And neither seem to have flushed SYS.DBA_TAB_MODIFICATIONS.

    Suggestions or is it time to open a ticket with Oracle?

    Thanks,

    Paul

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Paul,

      hmm i just setup a tiny test case here on 11.2.0.3.6.

      SQL> create table TABA (id  NUMBER NOT NULL, text VARCHAR2(10))     PARTITION BY RANGE (id)     (PARTITION part1 VALUES LESS THAN (11),      PARTITION part2 VALUES LESS THAN (21) ,      PARTITION part3 VALUES LESS THAN (31));SQL> beginfor i in 1..30 loop    insert into TABA values(i,'XXXXX');end loop;commit;end;/SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();SQL> select TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP      from DBA_TAB_MODIFICATIONS      where TABLE_NAME like '%TABA';

      SQL> exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => USER, TABNAME => 'TABA', PARTNAME => 'PART1',  ESTIMATE_PERCENT => .2, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, GRANULARITY => 'PARTITION', CASCADE => TRUE, NO_INVALIDATE => FALSE);SQL> select TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP      from DBA_TAB_MODIFICATIONS      where TABLE_NAME like '%TABA';

      So it works as expected in my case, but i don't know your exact partitioning as it is custom made. However i would crosscheck the underlying table SYS.MON_MODS_ALL$ and its populated values. If it is wrong there as well - i would open an Oracle SR.

      MOS note #1390718.1 describes a very specific case and can be verified easily 😊

      Regards

      Stefan

  • author's profile photo Former Member
    Former Member
    Posted on Aug 28, 2013 at 12:19 PM

    Update... looking through Oracle Support I found as a starting point..

    Incremental Statistics Gathering Not Working At Hash Sub-Partition Level (Doc ID 1390718.1)

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2013 at 12:46 PM

    It did turn out to be the simpler case (MOS note #1390718.1).

    By changing the Granularity to SUBPARTITION the stats where collected and all is good.

    Thanks,

    Paul

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Paul,

      ah ok - so you have done custom sub-partitioning, which is not SAP standard at all. Sub partitioning is supported (ABAP DDIC), but only with SAP_BASIS release 700 (or greater) and with Oracle 11. For details please check SAPnote #742243.

      If the BR*Tools have already implemented this logic as well is not described (unfortunately).

      Regards

      Stefan

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.