oracle性能调优之--Share Pool调整与优化(五)
shared_pool_size_factorsize_factor,
estd_lc_size,
estd_lc_memory_objectsobj_cnt,
estd_lc_time_saved_factorsav_factor
FROM v$shared_pool_advice; www.2cto.com
EST_SIZESIZE_FACTOR ESTD_LC_SIZE OBJ_CNT SAV_FACTOR
--------- ----------- ---------------------- ----------
640 .5556 642 54947 1
768 .6667 769 80736 1
896 .7778 896 101860 1
1024 .8889 1023 135536 1
1152 1 1150 167927 1
1280 1.1111 1277 200423 1
1408 1.2222 1404 234144 1
1536 1.3333 1535 257042 1
1664 1.4444 1662 270800 1
1792 1.5556 1789 282202 1
1920 1.6667 1914 294138 1
2048 1.7778 2040 306570 1
2176 1.8889 2169 317104 1
2304 2 2299 327659 1
十二、共享池调优工具
1.几个重要的性能视图
v$sgastat
v$librarycache
v$sql
v$sqlarea
v$sqltext
v$db_object_cache
2.几个重要参数
shared_pool_size
open_cursors
session_cached_cursors
cursor_space_for_time
cursor_sharing
shared_pool_reserved_size
3.查询视图获得相关信息
--查询执行次数小于5的SQL语句
scott@ORCL> select sql_text from v$sqlarea
2 where executions < 5 order by upper(sql_text);
--查询解析的次数
scott@ORCL> select sql_text,parse_calls,executions from v$sqlarea order by parse_calls;
对于那些相同的SQL语句，但不存在于Librarypool，可以查询视图v$sql_shared_cursor 来判断v$sql_shared_cursor www.2cto.com
为什么没有被共享，以及绑定变量的错误匹配等。
--查询特定对象获得句柄的命中率
select gethitratio
from v$librarycache
where namespace='SQL AREA';
--查询当前用户正在运行哪些SQL语句
select sql_text,users_executing,
executions,loads
from v$sqlarea
select * from v$sqltext
where sql_text like 'select * from scott.emp where %';
--收集表的统计信息
scott@ORCL> execute dbms_stats.gather_table_stats(- --注意此处-表示转义
> 'SCOTT','EMP');
PL/SQL procedure successfully completed.
--通过动态性能视图获得有关share pool size的建议
SELECT Shared_Pool_size_for_estimate AS pool_size
,shared_pool_size_factor AS factor
,estd_lc_size
,estd_lc_time_saved
FROM v$shared_pool_advice;
--通过视图v$sql_plan查看执行计划
SELECT operation www.2cto.com
,object_owner
,object_name
,COST
FROM v$sql_plan
ORDER BY hash_value;
--SQL语句与执行计划的对照
--v$sql中有一列为plan_hash_value 与v$sql_plan相互参照
SELECT a.operation
,object_owner
,object_name
,COST
,b.sql_text
FROM v$sql_plan a
JOIN v$sql b
ON a.plan_hash_value=b.plan_hash_value
WHERE a.object_owner = 'SCOTT'
ORDER BY a.hash_value;
作者 l106439814