Oracle 11g Enhanced Optimizer Statistics Maintenance!!

Page: 1 2 3 4

Oracle 11g enables you to default values of gather_database_stats with set_global_prefs, set_database_prefs, set_schema_prefs, set_table_prefs in the dbms_stats package. Oracle 11g also enables you to separate statistic gathering from statistic publishing. The benefit of separating statistic gathering from statistic publishing is that you can control when new statistics are published. Oracle By Example (OBE) scripts have made the life of database administrators easy by speeding up the testing of this powerful 11g feature.

Importing custemers_obe.dmp File:

First of all you need to import custemers_obe.dmp file under the SH Schema.

imp sh/sh file= customers_obe.dmp log=imp.log full=y

SQL> connect sh/sh

Setting STALE_PERCENT:

STALE_PERCENT determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be re-gathered. You can check the default settings of STALE_PERCENT by running below command

select dbms_stats.get_prefs ('STALE_PERCENT', ‘SH', ‘MY_STALE_PERCENT_TABLE') stale_percent from dual;

You can change the default setting for the database STALE_PERCENT by running below command.

execute dbms_stats.set_global_prefs ('STALE_PERCENT', ‘13');

Oracle 11g enables you to change STALE_PERCENT parameter on a table by table basis. For example you can run below command to set the STALE_PERCENT for the MY_STALE_PERCENT_TABLE table to 55%.

execute dbms_stats.set_table_prefs ('SH', ‘MY_STALE_PERCENT_TABLE', ‘STALE_PERCENT', ‘55');

In order to reset this parameter first you need to delete the table preference set on MY_STALE_PERCENT_TABLE so that the table has the global default value.

execute dbms_stats.delete_table_prefs ('SH', ‘MY_STALE_PERCENT_TABLE', ‘STALE_PERCENT');

select dbms_stats.get_prefs ('STALE_PERCENT', ‘SH', ‘MY_STALE_PERCENT_TABLE') stale_percent from dual;

Then reconnect as system or sysdba to reset the global defaults. Set the preference value to null and that restores the factory default value.

SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

SQL> conn sh/sh
SQL> select dbms_stats.get_prefs ('STALE_PERCENT', ‘SH', ‘MY_STALE_PERCENT_TABLE')
stale_percent from dual; Continued...

Page: 1 2 3 4

 More Database Articles
   Database Security: Step by step guideline
   Reveal the shades of Oracle result_cache!!
   Some Exciting Oracle Advisory Utilities you should not miss!!
   Oracle – SQL Guide for Parallel Stored Procedures
   The Secrets of Truly Dropping a Table in Oracle 10g!!