DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance Tuning Of Spooling

  1. #1
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow Performance Tuning Of Spooling

    One of the major performance problem that we face in our day to day operation is the fact that spooling a char delimited file using SQLPLUS is very slow.



    For example a script can take 3 minute to run and then 40 minutes to spool. Let’s say –





    Create table x as select * from table1



    And then



    Spool file.dat



    select column1||’|’||column2||’|’||column3 … from table



    spool off;





    We then call the SQL script using an NT command file of a UNIX shell script.



    I’ve been searching the web for a better solution and I’ve found topic like custom pro*c script or use the util_file package to write the file on the server. I don’t know how to use the pro*c and I have no access to the unix server where Oracle is installed and therefore can’t test the util_file solution.


    Any advice would be appreciated.
    http://www.perf-engg.com
    A performance engineering forum

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It could be that you are limited by the network - calculate the approx volume to be transmitted to your client and divide by (say) 75% of the network speed.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can try this:

    1 Increae array size within SQLPLUS.
    2 Increase SDU and TDU size.
    3 In unix server, increase tcp_recvspace and tcp_sendspace to 128K

    All of them will enhance throughput.

    Tamil

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Also in SQL*Plus, make sure you are not including trailing blank spaces (SET TRIM ON) and don't output to the terminal (SET TERM OFF).

    SQL*Plus SPOOL command will be a lot faster than UTL_FILE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width