OraFAQ Forum: Server Administration » Memory leak after upgrading 9.2.0.4 to 10.2.0.4

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Memory leak after upgrading 9.2.0.4 to 10.2.0.4 [message #415397]

Mon, 27 July 2009 09:55

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!

Report message to a moderator

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

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the EXACT error code and message.

Regards
Michel

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

using <code tags> post results from following SQL:
select * from v$pga_target_advice

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
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?

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

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?

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	How to Creating dictionary managed tablespace in 10g
Next Topic:	Reset database (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 01:25:39 CDT 2024