分页和buffer_cache关系
今天老大说分页时候会不会cache select语句的信息，然后再到下一页会不会快点，我说不会，我做个试验给他看看
首先清理内存中信息（重启数据库或者手动情况都一样）

alter system flush buffer_cache;

做一个10046时间，观察trace内容

set linesize 200
set timing on
alter session set tracefile_identifier='fenyetest0';
alter session set events '10046 trace name context forever,level 4';
variable start_num  number;
variable end_num number;

--1. 10000-2000数据
exec :start_num := 10000;
exec :end_num := 20000;

select *
       from (
             select rownum rn,t.*
             from
             (select  OPERATE_TIME,SERVER_INFO ,ACCESS_IP ,USERID from bi_logfile where    OPERATE_TIME>=(sysdate-10)) t
             where rownum<:end_num)
       where rn >:start_num;  

--查看buffer_cache中的内容
SQL> SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER        ='JSCNBI'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*);   

OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
BI_LOGFILE_IDX1                                       128
BI_LOGFILE                                           1160

--2. 20000-3000数据
exec :start_num := 20000;
exec :end_num := 30000;
select *
       from (
             select rownum rn,t.*
             from
             (select  OPERATE_TIME,SERVER_INFO ,ACCESS_IP ,USERID from bi_logfile where    OPERATE_TIME>=(sysdate-10)) t
             where rownum<:end_num)
       where rn >:start_num;  

--查看buffer_cache中的内容
SQL> SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER        ='JSCNBI'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*);     2    3    4    5    6

OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
BI_LOGFILE_IDX1                                       197
BI_LOGFILE                                           1665

--3. 30000-40000数据
exec :start_num := 30000;
exec :end_num := 40000;
select *
       from (
             select rownum rn,t.*
             from
             (select  OPERATE_TIME,SERVER_INFO ,ACCESS_IP ,USERID from bi_logfile where    OPERATE_TIME>=(sysdate-10)) t
             where rownum<:end_num)
       where rn >:start_num;  

--查看buffer_cache中的内容
SQL> SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER        ='JSCNBI'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*);  2    3    4    5    6

OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
BI_LOGFILE_IDX1                                       222
BI_LOGFILE                                           1983


alter session set events '10046 trace name context off';

格式化trace内容，格式的时候不合并相同的sql，也不查看sys sql
[oracle@DB-87 udump]$ tkprof jscnbi_ora_27002_fenyetest0.trc  fenyetest0.sql sys=no aggregate=no

格式化以后的结果如下：

TKPROF: Release 10.2.0.4.0 - Production on Fri Jul 13 13:57:48 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: jscnbi_ora_27002_fenyetest0.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
--------------------------------------------

*** SESSION ID:(309.11) 2012-07-13 13:56:17.864

*************************************************