DBMS_STATS consuming resource

Q: DBMS_STATS.GATHER_SCHEMA_STATS consuming much resources. How to optimize statistics collection using DBMS_STATS?

A: We DBAs used to collect object statistics to  improve performance of the database. But actually statistics collection will use much resources. You may see dbms_stats in top sql statements.

Collect statistics only in recovery window.

We do not need to collect statistics every day for all tables except those with a lot of changes. There is a way to collect statistics of only tables having outdated statistics. The method is to collect schema statistics only for stale statistics tables.

How does it works??

Our db will record all changes in a table in user_tab_modifications, When the modification reaches a threshold, the statistics will be marked as stale. And when you collect the statistics with stale option, It will collect statistics for only those tables with stale statistics.

How to do it?

Here is an example

SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS (
3 ownname => 'SCOTT',
4 estimate_percent => 20,
5 block_sample => TRUE,
6 method_opt => 'FOR COLUMNS SIZE 10',
7 options => 'GATHER STALE',
8 cascade => TRUE);
9 END;
10 /

You can also give GATHER AUTO instead of GATHER STALE.
visit oracle documentation for more information

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461