SHARED POOL 基础知识

1. 查看共享池组成
select * from v$sgastat
where pool='shared pool' and name in ('free memory','library cache','row cache');

2. 刷新(清空)共享池
# 此后会发生大量硬解析
alter system flush shared_pool;

3. 查看软/硬解析的具体情况
select name,value from v$sysstat where name like 'parse%';

4. 查看共享池中缓存的 SQL 语句
select sql_id,sql_text,executions from v$sql;

5. 查看没有被共享的 SQL 语句
# 在 v$sql 查找执行次数较小的 SQL 语句, 观察这些 SQL 语句是否是经常被执行

select sql_fulltext from v$sql where executions=1 and sql_text like '%hello%';
select sql_fulltext from v$sql where executions=1 order by sql_text;

6. 查看共享池中 trunk 总数
select count(*) from x$ksmsp;

7. 查看命中率
7-1. 软解析命中率
select sum(pinhits)/sum(pins)*100 from v$librarycache;

7-2. 数据字典命中率
select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets)
from v$rowcache where gets>0;

8. 查看 SGA 中动态分配的共享池大小
select component,current_size from V$sga_dynamic_components;

9. 修改共享池大小
# 数值必须大于 7 中动态分配的大小才有效

alter system set shared_pool_size=150M scope=both;

10. 根据 sql_id 查看执行计划
select * from table(dbms_xplan.display_cursor('g4pkmrqrgxg3b'));

11. 大对象
11-1. 查看内存中的大对象
select * from v$db_object_cache
where sharable_mem > 10000 and
(type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type='PROCEDURE') and
kept = 'NO';

11-2. 保留大对象
# 在内存中保留大对象, 使其不容易被置换出内存
@?/rdbms/admin/dbmspool.sql
执行 dbms_shared_pool.keep('对象名');

11-3. 查看大对象保留区
show parameter shared_pool_reserved_size
select request_misses from v$shared_pool_reserved;

12. 查看最浪费内存的前10个 SQL 占所有语句的比例, 建议控制在5%以内
select sum(pct_bufgets) "Percent"
from
    (
        select
            rank() over (order by buffer_gets desc) as rank_bufgets,
            to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets
        from v$sqlarea
    )
where rank_bufgets < 11;

13. 查看消耗物理 IO 资源最大的的 SQL 语句
select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads asc;

14. 使用共享池大小设置指导
select
    shared_pool_size_for_estimate,
    estd_lc_size,estd_lc_memory_objects,
    estd_lc_time_saved,
    estd_lc_time_saved_factor,
    estd_lc_memory_object_hits
from v$shared_pool_advice;

select
    'Shared Pool' component,
    shared_pool_size_for_estimate,
    estd_lc_time_saved_factor,
    case
        when current_parse_time_elapsed_s + adjustment_s < 0
        then 0
        else current_parse_time_elapsed_s + adjustment_s
    end response_time
from
(
    select
        shared_pool_size_for_estimate,
        shared_pool_size_factor,
        estd_lc_time_saved_factor,
        a.estd_lc_time_saved,
        e.value/100 current_parse_time_elapsed_s,
        c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
    from
        v$shared_pool_advice a,
        (select * from v$sysstat where name = 'parse time elapsed') e,
        (select estd_lc_time_saved from v$shared_pool_advice where shared_pool_size_factor = 1) c
);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22558114/viewspace-1122587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22558114/viewspace-1122587/