col component format a30
set linesize 132
prompt '
PROMPT' ============================================================================================'
prompt' SGA --Large Pool '
prompt' --Shared Pool '
prompt' Library Cache '
prompt' Shared SQL area (SQL Statement : eg select * from case ...) '
prompt' Result Cache ( Currenty set under paraeter RESULT_CACHE_MAX_SIZE '
prompt' --DATA Buffer Cache (data retrieve from disk) '
prompt' --Redo Log Buffer '
prompt' --JAVA Pool '
prompt' '
prompt' PGA -- SQL Work Area '
prompt ' Session Mem '
prompt' Private SQL area -> (SQL Statement) for dedicated connected session '
PROMPT' ==============================================================================================='
prompt' Automatic memory management is adopted to manage and tune the database memory automatically. '
prompt ' '
prompt' Under automatic memory management mode, management of the(SGA) and (PGA)'
prompt' will be dynamic allocated by Oracle n roughly 30%(SGA) / 70%(PGA) of Memory_Target'
prompt '-------------------------------------------------------------------------------------'
prompt' '
prompt' '
prompt' For wait event of gc buffer busy acquire '
prompt' Pay attention to the usage of Buffer Cache '
prompt' '
prompt' For wait event of enq: SQ - contention '
prompt' This is the contention of Oracle Object Sequence contention. '
prompt' alter the sequence with bigger CACHE (default 20) '
prompt' '
prompt' For wait event of library cache lock '
prompt' This is the contention of (SQL Code) '
prompt' That is to say, contention to add the SQL code into library pool for parsing '
prompt' which means difficult to get an latch in the library pool '
prompt' which inturn points to SQL not using blinding variable '
prompt' '
PROMPT' Current init set up
col name format a40
col value format a10
select name, round(value/1024/1024/1024,0)||'Gb' init_parameter from v$parameter where name in ('memory_max_target','memory_target','pga_aggregate_limit','pga_aggregate_target','sga_max_size','sga_target','streams_pool_size''large_pool_size','java_pool_size','shared_pool_reserved_size');
PROMPT' ============================================================================================'
prompt' Here is the R E A L T I M E value extracted from Oracle '
prompt 'SQL = select component, current_size, user_specified_size from v$memory_dynamic_components'
prompt '-------------------------------------------------------------------------------------'
--set heading off
select component memory_target, round(current_size/1024/1024/1024,0) CURR_Size, round(user_specified_size/1024/1024/1024,0) INIT_PAR
from v$memory_dynamic_components
where current_size <> 0
and component in ('SGA Target','PGA Target')
union
select 'Total--->', sum(current_size)/1024/1024/1024 sys_allocation, sum(user_specified_size/1024/1024/1024) user_config
from v$memory_dynamic_components
where component in ('SGA Target','PGA Target')
/
select component major_component_of_sga, round(current_size/1024/1024/1024,0) curr_size, round(user_specified_size/1024/1024/1024,0) INIT_PAR
from v$memory_dynamic_components
where current_size <> 0
and component in ('shared pool','DEFAULT buffer cache','java pool','DEFAULT 2K buffer cache','DEFAULT 8K buffer cache','DEFAULT 16K buffer cache')
/
prompt ' In any event we can:'
prompt ' alter the parameter MEMORY_TARGET to enlarge the PGA and SGA '
prompt ' Below shows the real time free memory of different poool '
PROMPT' ============================================================================================'
select pool, name, round(bytes/1024/1024,2)||' Mb'
from v$sgastat where name='free memory' and pool='shared pool'
/
exit