求以下SQL分析

奇怪的SQL：
有测试库TEST和正式库PROD，正式库PROD每天将前一天完备的数据恢复到TEST（两库数据量差不多，但PROD性能比TEST好很多）
在PLSQL Developer测试SQL1如下
SELECT max(sd.dop_id)
FROM dop_order sd,dop_order_operation ssd
WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
AND   sd.part_no LIKE nvl('&零件号','%')
AND   sd.configuration_id LIKE nvl('&配置号','%')
在TEST库输入配置号934415进行测试(5秒钟左右)，速度神奇的快，但在PROD正式数据库输入配置号934415
速度神奇的慢(412.7秒钟左右)，这是为什么，如何解悉？dop_order表数量1199600, dop_order_operation表数量1752408
另外我通过SQL2
SELECT max(sd.dop_id)
FROM dop_order sd,dop_order_operation ssd
WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
AND   sd.part_no LIKE nvl('&零件号','%')
AND   sd.configuration_id LIKE nvl('&配置号','%')
AND   ssd.work_center_no Like '%'
测试在TEST(5秒钟左右)和PROD(1.829秒钟左右)运行都很快，可以说PROD数据解悉dop_order_operation ssd
然后进行少量数量筛选吗？那如何分析两个SQL？
我对PROD经过各时间段测试：
dop_order sd和dop_order_operation 即使没有用户使用这两个表(读写很空闲了)这一个SQL1还是很慢，即使在忙时SQL2性能查询也很快。
故排除PROD里读写很频繁造成原因.
从SQL程序上分析PORD/TEST库执行计划一样，只有TEST库服务器性能和例程设置、buffer cache等不一样，
我现在认为是ORACEL自行根据相关参数比如buffer cache等优化产生的效果,一样的程序可能在性能差的机器运行得更好。我另一同事分析如下
ORACLE对Buffer Cache的分配是以稳定为第一考虑原则的，而且绝大多数时间内数值在上下浮动。比如在10：00这个时间段
上ORACLE 的Buffer Cache为600M，此时刚好满足系统的需求。
当在10:10分时用户执行上述的SQL，如果ORACLE分配的Buffer Cache的命中立马下降很多那么我们
可以认定它分配的600M已经无法满足高速的查询了，在这个时候ORACLE通过它内部的
运算后再次动态改变了Buffer Cache的大小来满足目前的查询需要，那么执行这段代码的效率就会很低了。
同样的事情现在一天发生个几百几千次也不奇怪的。
这个时候你会说为什么不指定Buffer Cache的大小，但事情远没这么简单的。
RACLE自动管理内存还包括许多的参数Library Cache与PGA等。
我们自己去设定各值大小还不一定比现在ORACLE自己做要好。我认同Buffer cache ，但不认同这种分配,为什么prod没有人访问时SQL1还是慢?为什么不能一次分配好cache给SQL1？
与其比较SQL2？
请教各位兄台解悉...

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

把SQL1再测试库TEST和正式库PROD上的执行计划贴一下
列出执行计划和统计信息需要比较同一个SQL在两台服务器上执行的时候cpu占用时间，逻辑读和物理读的次数多少等等相关信息，再确定问题原因
TEST库执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 耗费=6 基数=1 字节=32 IO 耗费=6 CPU 耗费=44929
SORT AGGREGATE                 基数=1 字节=32
NESTED LOOPS 耗费=6 基数=1 字节=32 IO 耗费=6 CPU 耗费=44929
TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_TAB费=4 基数=1 字节=23IO 耗费=4 CPU 耗费=29636
  INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_1_IX 耗费=3 基数=1 IO 耗费=3 CPU 耗费=21564
   INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_1_UIX 耗费=2 基数=1 字节=9 IO 耗费=2 CPU 耗费=15293PROD执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 耗费=2 基数=1 字节=32 IO 耗费=2 CPU 耗费=4493
SORT AGGREGATE 基数=1 字节=32
  NESTED LOOPS          耗费=2 基数=1 字节=32 IO 耗费=2 CPU 耗费=4493
   TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_TAB 耗费=1 基数=1 字节=23 IO 耗费=1 CPU 耗费=2964
    INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_1_IX 耗费=1 基数=1 IO 耗费=1 CPU 耗费=2156
   INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_1_UIX 耗费=1 基数=1 字节=9 IO 耗费=1 CPU 耗费=1529这是SQL1的执行计划,但在TEST较快,PROD慢得要死
SQL2解释计划:
PROD库
SELECT STATEMENT, GOAL = ALL_ROWS 耗费=2 基数=1 字节=38 IO 耗费=2 CPU 耗费=5279
SORT AGGREGATE 基数=1 字节=38
  TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_TAB 耗费=1 基数=1 字节=15 IO 耗费=1 CPU 耗费=2315
   NESTED LOOPS 耗费=2 基数=1 字节=38 IO 耗费=2 CPU 耗费=5279
    TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_TAB 耗费=1 基数=1 字节=23 IO 耗费=1 CPU 耗费=2964
     INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_1_IX 耗费=1 基数=1 IO 耗费=1 CPU 耗费=2156
    INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_1_UIX 耗费=1 基数=1 IO 耗费=1 CPU 耗费=1529test库
SELECT STATEMENT, GOAL = ALL_ROWS 耗费=7 基数=1 字节=38 IO 耗费=7 CPU 耗费=52790
SORT AGGREGATE 基数=1 字节=38
  TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_TAB 耗费=3 基数=1 字节=15 IO 耗费=3 CPU 耗费=23154
   NESTED LOOPS 耗费=7 基数=1 字节=38 IO 耗费=7 CPU 耗费=52790
    TABLE ACCESS BY INDEX ROWID 对象所有者=IFSAPP 对象名称=DOP_ORDER_TAB 耗费=4 基数=1 字节=23 IO 耗费=4 CPU 耗费=29636
     INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_1_IX 耗费=3 基数=1 IO 耗费=3 CPU 耗费=21564
    INDEX RANGE SCAN 对象所有者=IFSAPP 对象名称=DOP_ORDER_OPERATION_1_UIX 耗费=2 基数=1 IO 耗费=2 CPU 耗费=15293
现在主要是不明SQL1在TEST库与PROD库区别为什么这么大
我比较认同你的同事的看法,因为在test库运行时可能没有别的查询在运行,缓冲占用很小,所以查询快;
而你正式运行库可能还在运行别的查询或其他事务,被占用的缓冲已经比较大了,而你的SQL1可能查询需要的缓冲又比较多,所以查询就慢;
导入后要对test库中数据进行统计分析,用DBMS_STATS.GATHER_SCHEMA_STATS(user)
那我数据库中有几千张表，当然报表也很多，而其它报表也正常。另外我简单修改成SQL2后，执行解释就不一样了，但速度很快，且我在PROD几乎没人的时候使用(session很少)测试SQL1，还是慢...这能说明这个原因吗？
你用这句话把PROD库上的dop_order表， dop_order_operation表的信息重新统计一下，然后再测；
EXEC dbms_stats.gather_table_stats('你的用户名','表名');
强烈要求统计信息的比对，例如SQL> set autot traceonlySQL> select sysdate from dual;Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953                               -----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets             0  consistent gets
          0  physical reads
          0  redo size
        415  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)
          1  rows processedSQL>
不好意思,上班时间不准上网...
SQL1 test库
SQL> set autot traceonly;
SQL> SELECT max(sd.dop_id)
  2  FROM dop_order sd,dop_order_operation ssd
  3  WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
  4  And sd.configuration_id='934415';
执行计划
------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation           | Name                      | Rows  | Bytes |%CPU)|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |                           |     1 |    22 |
  (2)||   1 |  SORT AGGREGATE     |                           |     1 |    22 |
     ||   2 |   NESTED LOOPS      |                           |   233 |  5126 |
  (2)||   3 |    TABLE ACCESS FULL| DOP_ORDER_TAB             |   159 |  2067 |
  (2)||   4 |    INDEX RANGE SCAN | DOP_ORDER_OPERATION_1_UIX |     1 |     9 |
  (0)|--------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      31277  consistent gets
      31198  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>
prodC:\Documents and Settings\rocky.WINBO4X4>sqlplus ifsapp/pass2009@prodSQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 11 18:42:44 2009Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining optionsSQL> set autot traceonly;
SQL> SELECT max(sd.dop_id)
  2  FROM dop_order sd,dop_order_operation ssd
  3  WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
  4  And sd.configuration_id='934415';
执行计划
---------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)|-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                           |     1 |    22 |  3221   (7)||   1 |  SORT AGGREGATE              |                           |     1 |    22 |            ||   2 |   TABLE ACCESS BY INDEX ROWID| DOP_ORDER_TAB             |     1 |    13 |     1   (0)||   3 |    NESTED LOOPS              |                           |   130 |  2860 |  3221   (7)||   4 |     INDEX FULL SCAN          | DOP_ORDER_OPERATION_1_UIX |  1752K|    15M|   681   (1)||   5 |     INDEX RANGE SCAN         | DOP_ORDER_PK              |     1 |
|     1   (0)|-----------------------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
    3582573  consistent gets
      20669  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>
test库5.313秒而prod  库107.438秒
SQL2在PROD如下,执行约2.39s
SQL> SELECT max(sd.dop_id)
  2  FROM dop_order sd,dop_order_operation ssd
  3  WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
  4  And sd.configuration_id='934415'
  5  AND  ssd.work_center_no Like '%' ;
执行计划
--------------------------------------------------------------------------------| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT         |                   |     1 |    28 |  4371   (1)||   1 |  SORT AGGREGATE          |                    |     1 |    28 |            ||   2 |   TABLE ACCESS BY INDEX ROWID| DOP_ORDER_OPERATION_TAB   |     1 |    15 |     1   (0)||   3 |    NESTED LOOPS          |                      |   130 |  3640 |  4371   (1)||   4 |     VIEW                 | index$_join$_004          |    89 |  1157 |  4344   (1)||   5 |      HASH JOIN           |                           |       | |            ||   6 |       INDEX FAST FULL SCAN   | DOP_ORDER_PK           |    89 |  1157 |   501   (1)||   7 |       INDEX FAST FULL SCAN   | DOP_ORDER_1_IX          |    89 |  1157 |   595   (1)||   8 |     INDEX RANGE SCAN         | DOP_ORDER_OPERATION_1_UIX |     1 | |     1   (0)|
Note
-----
   - 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
       9104  consistent gets
          0  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>
对SQL1的结果能够看出：
测试环境上：|  3 |    TABLE ACCESS FULL| DOP_ORDER_TAB            |  159 |  2067 |  (2)|
|  4 |    INDEX RANGE SCAN | DOP_ORDER_OPERATION_1_UIX |    1 |    9 |  (0)|      31277  consistent gets
      31198  physical reads 生产环境：|  4 |    INDEX FULL SCAN          | DOP_ORDER_OPERATION_1_UIX |  1752K|    15M|  681  (1)|
|  5 |    INDEX RANGE SCAN        | DOP_ORDER_PK              |    1 ||    1  (0)|    3582573  consistent gets
      20669  physical reads 问题在于生产环境中的逻辑读非常高，几乎是测试环境的10倍，检查执行计划，猜测是生产库上不恰当的索引或者已经过时的统计信息导致导致CBO在选择执行计划的时候判断错误，从而导致查询非常慢楼主能重新收集一下生产库中的统计信息，并提供一下这两张表在两个环境下的数据量么
sql2的执行时间和统计信息没有意义，因为没有清除已经读到buffer cache中的数据块，直接再次执行查询的话，根本不会出现物理磁盘访问等等，所以这个时间统计也是不准确的
谢谢xiaoxiao1984
SQL1 在test(dop_order 表数据量1202576,dop_order_operation表数据量1758259)执行计划和统计信息
SQL> SELECT max(sd.dop_id)
  2  FROM dop_order sd,dop_order_operation ssd
  3  WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
  4  And sd.configuration_id='934415';执行计划
--------------------------------------------------------------------------------| Id  | Operation           | Name                   | Rows  | Bytes  | Cost (%CPU)|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |                           |     1 |    22 |  7149  (2)||   1 |  SORT AGGREGATE     |                           |     1 |    22 |     ||   2 |   NESTED LOOPS      |                           |   130 |  2860 |  7149  (2)||   3 |    TABLE ACCESS FULL| DOP_ORDER_TAB             |    89 |  1157 |  6971  (2)||   4 |    INDEX RANGE SCAN | DOP_ORDER_OPERATION_1_UIX |     1 |     9 |     2  (0)|--------------------------------------------------------------------------------Note
-----
   - 'PLAN_TABLE' is old version统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
      31281  consistent gets
      30636  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>SQL1 在prod(dop_order 表数据量1204372,dop_order_operation表数据量1760542)执行计划和统计信息
SQL> SELECT max(sd.dop_id)
  2  FROM dop_order sd,dop_order_operation ssd
  3  WHERE sd.DOP_ID = ssd.DOP_ID And sd.dop_order_id = ssd.dop_order_id
  4  And sd.configuration_id='934415';
执行计划
----------------------------------------------------------| Id  | Operation               | Name                  | Rows  | Bytes| Cost (%CPU)|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |              |     1 |    22 |  3221   (7)||   1 |  SORT AGGREGATE           |               |     1 |    22 |            ||   2 |   TABLE ACCESS BY INDEX ROWID| DOP_ORDER_TAB   |    1 |    13 |     1   (0)||   3 |    NESTED LOOPS           |               |   130 |  2860 |  3221   (7)||   4 |     INDEX FULL SCAN    | DOP_ORDER_OPERATION_1_UIX |  1752K|    15 M|   681   (1)||   5 |     INDEX RANGE SCAN      | DOP_ORDER_PK           |     1 | |     1   (0)|--------------------------------------------------------------------------------Note
-----
   - 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
    3591794  consistent gets
      40207  physical reads
        188  redo size
        343  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>
那么我如何做到准确?如何清除buffer cache的数据快?能给出相关资料吗
alter system flush buffer_cache;
alter system flush buffer_cache;