gorantla_rao

 Profile Answers by gorantla_rao 

• Mar 2nd, 2006

 

hi,

          I don't the server cofiguration, but as per the query my knowledge "Oracle automatically allocates some space in RAM that is called SGA(System Gloabal Area). we could not define the size of  SGA or Shared pool Memory".

        The default Size of Oracle block upto 9i is 2KB, but from 10g it is 8KB

Srinivas. 

Aruna

• Mar 6th, 2006

 

Srinivas, System does nt allocate SGA automatically. we have to configure SGA. to do the same we can set following parameters in pfile such as db_cache_size, shared_pool_size, db_block_size. - Aruna

monto

• Mar 8th, 2006

 

oooooooooooopppppppps what an answer mr.srinivas hahhaa

sga:its chunk of memory which is shared by oracle processes and its a memory allocate by oracle instance on server(server side memory).sga is divide into fixed memory(database buffer)is constant cannot be changed and memory is allocated in terms of granules and variable memory(shared pool,large pool,javapool,db_cache_size) which can changed dynamically (session level) or at instance level and memory is allocated in bytes.For optimal performance the  sga should fit into RAM.Shared pool contains recently used sql, pl/sql statments which can be shared among all users ,it includes shared sql area and data dictionary cache.

Deep Chandra Pandey

• Mar 30th, 2006

 

You can check your oracle Server configuratiom by typing:

SQL>Show sga

SGA is consist of memory area and the background process.

If

DANISH WADOOD

• May 18th, 2007

 

I have HP Pro ML150 server having 3GH processor and 2GB Ram

Arjun

• Jul 19th, 2007

 

Hi,

You can see current instance configuration by the below command :

         show parameters

It will display all the configuration of the instance. But if you want to see some special setting then just type :

         show parameters db

Now this comamd will show all the settings that contain words db only instead of complete parameter settings.

orafighter

 Profile Answers by orafighter 

• Sep 6th, 2007

 

Hello
sql>show sga , then it shows these parameters
---- Total system global area
---- Fixed size
----Variable size
----Database buffers
----Redo buffers 

or can query from select * from v$sga

parveshdata

 Profile Answers by parveshdata 

• Jun 26th, 2009

 

Use show sga to know all the important parameters that are there for you by default setting of oracle or may be DBA has set at the time of installing the database in your init.ora or sp file.

Many of the SGA values depend upon your system/user/org. environment. How many hits you are having and according to that you need to decide the value of share_pool_size, large_pool_size(if its a shared server), log_buffer, db_block_size (very important), db_cache_advice, sga_max_size.

shared_pool_size includes data dictionary cache (cahce information about data dictionaries , tables, indexes, user ..etc.) and library cache (sql & plsql related queried objects)

If you don't know what size you should use for db_block_size then go for oracle default size and make sure that your db_cache_advice parameter is on. then after some time check the view related to advisory and change the db_block_size accordingly.