SGA

"The SGA comprises several memory components, which are pools of memory used to satisfy a particular class of memory allocation requests." "The memory for dynamic components in the SGA is allocated in the unit of granules. The granule size is determined by the amount of SGA memory requested when the instance starts. Specifically, the granule size is based on the value of the SGA_MAX_SIZE initialization parameter."(2)

PGA_AGGREGATE_TARGET

Sizing the PGA

SELECT st.sid,

s.name,

st.value

FROM v$statname s,

v$sesstat st

WHERE st.statistic# = s.statistic#

AND s.name = 'session pga memory max';

TODO:

select * from v$pga_target_advice;

select * from v$pga_target_advice_histogram;

WORKAREA_SIZE_POLICY

Values

AUTO is the default value.

"AUTO When AUTO is specified, work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of each individual operator."(1)

"MANUAL When MANUAL is specified, the sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization."(1)

Check

SHOW PARAMETER WORKAREA_SIZE_POLICY

SHOW SPPARAMETER WORKAREA_SIZE_POLICY

Change

Current session only...

ALTER SESSION SET WORKAREA_SIZE_POLICY=AUTO;

In memory only, instance wide...

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO SCOPE=MEMORY;

Server Parameter file only...

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO SCOPE=SPFILE;

In memory, instance wide and in Server Parameter file...

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO SCOPE=BOTH;

Shared Pool

Shared Pool Usage Guidelines

To use the Shared Pool efficiently...

• Use bind variables instead of literals in SQL statements whenever possible.

• Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements.

• Ensure that users of the application do not change the optimization approach and goal for their individual sessions.

• Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

• Consider using stored procedures whenever possible.

For SQL statements which are identical but are not being shared, query the V$SQL_SHARED_CURSOR view to determine why the cursors are not shared.

SHARED_POOL_SIZE

To change the size of the Shared Pool...

SHARED_POOL_SIZE

Library Cache

The library cache stores executable (parsed or compiled) forms of recently referenced SQL cursors, PL/SQL programs, and Java classes.

• A library cache miss = hard parse

SELECT namespace,

pins AS executions,

pinhits AS LC_Hits,

reloads AS LC_misses,

invalidations

FROM v$librarycache

WHERE namespace IN ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')

ORDER BY namespace;

• LC_MIsses (hard parses) should have a value near zero

• Invalidations should have a value near zero

SELECT SUM(pins) AS executiuons,

SUM(pinhits) AS LC_Hits,

SUM(pinhits)/SUM(pins) AS LC_HitRatio

FROM v$librarycache;

• LC_HitRatio should be close to 100%

SELECT *

FROM v$sgastat

WHERE name = 'free memory'

AND pool = 'shared pool';

• If free memory is always available then increasing the size of the shared pool will yield little benefit.

See Also

• V$SHARED_POOL_ADVICE

• V$LIBRARY_CACHE_MEMORY

• V$JAVA_POOL_ADVICE

• V$JAVA_LIBRARY_CACHE_MEMORY

Data Dictionary Cache

The data dictionary cache stores data referenced from the data dictionary.

• Usernames

• Segment information

• Profile data

• Tablespace information

• Sequence numbers

• Metadata about schema objects (used when parsing SQL cursors or during the compilation of PL/SQL programs).

Generally, if the shared pool is adequately sized for the library cache, it will also be adequately sized for the data dictionary cache.

COLUMN parameter FORMAT a21

COLUMN pct_succ_gets FORMAT 999.9

COLUMN updates FORMAT 999,999,999

SELECT parameter,

SUM(gets),

SUM(getmisses),

100*SUM(gets - getmisses) / SUM(gets) pct_succ_gets,

SUM(modifications) updates

FROM V$ROWCACHE

WHERE gets > 0

GROUP BY parameter;

To get an overall hit ratio for the data dictionary cache use...

SELECT (SUM(gets - getmisses - fixed)) / SUM(gets) "row cache"

FROM V$ROWCACHE;

Server Result Cache

The server result cache is an optional cache that stores query and PL/SQL function results within the shared pool.

OPEN_CURSORS

The value of OPEN_CURSORS can impact usage of the Library Cache. However, as long as sessions do not suddenly open more cursors than normal, there is no added overhead to setting this value higher than actually needed. i.e. 100 open cursors will use the same amount of Library Cache irrespective of whether OPEN_CURSORS is set to 100 or 500.

Check

To check the current value of OPEN_CURSORS...

SHOW PARAMETER open_cursors;

Or, to check the value in the SPFile..

SHOW SPPARAMETER open_cursors;

Update

To update the value of OPEN_CURSORS...

ALTER SYSTEM SET OPEN_CURSORS=150 SCOPE=BOTH;

ORA-01000 maximum open cursors exceeded

In the event of an ORA-01000 error you need to do one of two things....

1. Increase the value of the OPEN_CURSORS parameter.

2. Change the code so that it requires less open cursors. For example, in PL/SQL you should explicitly CLOSE cursors when you are finished processing them.

Bibliography