[性能调优] dbms_stats 包的使用

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;
/