And talking about benefits... Sometimes if you get advanced monitoring features you can use provided information in a less-then-expected way. Here is one of my favorite examples.
As a lot of people know, some vendors provide their PL/SQL packages in a wrapped style. Also, as a lot of people know, if is often hard to get vendors to accept that something is wrong with their code (rather than with the way this code is being used).
Imagine, you have a slow-running module that takes tables/columns and converts it into formatted CLOB - and before talking to the vendor I want to collect as much data as I can:
SQL> DECLARE
2 v_CL CLOB;
3 BEGIN
4 dbms_hprof.start_profiling ('IO','HProf_Check.txt');
5 v_cl :=wrapped_pkg.f_getdata_cl('object_name','test_tab');
6 dbms_hprof.stop_profiling;
7 END;
8 /
I know that the table has 50k rows - and I see that there is 50k calls to DBMS_LOB.WRITEAPPEND! Yes, I can see what happens inside of the wrapped code - big surprise! Now I have a real evidence that the wrapped code is not efficient: whoever wrote it uses row-by-row approach.
As I wrote long-time ago, LOB operations are expensive (even LOB variables above 32K size are not real variables, but a segment in TEMP tablespace) and causing a lot of resource to be used. I.e. original code is something like that:
FUNCTION f_getData_cl(i_column_tx VARCHAR2, i_table_tx VARCHAR2) RETURN CLOB IS
v_cl CLOB;
v_tx VARCHAR2(32767);
v_cur SYS_REFCURSOR;
BEGIN
dbms_lob.createTemporary(v_cl,false,dbms_lob.call);
OPEN v_cur FOR 'SELECT '||
dbms_assert.simple_sql_name(i_column_tx)||' field_tx'||
' FROM '||dbms_assert.simple_sql_name(i_table_tx);
LOOP
FETCH v_cur into v_tx;
EXIT WHEN v_cur%notfound;
dbms_lob.writeAppend(v_cl,length(v_tx)+1,v_tx||'|');
END LOOP;
CLOSE v_cur;
RETURN v_cl;
END;
But if you use PL/SQL variable as a buffer, you can drastically decrease the number of LOB operations. True, it complicates your code - but gains are huge!
FUNCTION f_getData_cl(i_column_tx VARCHAR2, i_table_tx VARCHAR2) RETURN CLOB IS
v_cur SYS_REFCURSOR;
v_cl CLOB;
v_tx VARCHAR2(32767);
v_buffer_tx VARCHAR2(32767);
PROCEDURE p_flush IS
BEGIN
dbms_lob.writeappend(v_cl,length(v_buffer_tx), v_buffer_tx);
v_buffer_tx:=null;
END;
PROCEDURE p_add (i_tx VARCHAR2) IS
BEGIN
IF length(i_tx)+length(v_buffer_tx)>32767 THEN
p_flush;
v_buffer_tx:=i_tx;
ELSE
v_buffer_tx:=v_buffer_tx||i_tx;
END IF;
END;
BEGIN
dbms_lob.createTemporary(v_cl,true,dbms_lob.call);
OPEN v_cur FOR
'SELECT '||
dbms_assert.simple_sql_name(i_column_tx)||' field_tx'||
' FROM '||dbms_assert.simple_sql_name(i_table_tx);
LOOP
FETCH v_cur into v_tx;
EXIT WHEN v_cur%notfound;
p_add(v_tx);
END LOOP;
p_flush;
CLOSE v_cur;
RETURN v_cl;
END;
On a reasonably large data set that second code will buy back a lot of IO operations - really a lot! I strongly recommend you to run those cases by yourself - only in that case you will believe the performance gains :) But if you are too, well, busy - I will get stats in the next post of these series :)
No comments:
Post a Comment