1. 分析指定 schema

declare
start_time varchar2(21);
end_time varchar2(21);
user_name varchar2(30);
begin
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line('-----------------------------------');
user_name := upper('&username');
dbms_output.put_line('schema: ' || user_name);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into start_time from dual;
dbms_output.put_line('analyze start : ' || start_time);
dbms_stats.gather_schema_stats(
ownname => user_name,
estimate_percent => 20,
block_sample=> true,
cascade=>true
);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into end_time from dual;
dbms_output.put_line('analyze end : ' || end_time);
dbms_output.put_line('-----------------------------------');
end;
/

2. 分析全部 schema

declare
start_time varchar2(21);
end_time varchar2(21);
begin
dbms_output.disable;
dbms_output.enable(1000000);
dbms_output.put_line('-----------------------------------');
for owner in (select username from dba_users where username not in ('SYS','SYSTEM'))
loop
dbms_output.put_line('schema: '||owner.username);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into start_time from dual;
dbms_output.put_line('analyze start : '||start_time);
dbms_stats.gather_schema_stats(
ownname => owner.username,
estimate_percent => 20,
block_sample=> true,
cascade=>true
);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into end_time from dual;
dbms_output.put_line('analyze end : '||end_time);
dbms_output.put_line('');
end loop;
dbms_output.put_line('-----------------------------------');
end;
/

ITPub博客

dbms_stats包的使用

最新文章