Home » RDBMS Server » Performance Tuning » Oracle process exceed SGA_MAX_TARGET (Oracle database 11g R2,Windows 2008 server)
Oracle process exceed SGA_MAX_TARGET [message #560809] Tue, 17 July 2012 04:41 Go to next message
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
Re: Oracle process exceed SGA_MAX_TARGET [message #560812 is a reply to message #560809] Tue, 17 July 2012 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And PGA target? And memory target? Post ALL memory parameters.

Regards
Michel
Re: Oracle process exceed SGA_MAX_TARGET [message #560938 is a reply to message #560812] Tue, 17 July 2012 21:21 Go to previous message
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%'
Previous Topic: How to Tune my SQL (5 merged)
Next Topic: how can i know host name using sql id or sid
Goto Forum:
  


Current Time: Fri Apr 25 00:58:32 CDT 2014

Total time taken to generate the page: 0.05788 seconds