分页查询：最后一页显示极其缓慢，是倒数第二页执行效率的10分之一，请高人解决

Question

在做分页查询时，进行到最后一页时用时大概20秒左右，其他页只有0.3秒左右，下面附上语句，请高人帮忙看看是什么问题？select uidused,pid,ekuid,s1sid,flag from(select rownum rnm ,a.* from(select * from tab_test
where 1=1 and flag!=5 and systemtime>=to_date('2008-3-11 11:8:15','yyyy-mm-dd hh24:mi:ss') and ststem<=to_date('2008-6-11 11:8:15','yyyy-mm-dd hh24:mi:ss') order by uidused desc) a where rownum<=29010 )where rnm>=28996//该语句是执行查询最后一页时打印出的SQL语句。下面是执行倒数第二页时的查询语句：
select uidused,pid,ekuid,s1sid,flag from(select rownum rnm ,a.* from(select * from tab_test
where 1=1 and flag!=5 and systemtime>=to_date('2008-3-11 11:8:15','yyyy-mm-dd hh24:mi:ss') and ststem<=to_date('2008-6-11 11:8:15','yyyy-mm-dd hh24:mi:ss') order by uidused desc) a where rownum<=28995 )where rnm=>28981
附录：每页15条数据，数据库中共有45万条数据。表tab_test的主键为UIDUSED，在PID上也建有索引。只有执行最后一页查询时才比较缓慢，其他页查询非常快，请高手不吝赐教

Answer 1

这个问题在oracle 8i 和oracle 10g上都会出现，推测跟数据库环境关系不是很大

Answer 2

8i跟10g区别大了,
8i是rbo,10g是cbo,策略不一样你是在应用里测试的反应时间,
还是把sql语句放在sql/plus下执行得出的时间?要是在应用里测量的,那么可能就是编码的问题
如果是在sql/plus下执行的,你可以把统计信息贴出来让大家看看

Answer 3

在PLSQL里执行语句看到的时间，等下我在学着弄，弄好了把统计信息贴上来，谢谢大家！

Answer 4

看你找得挺麻烦的,我顺手给你贴两行吧
SQL> set autot trace statSQL> execute dbms_stats.gather_table_stats('SCOTT','T2') ;PL/SQL 过程已成功完成。SQL> execute dbms_stats.gather_INDEX_stats('SCOTT','I2');PL/SQL 过程已成功完成。SQL> select count(*) from T2;  COUNT(*)
----------
    229376
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=308 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=308 Card=23226
          9)统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1397  consistent gets
       1379  physical reads
       1052  redo size
        397  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
差不多就是这个样子, I2是T2上的索引
搜集了先,再看执行计划

Answer 5

你是用UIDUSED
order by 的话，可以把rownum <=29010放在最里面的where里，即order by 的前面此方法仅对用主键排序的有效
会比你放在外层的要快的假设你最里层的where 过滤后有40万纪录，这两种写法的差别rownum <=29010 直接出来29010条，然后再把<28996的过滤掉
然后放在第二层的话，就会最里层仍然出来40万条，然后再过滤掉29010条之后的，然后过滤之前的

Answer 6

说的有一定道理但10并不是说一定采用cbo的，默认是choose模式，如果你的表没做过分析，还是走rbo的，做过分析了，就会走cbo
而走cbo的话，就会和数据分布有关系了

Answer 7

我不知道10g别的版本是什么样子的，但是我这个版本默认是all_rows，all_rows是cbo的策略
网上很多人都记错了SQL> select * from product_component_version;
PRODUCT                                            VERSION      
-------------------------------------------------- -----------
NLSRTL                                             10.1.0.2.0   
Oracle Database 10g Enterprise Edition             10.1.0.2.0   
PL/SQL                                             10.1.0.2.0   
TNS for 32-bit Windows:                            10.1.0.2.0   SQL> show parameter optimizer_modeNAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
optimizer_mode                       string      ALL_ROWS

Answer 8

今天来了继续测试这个问题，奇怪的是在两条语句不做任何改变的情况下，执行效率相近，均为2秒左右！最后一页查询快了，而其他页查询变慢了，很奇怪的原因。按照HEBO2005老兄的办法，改了语句，执行效率未变，LPC大哥，我的数据库版本是10.2.0.1.0的，也是ALL-ROWS策略。为什么没做改变而两条语句的执行效率都变了呢？

Answer 9

执行效率除了跟语句的写法有关系外,跟数据库的状态(包括参数的设置和数据在硬盘上的组织形式,以及操作系统硬件网络等)也有很大关系
Oracle是能够灵活调整执行计划的all_rows策略在当时数据库的环境下, 会尽量选择占用资源较少的执行方案
而改用first_row策略,会选用返回数据较快的方案,
所以一切都要看了执行计划后才方便做具体的调优, 但是你一直没贴上来
靠猜测是不可靠的啊你昨天做测试时收集统计信息了没? 今天呢?
另外你可以尝试使用下first_rows看看性能如何

Answer 10

在会话级临时更改优化策略, 关闭会话后更改失效, 不改变系统设置SQL> alter session set optimizer_mode = 'FIRST_ROWS'
  2  /会话已更改。SQL> show parameter optimizer_modeNAME                                 TYPE            VALUE
------------------------------------ --------------- -------------
optimizer_mode                       string          FIRST_ROWS

Answer 11

能上网的时候不能测试，能测试的时候不能上网，因为其他工作的耽误，所以一直没做统计，请教一下LPC大哥，从3万条数据里找出15条用时2秒，这效率是不是有点太低了啊？谢谢！争取下午把统计信息贴上来！！

Answer 12

LPC大哥，下面是我整出来得统计信息，怎么看呢？非常感谢！！！ 最后一页查询得统计信息：
 
 统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10159  consistent gets
       3831  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
          
          
倒数第二页统计信息：
统计信息
----------------------------------------------------------
        188  recursive calls
          0  db block gets
      10207  consistent gets
       1517  physical reads
          0  redo size
       2105  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         15  rows processed第一页统计信息：
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       2105  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed         
         
优化后语句：select uidused,pid,ekuid,s1sid,flag from(select rownum rnm ,uidused,pid,ekuid,s1sid,flag from tab_test 
where 1=1 and flag!=5 and systemtime>=to_date('2008-3-11 11:8:15','yyyy-mm-dd hh24:mi:ss') and systemtime <=to_date('2008-6-11 11:8:15','yyyy-mm-dd hh24:mi:ss')
 and rownum <=29010 order by uidused desc)where rnm>=28996
该优化主要是将rownum <=29010移到了order by前面，而且去掉了用*查询和一个子句查询最后一页查询       
         
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10159  consistent gets
       1198  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

Answer 13

我的分析：
      1：频繁的数据库查询导致查询性能下降。数据库中共有近50万条数据，本论题的条件拣选出28998条记录，每次查询15条，连续数据库查询1934次！
      2：使用优化后的语句，见我上条恢复中最后所写语句，系统刚开始查时效率非常高，但查出4000多条时，性能急剧下降，每页（15条）查询平均耗时2秒！到最后一页仍然用时20多秒，可见优化的效果有限。疑惑：     1：在PLSQL里执行最后一页数据的查询语句，耗时2秒左右，但在JAVA程序里执行到最后一页数据查询时却耗时20多秒？如果是因为数据库性能下降，那倒数第二页才用两秒，为什么最后一页就用了十倍于前一页的时间呢？与PLSQL里执行的效果也相差甚远啊！
       
     

Answer 14

我用优化后的语句在程序里执行，其实也就是连续查询，到最后一页时仍然执行了20多秒，导致客户端超时。比较疑惑为什么在PLSQL里执行只用2秒，在程序里最后执行时却用了20多秒？

Answer 15

不过照道理你最后一页和前页只差15条纪录，应该不会相差很大的
你这15条纪录是否不太一样
最好能贴一下你的表结构上次看过一篇贴子，是同样的语句出来的时间相差很大
结果后来有人分析出来，是数据结构的问题
因为有blob字段，然后又是用select * 去查的，所以同样的语句查不同数据行，会时间上相差很大，主要就是物理读取时间上主要就是字段里的内容读取很费时间

Answer 16

另外一个要看下你的
pga的设置了，是不否设置过小，引起连续查询后，性能急剧下降

Answer 17

表结构如下，感觉很简单，表结构不应该有什么问题吧CREATE TABLE tab_test(
       uidused              VARCHAR2(20) NOT NULL,
       pid                  VARCHAR2(20) NOT NULL,
       ekuid                VARCHAR2(40) NULL,
       s1sid                VARCHAR2(32) NULL,
       flag                 INTEGER NULL,
   
);
ALTER TABLE tab_test(  ADD  PRIMARY KEY (uidused);
CREATE INDEX tab_test_idx1( ON tab_test(pid); 

Answer 18

确实相当简单，应该和上次看到的那贴情况不一样
那只能说是瞎猜了，看看oracle的设置是不是会造成大量数据查询后性能下降，SGA，PGA的设置看下下，不过这个我基本不懂，要找个有经验的DBA来看了

Answer 19

呵呵，谢谢hebo2005 老兄，希望明天能解决这个问题！！！

Answer 20

不好意思, 敲掉了一个东东,呵呵,你只出了统计信息,没有出执行计划SQL>  set autotrace on expl stat
SQL>  select count(*) from scott.t1;  COUNT(*)
----------
    229376
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=301 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=301 Card=22962
          2)统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1355  consistent gets
       1350  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed单纯从你的统计信息来看,可以发现一个问题,你的recursive calls没有为0情况,有一处甚至达到了188,
那就是说,每次执行SQL时都在做硬解析
虽然你没有加上执行时间,但我可以断定,188的这一处肯定是不快的而且我怀疑,你根本不容易确定查询时哪一页时最慢,
因为你不知道shared_pool什么时候被占满了,满了就会有执行LRU策略出列等操作
当然简单加大shared_pool_size是不可取也是危险的,较大的size会增加oracle的管理难度可以尝试的解决办法是使用绑定变量,当一句sql连续执行两次以上,
它就会在shared_pool_size的队列中占据一个较好的位置,再次执行就会直接从shared_pool中拿来用
查看参数设置的方法是
SQL>  show parameter shared_pool_size当然这只是初步的看法,我还需要看下执行计划,我上面的推论也不足以解释所有的疑惑
建议你先使用绑定变量改写sql,再看效果,还不满意的话再贴执行计划和统计信息

Answer 21

首先感谢LPC老兄的持续关注，对于哪一页查询缓慢是很容易确定的，在程序里打印了执行时间，倒数第二页是2秒左右，最后一页是22秒左右。我看了下shared_pool_size，如下所示:SQL> show parameter shared_pool_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0

Answer 22

差点忘了,你是10g,
10g的shared_pool_size的大小是自动管理的
如果它分配的空间用完了,会自动缩小缓冲区缓存来增大它你暂且不管这些了,先在你的sql语句里使用绑定变量,避免硬解析,这是必须要做的事情啊

Answer 23

有时会有最后一页快了,其它页慢了的现象出现,不是吗?还有,你的查询是从第一页一直查到最后一页吗? 有没有试过从最后一页查到第一页?
有时间做一下, 顺便贴个执行计划我建议你单纯在sql/plus里测试sql执行时间,这样可以避免考虑别的因素的影响

Answer 24

在JAVA程序里该怎么写呢？有没有例句？谢谢LPC老兄

Answer 25

单纯在SQLPLUS里执行，倒数第二页和最后一页均执行时间2秒左右，第一页只执行0.04秒左右。我的程序是一直从第一页查询到最后一页的，所以觉得连续查询对数据库性能影响比较大。我被这个问题已经搞的睡不好了，看似很简单的一个问题，弄了两天也没搞定！看来水平急待提高啊！！

Answer 26

第一页统计信息： 统计信息 
---------------------------------------------------------- 
          1  recursive calls 
          0  db block gets 
          6  consistent gets 
          0  physical reads 
          0  redo size 
      2105  bytes sent via SQL*Net to client 
        385  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
        15  rows processed      你第一页的物理读取是0,证明这个结果集被缓存了,而且肯定是软解析,它快是正常的你把数据库关闭,重新装载,只执行最后一页的查询,反复执行多次(在sql/plus里面),看下它是不是也会很快?  

Answer 27

我在程序里将普通的Statement改为PreparedStatement,整体效率大幅提高，倒数第二页执行时间不足一秒，但最后一页用时仍在20秒以上！！！无语了

Answer 28

这个如果是重启Oracle后在sql/plus里多次执行最后一页sql的结果,
应该可以排除数据库端的问题了你写的sql排列格式太不规整了, 我视力不好看不太清没帮你整, hebo2005的建议是很好的, 你可以根据实际情况调整下
外层尽量先把大量不用的数据过滤掉,里层再做小循环解决不了的话加200分让版主帮忙转到高级技术区去看看,也听听别人意见

Answer 29

原第一页
SELECT uidused, pid, ekuid, s1sid, flag
  FROM (SELECT ROWNUM rnm, a.*
          FROM (SELECT   *
                    FROM tab_test
                   WHERE 1 = 1
                     AND flag != 5
                     AND systemtime >=
                            TO_DATE ('2008-3-11 11:8:15',
                                     'yyyy-mm-dd hh24:mi:ss'
                                    )
                     AND ststem <=
                            TO_DATE ('2008-6-11 11:8:15',
                                     'yyyy-mm-dd hh24:mi:ss'
                                    )
                ORDER BY uidused DESC) a
         WHERE ROWNUM <= 29010)
 WHERE rnm >= 28996倒数第二页
SELECT uidused, pid, ekuid, s1sid, flag
  FROM (SELECT ROWNUM rnm, a.*
          FROM (SELECT   *
                    FROM tab_test
                   WHERE 1 = 1
                     AND flag != 5
                     AND systemtime >=
                            TO_DATE ('2008-3-11 11:8:15',
                                     'yyyy-mm-dd hh24:mi:ss'
                                    )
                     AND ststem <=
                            TO_DATE ('2008-6-11 11:8:15',
                                     'yyyy-mm-dd hh24:mi:ss'
                                    )
                ORDER BY uidused DESC) a
         WHERE ROWNUM <= 28995)
 WHERE rnm >= 28981
优化后的
SELECT uidused, pid, ekuid, s1sid, flag
  FROM (SELECT   ROWNUM rnm, uidused, pid, ekuid, s1sid, flag
            FROM tab_test
           WHERE 1 = 1
             AND flag != 5
             AND systemtime >=
                        TO_DATE ('2008-3-11 11:8:15', 'yyyy-mm-dd hh24:mi:ss')
             AND systemtime <=
                        TO_DATE ('2008-6-11 11:8:15', 'yyyy-mm-dd hh24:mi:ss')
             AND ROWNUM <= 29010
        ORDER BY uidused DESC)
 WHERE rnm >= 28996

Answer 30

你点到Oracle那个版主的名字bzszp,
然后给他发短信或者及时消息 :
fuck you~~~~
fuck you~~~~
fuck you~~~~
fuck you~~~~
fuck you~~~~他很快就会给你转过去了 (:
fuck you~~~~ 是我联系他的暗号