Tuesday, August 7, 2012

Oracle EBS R12 Database Memory Parameters

Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1] 

SELECT  ROUND(SUM(BYTES)/1024/1024,2) TOTAL_SGA,
ROUND(SUM(DECODE(NAME,'free memory',BYTES,0))/1024/1024,2) FREE,
ROUND((SUM(DECODE(NAME,'free memory',BYTES,0))/1024/1024)/(SUM(BYTES)/1024/1024)*100,2) FREE_PER
FROM V$SGASTAT

SELECT POOL, SUM(BYTES) FROM V$SGASTAT
WHERE POOL IS NOT NULL GROUP BY POOL
ORDER BY POOL;


Memory Management Data Dictionary Views

The following dynamic performance views provide information on memory management:
View Description
V$SGA Displays summary information about the system global area (SGA).
V$SGAINFO Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool.
V$PGASTAT Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
V$MEMORY_DYNAMIC_COMPONENTS Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.
V$SGA_DYNAMIC_COMPONENTS Displays the current sizes of all SGA components, and the last operation for each component.
V$SGA_DYNAMIC_FREE_MEMORY Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$MEMORY_CURRENT_RESIZE_OPS Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component.
V$SGA_CURRENT_RESIZE_OPS Displays information about dynamic SGA component resize operations that are currently in progress.
V$MEMORY_RESIZE_OPS Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET.
V$SGA_RESIZE_OPS Displays information about the last 800 completed SGA component resize operations.
V$MEMORY_TARGET_ADVICE Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management.
V$SGA_TARGET_ADVICE Displays information that helps you tune SGA_TARGET.
V$PGA_TARGET_ADVICE Displays information that helps you tune PGA_AGGREGATE_TARGET.

No comments:

Post a Comment