When upgrading from 11.2.0.2 to 11.2.0.4 from a customer
application, accidently saw the Incremental stats gathering feature got messed
up in 11.2.0.4 (even in 12c too)
The "incremental statistics” feature stores
"synopses" of each column of each partition in the data dictionary,
and then runs a query that (roughly speaking) aggregates the synopses for each
column across partitions to produce a synopsis fir the table. This allows
Oracle to get an accurate estimate of the number of distinct values for the column
without having to take a large sample of the table.
apps@DVDV01> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 -
64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
5 rows selected.
apps@DVDV01>
apps@DVDV01> create table t
2 partition by list(x)
3 (
4 partition p1 values (1),
5 partition p2 values (2)
6 )
7 as
8 select a.*, mod(rownum,2)+1 x ,
9 (mod(rownum,2)+1)*5 as order_status
10 from all_objects a;
Table created.
apps@DVDV01> exec dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
PL/SQL procedure successfully completed.
apps@DVDV01> exec
dbms_stats.set_table_prefs(user,'T','GRANULARITY','auto');
PL/SQL procedure successfully completed.
apps@DVDV01> exec
dbms_stats.set_table_prefs(user,'T','PUBLISH','TRUE');
PL/SQL procedure successfully completed.
apps@DVDV01> exec
dbms_stats.set_table_prefs(user,'T','ESTIMATE_PERCENT','dbms_stats.auto_sample_size');
PL/SQL procedure successfully completed.
apps@DVDV01> select dbms_stats.get_prefs('INCREMENTAL',user,'T')
incremental,
2
dbms_stats.get_prefs('GRANULARITY',user,'T') granularity,
3
dbms_stats.get_prefs('PUBLISH',user,'T') publish,
4
dbms_stats.get_prefs('ESTIMATE_PERCENT',user,'T') estimate_percent
5 from dual ;
INCREMENTA GRANULARIT PUBLISH
ESTIMATE_P
---------- ---------- ---------- ----------
TRUE AUTO TRUE DBMS_STATS.AUTO_SAMPLE_SIZE
1 row selected.
apps@DVDV01> exec dbms_stats.gather_table_Stats(user,'T');
PL/SQL procedure successfully completed.
apps@DVDV01> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 14-JUL-2014 02:19:28 AM
T P2 14-JUL-2014 02:19:28 AM
2 rows selected.
apps@DVDV01> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 3849 14-JUL-2014 02:19:28 AM
T
DATA_OBJECT_ID 39130
14-JUL-2014 02:19:28 AM
T
EDITION_NAME 0 14-JUL-2014 02:19:28 AM
T GENERATED 2 14-JUL-2014 02:19:28 AM
T
LAST_DDL_TIME 3651
14-JUL-2014 02:19:28 AM
T NAMESPACE 12 14-JUL-2014 02:19:28 AM
T OBJECT_ID 101253 14-JUL-2014 02:19:28 AM
T
OBJECT_NAME 42484
14-JUL-2014 02:19:28 AM
T
OBJECT_TYPE 32
14-JUL-2014 02:19:28 AM
T
ORDER_STATUS 2
14-JUL-2014 02:19:28 AM
T OWNER 28 14-JUL-2014 02:19:28 AM
T SECONDARY 2 14-JUL-2014 02:19:28 AM
T STATUS 2 14-JUL-2014 02:19:28 AM
T
SUBOBJECT_NAME 13799
14-JUL-2014 02:19:28 AM
T TEMPORARY 2 14-JUL-2014 02:19:28 AM
T TIMESTAMP 4065 14-JUL-2014 02:19:28 AM
T X 2 14-JUL-2014
02:19:28 AM
17 rows selected.
apps@DVDV01>
So far
everything looks fine; we have couple of partition with stats gathered
perfectly.
apps@DVDV01> update t set order_status = 77
2 where x = 1 and rownum = 1;
1 row updated.
apps@DVDV01> commit;
Commit complete.
apps@DVDV01>
This update is
done purposefully since the min or max value for a column could change after the
update in the table
apps@DVDV01> alter table t add partition p3 values(3);
Table altered.
apps@DVDV01> insert into t
2 select a.*, 3 as id,
3 (mod(rownum,3)+1)*5 as order_status
4 from all_objects a
5 where rownum <=1000;
1000 rows created.
apps@DVDV01> commit;
Commit complete.
apps@DVDV01>
Now added a
bunch of records to new partitions, and updated few rows into existing
partitions, when stats gathered P1 and P3 got modified (since data changed only
on those partition) without touching partition P2.
apps@DVDV01> exec dbms_stats.gather_table_Stats(user,'T');
PL/SQL procedure successfully completed.
apps@DVDV01>
apps@DVDV01> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T
P1 14-JUL-2014 02:19:49 AM
T P2 14-JUL-2014 02:19:28 AM
T
P3 14-JUL-2014 02:19:48 AM
3 rows selected.
apps@DVDV01>
apps@DVDV01> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 3849 14-JUL-2014 02:19:49 AM
T
DATA_OBJECT_ID 39130
14-JUL-2014 02:19:49 AM
T
EDITION_NAME 0
14-JUL-2014 02:19:49 AM
T GENERATED 2 14-JUL-2014 02:19:49 AM
T
LAST_DDL_TIME 3651
14-JUL-2014 02:19:49 AM
T NAMESPACE 12 14-JUL-2014 02:19:49 AM
T OBJECT_ID 101696 14-JUL-2014 02:19:49 AM
T OBJECT_NAME 42484 14-JUL-2014 02:19:49 AM
T
OBJECT_TYPE 32
14-JUL-2014 02:19:49 AM
T
ORDER_STATUS 4
14-JUL-2014 02:19:49 AM
T OWNER 28 14-JUL-2014 02:19:49 AM
T SECONDARY 2 14-JUL-2014 02:19:49 AM
T STATUS 2 14-JUL-2014 02:19:49 AM
T
SUBOBJECT_NAME 13799
14-JUL-2014 02:19:49 AM
T TEMPORARY 2 14-JUL-2014 02:19:49 AM
T TIMESTAMP 4065 14-JUL-2014 02:19:49 AM
T X 3 14-JUL-2014
02:19:49 AM
17 rows selected.
apps@DVDV01>
So everything
works expected in 11.2.0.2, but see what happens in 11.2.0.4 and 12.1.0.1
rajesh@ORA11G> select * from v$version ;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 -
64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5 rows selected.
rajesh@ORA11G>
rajesh@ORA11G> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 14-JUL-2014 12:51:51 PM
T P2 14-JUL-2014 12:51:51 PM
2 rows selected.
rajesh@ORA11G> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1340 14-JUL-2014 12:51:52 PM
T
DATA_OBJECT_ID 7069
14-JUL-2014 12:51:52 PM
T
EDITION_NAME 0
14-JUL-2014 12:51:52 PM
T GENERATED 2 14-JUL-2014 12:51:52 PM
T
LAST_DDL_TIME 1404
14-JUL-2014 12:51:52 PM
T NAMESPACE 17 14-JUL-2014 12:51:52 PM
T OBJECT_ID 84753 14-JUL-2014 12:51:52 PM
T
OBJECT_NAME 49944
14-JUL-2014 12:51:52 PM
T
OBJECT_TYPE 39
14-JUL-2014 12:51:52 PM
T
ORDER_STATUS 2
14-JUL-2014 12:51:52 PM
T OWNER 32 14-JUL-2014 12:51:52 PM
T SECONDARY 2 14-JUL-2014 12:51:52 PM
T STATUS 2 14-JUL-2014 12:51:52 PM
T
SUBOBJECT_NAME 196
14-JUL-2014 12:51:52 PM
T TEMPORARY 2 14-JUL-2014 12:51:52 PM
T TIMESTAMP 1496 14-JUL-2014 12:51:52 PM
T X 2 14-JUL-2014
12:51:52 PM
17 rows selected.
rajesh@ORA11G> update t set order_status = 77
2 where x = 1 and rownum = 1;
1 row updated.
rajesh@ORA11G> commit;
Commit complete.
rajesh@ORA11G> alter table t add partition p3 values(3);
Table altered.
rajesh@ORA11G> insert into t
2 select a.*, 3 as id,
3 (mod(rownum,3)+1)*5 as order_status
4 from all_objects a
5 where rownum <=1000;
1000 rows created.
rajesh@ORA11G> commit;
Commit complete.
rajesh@ORA11G> exec dbms_stats.gather_table_Stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T
P1 14-JUL-2014 12:52:13 PM
T
P2 14-JUL-2014 12:52:15 PM
T
P3 14-JUL-2014 12:52:15 PM
3 rows selected.
rajesh@ORA11G> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1340 14-JUL-2014 12:52:15 PM
T
DATA_OBJECT_ID 7069
14-JUL-2014 12:52:15 PM
T
EDITION_NAME 0
14-JUL-2014 12:52:15 PM
T GENERATED 2 14-JUL-2014 12:52:15 PM
T
LAST_DDL_TIME 1404
14-JUL-2014 12:52:15 PM
T NAMESPACE 17 14-JUL-2014 12:52:15 PM
T OBJECT_ID 84932 14-JUL-2014 12:52:15 PM
T OBJECT_NAME 49944 14-JUL-2014 12:52:15 PM
T
OBJECT_TYPE 39
14-JUL-2014 12:52:15 PM
T
ORDER_STATUS 4
14-JUL-2014 12:52:15 PM
T OWNER 32 14-JUL-2014 12:52:15 PM
T SECONDARY 2 14-JUL-2014 12:52:15 PM
T STATUS 2 14-JUL-2014 12:52:15 PM
T
SUBOBJECT_NAME 196
14-JUL-2014 12:52:15 PM
T TEMPORARY 2 14-JUL-2014 12:52:15 PM
T TIMESTAMP 1496 14-JUL-2014 12:52:15 PM
T X 3 14-JUL-2014
12:52:15 PM
17 rows selected.
rajesh@ORA11G>
So even though
data not changed in partition P2 It got analyzed. We can see the same thing happening in Oracle
12c too.
rajesh@PDB1> select * from v$version;
BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
5 rows selected.
rajesh@PDB1>
rajesh@PDB1> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T P1 14-JUL-2014 01:08:42 PM
T P2 14-JUL-2014 01:08:40 PM
2 rows selected.
rajesh@PDB1>
rajesh@PDB1> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1513 14-JUL-2014 01:08:42 PM
T
DATA_OBJECT_ID 6321
14-JUL-2014 01:08:42 PM
T
EDITIONABLE 2
14-JUL-2014 01:08:42 PM
T
EDITION_NAME 0 14-JUL-2014
01:08:42 PM
T GENERATED 2 14-JUL-2014 01:08:42 PM
T
LAST_DDL_TIME 1687
14-JUL-2014 01:08:42 PM
T NAMESPACE 18 14-JUL-2014 01:08:42 PM
T OBJECT_ID 89720 14-JUL-2014 01:08:42 PM
T
OBJECT_NAME 51544
14-JUL-2014 01:08:42 PM
T
OBJECT_TYPE 40
14-JUL-2014 01:08:42 PM
T
ORACLE_MAINTAIN 2
14-JUL-2014 01:08:42 PM
ED
T
ORDER_STATUS 2 14-JUL-2014 01:08:42 PM
T OWNER 32 14-JUL-2014 01:08:42 PM
T SECONDARY 2 14-JUL-2014 01:08:42 PM
T SHARING 3 14-JUL-2014 01:08:42 PM
T STATUS 1 14-JUL-2014 01:08:42 PM
T
SUBOBJECT_NAME 348
14-JUL-2014 01:08:42 PM
T TEMPORARY 2 14-JUL-2014 01:08:42 PM
T TIMESTAMP 1726 14-JUL-2014 01:08:42 PM
T X 2 14-JUL-2014 01:08:42 PM
20 rows selected.
rajesh@PDB1> update t set order_status = 77
2 where x = 1 and rownum = 1;
1 row updated.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1>
rajesh@PDB1> alter table t add partition p3 values(3);
Table altered.
rajesh@PDB1> insert into t
2 select a.*, 3 as id,
3 (mod(rownum,3)+1)*5 as order_status
4 from all_objects a
5 where rownum <=1000;
1000 rows created.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1>
rajesh@PDB1> exec dbms_stats.gather_table_Stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@PDB1>
rajesh@PDB1> select table_name,partition_name,last_analyzed
2 from user_tab_partitions
3 where table_name ='T' ;
TABLE_NAME PARTITION_ LAST_ANALYZED
---------- ---------- -----------------------
T
P1 14-JUL-2014 01:09:06 PM
T
P2 14-JUL-2014 01:09:06 PM
T
P3 14-JUL-2014 01:09:06 PM
3 rows selected.
rajesh@PDB1>
rajesh@PDB1> select table_name,column_name,num_distinct,
2 last_analyzed
3 from user_TAB_col_statistics
4 where table_name ='T'
5 order by column_name;
TABLE_NAME COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
T CREATED 1513 14-JUL-2014 01:09:07 PM
T
DATA_OBJECT_ID 6321
14-JUL-2014 01:09:07 PM
T
EDITIONABLE 2
14-JUL-2014 01:09:07 PM
T
EDITION_NAME 0
14-JUL-2014 01:09:07 PM
T GENERATED 2 14-JUL-2014 01:09:07 PM
T
LAST_DDL_TIME 1687
14-JUL-2014 01:09:07 PM
T NAMESPACE 18 14-JUL-2014 01:09:07 PM
T OBJECT_ID 90148 14-JUL-2014 01:09:07 PM
T OBJECT_NAME 51544 14-JUL-2014 01:09:07 PM
T
OBJECT_TYPE 40
14-JUL-2014 01:09:07 PM
T
ORACLE_MAINTAIN 2
14-JUL-2014 01:09:07 PM
ED
T
ORDER_STATUS 4
14-JUL-2014 01:09:07 PM
T OWNER 32 14-JUL-2014 01:09:07 PM
T SECONDARY 2 14-JUL-2014 01:09:07 PM
T SHARING 3 14-JUL-2014 01:09:07 PM
T STATUS 1 14-JUL-2014 01:09:07 PM
T
SUBOBJECT_NAME 348
14-JUL-2014 01:09:07 PM
T TEMPORARY 2 14-JUL-2014 01:09:07 PM
T TIMESTAMP 1726 14-JUL-2014 01:09:07 PM
T X 3 14-JUL-2014
01:09:07 PM
20 rows selected.
Seems to be we
are hitting up a Bug 16726844 - Statistics may be re-gathered on partitions
that have no changes (Doc ID 16726844.8) – But not sure.
No comments:
Post a Comment