This content has been marked as final. Show 9 replies
sam995972 wrote:Please read,
am new to database, how to gather statistics in oracle 11g? like database/schema/table wise?
what is the impact gathering stats daily basis? how to automate stats gathering database wise?
can anyone please suggest & explain with example if possible.
thanks Aman for sharing the link.
i have one doubt
1 exec dbms_stats.gather_schema_statistics('test',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE)
2 exec dbms_stats.gather_schema_statistics('test',CASCADE=>TRUE).
what is the difference between above statement.which one is recommended ?
what is the difference between above statement.which one is recommended ?Normally, there's no difference as DBMS_STATS.AUTO_SAMPLE_SIZE is the default (in 11.2), except if you change the default settings. You'll find more details on the link below:
Hope this help.
Edited by: Lubiez Jean-Valentin on Jun 7, 2013 9:48 AM
from the link posted before by Aman
AUTO_SAMPLE_SIZE lets Oracle Database determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes.
If you ommited this clause, full scan will be done: (again in the document attached by Aman)
Gathering statistics without sampling requires full table scans and sorts of entire tables. Sampling minimizes the resources necessary to gather statistics
thanks Jean-Valentin Lubiez for quick reply.
actually one of my prod box we are not gathered any stats yet.business is planning to gather the database stats for better performance.
what will be the impact ?any idea?what happens if we gather the stats now?any plans will changes?
Edited by: sam995972 on Jun 7, 2013 12:55 AM
what will be the impact ?any idea?what happens if we gather the stats now?any plans will changes?Yes, the Execution Plans may change. So, it's better to test before applying on a Production Database.
So as to reduce risks of regression, you may use SQL Plan Baselines:
Overview of SQL Plan Baselines
Hope this help,
thanks for reply.
actually my server is running with 8 cpu, do i need mention degree as 8 or more.
below statement is it correct approach?please suggest.
exec dbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
estimate_percent => dbms_stats.auto_sample_size, -
Edited by: sam995972 on Jun 7, 2013 1:36 AM
You have also the value DBMS_STATS.AUTO_DEGREE which let Oracle choose between 1 and the DEFAULT_DEGREE.
Anyway, don't forget that increasing parallelism could be CPU intensive. It's good to read the following Note of My Oracle Support so as to make the better choice:
*How To Troubleshoot Statistics Collection Running or Not running In Parallel Issues [ID 1408464.1]*Hope this help,
Edited by: Lubiez Jean-Valentin on Jun 7, 2013 10:55 AM
On 11g, when you create a database, Oracle creates a job, which will run at 22:00 every day and do the stats on all objects in the database, which are marked as stale.
Unless your DBAs have explicitely disabled/stopped this job, you need not do anything for stats.
Run following query on your database (this has objects from SYS as well), which will show when the stats was done on your database
select * from dba_autotask_operation ;
select table_name, object_type, last_analyzed, stale_stats from dba_tab_statistics order by last_analyzed desc ;