Tuesday, May 1, 2012

Check the value of ESTIMATE_PERCENT:

The default value of estimate is AUTO_SAMPLE_SIZE.

10g

SQL> select
DBMS_STATS.GET_PARAM ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PARAM ('CASCADE'),
DBMS_STATS.GET_PARAM ('DEGREE'),
DBMS_STATS.GET_PARAM ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PARAM ('METHOD_OPT'),
DBMS_STATS.GET_PARAM ('NO_INVALIDATE'),
DBMS_STATS.GET_PARAM ('GRANULARITY')
from dual;

11g

SQL> select
DBMS_STATS.GET_PREFS ('AUTOSTATS_TARGET'),
DBMS_STATS.GET_PREFS ('CASCADE'),
DBMS_STATS.GET_PREFS ('DEGREE'),
DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT'),
DBMS_STATS.GET_PREFS ('METHOD_OPT'),
DBMS_STATS.GET_PREFS ('NO_INVALIDATE'),
DBMS_STATS.GET_PREFS ('GRANULARITY'),
DBMS_STATS.GET_PREFS ('PUBLISH'),
DBMS_STATS.GET_PREFS ('INCREMENTAL'),
DBMS_STATS.GET_PREFS ('STALE_PERCENT')
from dual;

To set the value of estimate_percent:
SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------
100

To reset the value of estimate_percent:
SQL> exec dbms_stats.RESET_PARAM_DEFAULTS();

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

Note: For better performance for JDE application, set estimate_percent to 30.

To delete any existing statistics (Replace use WPCUSER to the user you want to delete the statistics):
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('WPCUSER');
EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');
gather status and bounce the database.

No comments:

Post a Comment