Steve,
Executive Summary:
The short answer is the key flag to watch/set is "GRANULARITY". It drives
what is DELETEd, or GATHERed. Note that while my tests are at TABLE not at
INDEX
level, as your question was framed, the results account for the behavior you
are seeing.
As for tests on query plan changes with dba_table_partitions.GLOBAL_STATS
changes, I
ran out of time to test that. ( My inner child revolted. ) I do know,
however, that
the CBO looks carefully at both partition and global stats. We have seen
disastrous
results after partition maintenance if we don't capture part and global
stats appropriately.
HTH.
~Ross
############################################################################
################################
select
dt.owner, dt.table_name, dtp.partition_name, dt.global_stats,
dtp.global_stats,dt.user_stats,dtp.user_stats
from
dba_tables dt, dba_tab_partitions dtp
where
dt.table_name=dtp.table_name and
dt.owner='ARBINET' and
dt.table_name='TCDR_CSP' and
dtp.partition_name = 'TCDR_CSP2004_12_31'
order by 1,2,3
/
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_31
YES YES NO YES
1 row selected.
SQL> exec
dbms_stats.delete_table_stats(ownname=>'ARBINET',tabname=>'TCDR_CSP',partnam
e=>'TCDR_CSP2004_12_31');
PL/SQL procedure successfully completed.
Now:
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_31
YES NO NO NO
1 row selected.
SQL> rem
SQL> rem Above, note the part-level stats are cleared in dtp; the flag is
not cleared in dt
SQL> rem
SQL> rem Now, clear out all stats, start again:
SQL> exec
dbms_stats.delete_table_stats(ownname=>'ARBINET',tabname=>'TCDR_CSP');
PL/SQL procedure successfully completed.
Now:
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_31
NO NO NO NO
1 row selected.
SQL> rem
SQL> rem i've deleted all stats from table successfully
SQL> rem
SQL> rem then we'll redo 'part-level' stats
SQL> rem
SQL> rem ...querying more rows to show global action....
select
dt.owner, dt.table_name, dtp.partition_name, dt.global_stats,
dtp.global_stats,dt.user_stats,dtp.user_stats
from
dba_tables dt, dba_tab_partitions dtp
where
dt.table_name=dtp.table_name and
dt.owner='ARBINET' and
dt.table_name='TCDR_CSP' and
dtp.partition_name like 'TCDR_CSP2004_12%'
order by 1,2,3
/
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_01
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_02
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_03
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_04
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_05
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_06
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_07
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_08
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_09
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_10
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_11
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_12
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_13
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_14
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_15
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_16
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_17
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_18
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_19
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_20
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_21
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_22
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_23
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_24
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_25
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_26
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_27
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_28
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_29
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_30
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_31
NO NO NO NO
31 rows selected.
SQL> set timing on
SQL> rem
SQL> rem now, going to re-run, trying to force to estimate just one
partition
SQL> rem ( I don't think it was doing that before, based on v$sql sweep...)
SQL> rem
SQL> exec
dbms_stats.gather_table_stats(ownname=>'ARBINET',tabname=>'TCDR_CSP',partnam
e=>'TCDR_CSP2004_12_31',granularity=>'PARTITION',estimate_percent=>1);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.31
SQL> rem
SQL> rem This works, apparently. :-)
SQL> rem
Now:
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_01
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_02
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_03
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_04
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_05
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_06
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_07
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_08
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_09
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_10
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_11
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_12
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_13
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_14
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_15
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_16
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_17
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_18
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_19
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_20
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_21
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_22
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_23
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_24
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_25
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_26
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_27
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_28
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_29
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_30
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_31
NO YES NO NO
31 rows selected.
Elapsed: 00:00:03.58
SQL> rem
SQL> rem in separate window, I analyzed, partlevel, granularity=PARTITION
for 12_08-12_10, and 12_30
SQL> rem
Now:
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_01
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_02
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_03
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_04
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_05
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_06
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_07
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_08
NO YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_09
NO YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_10
NO YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_11
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_12
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_13
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_14
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_15
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_16
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_17
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_18
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_19
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_20
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_21
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_22
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_23
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_24
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_25
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_26
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_27
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_28
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_29
NO NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_30
NO YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_31
NO YES NO NO
31 rows selected.
Elapsed: 00:00:00.68
SQL> rem
SQL> rem this has expected effect
SQL> rem
SQL> rem now, i'll try a very light global analyze.
SQL> rem still naming a partition, but letting the GRANULARITY default
SQL> rem
SQL> exec
dbms_stats.gather_table_stats(ownname=>'ARBINET',tabname=>'TCDR_CSP',partnam
e=>'TCDR_CSP2004_12_29',estimate_percent=>.001);
PL/SQL procedure successfully completed.
Elapsed: 00:11:06.09
Now:
OWNER TABLE_NAME PARTITION_NAME
GLO GLO USE USE
--------------- ------------------------------
- --- --- --- ---
ARBINET TCDR_CSP TCDR_CSP2004_12_01
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_02
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_03
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_04
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_05
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_06
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_07
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_08
YES YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_09
YES YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_10
YES YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_11
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_12
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_13
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_14
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_15
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_16
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_17
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_18
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_19
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_20
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_21
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_22
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_23
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_24
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_25
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_26
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_27
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_28
YES NO NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_29
YES YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_30
YES YES NO NO
ARBINET TCDR_CSP TCDR_CSP2004_12_31
YES YES NO NO
31 rows selected.
Elapsed: 00:00:01.05
SQL> rem
SQL> rem now, interestingly, i not only stat'ed the partition i asked for,
but the whole table!
SQL> rem
SQL> rem (watching underlying SQL validated this, btw)
SQL> rem
SQL>
-----Original Message-----
From: Orr, Steve [mailto:sorr_at_rightnow.com <mailto:sorr_at_rightnow.com> ]
Sent: Wednesday, December 08, 2004 10:54 AM
To: oracle-l_at_freelists.org
Subject: Partition Statistics
I created an index as follows:
CREATE UNIQUE INDEX iname ON tname(col1,col2,col3) LOCAL ( PARTITION
P20030101 TABLESPACE tname PCTFREE 0 NOLOGGING COMPUTE STATISTICS, PARTITION
P20030102 TABLESPACE tname PCTFREE 0 NOLOGGING COMPUTE STATISTICS, ...
PARTITION P20041231 TABLESPACE tname PCTFREE 0 NOLOGGING COMPUTE
STATISTICS);
When I query dba_ind_partitions all the stats are there but the value in the
global_stats column was 'NO' so I did this: EXEC =
dbms_stats.gather_index_stats(ownname=3D>'oname',indname=3D>'iname', -
partname=3D>'P20041207',granularity=3D>'PARTITION');
After the above the value in the global_stats column is 'YES' for the
specific partition recalc'd so now my question is what changed? Does the
optimizer look at the dba_ind_partitions.global_stats column and does it
make a difference?=20
On another experiment I tried this:
EXEC = dbms_stats.gather_index_stats(ownname=3D>'oname',indname=3D>'iname',
- partname=3D>'P20041206',granularity=3D>'DEFAULT');
With this granularity the system was churning away a long time so I
interrupted it. What was it doing? Was it gathering "global" status by
looking at all 700+ partitions even though I specified a single partition?
(There is just the one index on the table and it will be exceeding
100,000,000 rows sometime next week.)
Any good white papers on partitioning you would recommend? TIA!
Curious,
Steve Orr
Bozeman, Montana
--
http://www.freelists.org/webpage/oracle-l
<http://www.freelists.org/webpage/oracle-l>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 08 2004 - 13:16:48 CST
