In a comment to another blog on "How to be a good Newbie" I expressed the view that my personal bugbear was a request for help which amounted to 'I have a performance problem with this SQL:' and gives an SQL with no explain plan, no table sizes, no indexes.....
Seeing another of these today, I looked for a simple checklist of what is needed to look at an SQL performance problem. I couldn't find one, so here's mine.
1. What are you running ? Show us the SQL.
2. What should it be doing ?
It may be obvious to you, but if you leave it up to the respondant to guess, you may get a less than optimal result.
3. Give us an Explain Plan. That will tell us what the Optimizer thinks it should be doing.
4. Give us the sizes of the tables. I haven't found a query yet that runs slowly when all the tables are empty.
5. What columns are indexed ?
6. If there are views involved, we need the information in all the tables in the view
7. Tell us if there are 'exotica' such as object types, database links, partitions....
8. How long is it taking ? How long do you want it to take ? A performance problem may exist in a query that only takes 30 seconds, if it is run often enough. On the other hand, you may be happy for the query to take hours.
9. First Rows or All Rows. Do you want the shortest time to see the first rows out of the result set, or the shortest time to get them all ?
The following can be run as anonymous PL/SQL with the table names listed in the collection in line 3. While this may report on some table columns which are irrelevant to the issue, it will give all the necessary facts for an initial examination of the issue.
declare
t_tabs sys.DBMS_DEBUG_VC2COLL :=
sys.DBMS_DEBUG_VC2COLL('...table1....','...table2.....');
v_tab varchar2(100);
v_own varchar2(100);
v_typ varchar2(100);
v_user varchar2(100) := user;
v_long varchar2(32000);
v_ind number;
--
cursor c_obj is
select object_type, owner
from all_objects
where object_name = v_tab
and owner in ('PUBLIC',v_own)
order by decode(owner,v_own,1,2);
--
cursor c_syn is
select table_owner, table_name
from all_synonyms
where synonym_name = v_tab
and owner in ('PUBLIC',v_own)
order by decode(owner,v_own,1,2);
--
cursor c_vw is
select text from all_views
where owner = v_own and view_name = v_tab;
--
cursor c_tab is
select table_name, num_rows, blocks, avg_row_len, sample_size, last_analyzed
from all_tables
where table_name = v_tab
and owner = v_own;
--
cursor c_tcols is
select column_name, data_type, num_distinct, density, num_nulls, num_buckets,
last_analyzed, avg_col_len, rawtohex(low_value) low, rawtohex(high_value)
from all_tab_columns
where table_name = v_tab
and owner = v_own;
--
cursor c_ind is
select index_name, index_type, leaf_blocks, distinct_keys,
Clustering_Factor, num_rows, last_analyzed
from all_indexes
where table_name = v_tab
and owner = v_own;
--
cursor c_icols (p_iname varchar2) is
select c.index_name, c.column_position, c.column_name, 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.table_name = v_tab
and c.table_owner = v_own
and c.index_name = p_iname
order by 1,2;
--
PROCEDURE sp_out (p_text IN VARCHAR2, p_wrap IN NUMBER DEFAULT 1) IS
v_text VARCHAR2(32500);
v_point NUMBER;
e_overflow exception;
pragma exception_init (e_overflow, -20000);
v_temp_arr dbms_output.chararr;
v_lines number := 150;
BEGIN
v_text := p_text;
--Break it down into lengths up 248 characters in length
-- If p_wrap is 1, it will try to 'wrap' the line at word breaks
WHILE v_text IS NOT NULL LOOP
v_point := LENGTH(v_text)+1;
IF v_point > 248 AND p_wrap = 1 THEN
v_point := INSTR(SUBSTR(v_text,1,248),CHR(10),-1);
END IF;
IF v_point = 0 AND p_wrap = 1 THEN
v_point := INSTR(SUBSTR(v_text,1,248),' ',-1);
END IF;
IF v_point = 0 AND p_wrap = 1 THEN
v_point := INSTR(SUBSTR(v_text,1,248),'/',-1);
END IF;
IF v_point = 0 THEN
v_point := 248;
END IF;
BEGIN
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,1,v_point-1));
EXCEPTION
WHEN e_overflow THEN
IF SUBSTR(SQLERRM,12,9) ='ORU-10027' THEN
--Clear out the oldest 150 lines to make room
--in the DBMS_OUTPUT buffer
DBMS_OUTPUT.GET_LINES(V_TEMP_ARR,V_LINES);
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_text,1,v_point-1));
ELSE
RAISE;
END IF;
END;
v_text := SUBSTR(v_text,v_point+1);
END LOOP;
END;
--
begin
v_ind := t_tabs.first;
while v_ind is not null loop
v_tab := t_tabs(v_ind);
sp_out('TABLE:'||v_tab);
v_own := v_user;
open c_obj;
fetch c_obj into v_typ, v_own;
if c_obj%notfound then
close c_obj;
raise no_data_found;
end if;
close c_obj;
--
while v_typ = 'SYNONYM' loop
open c_syn;
fetch c_syn into v_own, v_tab;
close c_syn;
sp_out('SYN:'||v_typ||'/'||v_own||'/'||v_tab);
open c_obj;
fetch c_obj into v_typ, v_own;
close c_obj;
end loop;
--
sp_out(v_typ||'/'||v_own||'/'||v_tab);
if v_typ = 'VIEW' then
open c_vw;
fetch c_vw into v_long;
close c_vw;
sp_out(v_long);
else
for rec in c_tab loop
sp_out('CARD:'||rec.num_rows||
' BLKS:'||rec.blocks||
' AVGLEN:'||rec.avg_row_len||
' SMPL:'||rec.sample_size||
' ANAL:'||to_char(rec.last_analyzed,'DDMMYY'));
sp_out('----------------------------------------------');
end loop;
for rec in c_tcols loop
sp_out('COL:'||rpad(rec.column_name,30)||
' TYP:'||rpad(rec.data_type,10)||
' VALS:'||to_char(rec.num_distinct,'999,999,990')||
' DENS:'||lpad(round(rec.density,4),8)||
' NULLS:'||to_char(rec.num_nulls,'999,999,990')||
' HIST:'||lpad(rec.num_buckets,4));
end loop;
sp_out('----------------------------------------------');
for rec in c_ind loop
sp_out('INAME:'||rpad(rec.index_name,30)||
' ITYP:'||rpad(rec.index_type,20)||
' LBLKS:'||to_char(rec.leaf_blocks,'9999,999')||
' KEYS:'||to_char(rec.distinct_keys,'999,999,990')||
' CLUSTR:'||round(rec.Clustering_Factor,4));
sp_out('..ROWS:'||to_char(rec.num_rows,'999,999,990')||
' ANAL:'||to_char(rec.last_analyzed,'DDMMYY'));
for rec2 in c_icols (rec.index_name) loop
v_long := rec2.column_expression;
if v_long is not null then
v_long := ' EXP:'||substr(v_long,1,150);
end if;
sp_out('....POS:'||to_char(rec2.column_position,'99')||
' COL:'||rpad(rec2.column_name,30)||v_long);
end loop;
end loop;
end if;
v_ind := t_tabs.next(v_ind);
end loop;
dbms_output.put_line('Done.');
end;
No comments:
Post a Comment