Oracle 11g结果集缓存限制

有个不大的表数据量大于百万级别这是类似IP地址范围查找. 表的查询量很大.数实时生成,有时会被更新该表上的索引主键和表都被我KEEP进内存了。
查询速度基本保持在50-30毫秒之间.
该表的数据一天要查500万次以上.同时被重复查询的次数也蛮多子.鉴于这个原因所以向开发人员提出在SQL 增加提示 /*+ result_cache */结论是结果缓存通过表的依赖而失效,也就是说相应表发生了数据变化那么就重新从表获取.相比时间比普通45毫秒高很多达到945毫秒. 难道它不晓得从内存中获取吗？还是觉得表数据有变化,直接从硬盘上读取才是真！declareB1 VARCHAR2（20）;B2 NUMBER;ORG NUMBER（10）;S TIMESTAMP;E TIMESTAMP;R TIMESTAMP;begin B2:=12; B1:=668239581895;FOR I IN 1..100 LOOP S:=SYSTIMESTAMP;
SELECT /*+ result_cache */ USE_IP into IP FROM （SELECT A.USE_IP A.IP_TYPE FROM BACK_IP A WHEREA.IP_TYPE IN （3, 5, 9） ANDIP_LEGN = B2 ANDA.START_IP <= B1 ANDA.END_IP >= B1 ORDER BYA.DISP_DATE DESC, A.CREATE_TIME DESC）WHERE ROWNUM <= 1;E:=SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE（TO_CHAR（E-S））;DBMS_LOCK.SLEEP（10）;END LOOP;END ;测试结果不很理想波动性太大了.+00000000000:00:00.090374000+00000000000:00:00.102860000+00000000000:00:00.000237000+00000000000:00:00.000139000+00000000000:00:00.000135000+00000000000:00:00.000163000+00000000000:00:00.000170000+00000000000:00:00.000206000+00000000000:00:00.000173000+000000000 00:00:00.000171000+00000000000:00:00.000170000+00000000000:00:00.000139000+00000000000:00:00.000267000+00000000000:00:00.000171000+00000000000:00:00.000160000+00000000000:00:00.000180000+00000000000:00:00.000161000+00000000000:00:00.000183000+00000000000:00:00.000182000+00000000000:00:00.000139000+00000000000:00:00.000147000
+00000000000:00:00.000160000+00000000000:00:00.000212000+00000000000:00:00.000353000
+00000000000:00:00.094454000更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址