3-4tuning the shared pool
library cache
data dictionary cache
User global area--一般放在large pool
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过多
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 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
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
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
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
v$sql_shared_cursor
相同用户登录,减少数据字典的负荷
DDL要在业务低峰期做----非常重要
Optimally,free memory should be as low as possible,
without causing any reloads on the system
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
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
碎片化
保留池--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
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<15%
改变太平凡引起的getmisses增加
col parameter format a21
col pct_succ_gets format 999.9
col updates format 999,999,999
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;
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博客 ” ,链接:http://blog.itpub.net/27573546/viewspace-757093/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27573546/viewspace-757093/