Wednesday, May 4, 2016

In oracle, How to obtain statistics preferences for all tables

The oracle package dbms_stats provides a get_prefs stored procedure to retrieve statisticspreferences , for one given parameter at a time and:

  1. At DB level: 
    select dbms_stats.get_prefs('METHOD_OPT', null,null)  from dual;
  2. At schema level: 
    select dbms_stats.get_prefs('METHOD_OPT', 'MYSCHEMA',null)  from dual;
  3. At table level: 
    select dbms_stats.get_prefs('METHOD_OPT', 'MYSCHEMA','MYTABLE')  from dual;
Naturally that's a very cumbersome method if a parameter needs to be verified at table level.

The following PL/SQL will display the statistics preferences for all tables in the database by default or a specific table if mentioned. It's easy to modify it and insert the result in a table instead or change it into a full on stored procedure.

DBA privilege is required.

declare
 p_owner varchar2(15) := 'SOMESCHEMA';
 p_tablename varchar2(20) := null;
 v_result varchar(100);
 v_tablename dba_tables.table_name%type;
 cursor tablelist is
            select table_name 
            from dba_tables
            where owner=p_owner;
begin
   
    if p_tablename is null then
         open tablelist;
         loop
            fetch tablelist into v_tablename;
            exit when tablelist%notfound;
                dbms_output.enable(5000);
                select dbms_stats.get_prefs('METHOD_OPT', p_owner,v_tablename) into v_result from dual;
                dbms_output.put_line(p_owner||' - '||v_tablename||' - METHOD_OPT - '||v_result);
                select dbms_stats.get_prefs('ESTIMATE_PERCENT', p_owner,v_tablename) into v_result from dual;
                dbms_output.put_line(p_owner||' - '||v_tablename||' - ESTIMATE_PERCENT - '||v_result);
                select dbms_stats.get_prefs('DEGREE', p_owner,v_tablename) into v_result from dual;
                dbms_output.put_line(p_owner||' - '||v_tablename||' - DEGREE - '||v_result);
                 select dbms_stats.get_prefs('CASCADE', p_owner,v_tablename) into v_result from dual;
                dbms_output.put_line(p_owner||' - '||v_tablename||' - CASCADE - '||v_result);
                 select dbms_stats.get_prefs('NO_INVALIDATE', p_owner,v_tablename) into v_result from dual;
                dbms_output.put_line(p_owner||' - '||v_tablename||' - NO_INVALIDATE - '||v_result);
         end loop;
         close tablelist;
    else
        select dbms_stats.get_prefs('METHOD_OPT', p_owner,p_tablename) into v_result from dual;
        dbms_output.put_line(p_owner||' - '||p_tablename||' - METHOD_OPT - '||v_result);
        select dbms_stats.get_prefs('ESTIMATE_PERCENT', p_owner,p_tablename) into v_result from dual;
        dbms_output.put_line(p_owner||' - '||p_tablename||' - ESTIMATE_PERCENT - '||v_result);
        select dbms_stats.get_prefs('DEGREE', p_owner,p_tablename) into v_result from dual;
        dbms_output.put_line(p_owner||' - '||p_tablename||' - DEGREE - '||v_result);
         select dbms_stats.get_prefs('CASCADE', p_owner,p_tablename) into v_result from dual;
        dbms_output.put_line(p_owner||' - '||p_tablename||' - CASCADE - '||v_result);
         select dbms_stats.get_prefs('NO_INVALIDATE', p_owner,p_tablename) into v_result from dual;
        dbms_output.put_line(p_owner||' - '||p_tablename||' - NO_INVALIDATE - '||v_result);
    end if;   
end;
/ 

No comments :

Post a Comment