Home » RDBMS Server » Server Administration » Memory leak after upgrading 9.2.0.4 to 10.2.0.4 (Oracle 10.2.0.4, Red Hat Linux 5.3)
Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415397] Mon, 27 July 2009 09:55 Go to next message
linuxos
Messages: 31
Registered: April 2009
Member
Hi!

I've same set of program (mostly DELETE and INSERT statements) which can be executed successfully in 9i, but not 10g. All values of memory parameters are larger than or same as before.

It always throws "out of process memory" error in 10g. My PGA_AGGREGATE_TARGET is not over 2GB. (32 bit linux). I don't know why!

a. Is it possible for the SQL statement to bypass the Log buffer or Redo Log?
b. Can I set the LGWR and DBWR to run faster? (due to lots of SQL in a batch)
c. How can I efficiently use the PGA to avoid memory leak when running lots of SQL in a short period of time?
d. Is it valid to to SGA_TARGET + PGA_AGGREGATE_TARGET > 2GB?

Thanks in advance!
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415415 is a reply to message #415397] Mon, 27 July 2009 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the EXACT error code and message.

Regards
Michel
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415432 is a reply to message #415397] Mon, 27 July 2009 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
using <code tags> post results from following SQL:
select * from v$pga_target_advice
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415444 is a reply to message #415397] Mon, 27 July 2009 21:24 Go to previous messageGo to next message
linuxos
Messages: 31
Registered: April 2009
Member
I set the pga_aggregate_target to around 900MB. From the following result, is it suitable to set it to 1.5GB?

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
              112499712                 0 ON          5054464                   0                           100                    5
              224999424                 0 ON          5054464                   0                           100                    5
              449999872                 1 ON          5054464                   0                           100                    5
              674999296                 1 ON          5054464                   0                           100                    4
              899999744                 1 ON          5054464                   0                           100                    3
             1079999488                 1 ON          5054464                   0                           100                    3
             1259999232                 1 ON          5054464                   0                           100                    2
             1439998976                 2 ON          5054464                   0                           100                    2
             1619998720                 2 ON          5054464                   0                           100                    0
             1799999488                 2 ON          5054464                   0                           100                    0
             2699999232                 3 ON          5054464                   0                           100                    0
             3599998976                 4 ON          5054464                   0                           100                    0
             5399998464                 6 ON          5054464                   0                           100                    0
             7199997952                 8 ON          5054464                   0                           100                    0
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415447 is a reply to message #415397] Mon, 27 July 2009 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>From the following result, is it suitable to set it to 1.5GB?
Yes. It likely would be better than current value.

If/when the change is made, then run production workload for 1 - 2 days or more to sustain peak workload & then run same query again.
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415450 is a reply to message #415397] Mon, 27 July 2009 22:45 Go to previous messageGo to next message
linuxos
Messages: 31
Registered: April 2009
Member
When the total PGA inuse jumps to about 1.7GB, it died with these error.
ORA-12801: error signaled in parallel query server P023
ORA-00604: error occurred at recursive SQL level 2
ORA-04030: out of process memory when trying to allocate xxxxx bytes (QERHJ hash-joi,QERHJ Bit vector)


I can't control the memory leak of the PGA.
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415451 is a reply to message #415397] Mon, 27 July 2009 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ORA-12801: error signaled in parallel query server P023
Does the code run to completion when parallelization is disabled?
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415453 is a reply to message #415450] Mon, 27 July 2009 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-04030: out of process memory when trying to allocate %s bytes (%s,%s)
 *Cause:  Operating system process private memory has been exhausted
 *Action:

It is an OS error when Oracle tries to allocate, NOT an Oracle error.
Check ulimit and increase it for Oracle process owner.

Regards
Michel
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415455 is a reply to message #415397] Mon, 27 July 2009 23:11 Go to previous messageGo to next message
linuxos
Messages: 31
Registered: April 2009
Member
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 278528
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 4194302
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


Which should be modified?
Re: Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415456 is a reply to message #415451] Mon, 27 July 2009 23:13 Go to previous message
linuxos
Messages: 31
Registered: April 2009
Member
I found that "parallel_server" is FALSE, and I am not sure whether Logical Standby would use it. The "parallel_max_servers" is 150.
Previous Topic: How to Creating dictionary managed tablespace in 10g
Next Topic: Reset database (merged)
Goto Forum:
  


Current Time: Fri Dec 09 00:02:23 CST 2016

Total time taken to generate the page: 0.23158 seconds