oracle 手动收集统计信息,Oracle 手动搜集统计信息

--禁用自动收集

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

--启用自动收集

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

--查看job

select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection DISABLED

auto space advisor DISABLED

sql tuning advisor DISABLED

sqlplus / as sysdba<

BEGIN

dbms_stats.gather_schema_stats(

ownname=>'VXSPACE',

METHOD_OPT=>'for all columns size repeat',

CASCADE=>TRUE,

ESTIMATE_PERCENT=>100,

DEGREE=>2);

END;

/

exit

!