oracle关闭删除自动统计信息

来源：互联网发布：h3c vlan加端口编辑：程序博客网时间：2024/06/14 17:45

--关闭自动统计信息

select client_name,status from dba_autotask_client;

auto optimizer stats collection ENABLE
sql tuning advisor DISABLED
auto space advisor DISABLED

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

PL/SQL procedure successfully completed

SQL>

select client_name,status from dba_autotask_client;

auto optimizer stats collection DISABLED
sql tuning advisor DISABLED
auto space advisor DISABLED

–删除收集信息
EXEC DBMS_STATS.delete_database_stats; --删除数据库统计信息
EXEC DBMS_STATS.delete_schema_stats('SCOTT'); --删除用户的统计信息
--逐个用户删除
select 'exec DBMS_STATS.delete_schema_stats('''||username||''''||');' from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','DBSNMP')

EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES'); --删除表的统计信息
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK'); --删除索引统计信息

0 0