 |
|
SGA - System Global Area: composed of shared memory
structures, created at the database instance startup. Memory structures
hold information about the database instance and control instance behavior
SGA is mainly divided into 3 parts : Share Pool (
Library Cache + Dictionary Cache + optional memory structures) , Database
Buffer Cache , Redo Log Buffer
Share Pool - SHARED_POOL_SIZE
Library cache has memory distribution as SQL or procedural
code is run, based on the individual users’ sessions and in accordance
with the least recently used (LRU) algorithm.
Dictionary Cache provides information about object
structures from the data dictionary and provides to the SQL statements
being parsed
Datase Buffer Cache - DB_CACHE_SIZE
Database Buffer Cache holds copies
of data requested by SQL and reduces requests to disk drive by having
data in memory
Redolog Buffer - LOG_BUFFER
Redo Log Buffer holds changes made to
data and allows for the reconstruction of data in the case of failure.
Log buffer improves I/O by buffering the redo entries that are written
to the online redo logs
Rule:DAMICO - ( M - Memory Structure) |
| Library Cache |
SELECT (SUM(PINS - RELOADS)) / SUM(PINS)
"LIB CACHE" FROM V$LIBRARYCACHE; |
If the ratios are close to 1, you
do not need to increase the pool size |
Interpreting the V$LIBRARYCACHE Table
Examining the data returned by the sample query
leads to these observations:
* The sum of the EXECUTIONS column indicates that SQL statements,
PL/SQL blocks, and object definitions were accessed for execution
a total of 320,871 times.
* The sum of the CACHE MISSES WHILE EXECUTING column indicates
that 549 of those executions resulted in library cache misses
causing Oracle to implicitly reparse a statement or block or reload
an object definition because it aged out of the library cache.
* The ratio of the total misses to total executions is about 0.17%.
This value means that only 0.17% of executions resulted in reparsing.Total
misses should be near 0. If the ratio of misses to executions
is more than 1%, try to reduce the library cache misses through
the means discussed in the next section.
Reducing Library Cache MissesYou can reduce library cache misses
by: * Allocating additional memory to the library cache
* Writing identical SQL statements whenever possible |
Dictionary Cache |
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED))
/ SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; |
the ratio of total GETMISSES to total GETS should
be less than 10% or 15%, depending on the application |
Increasing the amount of memory available to the
cache if all of the following are true:
Your application is using the shared pool effectively.
See "Using the Shared Pool Effectively" .
Your system has reached a steady state, any of the
item-specific hit ratios are low, and there are a large numbers
of gets for the caches with low hit ratios.
Increase the amount of memory available to the data
dictionary cache by increasing the value of the initialization
parameter SHARED_POOL_SIZE
|
| Database Buffer Cache |
SELECT name, block_size,
100*(1 - (physical_reads /
decode((db_block_gets+consistent_gets),
0,1,null,1,(db_block_gets+consistent_gets))))
buffer_hit_ratio
FROM v$buffer_pool_statistics; |
buffer_hit_ratio of 95% or greater is considered to be a good
hit ratio for OLTP systems.The hit ratio for DSS (Decision Support
System) may vary depending on the database load. A lower hit ratio
means Oracle is performing more disk IO on the server |
1.Capture the actual statistic values before you run a workload
through the database.
2 Capture the ending statistics after the workload running.
3 Subtract the beginning hit ratio from the ending hit ratio to
find the buffer hit ratio for
the workload period.
|
| Redo Log Buffer |
select name, value
from v$sysstat
where name='redo buffer allocation retries'; |
If the returned value is any value other than zero, the system
has been unable to obtain
space in the log buffer. |
alter the parameter LOG_BUFFER or improve the placement of the
files associated with the online redo logs (the storage array is
unable to handle the I/O requests in relation to where the online
redo logs exist)
|
|
G
r e a t D e a l s
|
Orcl-Experts
