9 Replies Latest reply: Apr 2, 2013 5:52 AM by jeneesh RSS

    Spooling large amount of data

    Harshakb
      Hi,

      I need to spool about 25 million rows of data to text for a customer requirement.

      Please let me know if any faster way of retrival of data is there or any way to shorten the time of spooling the data.\

      My environment is Oracle 10g in Windows 2003 32-bit enterprise server.

      Harsha
        • 1. Re: Spooling large amount of data
          sb92075
          Please let me know if any faster way of retrival of data is there or any way to shorten the time of spooling the data.
          Is the bottleneck reading the data or writing the data?
          You likely could (considerably?) reduce the number of bytes being written by using RTRIM, LTRIM, & COLSEP
          • 2. Re: Spooling large amount of data
            sulimo
            spooling the data
            as in SQL> spoo \tmp\file.csv ???

            try creating a custm SQL

            set echo off heading off newpage 0 feedback off linesize 10000
            set pagesize 0 wrap off recsep off con off trim on trims on
            set verify off doc off arraysize 5000 colsep ,
            -- and any other you may need

            -- your csv query;

            and then

            sqlplus -s <user>/<passwd>@db @your_file.sql > your_file.csv

            worth a try if you haven't already
            • 3. Re: Spooling large amount of data
              Harshakb
              Its writing the data. The query's are pretty much defined and tuned.. my requirement is that to spool large amt of records into text files.

              --
              Harsha
              • 4. Re: Spooling large amount of data
                Hemant K Chitale
                Spooling to a client will suffer overheads of network latency and round trips.

                Run your extract job on the database server , without using a SQLNet connect-string (i.e. using ORACLE_SID or , if you know how, using IPC).

                Please check to see that your spool file size does not exceed filesize limits on the filesystem !


                But, why you do need to spool 25million rows out of the database ? Can you not provide the data in some other format (e.g. export dump) ? A text file that is spooled will also have a "bloated" size because of the blank characters within and between columns, besides the obvious security vulnerabilities --- unless you compress and encrypt the file before it is sent to the customer.


                Hemant K Chitale
                • 5. Re: Spooling large amount of data
                  635471
                  Hemant K Chitale wrote:
                  But, why you do need to spool 25million rows out of the database ? Can you not provide the data in some other format (e.g. export dump) ? A text file that is spooled will also have a "bloated" size because of the blank characters within and between columns, besides the obvious security vulnerabilities --- unless you compress and encrypt the file before it is sent to the customer.
                  Also the presence of non-printing control codes or carriage returns or line feeds or characters the same as your delimiter is going to break the file format.
                  • 6. Re: Spooling large amount of data
                    Harshakb
                    Hemant and david,

                    THe Job is being run directly on the console of the server. Also the spool size of the file will be low because the output is broken into the files in the query stage only.

                    The customer want the output in text file only. To reduce the size we are generating the reports using '|' as an delimiter. This reduces much of the blank space problem. Also use of trimspool parameter further reduces the Blank space of the file.

                    Securiy is not a issue as the server is at the client location only.

                    Now coming back to the main question.. how to increase the spooling speed

                    Harsha
                    • 7. Re: Spooling large amount of data
                      Satish Kandi
                      Change the sql*plus arraysize and then run the spool process again.

                      Other option: Minimize the sql*plus window to taskbar once the spooling begins - that should increase the speed to some extent.
                      • 8. Re: Spooling large amount of data
                        635471
                        Satish Kandi wrote:
                        Other option: Minimize the sql*plus window to taskbar once the spooling begins - that should increase the speed to some extent.
                        Setting termout off will do the trick for suppressing display in SQL*Plus
                        • 9. Re: Spooling large amount of data
                          jeneesh
                          Wrongly pasted..

                          Edited by: jeneesh on Apr 2, 2013 4:22 PM