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;
/
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博客 ” ,链接:http://blog.itpub.net/22558114/viewspace-1097820/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22558114/viewspace-1097820/