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

sql loader performance problem with xml

989623 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    OS is windows 7

    so not really a business environment
  • 4. Re: sql loader performance problem with xml
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points