Skip to Content
avatar image
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 comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • 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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 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

  • avatar image
    Former Member
    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 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

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 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