Monday, August 29, 2005

Another Stats Extraction Routine

This post is related to my previous entry about extracting useful information to aid in diagnosing query performance.

This routine can be passed a statement id (for the plan_table) or a hash_value (for v$sql_plan). It then looks at the base tables and indexes referenced by the plan and extracts statistics. Checking some of these details, such as last analyzed date or the number of rows, may indicate why the optimizer has chosen a particular path.

Like my previous entry on using DBMS_XPLAN with V$SQL_PLAN, it uses invoker rights and dynamic SQL so that an explicit grant is not required for the user on V$SQL_PLAN.

create or replace function optimizer_data
  (p_statement_id in varchar2 default null,
  p_hash_value in number default null,
  p_tab_name in varchar2 default 'PLAN_TABLE')
return sys.DBMS_DEBUG_VC2COLL
authid current_user is
  --
  cur_plan_det SYS_REFCURSOR;
  TYPE rec_plan_det is RECORD
    (obj_owner varchar2(50),
     obj_name varchar2(50),
     access_pred varchar2(4000),
     filter_pred varchar2(4000),
     operation varchar2(50));
  TYPE tab_plan_det is TABLE of rec_plan_det index by pls_integer;
  r_last rec_plan_det;
  r_curr rec_plan_det;
  t_plan_det tab_plan_det;
  v_ind number;
  v_anal_date date;
  --
  v_return sys.DBMS_DEBUG_VC2COLL := sys.DBMS_DEBUG_VC2COLL();
  v_ret_ind number := 1;
  --
  FUNCTION hexstr( p_in IN VARCHAR2 ) RETURN VARCHAR2 IS
    v_str VARCHAR2(4000);
    v_return VARCHAR2(4000);
  BEGIN
    v_str := p_in;
    WHILE ( v_str IS NOT NULL ) LOOP
      v_return := v_return || CHR(TO_NUMBER(SUBSTR(v_str,1,2),'xx'));
      v_str := SUBSTR( v_str, 3 );
    END LOOP;
    RETURN v_return;
  END;
  --
begin
  if p_hash_value is not null then
    open cur_plan_det for
     'select distinct object_owner, object_name, access_predicates, filter_predicates, operation '||
     'from v$sql_plan '||
     'where operation in (''TABLE ACCESS'',''INDEX'') '||
     'and hash_value = :b1 '||
     'order by 1,2 ' using p_hash_value;
  elsif p_statement_id is not null then
    open cur_plan_det for
     'select distinct object_owner, object_name, access_predicates, filter_predicates, operation '||
     'from '||p_tab_name||' '||
     'where operation in (''TABLE ACCESS'',''INDEX'') '||
     'and statement_id = :b1 '||
     'order by 1,2 ' using p_statement_id;
  else
    open cur_plan_det for
     'select distinct object_owner, object_name, access_predicates, filter_predicates, operation '||
     'from '||p_tab_name||' '||
     'where operation in (''TABLE ACCESS'',''INDEX'') '||
     'and timestamp = (select max(timestamp) from '||p_tab_name||')'||
     'order by 1,2 ';
  end if;
  fetch cur_plan_det bulk collect into t_plan_det;
  close cur_plan_det;
  --
  v_ind := t_plan_det.first;
  while v_ind is not null loop
    r_curr := t_plan_det(v_ind);
    if r_curr.obj_owner||'.'||r_curr.obj_name != r_last.obj_owner||'.'||r_last.obj_name then
      if r_curr.operation = 'INDEX' then
         v_return.extend(3);
         v_return(v_ret_ind) := ' ';
         v_ret_ind := v_ret_ind + 1;
         select 'INDEX:'||r_curr.obj_owner||'.'||r_curr.obj_name||' ('||index_type||')',
                ' Lblk:'||to_char(leaf_blocks,'999,999,990')||
                ' Keys:'||to_char(distinct_keys,'999,999,999,990')||
                ' Rows:'||to_char(num_rows,'999,999,999,990')||
                ' Cstr:'||to_char(Clustering_Factor,'999,990.00')||
                ' Anal:'||to_char(last_analyzed,'DD-MON-YY'), last_analyzed
         into v_return(v_ret_ind), v_return(v_ret_ind+1), v_anal_date
         from all_indexes
         where owner=r_curr.obj_owner
         and index_name=r_curr.obj_name;
         v_ret_ind := v_ret_ind + 2;
      else
         v_return.extend(3);
         v_return(v_ret_ind) := ' ';
         v_return(v_ret_ind+1) := 'TABLE:'||rpad(r_curr.obj_owner||'.'||r_curr.obj_name,64);
         v_ret_ind := v_ret_ind + 2;
         select ' Blks:'||to_char(blocks,'999,999,990')||
                ' Rows:'||to_char(num_rows,'999,999,999,990')||
                ' Smpl:'||to_char(sample_size,'999,999,999,990')||
                ' Len:'||to_char(avg_row_len,'999,999,990')||
                ' Anal:'||to_char(last_analyzed,'DD-MON-YY'), last_analyzed
         into v_return(v_ret_ind), v_anal_date
         from all_tables
         where owner = r_curr.obj_owner
         and table_name = r_curr.obj_name;
         v_ret_ind := v_ret_ind + 1;
      end if;
    end if;
    if r_curr.operation = 'INDEX' then
      for c_tcol in
          (select t.column_name, t.data_type, t.num_distinct, trunc(t.density,4) density,
                  t.num_nulls, t.num_buckets, t.last_analyzed, t.avg_col_len,
                  rawtohex(t.low_value) low, rawtohex(t.high_value) high, i.column_position
          from all_tab_columns t, all_ind_columns i
          where t.owner = i.table_owner
          and t.table_name = i.table_name
          and t.column_name = i.column_name
          and i.index_owner = r_curr.obj_owner
          and i.index_name = r_curr.obj_name
          order by column_position)
      loop
          v_return.extend(2);
          if c_tcol.data_type in ('CHAR','VARCHAR2') then
            v_return(v_ret_ind) := ' "'||hexstr(c_tcol.low)||'" to "'||hexstr(c_tcol.high)||'"';
          elsif c_tcol.data_type in ('NUMBER') then
            v_return(v_ret_ind) := ' "'|| to_number(c_tcol.low, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')||'" to "'||
                                            to_number(c_tcol.high,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')||'"';
          end if;
          v_return(v_ret_ind) := ' '||to_char(c_tcol.column_position,'990')||' '||
                                 rpad(c_tcol.column_name,31)||rpad(c_tcol.data_type,31)||v_return(v_ret_ind);
          v_return(v_ret_ind+1) := ' Dist:'||to_char(c_tcol.num_distinct,'999,999,990')||
                                   ' Null:'||to_char(c_tcol.num_nulls,'999,999,990')||' '||
                                   ' Dens:'||to_char(c_tcol.density,'0.00000')||
                                   ' Hist:'||to_char(c_tcol.num_buckets,'99,990')||
                                   ' Len :'||to_char(c_tcol.avg_col_len,'9990')||
                                   ' Anal:'||to_char(c_tcol.last_analyzed,'DD-MON-YY');
          v_ret_ind := v_ret_ind + 2;
      end loop;
      -- For Function based indexes
      for c_icol in
          (select c.column_position, e.column_expression
          from all_ind_columns c, all_ind_expressions e
          where e.index_owner = c.index_owner
          and e.index_name = c.index_name
          and e.column_position = c.column_position
          and c.index_owner = r_curr.obj_owner
          and c.index_name = r_curr.obj_name
          order by 1)
      loop
          v_return.extend;
          v_return(v_ret_ind) := ' '||c_icol.column_position||' '||c_icol.column_expression;
          v_ret_ind := v_ret_ind + 1;
      end loop;
    else
      for c_tcol in
          (select column_name, data_type, num_distinct, trunc(density,4) density, num_nulls, num_buckets,
                 last_analyzed, avg_col_len, rawtohex(low_value) low, rawtohex(high_value) high
          from all_tab_columns
          where owner = r_curr.obj_owner
          and table_name = r_curr.obj_name
          and (instr(nvl(r_curr.access_pred,'.'),'"'||column_name||'"') > 0
          or instr(nvl(r_curr.filter_pred,'.'),'"'||column_name||'"') > 0))
      loop
          v_return.extend(2);
          if c_tcol.data_type in ('CHAR','VARCHAR2') then
            v_return(v_ret_ind) := ' "'||hexstr(c_tcol.low)||'" to "'||hexstr(c_tcol.high)||'"';
          elsif c_tcol.data_type in ('NUMBER') then
            v_return(v_ret_ind) := ' "'|| to_number(c_tcol.low, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')||'" to "'||
                                            to_number(c_tcol.high,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')||'"';
          end if;
          v_return(v_ret_ind) := ' '||rpad(c_tcol.column_name,31)||rpad(c_tcol.data_type,31)||v_return(v_ret_ind);
          v_return(v_ret_ind+1) := ' Dist:'||to_char(c_tcol.num_distinct,'999,999,990')||
                                   ' Null:'||to_char(c_tcol.num_nulls,'999,999,990')||' '||
                                   ' Dens:'||to_char(c_tcol.density,'0.00000')||
                                   ' Hist:'||to_char(c_tcol.num_buckets,'99,990')||
                                   ' Len :'||to_char(c_tcol.avg_col_len,'9990')||
                                   ' Anal:'||to_char(c_tcol.last_analyzed,'DD-MON-YY');
          v_ret_ind := v_ret_ind + 2;
      end loop;
    end if;
    if r_curr.access_pred is not null then
      v_return.extend;
      v_return(v_ret_ind) := 'Access:'||r_curr.access_pred;
      v_ret_ind := v_ret_ind + 1;
    end if;
    if r_curr.filter_pred is not null then
      v_return.extend;
      v_return(v_ret_ind) := 'Filter:'||r_curr.filter_pred;
      v_ret_ind := v_ret_ind + 1;
    end if;
    r_last := r_curr;
    v_ind := t_plan_det.next(v_ind);
  end loop;
  return v_return;
end;
.
/

show errors
set serveroutput on size 100000

select column_value from table(optimizer_data);

begin
 dbms_output.put_line('Done');
end;
.
/

No comments: