Starting
with Oracle 12c, DBMS_STATS can create a synopsis for a non-partition table and
this synopsis can be used to maintain incremental statistics as part of a
partition exchange operation without having to explicitly gather the statistics
on the partition after the exchange.
rajesh@ORA12C> create table t1
2 partition by list(x)
3 ( partition p1 values(1) ,
4 partition p2 values(2) )
5 as
6 select owner,object_type,object_name,
7 mod(rownum,2)+1 x
8 from all_objects a ;
Table created.
rajesh@ORA12C> exec dbms_stats.set_table_prefs(user,'T1','INCREMENTAL','TRUE');
PL/SQL procedure successfully completed.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
90143 628 17-MAR-2016 05:56:49 pm
1 row selected.
rajesh@ORA12C> select partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 42676
17-MAR-2016 05:56:48 pm
P1
OBJECT_TYPE 38
17-MAR-2016 05:56:48 pm
P1 OWNER 33 17-MAR-2016 05:56:48 pm
P1 X 1 17-MAR-2016
05:56:48 pm
P2
OBJECT_NAME 42368
17-MAR-2016 05:56:49 pm
P2
OBJECT_TYPE 37
17-MAR-2016 05:56:49 pm
P2 OWNER 33 17-MAR-2016 05:56:49 pm
P2 X 1 17-MAR-2016
05:56:49 pm
8 rows selected.
rajesh@ORA12C>
So far created a
partition table with Incremental preference set on the table and gathered
statistics on it.
rajesh@ORA12C> alter table t1 add partition p3 values(3);
Table altered.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@ORA12C>
Now, created an empty
partition and gather stats on it to have synopsis created for it.
Looking into the
statistics, show this.
rajesh@ORA12C> select num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
90143 628 17-MAR-2016 05:58:20 pm
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> select
partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 42676
17-MAR-2016 05:56:48 pm
P1
OBJECT_TYPE 38
17-MAR-2016 05:56:48 pm
P1 OWNER 33 17-MAR-2016 05:56:48 pm
P1 X 1 17-MAR-2016 05:56:48 pm
P2
OBJECT_NAME 42368
17-MAR-2016 05:56:49 pm
P2
OBJECT_TYPE 37
17-MAR-2016 05:56:49 pm
P2 OWNER 33 17-MAR-2016 05:56:49 pm
P2 X 1 17-MAR-2016 05:56:49 pm
P3
OBJECT_NAME 0
17-MAR-2016 05:58:19 pm
P3
OBJECT_TYPE 0
17-MAR-2016 05:58:19 pm
P3 OWNER 0 17-MAR-2016 05:58:19 pm
P3 X 0 17-MAR-2016 05:58:19 pm
12 rows selected.
rajesh@ORA12C>
Now, let us load a
non-partitioned table with statistics and synopsis created on it.
rajesh@ORA12C> create table t2
2 as
3 select owner,object_type,object_name, 3 x
4 from all_objects a ;
Table created.
rajesh@ORA12C> exec
dbms_stats.set_table_prefs(user,'T2','INCREMENTAL','TRUE');
PL/SQL procedure successfully completed.
rajesh@ORA12C> exec
dbms_stats.set_table_prefs(user,'T2','INCREMENTAL_LEVEL','TABLE');
PL/SQL procedure successfully completed.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
rajesh@ORA12C>
Let us review the
statistics on table T1 before the exchanging partition.
rajesh@ORA12C> select num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
90143 628 17-MAR-2016 05:58:20 pm
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> select
partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 42676
17-MAR-2016 05:56:48 pm
P1
OBJECT_TYPE 38
17-MAR-2016 05:56:48 pm
P1 OWNER 33 17-MAR-2016 05:56:48 pm
P1 X 1 17-MAR-2016 05:56:48 pm
P2
OBJECT_NAME 42368
17-MAR-2016 05:56:49 pm
P2
OBJECT_TYPE 37
17-MAR-2016 05:56:49 pm
P2 OWNER 33 17-MAR-2016 05:56:49 pm
P2 X 1 17-MAR-2016 05:56:49 pm
P3
OBJECT_NAME 0
17-MAR-2016 05:58:19 pm
P3
OBJECT_TYPE 0
17-MAR-2016 05:58:19 pm
P3 OWNER 0 17-MAR-2016 05:58:19 pm
P3 X 0 17-MAR-2016 05:58:19 pm
12 rows selected.
rajesh@ORA12C>
Exchanging the
partition P3 with table T2.
rajesh@ORA12C>
rajesh@ORA12C> alter table t1
2 exchange partition p3
3 with table t2 ;
Table altered.
Looking into the statistics
shows this.
rajesh@ORA12C> select num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
90143 628 17-MAR-2016 05:58:20 pm
1 row selected.
rajesh@ORA12C> select
partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 42676
17-MAR-2016 05:56:48 pm
P1
OBJECT_TYPE 38
17-MAR-2016 05:56:48 pm
P1 OWNER 33 17-MAR-2016 05:56:48 pm
P1 X 1 17-MAR-2016
05:56:48 pm
P2
OBJECT_NAME 42368
17-MAR-2016 05:56:49 pm
P2
OBJECT_TYPE 37
17-MAR-2016 05:56:49 pm
P2 OWNER 33 17-MAR-2016 05:56:49 pm
P2 X 1 17-MAR-2016
05:56:49 pm
P3
OBJECT_NAME 51936
17-MAR-2016 06:02:52 pm
P3
OBJECT_TYPE 40
17-MAR-2016 06:02:52 pm
P3
OWNER 33
17-MAR-2016 06:02:52 pm
P3
X 1
17-MAR-2016 06:02:52 pm
12 rows selected.
rajesh@ORA12C>
What happened is as
part of exchange partition the database swapped the segment data and statistics
of table T2 with partition P3 and updated the synopsis. But no changes to global statistics. So don’t be shocked!!!
The next time we do
stats gathering on table T1 database will make use of the synopsis from
partition P3 to update its global statistics.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
180255 1237 17-MAR-2016
06:13:47 pm
1 row selected.
rajesh@ORA12C> select
partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 42676
17-MAR-2016 05:56:48 pm
P1
OBJECT_TYPE 38
17-MAR-2016 05:56:48 pm
P1 OWNER 33 17-MAR-2016 05:56:48 pm
P1 X 1 17-MAR-2016
05:56:48 pm
P2
OBJECT_NAME 42368
17-MAR-2016 05:56:49 pm
P2
OBJECT_TYPE 37
17-MAR-2016 05:56:49 pm
P2 OWNER 33 17-MAR-2016 05:56:49 pm
P2 X 1 17-MAR-2016
05:56:49 pm
P3
OBJECT_NAME 51936
17-MAR-2016 06:02:52 pm
P3
OBJECT_TYPE 40
17-MAR-2016 06:02:52 pm
P3 OWNER 33 17-MAR-2016 06:02:52 pm
P3 X 1 17-MAR-2016
06:02:52 pm
12 rows selected.
rajesh@ORA12C>
You can see how the
global statistics got updated, but no changes to partition level statistics.
When repeated this
test in 11g, we see this.
rajesh@ORA11G> alter table t1
2 exchange partition p3
3 with table t2 ;
Table altered.
rajesh@ORA11G> select
num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
84692 592 17-MAR-2016 06:17:40 pm
1 row selected.
rajesh@ORA11G>
rajesh@ORA11G> select
partition_name,column_name,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
LAST_ANALYZED
---------- --------------- -----------------------
P1
OBJECT_NAME 17-MAR-2016
06:17:21 pm
P1
OBJECT_TYPE 17-MAR-2016 06:17:21
pm
P1 OWNER 17-MAR-2016 06:17:21 pm
P1 X 17-MAR-2016 06:17:21 pm
P2
OBJECT_NAME 17-MAR-2016
06:17:22 pm
P2
OBJECT_TYPE 17-MAR-2016
06:17:22 pm
P2 OWNER 17-MAR-2016 06:17:22 pm
P2 X 17-MAR-2016 06:17:22 pm
P3
OBJECT_NAME 17-MAR-2016
06:18:08 pm
P3
OBJECT_TYPE 17-MAR-2016
06:18:08 pm
P3 OWNER 17-MAR-2016 06:18:08 pm
P3 X 17-MAR-2016 06:18:08 pm
12 rows selected.
rajesh@ORA11G> exec
dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@ORA11G> select
num_rows,blocks,last_analyzed
2 from user_tables
3 where table_name ='T1';
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -----------------------
169386 1165 17-MAR-2016
06:18:44 pm
1 row selected.
rajesh@ORA11G>
rajesh@ORA11G> select
partition_name,column_name,num_distinct,last_analyzed
2 from user_part_col_statistics
3 where table_name ='T1'
4 order by partition_name,column_name;
PARTITION_ COLUMN_NAME
NUM_DISTINCT LAST_ANALYZED
---------- --------------- ------------ -----------------------
P1
OBJECT_NAME 40196
17-MAR-2016 06:17:21 pm
P1
OBJECT_TYPE 38
17-MAR-2016 06:17:21 pm
P1 OWNER 31 17-MAR-2016 06:17:21 pm
P1 X 1 17-MAR-2016
06:17:21 pm
P2
OBJECT_NAME 40436
17-MAR-2016 06:17:22 pm
P2
OBJECT_TYPE 37
17-MAR-2016 06:17:22 pm
P2 OWNER 30 17-MAR-2016 06:17:22 pm
P2 X 1 17-MAR-2016
06:17:22 pm
P3
OBJECT_NAME 49920
17-MAR-2016 06:18:44 pm
P3
OBJECT_TYPE 40
17-MAR-2016 06:18:44 pm
P3
OWNER 31
17-MAR-2016 06:18:44 pm
P3
X 1
17-MAR-2016 06:18:44 pm
12 rows selected.
rajesh@ORA11G>
So gathering the
stats after the exchange partition operation in 11g, has resulted in changes to
both global and partition level statistics. But in 12c no changes to partition
level statistics, it make use of the synopsis to refresh the global statistics.
No comments:
Post a Comment