Re: Partition Statistics
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Dec 2004 20:33:47 +0100
Hi Steve,
>----- Original Message -----
>From: "Orr, Steve" <sorr@xxxxxxxxxxxx>
>To: <oracle-l@xxxxxxxxxxxxx>
>Sent: Wednesday, December 08, 2004 4:54 PM
>Subject: Partition Statistics
>
>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
In my experience with rolling windows partitions you can expect satisfactory
result while maintaining statistics on the partition level only.
Particularly when those conditions are fulfilled:
* the number of rows resp. avg_row_len in each partition is more or less the
same
* the column statistics (above all density) is similar in all partitions for
each column
* and most important - the global density is close to the local (partition)
density for each column
(This may be a common case when you have partitions containing daily or
monthly data and a constant number of partitions)
In case when all indices on the partitioned table are defined local there is
really not very much "value added" in the global statistics. (Of course in
case when e.g. the local density of some columns is low but the global is
high this is not true).
Mathematically seen there is no possibility to calculate all global
statistics from local (partition) statistics - particularly the density. But
if the above condition are in place, it isn't a big task. I assume there is
an algorithm for default global statistics for partitioned table in case
that local statistics are defined. I assume this could be something like
"get statistics from one (or more) partition(s) and do a little math on it"
.
I observed relatively precise CBO estimation on queries of a partitioned
table with local statistics only.
In times of dynamic sampling there is always a danger of idle work if
statistics are missing, so there is a possible compromise to refresh the
global statistics not very frequently with appropriate sampling or even to
set them manually. Once more this is valid only on rolling windows with the
conditions above fulfilled.
> I created an index as follows
did you consider the possibility of computing statistics while creating
index?
>What was it doing? Was it gathering "global" status by
>looking at all 700+ partitions even though I specified a single
>partition?
One of the advantages of dbms_stats over analyze table is that you can
observe the SQL statement gathering statistics via standard querying of
v$session and v$sqlarea.
(The second advantage is that you get reportedly different results:)
>Any good white papers on partitioning you would recommend?
Either do I. Any volunteers?
Regards
Jaromir D.B. Nemec
http://www.db-nemec.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
