Oracle学习之shared pool及sga的大小的设置

1、Oracle若没有实现SQL语句共享，则shared pool不应该设置为较大的值

2、查看执行计划

select sql_id,sql_text from v$sql where sql_text like '%count(*) from dba_objects%';--生成的sql语句id

select * from table(dbms_xplan.display_cursor('07hpk6hpb7pp8'));--上边语句生成的sql id：07hpk6hpb7pp8

3、在Oracle10g中允许有多个sub shared pool，可以设置大于1G的shared pool

4、

设置shared pool的大小
SELECT
shared_pool_size_for_estimate "SP",
estd_lc_size "EL",
estd_lc_memory_objects "ELM",
estd_lc_time_saved "ELT", estd_lc_time_saved_factor "ELTS",
estd_lc_memory_object_hits "ELMO"
FROM v$shared_pool_advice;

SELECT 'Shared Pool' component,shared_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_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);