6 Replies Latest reply: Feb 11, 2013 4:38 PM by Srini Chavali-Oracle RSS

    sql loader performance problem with xml

    989623
      Hi,

      i have to load a 400 mb big xml file into mz local machine's free oracle db

      i have tested a one record xml and was able to load succesfully, but 400 mb freeying for half an hour and does not even started?

      it is normal? is there any chance i will be able to load it, just need to wait?

      are there any faster solution?

      i ahve created a table below

      CREATE TABLE test_xml
      (
      COL_ID VARCHAR2(1000),
      IN_FILE XMLTYPE
      )
      XMLTYPE IN_FILE STORE AS CLOB

      and control file below

      LOAD DATA
      CHARACTERSET UTF8
      INFILE 'test.xml'
      APPEND
      INTO TABLE product_xml
      (
      col_id filler CHAR (1000),
      in_file LOBFILE(CONSTANT "test.xml") TERMINATED BY EOF
      )



      anything i am doing wrong? thanks for advices
        • 1. Re: sql loader performance problem with xml
          Srini Chavali-Oracle
          Pl post OS and database versions, along with the complete sqlldr command used and the contents of the sqlldr log file

          HTH
          Srini
          • 2. Re: sql loader performance problem with xml
            989623
            hi, thanks for the reply

            now it is finsihed after 1 hour and failed with data space issues (unable to increase temp segment)

            i have a 11g XE free version it can store maximum 4gb

            i had at least 2 giga free space, but could not load the data (tablespace size is 4gb and 2 is free from it)

            can anyone suggest why i cannot load a 400mb into 2 giga? it is putting so many extra spaces or what can be the problem


            this is how i call sqlloader

            app\oracle\product\11.2.0\server\bin\sqlldr userid=a/a control=test.ctl errors=500
            • 3. Re: sql loader performance problem with xml
              989623
              OS is windows 7

              so not really a business environment
              • 4. Re: sql loader performance problem with xml
                Srini Chavali-Oracle
                Pl post exact version of Win 7 and whether it is 32-bit or 64-bit. If you are using an unsupported OS version ( see http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#BABGGAJA for supported OS versions ), then issues/problems should be expected.

                Pl post the contents of the sqlldr log file as requested

                HTH
                Srini
                • 5. Re: sql loader performance problem with xml
                  989623
                  SQL*Loader: Release 11.2.0.2.0 - Production on H. Febr. 11 18:57:09 2013

                  Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                  Control File: prodxml.ctl
                  Character Set UTF8 specified for all input.

                  Data File: test.xml
                  Bad File: test.bad
                  Discard File: none specified

                  (Allow all discards)

                  Number to load: ALL
                  Number to skip: 0
                  Errors allowed: 5000
                  Bind array: 64 rows, maximum of 256000 bytes
                  Continuation: none specified
                  Path used: Conventional

                  Table PRODUCT_XML, loaded from every logical record.
                  Insert option in effect for this table: APPEND

                  Column Name Position Len Term Encl Datatype
                  ------------------------------ ---------- ----- ---- ---- ---------------------
                  COL_ID FIRST 1000 CHARACTER
                  (FILLER FIELD)
                  IN_FILE DERIVED * EOF CHARACTER
                  Static LOBFILE. Filename is bv_test.xml
                  Character Set UTF8 specified for all input.

                  SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

                  ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


                  Table PRODUCT_XML:
                  0 Rows successfully loaded.
                  0 Rows not loaded due to data errors.
                  0 Rows not loaded because all WHEN clauses were failed.
                  0 Rows not loaded because all fields were null.


                  Space allocated for bind array: 256 bytes(64 rows)
                  Read buffer bytes: 1048576

                  Total logical records skipped: 0
                  Total logical records rejected: 0
                  Total logical records discarded: 0

                  Run began on H. Febr. 11 18:57:09 2013
                  Run ended on H. Febr. 11 19:20:54 2013

                  Elapsed time was: 00:23:45.76
                  CPU time was: 00:05:05.50




                  this is the log
                  i have truncated everything i am not able to load 400 mega into 4 giga i cannot understand

                  windows is not licensed 32 bit
                  • 6. Re: sql loader performance problem with xml
                    Srini Chavali-Oracle
                    This is a TEMP tablespace issue - how big is your TEMP tablespace ? Set it to AUTOEXTEND and try again

                    http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces007.htm#ADMIN13453
                    http://docs.oracle.com/cd/E11882_01/server.112/e10897/storage.htm#ADMQS12068

                    HTH
                    Srini