求以下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？
请教各位兄台解悉...






------解决方案--------------------
列出执行计划和统计信息

需要比较同一个SQL在两台服务器上执行的时候cpu占用时间，逻辑读和物理读的次数多少等等相关信息，再确定问题原因
------解决方案--------------------
我比较认同你的同事的看法,因为在test库运行时可能没有别的查询在运行,缓冲占用很小,所以查询快;
而你正式运行库可能还在运行别的查询或其他事务,被占用的缓冲已经比较大了,而你的SQL1可能查询需要的缓冲又比较多,所以查询就慢;


------解决方案--------------------
导入后要对test库中数据进行统计分析,用DBMS_STATS.GATHER_SCHEMA_STATS(user)
------解决方案--------------------
对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在PROD如下,执行约2.39s


统计信息
----------------------
15 recursive calls
0 db block gets
[color=#FF0000] 9104 consistent gets
0 physical reads[/color]
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 processed