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
);

SHARED POOL 基礎知識

相關文章