some scripts of sga

[Lian...@gmail.com]

-- calculate the hit ratio of the db cache
-- (goal: Buffer Cache Hit Ratio > 0.9)

SELECT NAME, physical_reads, db_block_gets, consistent_gets,
1
- (physical_reads / (db_block_gets + consistent_gets))
"Buffer Cache Hit
Ratio"
FROM v$buffer_pool_statistics;

---------------------------------------------------------------------------

-- display the hit ratio of the library cache in shared pool
--
-- The library cache holds executable forms of SQL cursors, PL/SQL
programs, and Java
-- classes. This section focuses on tuning as it relates to cursors,
PL/SQL programs, and
-- Java classes.
--
-- (goal: reloads = 0, pinhitratio > 0.9)

SELECT namespace, pins, pinhits, pinhitratio, reloads
FROM v$librarycache;

---------------------------------------------------------------------------

-- calculate the get ratio of the dictionary cache in shared pool
--
-- Information stored in the data dictionary cache includes usernames,
segment, information,
-- profile data, tablespace information, and sequence numbers.
--
-- (goal: pct_succ_get > 90 )

SELECT parameter, SUM (gets), SUM (getmisses),
DECODE (SUM (gets),
0, 1,
100 * SUM (gets - getmisses) / SUM (gets)
) "pct_succ_gets",
SUM (modifications) "updates"
FROM v$rowcache
GROUP BY parameter;

---------------------------------------------------------------------------

-- display the statistics of uga memory used
--
-- If you are using a shared server, you can use
-- the following query to decide how much larger to make the shared
pool.
--

SELECT SUM (DECODE (sn.NAME, 'session uga memory', ss.VALUE, 0)
)
|| ' bytes' "total used uga memory",
SUM (DECODE (sn.NAME, 'session uga memory max', ss.VALUE, 0)
)
|| ' bytes' "total used uga memory(max)"
FROM v$statname sn, v$sesstat ss
WHERE ss.statistic# = sn.statistic#
AND sn.NAME IN ('session uga memory', 'session uga memory max');

---------------------------------------------------------------------------

-- calculate the request misses of shared_pool_reserved
-- (goal: request_misses = 0, request_failures = 0, pct_free_space <
0.5)

SELECT free_space, requests,
free_space / (free_space + used_space) "pct_free_space",
request_misses, request_failures
FROM v$shared_pool_reserved;

---------------------------------------------------------------------------

-- display the number of times a user process waits for space in the
redo log buffer
--
-- The statistic REDO BUFFER ALLOCATION RETRIES reflects
-- the number of times a user process waits for space in the redo log
buffer.
--
-- (goal: value = 0)

SELECT NAME, VALUE
FROM v$sysstat
WHERE NAME = 'redo buffer allocation retries';