Taking statistics and have them updated is a very important task on most Oracle databases, the CBO (Cost Based Optimizer) evaluates the cost of several Execution Paths, this cost is mainly based on physical and logical Disk IO, CPU use and network IO, if our stats are not being taken or aren’t updated there is a possibility for the CBO to take a wrong decision.
Make sure you have the system parameter STATISTICS_LEVEL set to TYPICAL or ALL (default=typical), this parameter indicates the level of the statistics to be taking for both, database and OS.
Statistics can be taken with only one command for the whole database, and we can use several options to improve the quality and the time it takes to collect them.
Let’s start with the basic recommendations:
1st.- Use the option GATHER_AUTO
2nd.- If possible take them in parallel
3rd.- Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE in the ESTIMATE_PERCENT parameter
Example to take database stats:
begin
dbms_stats.gather_database_stats(
options=>’gather auto’,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=>3) ;
end;
And here is an example to take stats from a particular schema:
begin
dbms_stats.gather_schema_stats(
ownname=>’myschema’,
options=>’gather auto’,
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
degree=>3);
end;
1st.- Use the option gather_auto (options=>’gather auto’)
You have several options when taking statistics, but GATHER AUTO will gather stats only from tables with EMPTY or STALE stats.
When a table or an object will have the STALE status? When the object have suffered at least 10% of changes.
Thanks to the monitoring feature Oracle can keep track of the changes and determine the stale tables. Starting with Oracle 10g this feature is ON by default, if you are using oracle 9i you can manually activate the monitoring:
alter table monitoring|nomonitoring;
To see how Oracle keep track of changes you can query the view *_TAB_MODIFICATIONS.
Where can I see the statistics?
To easily see the statistics you can query the following views:
USER|ALL|DBA_TAB_STATISTICS
USER|ALL|DBA_IND_STATISTICS
The column STALE_STATS will indicate those objects that are going to be analyzed the next time the procedure runs.
To see column stats you can also query:
USER|ALL|DBA_TAB_COL_STATISTICS;
USER|ALL|DBA_PART_COL_STATISTICS;
Here is a list of all options available:
• GATHER: Gathers statistics on all objects in the schema.
• GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
• GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
• GATHER EMPTY: Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics.
• LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.
• LIST STALE: Returns a list of stale objects as determined by looking at the *_tab_modifications views.
• LIST EMPTY: Returns a list of objects which currently have no statistics.
2nd.- If possible take them in parallel (degree=>3)
When taking statistics you can perform parallel queries using the degree parameter,this way your procedure will perform faster, the basic rule is to set the degree parameter equal to the number of CPUs (or cores) minus 1.
3rd. – Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE in the estimate_percent parameter
(estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE)
The RDBMS can determine this parameter and maybe set it to a lower value than the one we could assign manually.
I believe that the RDBMS have ways to calculate this value and it will be, probably, more accurate that the one set by us (and that sometimes it is figured out of nothing).
In the next post (part 2) I will explain the options for partitioned tables.
Here is the official documentation for oracle10g and oracle11g
source knowledge : https://oracleexamples.wordpress.com/2009/09/08/tips-for-dbms_stats-part-12/
asiandba 