on 08-27-2013 3:02 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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> begin
for 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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.