Oracle-buffer cache过小导致SQL执行时间长

linesize 500 set termout off alter session set statistics_level=all; alter session set current_schema=xx; --这种方式可以对业务的SQL无需每个表对象前缀添加用户！ 但是还是无法与statistics_level 配合起来。
因此需要对执行SQL 添加hint
select  /*+ gather_plan_statistics */   xxx

select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));

 

对比执行计划，完全相同！
问题来了？ 什么情况下执行计划相同，但是执行的时间却大幅度提升呢？ Cache 内存? 

> select o.object_name,count(*) blocks
from dba_objects o,v$bh bh
where o.object_id=bh.objd and o.owner in (‘xx‘)
and o.object_name in(‘xx‘)
group by o.object_name;

通过在测试库执行两次相同的SQL 查询，耗时均需要45s ，然后生产环境执行SQL只需要13s;

在对比v$bh cache中的blocks的数量，

OBJECT_NAME BLOCKS
--------------------------
xx           1

生产环境

OBJECT_NAME BLOCKS
--------------------------
xx           4000

 为什么这个SQL涉及的对象，查询2次都还没有放到cache中？

 

memory_max_target              0
memory_target                  0
sga_max_size     564M   ！！！ 测试环境 sga 560m ???   sql查询的一个表，单表大小1G，内存都不够。 

既然明白了事情的原委，问题处理就很简单了！

alter system set memory_max_target=36g scope=spfile;
alter system set memory_target=36g scope=spfile;
alter system set sga_max_size=30g scope=spfile;

SYS> alter system checkpoint;
SYS> shutdown immediate;
SYS> startup

 

重启后，第一次执行45s, 第二次执行 19s 虽然比生产环境执行SQL 13s效率低，但是与最初相比，效率明显提升！

本次使用 memory auto自动管理方式，因此不需要明确指定 buffer cache的大小！   
sql执行效率提升一倍！ 可以说cache 内存读>> 效率优于物理读，oracle设计复杂的内存组件就是基于这个cache 优于物理读。 

 

Oracle-buffer cache过小导致SQL执行时间长

标签：时间   immediate   play   单表   pre   dba   targe   select   buffer