Oracle performance tuning study notes (d) - Library Cache Statistics

2011-09-16  来源:本站原创  分类:Database  人气:157 

Library Cache Statistics
1. When you change the objectives of the shared pool of soft parsing SQL statements.
reload: reload object is invalid or changes in use need to reload. to reduce the reload value.
INVLIDATIONS: in v $ librarycache, the number of library cache is invalid.
2 shared pool free memory size (v $ sgastat).
Library cache hit ratio.
select gethitratio from v $ librarycache
where namespace = 'SQL AREA';
See the SQL statement is run
select sql_text, users_executing, executions, loads
from v $ sqlarea;
select * from v $ sqltext
where sql_text like '% q%';

3 library cache reload must be less than 1%.
select sum (pins) "Excutions",
sum (reloads) "Cache Misses",
sum (reloads) / sum (pins) reloads-to-pins
from v $ librarycache;
If the reloads-to-pins is greater than 1%, the need to increase shared pool size.

select namespace, gets, gethits, pins, pinhits, reloads, invalidations from v $ librarycache;
Note: generally only concerned with: the first four results, a few other inaccurate.
4. Invalid number
select namespace, gets, gethits, pins, pinhits, reloads, invalidations from v $ librarycache;
The information collected in Table
exec dbms_stats.gather_table_stats ('SCOTT', 'EMP');

library Cache Sizing:
Of the storage size of various objects.
(2) memory information is usually the sql statement.
3 defined in the share pool a large memory space (reserve space forlarge memory). frequency
Shared Pool Advisory
share_pool tuning must see share_pool_size and time-saving relationship
select shared_pool_size_for_estimate as pool_size,
estd_lc_size, estd_lc_time_saved
from v $ shared_pool_advice;
Execution Plan Caching
SQL statement cache in memory.
SQL statements and SQL execution plans co-exist.
Query execution plan for performance tuning.
v $ sql_plan: to save the library cache part of the cache.
v $ sql_plan in plan_hash_value and v $ sql in hash_value column association.
select operation, object_owner, object_name, cost
from v $ sql_plan
order by hash_value;

Global non-SQL statement cache space occupied by the object library cache memory size.
select sum (sharable_mem) from v $ db_object_cache;
SQL statement library cache memory for objects
select sum (sharable_mem) from v $ sql_area
where executions> 5;
Retention pond (about 10% of the shared pool):
Retention pond: fragmentation, etc. When the shared memory pool when the pool Zhennan Guan can be used to retain the memory. Shared pool free memory, will be returned to keep the pool.
shared_pool_reserved_size to keep the size of the pool.
shared_pool_size for the shared pool size
v $ shared_pool_reserved reserved pool size for tuning. free_space,
request_misses use the number of reserved pool if shared pool reserved and non-retained and is still insufficient, then use the LRU algorithm to remove the data.
select free_space, requests, request_misses, request_failures
from v $ shared_pool_reserved;
v $ shared_pool_reserved the request_failures, will continue to increase, shared_pool too small:
request_misses if often 0 or free_memory often shared
Retained more than 50% of the pool, you can appropriately reduce the reserved pool.
request_failures> 0 and request_misses> 0 increase the retention pond.
request_failures> 0 and free_memory> = 50% increase in the shared pool
request_failures = 0 or free_memory> = 50% reduction sharing reserved pool

Piles of curing the query in memory objects of non-curing
select * from v $ db_object_cache
where sharable_mem> 10000
and kept = 'NO';
Cure object
exec dbms_shared_pool.keep ('package_name');

Clear the contents of shared pool
alter system flush shared_pool;
Anonymous PLSQL block
select sql_text from v $ sqlarea
where command_type = 47
and length (sql_text)> 500;

Data directionary Cache
v $ rowcache access to the data dictionary cache information content: data dictionary objects
gets: The number of Object Request
getmisses: the number of times to read the data dictionary cache.
select parameter, gets, getmisses from v $ rowcache;

Data dictionary cache statistics:
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;

StatsPack report percent misses the best is relatively low.
<2%: Data dictionary object query (Pct SGA: the size of the buffer percentage of applications)
<15%: the data dictionary cache (Cache Usage)

Large Pool:
UGA best on the Large pool, do not put in the share pool.
Large Pool in memory usage:
IO server process:
Backup and recovery operations.
Shared server sessions in parallel query message
select * from v $ sgastat
Configuration parameters LARGE_POOL_SIZE.

UGA user connection to use:
select sum (value) | | 'bytes' "Total session mermory"
from v $ mystat, v $ statname
where name = 'session uga memory'
and v $ mystat.statistic # = v $ statname.statistic #;
UGA in the Oracle shared server users:
select sum (value) | | 'bytes' "Total session mermory"
from v $ sesstat, v $ statname
where name = 'session uga memory'
and v $ sesstat.statistic # = v $ statname.statistic #;
UGA's largest space:
select sum (value) | | 'bytes' "Total session mermory"
from v $ sesstat, v $ statname
where name = 'session uga memory max'
and v $ sesstat.statistic # = v $ statname.statistic #;