#Oracle sql developer dump code#

#Oracle sql developer dump code#

Our baseline code has two repetitive operations. Tuning (1): buffering to reduce utl_file i/o

Note that readers on 9i databases will need to convert this and other implicit cursor examples to use explicit array fetches with BULK COLLECT to ensure that all examples are comparable. All examples in this article will use either implicit or explicit bulk fetches of the same size. From Oracle 10g Release 1, this will be optimised by the PL/SQL compiler into bulk collects with an array-size of 100 rows. Lines 11-13: we are using an implicit cursor-for-loop.A dump utility would usually accept the delimiter as a parameter and concatenate a record piecemeal (especially if using dynamic SQL) Line 11: each record is pre-formatted with a delimiter in the SQL cursor for convenience.Our baseline performance is approximately 66 seconds to write the data to file. SQL> DECLARE 2 3 v_file UTL_FILE.FILE_TYPE 4 v_name VARCHAR2(128) := 'utl_file_untuned.txt' 5 v_lines PLS_INTEGER := 0 6 7 BEGIN 8 9 v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767) 10 11 FOR r IN (SELECT x || ',' || y || ',' || z AS csv 12 FROM source_data) 13 LOOP 14 UTL_FILE.PUT_LINE(v_file, r.csv) 15 v_lines := v_lines + 1 16 END LOOP 17 18 UTL_FILE.FCLOSE(v_file) 19 20 DBMS_OUTPUT.PUT_LINE('File='||v_name||' Lines='||v_lines) 21 22 END 23 /įile=utl_file_untuned.txt Lines=1000000PL/SQL procedure successfully completed. In this case, it will be the time it takes to unload 1 million rows to a flat-file using UTL_FILE.PUT_LINE calls, using the following PL/SQL block. baseline utl_file performanceīefore we can tune our code, we need to know our baseline performance.

SQL> CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dir' ĭirectory created. This is where we will write our flat-files. We have reduced the physical I/O as intended, so to complete our setup we will create an Oracle directory below. SQL> CREATE TABLE source_data 2 ( x, y, z 3, CONSTRAINT source_data_pk 4 PRIMARY KEY (x,y,z) 5 ) 6 ORGANIZATION INDEX 7 AS 8 SELECT ROWNUM AS x 9, RPAD('x',50,'x') AS y 10, RPAD('y',50,'y') AS z 11 FROM dual 12 CONNECT BY ROWNUM set autotrace traceonly statisticsSQL> SELECT * FROM source_data ġ000000 rows selected.Statistics- 88 recursive calls 0 db block gets 17779 consistent gets 15647 physical reads 0 redo size 108077685 bytes sent via SQL*Net to client 16231 bytes received via SQL*Net from client 2001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processedġ000000 rows selected.Statistics- 0 recursive calls 0 db block gets 17668 consistent gets 0 physical reads 0 redo size 108077883 bytes sent via SQL*Net to client 16231 bytes received via SQL*Net from client 2001 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1000000 rows processed The data to be dumped to flat-file will be sourced from a single table of 1 million rows, which we will create as follows. We will run these on an 11g Release 1 database, but all examples are compatible with 10g and most of them with 9i. We are going to test several versions of a standard data-unload routine. We will see that with simple techniques we can achieve significant performance gains for our data unloads. The main issue with UTL_FILE, however, is that it is a relatively slow tool for unloading data, but in this article we will demonstrate some methods for speeding this up. UTL_FILE, on the other hand, is a built-in package around which we can very easily base our unloader utilities. This makes it generally unsuited to being part of a robust database application. For example, sqlplus can spool data to flat-files very quickly, but it is a command utility and not part of the database. We can use Perl, C, C++, Pro*C, Java and other languages that interface with Oracle but it is far more common for us to use SQL or PL/SQL to perform this operation. There are several ways to generate flat-files from data stored in Oracle. The tools that are provided by Oracle (export, Data Pump, writeable external tables) write data quickly, but in a proprietary format, so for true ASCII flat-files, we have to resort to our own homegrown utilities. Methods for writing data to flat-files vary, but strangely Oracle has never provided a tool to do this for us. There are numerous 'unloader' utilities on the web for this purpose and there are also many related topics in the Oracle forums. Unloading Oracle data to flat-files is still very common.