oracle 통계정보 수집

oracle 통계정보 수집

10g

 

create or replace procedure sp_stats2
(
    p_return_val out number,
    p_return_msg out varchar2
)

 is

    v_per number; -- 분석비율
    v_objname varchar2(100); -- 분석대상
    v_objtype varchar2(100); -- 분석타입(table, index)
    v_cnt number; -- 대상갯수

    cursor cur_sql is
        select t.object_type,
               t.object_name
          from user_objects t
         where t.object_type in ('TABLE', 'INDEX')
           and t.object_name in ('TABLE_NAME')
         order by 1,
                  2;

begin

    -- ORA-20005
    -- exec dbms_stats.unlock_table_stats('OWNER', 'TABLE_NAME');
    -- exec dbms_stats.lock_table_stats('OWNER', 'TABLE_NAME');
    
    v_per := 10;
    v_cnt := 0;

    p_return_msg := 'PR_STATS FOR ';

    for rec_sql in cur_sql
    loop

        v_objtype := rec_sql.object_type;
        v_objname := rec_sql.object_name;

        p_return_msg := 'PR_STATS dbms_stats.gather ' || v_objtype || '/' || v_objname;
        dbms_output.put_line(p_return_msg);

        if v_objtype = 'TABLE' then
            dbms_stats.gather_table_stats(ownname => 'OWNER', tabname => v_objname, partname => null, estimate_percent => v_per);
        elsif v_objtype = 'INDEX' then
            dbms_stats.gather_index_stats(ownname => 'OWNER', indname => v_objname, partname => null, estimate_percent => v_per);
        end if;

        v_cnt := v_cnt + 1;

    end loop;

    p_return_val := 1;
    p_return_msg := '▶ PR_STATS OK/' || to_char(v_cnt);
    dbms_output.put_line(p_return_msg);

exception
    when others then
        p_return_val := -9;
        p_return_msg := sqlerrm || '/' || p_return_msg;
        rollback;
end;