RDBMS Version : 11.2.0.4
OS Platform : Oracle Linux 6.4
Clustering : Yes. 3-Node RAC
I just discovered that Auto Gather stats were not running in one our Critical DBs for a month. This is an OLTP DB. Application team have complained about bad DB performance.
Around 800 tables in a schema are stale and I want to run schema level Gather stats using DBMS_STATS.GATHER_SCHEMA_STATS . I am thinking of using DOP of 100. This schema is 8 TeraBytes in size.
This schema has around 300 tables which are partitioned . Is it safe to run gather stats during business hours ?
I mean what could be performance impact (or any other sort of impact) in running gather stats during business hours ?
Application team manager want me to quantify the performance degradtion due to Gather stats . like DB will 2% slower ...