• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Oracle statistics 9i

I'm not able to find what is the difference in method_op parameter values.
What is the difference between value FOR ALL COLUMNS SIZE AUTO and FOR ALL INDEXED COLUMNS SIZE AUTO.
If we have indexed just few columns of the table, do we need to collect statistics for all columns
or is it sufficient to calculate statistics just for indexded columns?
0
folvo
Asked:
folvo
  • 3
  • 2
1 Solution
 
ravibhardwajCommented:
FOR ALL COLUMNS SIZE AUTO--> This basically means that Oracle will automatically decide for us which columns need histograms and which columns dont based on what it considers to be the distribution of values within a column and based on the workload associated with the table.
FOR ALL INDEXED COLUMNS--> This basically collects statistics for the indexed columns.

If you are collecting statistics only for the indexed columns, then for other columns,  the Optimizer is forced to make a guess, and that guess is 1%.
So Statistics are required on all columns to yield good plans  not just indexed columns.
You probably should not be using METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO', especially in a data warehouse where indexes are used sparingly.
0
 
folvoAuthor Commented:
Hi ravibhardwaj,

What exactly you mean by the "indexes are used sparingly". We have the staging area
in which are stored data for the migration from the source system to target. We are using
partitioned tables and local indexes. Data in this schema will be processed 24/7 this means that
we should maybe calculate statisticts often. We are not using monitoring but each extraction process
is extracting a huge amount of the data divided into the batches / paritiions. We are not using monitoring

Thanks
Folvo
0
 
folvoAuthor Commented:
Hi ravibhardwaj,

What exactly you mean by the "indexes are used sparingly". We have the staging area
in which are stored data for the migration from the source system to target. We are using
partitioned tables and local indexes. Data in this schema will be processed 24/7 this means that
we should maybe calculate statisticts often. We are not using monitoring but each extraction process
is extracting a huge amount of the data divided into the batches / paritiions. We are not using monitoring
over the tables, but the dml changes over the tables (transformation / validation) are bigger than 10%.
Currently the process of calculating statistics is taking like 14 hours using gather_schema_stats and I
just wanted to know if using just ALL INDEXED COLUMNS would be more sufficient.
dbms_stats.gather_schema_stats(ownname => 'MIG',
estimate_percent => NULL,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => dbms_stats.default_degree,
granularity => 'ALL',
cascade => true);

Maybe it is just the machine we are using (I don't know the specs).

Thanks
Folvo
0
 
ravibhardwajCommented:
Hi,
I just wanted to say that you should not use FOR ALL INDEXED COLUMNS SIZE AUTO in the situation where u have less number of indexes and u have more data retrieving on non-indexed columns also.
If in you have lots of indexes and you are mostly retrieving data on the indexing columns then you can use FOR ALL INDEXED COLUMNS SIZE AUTO.
0
 
folvoAuthor Commented:
ravibhardwaj , thanks for you help and quick response.

Best regards
Folvo
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now