我有一个PL/SQL程序,在做了一些改变后,运行时间从30分钟变成了3小时,我们一直无法确定如何提高性能。该程序最初输出一个csv文件在excel中使用。该程序现在已更改为输出XML用于excel。因此,在SQL或数据库读取方面没有代码更改。相关的代码添加是从构建clob的相关包中提取的过程,然后定期将clob输出到文件中,以避免过多的内存使用。utl_file的个数。在两个版本的程序中,Put_line的用法是相似的。
用于构建CLOB的代码示例如下:PROCEDURE cell_write(p_xml_body IN OUT NOCOPY CLOB,
p_data_type IN VARCHAR2 := 'String',
p_style_id IN VARCHAR2 := NULL,
p_merge IN VARCHAR2 := NULL,
p_formula IN VARCHAR2 := NULL,
p_line_feed IN BOOLEAN := TRUE,
p_content IN VARCHAR2) IS
v_line_feed VARCHAR2(01) ;
BEGIN
IF p_line_feed
THEN
v_line_feed := chr(10) ;
ELSE
v_line_feed := ' ' ;
END IF ;
p_xml_body := p_xml_body || ' <Cell';
IF p_merge IS NULL
THEN
p_xml_body := p_xml_body ;
ELSE
p_xml_body := p_xml_body || ' ss:MergeAcross="' || p_merge || '"' ;
END IF ;
IF p_style_id IS NULL
THEN
p_xml_body := p_xml_body ;
ELSE
p_xml_body := p_xml_body || ' ss:StyleID="'||p_style_id||'"' ;
END IF;
IF p_formula IS NULL
THEN
p_xml_body := p_xml_body ;
ELSE
p_xml_body := p_xml_body || ' ss:Formula="'||p_formula||'"' ;
END IF;
p_xml_body := p_xml_body || '><Data ss:Type="'||p_data_type||'">' || P_content || '</Data></Cell>' || v_line_feed;
END cell_write;
输出代码为
PROCEDURE write_file(p_filename IN VARCHAR2,
p_dir IN VARCHAR2,
p_file_handle IN utl_file.file_type,
p_clob IN CLOB)
IS
c_amount CONSTANT BINARY_INTEGER := 32767;
l_buffer VARCHAR2(32767);
l_chr10 PLS_INTEGER;
l_cloblen PLS_INTEGER;
l_fhandler utl_file.file_type;
l_pos PLS_INTEGER := 1;
BEGIN
l_cloblen := dbms_lob.getlength(p_clob);
WHILE l_pos < l_cloblen
LOOP
l_buffer := dbms_lob.substr(p_clob, c_amount, l_pos);
EXIT WHEN l_buffer IS NULL;
l_chr10 := instr(l_buffer, chr(10), -1);
IF l_chr10 != 0 THEN
l_buffer := substr(l_buffer, 1, l_chr10 - 1);
END IF;
DBMS_OUTPUT.PUT_LINE('Buffer Length ' || LENGTH(l_buffer)) ;
DBMS_OUTPUT.PUT_LINE(l_buffer) ;
utl_file.put_line(p_file_handle, l_buffer, TRUE);
l_pos := l_pos + least(length(l_buffer) + 1, c_amount);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Buffer Length ' || LENGTH(l_buffer)) ;
DBMS_OUTPUT.PUT_LINE(l_buffer) ;
IF utl_file.is_open(l_fhandler) THEN
utl_file.fclose(l_fhandler);
END IF;
RAISE;
END;
和一个示例调用是
pk_create_excel_workbook.cell_write(p_xml_body =>v_clob_term,
p_line_feed => FALSE,
p_data_type =>'Number',
p_style_id =>'s94',
p_content =>w_totemployeecontribT);
每个输出行调用过程cell_write大约100次,大约有30,000个输出行。因此输出行通常有8000到10000字节长。
我们预计运行时间会有适度的增加,但没想到会出现这么大的跳跃。我忽略了什么?
我的猜测是cell_write
中的多个连接。(分析将确认)
理想情况下,您应该使用提供的XML接口工具,而不是像这样手动连接标签,但无论如何,作为一个快速修复,我会尝试这样做(未经测试):
procedure cell_write
( p_xml_body in out nocopy clob
, p_data_type in varchar2 := 'String'
, p_style_id in varchar2 := null
, p_merge in varchar2 := null
, p_formula in varchar2 := null
, p_line_feed in boolean := true
, p_content in varchar2 )
is
k_line_feed constant varchar2(1) := case when p_line_feed then chr(10) else ' ' end;
begin
p_xml_body := p_xml_body ||
' <Cell' ||
case when p_merge is not null then ' ss:MergeAcross="' || p_merge || '"' end ||
case when p_style_id is not null then ' ss:StyleID="' || p_style_id || '"' end ||
case when p_formula is not null then ' ss:Formula="' || p_formula || '"' end ||
'><Data ss:Type="' || p_data_type || '">' || p_content || '</Data></Cell>' ||
k_line_feed;
end cell_write;
我现在已经使用了很长时间的一种方法,就是在执行CLOB
追加之前使用varchar2
有状态的PL/SQL
变量作为缓冲区来处理临时CLOBs
。我围绕核心DBMS_LOB
创建了一个简单的API,允许我随时向多个CLOB
编写代码,必要时在它们之间交替。你可以看一下,也许你可以实现更好的性能:
https://github.com/GeraldoViana/nksg/blob/master/src/nksg_tempclob.pkshttps://github.com/GeraldoViana/nksg/blob/master/src/nksg_tempclob.pkb