Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Talking Unanswered: sqlldr direct path, unable to locate shared memory

    I am using Oracle 9i sqlldr, direct path to load data from external files into
    staging tables. After data is loaded, we invoked stored procedures to
    transform data and move them to the target tables. The steps are:
    1. delete all entries from 20 staging tables using TRUNNCATE
    2. invoke "sqlldr userid=dbimpl/dbimpl control=<controlFile> direct=true" to
    load data to all 20 staging tables
    3. invoke stored procedures to transform data from the staging tables to the
    final tables. Currently these stored procedures are standalone.
    4. invoke stored procedures to remove out-of-date entries from the final
    tables.

    I monitor invalidations column in v$sqlarea. Every time
    after sqlldr is invoked for data loading (step 2), all the
    sql statements that reference the staging tables are
    invalidated, including "delete from <stageing_table>" sql
    statement. I setup a test and used a java program to loop
    steps 1-4 every ~2 minutes. There were no other activities
    in the database except data loading and transformation.
    After a couple days, I got the following error: ORA-04031:
    unable to allocate 4212 bytes of shared memory ("shared
    pool","unknown object","sga heap(1,0)","stat array mem")

    The questions are:
    1. In order to do fast loading, I need to TRUNCATE table. In the sqlldr ctl file,
    I use "truncate on file..." how/why sqlldr would invalidate the sql statement in
    the stored procedures and there was no change in the stored procedures?

    2. The error ORA-04031 in this case, is it due to shared memory fragmentation? I
    suspect that the culprint is invalidations. How do invalidations cause shared
    memory fragmentation?

    3. I cannot pin down the transform stored procedures because they get invalidated
    everytime sqlldr is invoked. Because I need to issue sqlldr in a tight loop that leads to
    shared memory fragmentation problem. Any suggestions on how to get around shared memory
    fragmentation problem. Any comment on if I issue "ALTER SYSTEM FLUSH SHARED_POOL"
    periodically?


    Please see the attachment for the staging and final table schema and testing steps.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    if you change all your SPs into packages, they won't be dependent.
    That would solve one of your problems.

    I also have noticed strange problems using DIRECT path and sqlloader. It seemed to invalidate all of my indexes which really was pissing me off.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by The_Duck
    if you change all your SPs into packages, they won't be dependent.
    That would solve one of your problems.

    I also have noticed strange problems using DIRECT path and sqlloader. It seemed to invalidate all of my indexes which really was pissing me off.

    Changing standaline stored procedures to package is in our plan.

    To confirm your finding, I also learned that DIRECT path does invalidate indexes that requires a rebuild.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by klee
    Changing standaline stored procedures to package is in our plan.

    To confirm your finding, I also learned that DIRECT path does invalidate indexes that requires a rebuild.
    My next question (which I didn't bother to investigate) is what is the relative time difference (or any benefit) using DIRECT vs. standard loading. To me, it was not worth bypassing the log-buffer since I sure as hell didn't want to rebuild the indexes every time I loaded my table.

    Perhaps there is a larger benefit when using a much larger dataset.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by The_Duck
    My next question (which I didn't bother to investigate) is what is the relative time difference (or any benefit) using DIRECT vs. standard loading. To me, it was not worth bypassing the log-buffer since I sure as hell didn't want to rebuild the indexes every time I loaded my table.

    Perhaps there is a larger benefit when using a much larger dataset.
    It does not make much difference if the table is small. In my test, the table has over 24,000 records. The ratio between direct path and conventional path loading is 1:6.

Posting Permissions

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