大文件的Oracle APEX apex_data_parser执行时间激增 [英] Oracle APEX apex_data_parser execution time explodes for big files

问题描述

(抱歉，没有足够的回复点无法对类似的问题进行评论)

(Sorry don't have enough rep-point to comment on the similar asked question)

当apex_data_parser处理超过50MB的文件时，执行时间将激增. 就像在这里问的问题一样: apex_data_parser文件> 50MB ，我必须同意oCoXa，解析器中必须有一个错误.

When the apex_data_parser processes file of more than 50MB, execution time explodes. As in the question asked here: apex_data_parser file > 50MB, I must agree with oCoXa, there must be a bug in the parser.

我在ATP + APEX 19.2.0.00.18上发现了完全相同的问题. 解析大于50mb的文件时，我还会看到IO和CPU的峰值，低于50mb时根本没有峰值.

I find exactly the same issue on ATP + APEX 19.2.0.00.18. I also see the spike in IO and CPU when parsing files >~50mb, no spike at all when below 50mb.

我已将两个文件加载到一张表(A41_temp_files)中，一个文件为54mb，另一个文件为47mb. (相同的行直到47mb) 在这两种情况下，我都只解析文件中171.000和199.000行中的3000行.

I have loaded two files to a table (A41_temp_files), one being 54mb, the other one 47mb. (same rows until 47mb) In both cases I'm just parsing 3000 lines of the respective 171.000 and 199.000 lines in the files.

我在SQL Developer中运行解析器，它需要1,9sec占用47mb，而88,6sec则占用54mb.

I run the parser in SQL Developer, it take 1,9sec for 47mb and 88,6sec for 54mb.

select * 
from A41_temp_files  f,
    table(  
            apex_data_parser.parse(
                p_content                     => f.BLOB_CONTENT,
                p_max_rows                    => 3000,
                P_DETECT_DATA_TYPES => 'N',
                p_file_name                   => f.FILENAME 
            ) 
        ) p
where 1=1
and f.filename = 'CHUCK_47mb.txt' --3000 lines    1,9sec
--and f.filename = 'CHUCK_54mb.txt' --3000 lines 88,6sec
order by col001
;

I/O和CPU使用率

推荐答案

APEX_DATA_PARSER软件包确实具有50MB的阈值".

the APEX_DATA_PARSER package has indeed a 50MB "threshold".

对于小于50MB的BLOB，APEX会创建一个新的，缓存的(！)临时LOB，将数据复制并对该临时LOB进行所有解析.因此，所有LOB操作都在内存中进行.

For BLOBs smaller than 50MB, APEX creates a new, cached(!), temporary LOB, copies the data over and does all parsing on that temporary LOB. So all LOB operations happen in memory.

如果BLOB大于50MB，则不会创建临时LOB，并且对传入的LOB定位器执行APEX_DATA_PARSER的LOB操作.在您的情况下，它是列A41_TEMP_FILES.BLOB_CONTENT的LOB定位器.因此，所有LOB操作都在磁盘中进行，这使其速度变慢.

If the BLOB is larger than 50MB, no temporary LOB is created and the LOB operations of APEX_DATA_PARSER are performed on the LOB Locator which has been passed in. In your case, it's the LOB locator for your column A41_TEMP_FILES.BLOB_CONTENT. Thus all the LOB operations happen in disk, which makes it slower.

APEX旨在在具有许多工作空间和并行用户的共享实例上运行-因此，此阈值是为了保护(共享的)SGA和数据库内存.

APEX is built to run on shared instances with many workspaces and parallel users as well - so this threshold is to protect the (shared) SGA and database memory.

但是，使用APEX_DATA_PARSER包的开发人员始终可以自己创建一个缓存的临时LOB，并将其传递给APEX_DATA_PARSER.PARSE.为此，请使用DBMS_LOB.CREATETEMPORARY过程，将其P_CACHE => TRUE创建临时LOB，然后使用DBMS_LOB.COPY将数据从上传的BLOB复制到临时BLOB，然后将临时BLOB传递到APEX_DATA_PARSER.PARSE.

However, developers working with the APEX_DATA_PARSER package can always create a cached temporary LOB themselves and pass that to APEX_DATA_PARSER.PARSE. For this use the DBMS_LOB.CREATETEMPORARY procedure with P_CACHE => TRUE to create the temporary LOB, then DBMS_LOB.COPY to copy the data from the uploaded BLOB to the temporary BLOB and then pass the temporary BLOB to APEX_DATA_PARSER.PARSE.

例如

create or replace function create_cached_blob( p_file in blob ) return blob 
is
    l_blob blob;
begin
    dbms_lob.createtemporary( lob_loc => l_blob, cache => true );
    dbms_lob.copy( 
        dest_lob => l_blob, 
        src_lob  => p_file, 
        amount   => dbms_lob.lobmaxsize);

    return l_blob;
end create_cached_blob;

然后按如下所示在APEX_DATA_PARSER中使用此功能:

then use this function in APEX_DATA_PARSER as follows:

:
apex_data_parser.parse(
     p_content                     => create_cached_blob( f.BLOB_CONTENT),
     p_max_rows                    => 3000,
:

这篇关于大文件的Oracle APEX apex_data_parser执行时间激增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！