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