I found my answer to gathering statistics on partitioned tables. In short, the "LIST STALE"
option of dbms_stats.gather_database_stats will report the stale partition when
enough data has changed. In addition, if enough of the table structure has
changed, it will report that the entire table needs analyzing.
See example below:
Lets setup a partitioned table with 20 partitions and turn on monitoring for the table...
ops$jeffh@dev920.us> !more part_test_setup.sql
CREATE TABLE PartTest (
dt DATE,
txt VARCHAR2(60)
)
PARTITION BY RANGE (dt) (
PARTITION part_01 VALUES LESS THAN
(to_date('07/01/2005','mm/dd/yyyy')),
PARTITION part_02 VALUES LESS THAN
(to_date('08/01/2005','mm/dd/yyyy')),
PARTITION part_03 VALUES LESS THAN
(to_date('09/01/2005','mm/dd/yyyy')),
PARTITION part_04 VALUES LESS THAN
(to_date('10/01/2005','mm/dd/yyyy')),
PARTITION part_05 VALUES LESS THAN
(to_date('11/01/2005','mm/dd/yyyy')),
PARTITION part_06 VALUES LESS THAN
(to_date('12/01/2005','mm/dd/yyyy')),
PARTITION part_07 VALUES LESS THAN
(to_date('01/01/2006','mm/dd/yyyy')),
PARTITION part_08 VALUES LESS THAN
(to_date('02/01/2006','mm/dd/yyyy')),
PARTITION part_09 VALUES LESS THAN
(to_date('03/01/2006','mm/dd/yyyy')),
PARTITION part_10 VALUES LESS THAN
(to_date('04/01/2006','mm/dd/yyyy')),
PARTITION part_11 VALUES LESS THAN
(to_date('05/01/2006','mm/dd/yyyy')),
PARTITION part_12 VALUES LESS THAN
(to_date('06/01/2006','mm/dd/yyyy')),
PARTITION part_13 VALUES LESS THAN
(to_date('07/01/2006','mm/dd/yyyy')),
PARTITION part_14 VALUES LESS THAN
(to_date('08/01/2006','mm/dd/yyyy')),
PARTITION part_15 VALUES LESS THAN
(to_date('09/01/2006','mm/dd/yyyy')),
PARTITION part_16 VALUES LESS THAN
(to_date('10/01/2006','mm/dd/yyyy')),
PARTITION part_17 VALUES LESS THAN
(to_date('11/01/2006','mm/dd/yyyy')),
PARTITION part_18 VALUES LESS THAN
(to_date('12/01/2006','mm/dd/yyyy')),
PARTITION part_19 VALUES LESS THAN
(to_date('01/01/2007','mm/dd/yyyy')),
PARTITION part_20 VALUES LESS THAN
(to_date('02/01/2007','mm/dd/yyyy'))
)
/
ALTER TABLE PartTest MONITORING
/
ops$jeffh@dev920.us> @part_test_setup.sql
Table created.
Elapsed: 00:00:00.34
Table altered.
Elapsed: 00:00:00.02
Great. Now we need to fill that table with some data. We don't need random data, just a bunch of data in some of the partitions. I'll also analyze the table and see what modifications Oracle thinks is done.
ops$jeffh@dev920.us> !more part_test_fill.sql
DECLARE
myDate DATE;
BEGIN
FOR i IN 1..1000000 LOOP
myDate := SYSDATE+MOD(i,400);
INSERT INTO PartTest
VALUES (
myDate,
to_char(myDate,'YYYYMMDDHH24MISS')
);
IF mod(i,10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
analyze table parttest compute statistics
/
set pages 2000
select partition_name, num_rows
from dba_tab_partitions
where table_name = 'PARTTEST'
order by partition_name
/
select
partition_name,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
TRUNCATED,
DROP_SEGMENTS
from user_tab_modifications
where table_name = 'PARTTEST'
/
ops$jeffh@dev920.us> @part_test_fill
PL/SQL procedure successfully completed.
Elapsed: 00:06:02.39
Table analyzed.
Elapsed: 00:00:38.23
PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART_01 62500
PART_02 77500
PART_03 77500
PART_04 75000
PART_05 77500
PART_06 75000
PART_07 77500
PART_08 77500
PART_09 70000
PART_10 77500
PART_11 75000
PART_12 77500
PART_13 75000
PART_14 25000
PART_15 0
PART_16 0
PART_17 0
PART_18 0
PART_19 0
PART_20 0
20 rows selected.
Elapsed: 00:00:00.40
no rows selected
Elapsed: 00:00:00.00
ops$jeffh@dev920.us>
Lets add some data to the later partitions just so Oracle will have something to report.
ops$jeffh@dev920.us> !more part_test_fill15.sql
DECLARE
myDate DATE;
BEGIN
FOR i IN 1..10000 LOOP
myDate := to_date('09/01/2006','mm/dd/yyyy')
- mod(i, 30);
INSERT INTO PartTest
VALUES (
myDate,
to_char(myDate,'YYYYMMDDHH24MISS')
);
END LOOP;
COMMIT;
END;
/
set pages 2000
select
partition_name,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
TRUNCATED,
DROP_SEGMENTS
from user_tab_modifications
where table_name = 'PARTTEST'
/
ops$jeffh@dev920.us> @part_test_fill15
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.64
no rows selected
Elapsed: 00:00:00.01
Now, we have to wait 15 minutes until SMON picks up the changes.
OK. Lets see what dbms_stats.gather_database_stats tells us...
ops$jeffh@dev920.us> @need_to_analyze
OPS$JEFFH.PARTTEST(TABLE)|PART_15
OPS$JEFFH.PARTTEST(TABLE)|PART_16
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.87
Alrighty, then. dbms_stats.gather_database_stats is telling us that PART_15 and PART_16 need to be analyzed.
Just for fun, lets see what happens when we drop a couple of the partitions in the "front" of the table...
ops$jeffh@dev920.us> alter table parttest
drop partition part_01;
Table altered.
Elapsed: 00:00:00.54
ops$jeffh@dev920.us> alter table parttest
drop partition part_02;
Table altered.
Elapsed: 00:00:00.06
ops$jeffh@dev920.us> alter table parttest
drop partition part_03;
Table altered.
Elapsed: 00:00:00.07
ops$jeffh@dev920.us> alter table parttest
drop partition part_04;
Table altered.
Elapsed: 00:00:00.05
ops$jeffh@dev920.us> alter table parttest
drop partition part_05;
Table altered.
Elapsed: 00:00:00.05
ops$jeffh@dev920.us> @need_to_analyze
OPS$JEFFH.PARTTEST(TABLE)|PART_15
OPS$JEFFH.PARTTEST(TABLE)|PART_16
OPS$JEFFH.PARTTEST(TABLE)|
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.70
ops$jeffh@dev920.us>
Sweet. Oracle thinks we need to analyze PART_15, PART_16, and the entire PARTTEST table.
2 comments:
On my last reply I was going to mention ALL_TAB_MODIFICATIONS - could give you a view as to what is going on behind the covers
Yeah, I found that out from your hint about user_tab_modifcations and some docs. I thought there was a dba_tab_modifications too, but I didn't see it on my test instance. Hmmm. This method will fit in good with my analyze procedure.
Post a Comment