Automatic Memory Management

[Dulima-SP]

Environment information:
* Oracle version: 10g
* Operating system: 2003 Sever


Database masters.


I am in the process of homologation for migration of the database .. 9i to 10g
I will use the managely automatic memory ..
Here comes the doubts

this coonfiguration is correct ??

Select all

 
ALTER SYSTEM SET SGA_TARGET = 1504M; -- esse e o tamanho da minha SGA Target 
ALTER SYSTEM SET SHARED_POOL_SIZE = 0; 
ALTER SYSTEM SET LARGE_POOL_SIZE = 0; 
ALTER SYSTEM SET JAVA_POOL_SIZE = 0; 
ALTER SYSTEM SET DB_CACHE_SIZE = 0; 
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0; 
 

After configured has somewhere that shows me that it is configured that way ??

Thanks Garela ...

[elidias7]

Good Morning.

You can check through the SGA_TARGET parameter

show parameter sga_target

Another question does not forget your sga_max_size ... ....


Hugs,

[Dulima-SP]

OO OLD ... VLW for the answer

are my sga_max_size this with the same size as the target

Select all

 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
sga_max_size                         big integer 1504M 
sga_target                              big integer 1504M 
 

it's correct???
What parameter of show Parameter informs me that Oracle is managing the memory ???

Worth it. !!

[Andre-PE]

Good afternoon,
Dulima, I saw your post and I was preparing a script for you to check the mapping of your SGA!

Select all

set echo off 
set feedback off 
set linesize 512 
 
prompt 
prompt SGA Memory Map (Visao Geral) 
prompt 
 
column dummy noprint 
column area format a20 heading 'Main SGA Areas' 
column name format a20 
column pool format a20 
column bytes format 999,999,999,999 
column sum(bytes) format 999,999,999,999 
 
break on report 
compute sum of sum(bytes) on report 
 
SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes) 
FROM v$sgastat WHERE pool is null and name = 'db_block_buffers' 
group by name 
union all 
SELECT 2, 'Shared Pool', pool, sum(bytes) 
FROM v$sgastat WHERE pool = 'shared pool' 
group by pool 
union all 
SELECT 3, 'Large Pool', pool, sum(bytes) 
FROM v$sgastat WHERE pool = 'large pool' 
group by pool 
union all 
SELECT 4, 'Java Pool', pool, sum(bytes) 
FROM v$sgastat WHERE pool = 'java pool' 
group by pool 
union all 
SELECT 5, 'Redo Log Buffer', name, sum(bytes) 
FROM v$sgastat WHERE pool is null and name = 'log_buffer' 
group by name 
union all 
SELECT 6, 'Fixed SGA', name, sum(bytes) 
FROM v$sgastat WHERE pool is null and name = 'fixed_sga' 
group by name 
ORDER BY 4 desc; 
 
 
column area format a20 heading 'Shared Pool Areas' 
 
prompt 
prompt Mapeamento da memoria SGA (Shared pool) 
prompt 
 
SELECT 'Shared Pool' area, name, sum(bytes) 
FROM v$sgastat WHERE pool = 'shared pool' and name in ('library cache','dictionary cache','free memory','sql area') 
group by name 
union all 
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes) 
FROM v$sgastat WHERE pool = 'shared pool' and name not in ('library cache','dictionary cache','free memory','sql area') 
group by pool 
order by 3 desc; 
 
prompt 
prompt Mapeamento da Cache SGA (DB cache size) 
prompt 
 
SELECT name, value 
FROM v$spparameter 
WHERE name = 'db_cache_size' 

Any doubts put there?

[] 's

[Dulima-SP]

Oo Old ... vlw ...

More What goal for this script ??
What do I need to know ???

And my configuration to let Oracle manage the memory is correct ???

VLW VLY

[Andre-PE]

Oops, you can create a script with these information through a notepad and name it as you want, example: "script_monitorora_sga.sql" and call it in sqlplus for example, as follows:
connects to SQLPlus:
SQL> @ "C: \ path \ script_monitorora_sga.sql"

then you can view your configurations, as you previously requested.

Pro Oracle Manage your memory automatically You need to set up a PGA structure:

The PGA is the memory buffer that contains data and some information control of a user's session. The PGA is created and allocated when a new process is initialized on the server. Your information depends on the Oracle configuration. Thus, there is a PGA memory area for each user who is running their work in Oracle. Inside the PGA there are three structures: one containing a space for the stack (to store the variables and arrays), another containing data on the user session and a third with the information of the cursors used. PGA is not shared among users; It is unique for each session.

of a searched in PGA memory management before configuring your management!

[] 's

[Nadja]

Good Dulima, I want to try to help you:
My configuration to let Oracle manage the memory is correct ???
Yes.

What parameter of the show Parameter informs me that Oracle is managing the memory ???
To turn off the ASMM You must set the SGA_TARGET parameter as zero, so believe that once this parameter is in a value, this means that Oracle is automatically managing memory.

to verify the current sizes of SGA areas:

Select all

SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;