Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: shared pool memory decreasing when using sqlldr

    We have an application that regurarly inserts records using
    the sqlldr. This is in Oracle 9.2.0.5.

    It works fine for some time (days/weeks) until the shared pool memory,
    after some time, is down to zero.

    Monitoring this in more detail, we can see that each time sqlldr
    has been used the free amount of drops while the sqlldr is
    running (fine and expected), but then does dnot recover all.
    Typically we have lost ~100-300KB per sqlldr insertion.

    We monitor available shared pool memory by:
    Code:
    select pool,name,bytes/1024/1024 MB from v$sgastat 
    where name='free memory' and pool='shared pool';
    I can also add that during this time there is, in principle, no other activity
    to the db.

    Anyone seen anything like this before? Ideas/pointers/suggestions?

    Thanks // Björn.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What is the benefit of having unused shared memory pool?
    I strongly suspect that your two observations have no relationship to each other.
    Other than claiming that sometimes SQLLDR has a problem, you've provided ZERO evidence (error message/code, logfile excerpts, etc) of anything is amiss.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by anacedent
    What is the benefit of having unused shared memory pool?
    No use, of course. The problem is when we run out of memory so access
    will stopped.
    Quote Originally Posted by anacedent
    I strongly suspect that your two observations have no relationship to each other.
    Other than claiming that sometimes SQLLDR has a problem, you've provided ZERO evidence (error message/code, logfile excerpts, etc) of anything is amiss.
    I'm not trying to claim anything, it's just my best effort of observation.

    The scenario, in some more detail:

    1) A idle oracle database, have been running and been used (access/insert/delete/etc) for some time.

    2) Looking up free memory pool gives me 116.71MB. Fine.

    3) Testing by accessing it through jdbc works fine, doing some
    (a couple of hundreds of thousands) inserts, lookups, updates.
    The free memory pool stays around the previous mentioned, 117.71MB.
    Not exactly, but around it.

    4) Removing all other connection, checking free mem, still about 117.7MB

    5) Testing some sqlldr, memory dips while inserting (~2-5MB), and then
    recovers after sqlldr is done, but only recovers to about 117.3MB
    Repeating this scenario, the memory is slowly consumed.

    I made a script logging the free memory, date and number of sqlldr
    inserts (number of times sqlldr has been called), the linearity is
    perfect, to the limit that I on a live system can predict at what
    time (on a 5-10 minutes precision) the system will go down
    due to out of shared pool memory. This prediction can be done
    on a precision of 20 minute a week in advance.

    During this time, no one else uses the oracle system at all (apart
    from me using a sqlplus connection for logging the memory consumption
    every hour).

    Again, anyone seen this behavior or can give any clues, I'd be more
    than glad for any suggestions/pointers.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Can you check v$sgastat and see which part of the shared memory pool is increasing while the free memory is decreasing. Then do a search on metalink to see if its a known bug for your version of Oracle.

    If this is a production system and until you find a proper solution does alter system flush shared_pool fix the problem, if it does you could execute it periodically (though there are downsides to this).

    Alan

  5. #5
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by AlanP
    Can you check v$sgastat and see which part of the shared memory pool is increasing while the free memory is decreasing. Then do a search on metalink to see if its a known bug for your version of Oracle.
    Just tried that. Seem to be the 'miscellaneous' column which decreases (most).
    I see a small decrease in 'liubrary cache' and 'sql area' but these are
    so small I consider it negliable. I'll try to see if it is known or not.
    If this is a production system and until you find a proper solution does alter system flush shared_pool fix the problem, if it does you could execute it periodically (though there are downsides to this).

    Alan
    I wasn't aware of this command and, indeed, it seem to free up quite some memory. I don't know what is normal here and what is not. I give you some
    figures in the case someone knows if they are realistic or not:
    Shared memory pool initial size is ~235MB
    Almost immediately after starting oracle it is down to roughly 125MB
    After this the free shared memory slowly decreases.
    I tried the suggested command when I had 13MB left and it did free
    roughy 100MB of memory to ~113MB free.

    I compared the v$sgastat figures on by one, and the only big contributor
    to the newly freed memory seem to be 'sql area' which freed from 81MB
    to 1.5MB, ie a little over 80MB.

    Can anyone say if these figures are normal or abnormal?
    Should more memory have been freed?
    Other ideas?
    Thanks a lot for all help!!
    //Bjorn

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK I'm not sure sql area is the problem or not but if it is do a query against v$sqlarea. Check to see if you see lots of queries which are using literals instead of bind variables as this may indicate a badly written app.

    The other thing which may be necessary is to simply increase your SGA target or shared_pool_size (depending on your version of Oracle).

    Alan

  7. #7
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by AlanP
    OK I'm not sure sql area is the problem or not but if it is do a query against v$sqlarea. Check to see if you see lots of queries which are using literals instead of bind variables as this may indicate a badly written app.

    The other thing which may be necessary is to simply increase your SGA target or shared_pool_size (depending on your version of Oracle).

    Alan
    Alan,

    first, thanks a lot for your help and insightful comments!

    I just wanted to stress what I wrote in the initial msg: we do not have any
    activity at all to the oracle db, except for the sqlldr and a single
    sqlplus shell (mine) to check the memory status.

    However, there used to be sqlaccess before I started monitoring,
    so perhaps it is that memory that hes been freed. I'll
    check that by flushing the shared pool again, this time
    without any activity between the flushes except sqlldr.

    I can of course increase the memory, but that only feels like
    a temporary solution, that memory will also be filled up, just
    postponing the crash.

    /Bjorn

  8. #8
    Join Date
    Nov 2005
    Posts
    6

    Thumbs down

    Quote Originally Posted by bjorn.julander
    However, there used to be sqlaccess before I started monitoring,
    so perhaps it is that memory that hes been freed. I'll
    check that by flushing the shared pool again, this time
    without any activity between the flushes except sqlldr.
    A comment on my previous post after rechecking the memory
    after another 40h.
    Indeed, it was only the sqlaccess memory that vanished,
    the memory (defined as miscellanous) is steadly increasing,
    so I only postponed the crash by flushing the shared pool.

    Conclusion is that I still seem to have a memory issue while
    running the sqlldr.

    Is there some way to analyze/understand what is in the misc shared
    memory, alternatively a good pointer to some information about it?

    //Bjorn

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would think the best thing is to open a TAR on metalink in this case. Also what version of Oracle are you on, if it isnt the latest you may find a patch for it.

    Alan

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    BTW - 9.2.0.5 is the buggiest Oracle Patch ever. They came out with 9206 a few days after 9205 because of this. You definitely should not be on 9205.

    From Metalink Note: 263792.1
    Known Issues specific to the 9.2.0.5 Patch Set
    9.2.0.5 is superceeded by 9.2.0.6 or higher on most platforms.
    Please use the latest 9.2 patch set available for your platform.

    Issues introduced in 9.2.0.5
    PHP Code:
    This section lists bugs introduced in 9.2.0.5 (if any). 
    Such issues may be either serious or trivial but the aim 
    is to 
    list them all to help customers assess the risk of 
    applying the Patch Set on top of 9.2.0.4

    Note
    :297306.1ORA-7445 SKGXPDMPCTX Instance Crash with 9.2.0.5 or 9.2.0.6 Patchset 
    Note
    :281047.1Corruption possible in automatic space managed segments 
    Note
    :269472.1PAIXHSODBC does not work in 9.2.0.5 on AIX platforms 
    Note
    :271084.1PSolarisOERI:[KSLAWE:!PWQcan occur in 9.2.0.5 on 64bit Oracle on Solaris 
    3894096
    Poor MView refresh performance in ASSM tablespaces 
    3771668
    Poor INSERT performance with ASSM 
    3531336I
    9.2.0.5 catpatch does not run catcio can cause OERI:KSSRMP1 ORA-8120 errors 
    4492257 Suboptimal plan possible 
    4490782 Unparse operations give wrong date 
    for BC TO_DATE literals 
    4297169 Shared server dumps when 
    "alter system kill session" used 
    4285294 Memory leak in AQ messaging 
    4153531 Direct load with SQL expression can incorrectly load NULL data 
    4057147 JDBC OCI returns invalid argument exception when binding over 64kb 
    array 
    4031272 ORA-6502 using collection tables in PLSQL 
    4007202 Large multi
    -table insert SQL statement fails with ORA-24335 
    3933012 ORA
    -904 from IN subquery with UNION ALL having co-located remote references 
    3788530 OERI
    [kohdtf048with sqlldr direct load with a PLSQL function 
    3763027 Dump (in KKOJNPpossible parsing query with pushed join predicate 
    3740073 Parallel insert allocates an extra buffer row source 
    3737955 Long parse times 
    for long inlists many AND/OR terms 
    3735856 ORA
    -2031 on SELECT of ROWID from a non mergable view on a Vview 
    3720104 Fix 
    for BUG 2276769 can cause SQL monitoring difficulties 
    3709345 ORA
    -28500 SQLCODE-310 using PACKED DECIMAL DB2 columns 
    3653586 DBMS_METADATA
    .GET_DDL performance very slow 
    3648249 SQL may dump 
    (in KKOGTPduring parse 
    3635177 Dump 
    [KXCCUINpossible from DML with concurrent ONLINE index rebuild 
    3628621 ORA
    -6502 from DYNAMIC SQL in AL32UTF8 
    3627650 ORA
    -701 OERI:17058 from GRANT REVOKE select ON OBJECT_USAGE 
    3624684 Remote subquery not unnested when expected 
    3616023P Tru64
    "skgpspawn failed:category = 27143" during process instance startup 
    3614289 Index range cost with predicate on descending key may be too high 
    3609791 SQLCODE 
    -310 incorrect SCALE over HS for numbers with zeroes after the decimal place 
    3583215 Dump 
    (qersoRowPwith complex view merging and subquery in SELECT list 
    3572558 SQLPLUS incorrectly appends .LST to spool filenames with environment variable 
    3557842 ORA
    -903 with UNION ALL in a view 
    3545209 Poor performance from semi join with NL to remote table 
    3448711 ORA
    -1722 can occur binding NULL bind values if event 10843 is set 
    3447792 Multi
    -table insert can fail with ORA-24335 
    3432427 Dump possible in LGWR 
    (in ksliwatin RAC mode 
    3408192 Heavy concurrent DML scenarios can cause $R table to contain deleted rowids 
    3277927 Unexpected ORA
    -2070 possible when using a SYS_CONTEXT function 
    3262241 ORA-1422 executing DBMS_STATS.GATHER_SCHEMA_STATS('SYS'
    3107414 Large wrapped PLSQL packages can fail with PLS-908 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by The_Duck
    BTW - 9.2.0.5 is the buggiest Oracle Patch ever. They came out with 9206 a few days after 9205 because of this. You definitely should not be on 9205.

    From Metalink Note: 263792.1
    Known Issues specific to the 9.2.0.5 Patch Set
    9.2.0.5 is superceeded by 9.2.0.6 or higher on most platforms.
    Please use the latest 9.2 patch set available for your platform.
    Thanks for that insightful information. Will try a fresh patch instead.

    As a sidenote, it was a oracle-consultant, who has helped us
    have a look-through of the system, that suggested this particular patch.

    /Bjorn

Posting Permissions

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