OraFAQ Forum: Performance Tuning » Oracle process exceed SGA_MAX

Home » RDBMS Server » Performance Tuning » Oracle process exceed SGA_MAX_TARGET (Oracle database 11g R2,Windows 2008 server)

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

Oracle process exceed SGA_MAX_TARGET [message #560809]

Tue, 17 July 2012 04:41

Sami_Younis
Messages: 49
Registered: February 2008
Location: Egypt

Member

Hello guys,
I have an Oracle Database 11gR2 installed on Windows 2008 server. But there is a kind of hang sometimes arise during work hours.
while i am opening control panel i saw oracle process is around 15G even we configured SGA_MAX_TARGET=6g.
Thanks,
Sami

Report message to a moderator

Re: Oracle process exceed SGA_MAX_TARGET [message #560812 is a reply to message #560809]

Tue, 17 July 2012 04:45

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

Senior Member
Account Moderator

And PGA target? And memory target? Post ALL memory parameters.

Regards
Michel

Report message to a moderator

Re: Oracle process exceed SGA_MAX_TARGET [message #560938 is a reply to message #560812]

Tue, 17 July 2012 21:21

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I find the following a start on understanding memory parameters.
You will have to research each parameter to really understand how
to set them.

ECSCDAP1P > show sga

Total System Global Area 3.2068E+10 bytes
Fixed Size                  2244192 bytes
Variable Size            1.7314E+10 bytes
Database Buffers         1.4697E+10 bytes
Redo Buffers               55267328 bytes

ECSCDAP1P > @RAC_DISPLAY_OS_MEMORY.sql

INSTANCE GIGABYTES STAT_NAME
-------- --------- ------------------------------------------------------------
CSCDAP1        252 PHYSICAL_MEMORY_BYTES
CSCDAP2        252 PHYSICAL_MEMORY_BYTES
CSCDAP3        252 PHYSICAL_MEMORY_BYTES
CSCDAP4        252 PHYSICAL_MEMORY_BYTES

ECSCDAP1P > list
  1  select i.instance_name instance, value/1024/1024/1024 gigabytes,
  2  stat_name
  3  from dba_hist_osstat o,gv$instance i
  4  where snap_id in
  5  (select max(snap_id) from dba_hist_osstat) and i.inst_id=o.instance_number
  6  and upper(stat_name) like upper('%MEMORY%')
  7* order by instance_name

ECSCDAP1P > @display_RAC_Memory_parameters.sql

GIGABYTES NAME
--------- ----------------------------------------
       30 sga_max_size
        0 shared_pool_size
        0 shared_pool_reserved_size
       20 sga_target
        5 db_cache_size
        0 db_2k_cache_size
        0 db_4k_cache_size
        0 db_8k_cache_size
        0 db_16k_cache_size
        0 db_32k_cache_size
        3 db_keep_cache_size
        3 db_recycle_cache_size
        0 db_flash_cache_size
       20 pga_aggregate_target

ECSCDAP1P > list
  1  select value/1024/1024/1024 gigabytes,name from v$parameter
  2  where name like 'db_%cache_size' or name like 'sga_%'
  3* or name like 'pga_aggregate_target' or name like 'shared_pool%'

Report message to a moderator

Previous Topic:	How to Tune my SQL (5 merged)
Next Topic:	how can i know host name using sql id or sid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 27 23:02:20 CDT 2023