Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    46

    Unanswered: Can sga_max_size and pga_aggregate_target be equal ??

    Dear Support,

    I need your assistance to resolve a recurring problem in my database.

    I keep getting ORA-04030 out of process memory when trying to allocate.

    Upon searches the net, there was suggestion to increase the pga_aggregate_target. I implemented this and the error stopped flooding the alert_log.

    However, after some time as volume of transaction increaased, the error started reappearing. Again, I increased pga_aggregate_target and the error stoped. On subsequent times I did same.

    But my sga_max_size is 1GB and as at now pga_aggregate_target is 450M

    I will therefore like to know
    1. how to resolve this error permanently and
    2. how large can I keep increasing the pga_aggregate_target
    3. can I keep increasing the pga_aggregate_target to be equal to sga_max_size ??

    I assistance is most welcome

    W.K

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Basically you should try to avoid ALL of your processes (including Oracle) exceeding the amount of physical RAM on the server. So the sga+pga should be less than the amount of RAM. Of course you should allow for the OS to consume some RAM. The other thing which might affect is if you are running on a 32bit OS which might in Windows limit you to between 2 -3 GB per process (even if you have more than 4GB of RAM).

    So how much RAM does your server have and what is the memory consumption of the other processes.

    Alan

  3. #3
    Join Date
    Mar 2009
    Posts
    46
    Hi Alan,

    Thanks for your explanation.
    sory I did not inform you about the OS Platform.

    OS Platform is Windows 2003 R2 and RAM size is 2G

    As I checked the right now, total size of RAM in use by all processes is approx. 1.6GB

    Out of this, Oracle alone is consuming about 1.1GB

    Regards

    W.K

  4. #4
    Join Date
    Mar 2009
    Posts
    46
    Dear Support mates,

    The error has started flooding my alert log
    Below are the messages and the contents of the trace file
    Your assistance is very much welcome:
    ============================

    Errors in file c:\oracle\product\10.2.0\admin\smartbk\bdump\smart bk_j000_1696.trc:
    ORA-12012: error on auto execute of job 1
    ORA-04030: out of process memory when trying to allocate 60 bytes (kxs-heap-p,continuous uac)
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 48
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 936
    ORA-06512: at line 1

    Sun Aug 08 04:16:09 2010
    Errors in file c:\oracle\product\10.2.0\admin\smartbk\bdump\smart bk_j000_1696.trc:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04030: out of process memory when trying to allocate 44 bytes (kxs-heap-d,allocator state)
    ORA-12012: error on auto execute of job 1
    ORA-04030: out of process memory when trying to allocate 60 bytes (kxs-heap-p,continuous uac)
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 48
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 936
    ORA-06512: at line 1


    ======================
    contents in the trace file smartbk_j000_1696.trc:


    Dump file c:\oracle\product\10.2.0\admin\smartbk\bdump\smart bk_j000_1696.trc
    Sun Aug 08 04:16:09 2010
    ORACLE V10.2.0.1.0 - Production vsnsta=0
    vsnsql=14 vsnxtr=3
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Windows NT Version V5.2 Service Pack 2
    CPU : 8 - type 586, 2 Physical Cores
    Process Affinity : 0x00000000
    Memory (Avail/Total): Ph:406M/2045M, Ph+PgF:4506M/5995M, VA:44M/2047M
    Instance name: smartbk

    Redo thread mounted by this instance: 1

    Oracle process number: 240

    Windows thread id: 1696, image: ORACLE.EXE (J000)


    *** ACTION NAME ) 2010-08-08 04:16:09.827
    *** MODULE NAME ) 2010-08-08 04:16:09.827
    *** SERVICE NAMESYS$USERS) 2010-08-08 04:16:09.827
    *** SESSION ID553.1857) 2010-08-08 04:16:09.827
    *** 2010-08-08 04:16:09.827
    ORA-12012: error on auto execute of job 1
    ORA-04030: out of process memory when trying to allocate 60 bytes (kxs-heap-p,continuous uac)
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 48
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 936
    ORA-06512: at line 1
    *** 2010-08-08 04:16:09.842
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04030: out of process memory when trying to allocate 44 bytes (kxs-heap-d,allocator state)
    ORA-12012: error on auto execute of job 1
    ORA-04030: out of process memory when trying to allocate 60 bytes (kxs-heap-p,continuous uac)
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 48
    ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 936
    ORA-06512: at line 1

    Much thanks in advance
    W.K

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Running low on swap space can throw up ORA-04030 issues...
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Mar 2009
    Posts
    46
    Hi cis_groupie,

    Thanks for the suggestion. But how do I resolve this issue.

    Currently my RAM is 2G and my paging space (or swap space ) is 4G; ie 2 X RAM.

    So , what else should I do to resolve this issue??

    w.K

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Short of listing every option it would be best if you start here:

    OTN Discussion Forums : Sorting error : ORA-04030 ...

    Then if you still haven't resolved it try googling 'ORA-04030'.

    I'm not trying to fob you off, but all we can do here is recommend something, wait for you to try it, then if it doesn't work recommend something else - you'd be much quicker missing us out (as our immediate suggestions haven't worked) & searching the interent & forums for past reportings of this error & see what was done to fix it.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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