3-4tuning the shared pool

library cache
data dictionary cache
User global area--一般放在large pool

shared pool:
shared_pool_size

SGA_TARGET--10G开始提供

library cache:

LRU算法
used to prevent statements reparsing---适合oltp系统
used to store SQL statements and PL/SQL blocks that are to be shared by users
reports error ORA-04031 if the shared pool is out of free memory
--扩内存，reparsing过多

主要就是减少parsing

show parameter share

select * from v$sgastat

SQL sharing Criteria:
hash值得产生---根据字符组合
cursor_sharing exact similar force
共享sql时，不会再做表分析

select * from v$sysstat where statistic#=233;

能否看到已经解析的SQL列表????

绑定变量
select * from employees where id=:id;

动态语句？？？？？---有什么作用
execute immediate 'insert into m values(:x)' using i;

execute immediate 'insert into m vales('||i||')';

插入1万条记录，后者时间是前者的五倍

ddl操作，导致SQL要reparse

v$librarycache非常重要

Gets:(Parse) The number of lookups for objects of the
namespace
Pins:(Execution) The number of reads or executions of the
objects of the namespace
Reloads:(Parse)The number of library cache misses on the
execution step,thereby causing an implicit reparsing
of the SQL statement

Invalidations:(Parse) If an object is modified then
all explain plans that reference the object are maked
invalid and must be parsed again

dbms_stats.gather_table_stats(username,tablename)也是ddl操作
会导致v$librarycache的invalidations数增加,进行硬解析

select namespace,gets,pins,pinhits,reloads,invalidations from v$librarycache;

shared pool 调试工具

v$sgastat --shared pool free memory
v$librarycache----gethitratio,gets,gethits,reloads,invalidations(ddl引起)
reloads 小于1%;考虑累计情况，要定时执行求差;LRU清算操作引起
sum(reloads)/sum(pins)--execution,太高说明shared pool太小
v$sql
v$sqlarea---executions;parse_calls--可能是指硬解析
users_executing,loads
v$sqltext
v$db_object_cache----objects include table,index等信息
参数
shared_pool_size
open_cursors
session_cached_cursors
cursor_space_for_time
cursor_sharing
shared_pool_reserved_size

result cache 10g和11g都是支持的？？？？？？？？？

减少硬解析：

避免使用动态sql语句

bbk2157

优化器不要随意改

多用存储过程

制定开发sql开发规范，大小写，空格
v$sql_shared_cursor

相同用户登录，减少数据字典的负荷

DDL要在业务低峰期做----非常重要

Optimally,free memory should be as low as possible,
without causing any reloads on the system

v$shared_pool_advice

SQL state和sql执行计划LRU淘汰

v$sql_plan ---cost
select operation,object_owner,object_name,cost
from v$sql_plan

通过hash_value和v$sql的plan_hash_value关联

library cache占用内存情况
select sum(sharable_mem) from v$db_object_cache
非SQL占用内存情况
select sum(sharable_mem)
from v$sqlarea where executions>5
SQL占用内存情况

bbk2173
碎片化
保留池--reserved pool

show parameter shared_pool_resersed_size 5-10%

给大块SQL用

v$shared_pool_reserved

free_space,requests,request_misses,request_failures

dbms_shared_pool.aborted_request_threshold

Tuning SP Reserved Space
太小的时候，request_failures会变大
shared_pool_size太小的时候，也会出现request_failure多

shared_pool_reserved_size is too large

request_misses

bbk2174

Keeping Large Objects

刚启动时执行

Execute dbms_shared_pool.keep('package_name');

select * from v$db_object_cache

alter system flush shared_pool;
这个操作对keep部分无效

Other Parameters
OPEN_CURSORS
CURSOR_SPACE_FOR_TIME
SESSION_CACHED_CURSOR
CURSOR_SHARING

Tuning the DD Cache
v$rowcache
gets,getmisses，parameter

V$fixed_table

Statspack
getmisses<15%

改变太平凡引起的getmisses增加

col parameter format a21
col pct_succ_gets format 999.9
col updates format 999,999,999

select parameter,sum(gets),
sum(getmisses),100*sum(gets-getmisses)/sum(gets) pct_succ_gets
,sum(modification) updates
from v$rowcache
where gets>0
group by parameter;

If there are too many cache missses,increase the
SHARED_POOL_SIZE parameter

UGA专有模式下，放在PGA里

LARGE POOL
--不是LRU算法管理，只有一次
用于：
IO server processes:DBWR_IO_SLAVES--模拟异步IO
Backup and restore operations
Session memory for the shared servers
Parallel query messaging
Used to avoid performance overhead caused by
shrinking the shared SQL cache

LARGE_POOL_SIZE来设定

ITPub博客

3-4tuning the shared pool

最新文章