ora-04031 in 8.1.7.4.1
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ora-04031 in 8.1.7.4.1

  1. #1
    Join Date
    Sep 2002
    Posts
    15

    Lightbulb

    Hi,

    I recently upgraded to 8.1.7.4.1. from 7.3.4.5.2. All worked o.k. until approx 1 week of usage.

    I started to get ora-4031 errors. Recommended fix was to bump up shared_pool - mine is now 400 megs for 600 sessions.

    I set the retained size to 100 megs and pinned a lot of shared_code. All went well for approx 10 days and we hit the problem again.

    At any given time 30% of my shared pool is free, so I figure I am hitting fragmentation issues and not the 8.1.7 memory leak.

    I monitored a large insert job which appeared to be using 80n megs of shared pool every hour of execution and just today moved this to a standalone database for processing.

    I am hoping this will reduce the fragmentation but am worried that I will need to introduce frequent flushing and take on board the performance hit of using this. I have since not pinned any code as a flush frees up 80+ % of my shared_pool and I presume defragments also.

    I am running a 3 cpu (700 mhz each) with 1 4.5 gb of ram = sga = 400 shared_pool + 1.8 for buffer cache. 600 users without MTS server. I did hit a problem when my Oracle process was running at 1.2 gb (reduce sort area size to 100 k) = now process at 900 megs. - did use /3gb and /pae but still couldn't get the process (less buffer cache) above 1.2 gb

    Any help on the 04031 would be much appreciated as I am peed that I have hit issues on going from stable 7 to (apparently) unstable 8i


  2. #2
    Join Date
    Sep 2002
    Posts
    15
    anybody experienced something similar??

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by seanryan
    I monitored a large insert job which appeared to be using 80n megs of shared pool every hour of execution and just today moved this to a standalone database for processing.
    What kind of insert job could be using 80 megs of shared pool? What is it using such amount of shared pool for? Do you extensively use PL/SQL tables during that insert job?

    It simply doesn't make to much sence to me, can you describe n more detail what this insert job is doing?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Another idea about that insert job:

    Are you performing this insert in paralell (paralell DML)? If so, is it possible that you don't have LARGE_POOL defined in your instance? If there is no large pool, parallel execution uses shared pool for IPC messages between coordinator and slave processes and this can consume *a lot* of shared pool, not to mention that it contributes extensively to shared pool fragmentation!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2002
    Posts
    15
    the insert is from a bpcs system. approx 27,000 rows of data used for calculations of stock levels. After which it is deleted and inserted again (every hour)

    Since this put a large strain on redo - the plan was to remove at some point.

    I figured since it was the biggest shared pool user, it would contribute the most to fragmentation - as it grabbed and released a lot of memory at intervals.

    The insert is a standard insert - no parallel



Posting Permissions

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


Click Here to Expand Forum to Full Width