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:
Post a Comment