[救命]上月2小时的查询现在看来1个星期都结束不了了

biti_rainy

对于大量数据join的，坚决不要做 nested  loops !

宁愿sort  merge 也不要 nested  loops

预测时间应该是不很准确的，但是增加 pga 应该会减少执行时间的

非要走nested  loop可以使用  hints
关于hints ,参考  http://tahiti.oracle.com   很多 介绍

SweetMemory

是什么东东的预告时间？到底准不准啊？能否在PGA1.5G下走HASH JION等10~20min看看？在SQLPLUS里执行并set timing on。

anty_chen

在create table时是否可以去掉having count(*)!=0?在create table 后再去掉!=0的我想可能会快很多吧

wdq4587

最初由 SweetMemory 发布
[B]是什么东东的预告时间？到底准不准啊？能否在PGA1.5G下走HASH JION等10~20min看看？在SQLPLUS里执行并set timing on。 [/B]

是Enterprise manager console的预告时间啊，有准也有不准的啊，不准的都是预告时间越变越长的，还没见过变短的，这个我也等了12分钟以上的，感觉没变短希望了，预告加已执行时间超过1小时40分钟。想想觉得再短也要1小时以上，出来也说明不了问题了。

现在的问题是你要sort merge 它不听啊，我再来看看hint.。

最初由 anty_chen 发布
[B]在create table时是否可以去掉having count(*)!=0?在create table 后再去掉!=0的我想可能会快很多吧 [/B]

having条件 是group by之后再执行的过滤，应该对处理速度的影响是微不足道的。

biti_rainy

select /*+ use_merge(a b)*/  ......

提示直接换成这样看看，走了  index  fast  full  scan 肯定就不会是  sort merge了，应该是 index  range  scan 才有可能是 sort  merge

wdq4587

用select /*+ use_merge(a b)*/ ......
强制出来了，可惜sort join运行预告要2小时20分钟以上，运行19分钟后切断。
看执行计划，写盘要2G多，难怪慢。

再用user_nl，刚才没出来的index range scan出来了（前面还是INDEX FAST FULL SCAN），尝试执行下，console里看不到进度条了，晕了，反正下班了，等明天看结果吧。

wdq4587

今天早上看，select /*+ use_nl(a b)*/ ... 花了7小时56分钟执行完，这个不算慢了，在预料之中的，因为nested loop是基本上是随机读。

今天试验重排记录后的效果，因为数据库空间不太够，先处理6月份的数据，这个当初没优化处理大概一个半小时。按user_name,start_time排序，再按这个生成索引，执行NL查询，发现NL查询看不到进度，也看不到trace的统计，结果是5分13秒就出来了。下一步准备再看11月份结果会如何。执行计划如下：

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     1 |   108 |    30M|
|*  1 |  FILTER                         |                               |       |       |       |
|   2 |   SORT GROUP BY NOSORT          |                               |     1 |   108 |    30M|
|*  3 |    TABLE ACCESS BY INDEX ROWID  | ADSL06_DETAIL                 |     1 |    54 |     2 |
|   4 |     NESTED LOOPS                |                               |     1 |   108 |    30M|
|   5 |      TABLE ACCESS BY INDEX ROWID| ADSL06_DETAIL                 |    15M|   777M|   826 |
|   6 |       INDEX FULL SCAN           | A06_USER_NAME_AND_START_TIME  |    15M|       |    26 |
|*  7 |      INDEX RANGE SCAN           | A06_USER_NAME_AND_START_TIME  |     1 |       |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)<>0)
   3 - filter("A"."NAS_IP"="B"."NAS_IP" AND "A"."NAS_PORT"="B"."NAS_PORT" AND "A"."FRAME_IP"<>
              "B"."FRAME_IP"
   7 - access("A"."USER_NAME"="B"."USER_NAME" AND "A"."START_TIME"<"B"."START_TIME" AND "B"."S
              TART_TIME"<"A"."STOP_TIME"-.003472222222222222222222222222222222222222

这个执行计划次序正合我意。
然后看总共花的时间：

原始user_name,start_time索引生成时间，忘记了，应该不会太长。
按user_name,start_time排序倒入另一个表：3分46秒
再生成user_name,start_time索引时间：2分23秒
最后查询时间：5分13秒
合计估算从单纯的原始数据到最后结果大概刚好在15分钟之内。表的大小估计是800MB。

wdq4587

对11月份数据同样步骤查询，这个表大小1491MB，user_name,start_time索引512MB。

原始user_name,start_time索引生成时间，可能是7分50秒，但如果不要这步骤，下面这步估计增加5分钟，因为少一次全表扫描。
按user_name,start_time排序倒入另一个表：7分整
再生成user_name,start_time索引时间：3分17秒
最后查询时间：5分50秒
合计估算从单纯的原始数据到最后结果大概在20分钟样子。

从前面提到开始到现在都是按PGA 1.5GB目标设置的，下面试验sort merge的结果，然后重新试验PGA目标512MB的情况。

biti_rainy

你的这个  NL 之所以快，最重要的原因是不用去表中获取数据，并且920版本正好对这种有序数据做过特殊的处理，,如果全部都是在索引内完成，而 hash join 依然很慢，只能说明 写临时表和读临时表 实在太慢，有些不合情理。

希望你在相同条件下同时做 nl  /  sort merge  /  hash  join 组合测试，想同条件指 都通过索引完成而不用访问表，同时执行查询的过程中不断地查询 v$sort_usage 中看查询所使用到的最大 临时空间大小，这可以作为临时表空间 读写 效率评价的一个侧面依据

wdq4587

最初由 biti_rainy 发布
[B]你的这个  NL 之所以快，最重要的原因是不用去表中获取数据......[/B]

非也，非也，你没看我过程是建个新表吗？那老表连全字段的索引都被我删了，现在查询只有user_name,start_time索引。在NL查询时，这个索引做第一级判断，join还是要读表的啊。

为什么做Nested Loop开始要差不多8小时，记录重排序后只要不到6分钟，其实原理很简单：这个NL查询也就是按group by的字段user_name一个一个统计，一般性的理解不考虑什么代价的优化的话（也就是NL），查询应该是一个user_name一个的扫描，每个user_name统计时只用到表中同样user_name的记录，和不同user_name的记录是完全没关系的。第一次做NL扫描，某个user_name的记录是分散在数据库中的，接近随机分布，粗略估计全表扫描可以等效为user_name数乘以每user_name的平均记录数次数的随机磁盘寻址（这里假定第二级扫描同一个user_name值的记录处理时都在内存里，不用再次读），也就是等效整个表记录数次数的随机寻址，当然缓存能减少这种寻址次数。而第二次扫描，user_name相同的记录已经重排到一起了，读一个记录磁盘缓存就把其它的记录也同时读入了，所以最快的可能时间等于线性读整个表和索引的时间之和。

磁盘读写的一个基本特性就是随机寻址很慢，而顺序读写很快，我用的这个服务器的SCSI盘随机寻址时间大概6ms样子，一秒钟大概能随机读160次，而普通磁盘顺序读1秒钟30~50MB也是很容易的。

对于第一次NL查询，我这个表记录数为16537964，乘以6ms的话，等于27.56小时，也就是最坏情况会这么长时间（按一个user_name一个的扫描模式查询），而实际时间只用了接近8小时，是因为服务器2G内存还算比较大，虽然要读2GB的数据，缓存有71%的命中率（按这个命中率估算似乎大了些），另外根据索引做了第一级判断筛选，也减少了一点读记录的量（user_name有223405种，而结果记录数只有15312条，当然这不等于只要读15312种user_name部分的记录，不过前面估算也没有把读索引时间计算在内）。

反过来看，像第二次NL查询，2GB的数据假定30MB/s的速率，67秒也就读完了（实际读当然不可能都这么完全按顺序来，特别是索引，我光查表的记录数就用了51秒），但是这样看不到6分钟的查询时间就不令人惊讶了，而且后一查询对内存要求非常低，因为每个user_name的平均记录数只有74，我重新设PGA目标128M，6分19秒再次完成查询。假如表是单纯的数据文件，已知按user_name排过序了，用C程序处理，不需要读索引，相信2分钟之内可以出结果，但是数据库表不行，没有索引数据库会扫描完全表才相信这个user_name的记录都在这一段。

NL执行计划：
[php]
-------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     1 |   118 |    40M|
|*  1 |  FILTER                         |                               |       |       |       |
|   2 |   SORT GROUP BY NOSORT          |                               |     1 |   118 |    40M|
|*  3 |    TABLE ACCESS BY INDEX ROWID  | ADSL11_DETAIL                 |     1 |    59 |     2 |
|   4 |     NESTED LOOPS                |                               |     1 |   118 |    40M|
|   5 |      TABLE ACCESS BY INDEX ROWID| ADSL11_DETAIL                 |    20M|  1139M|   826 |
|   6 |       INDEX FULL SCAN           | A11_USER_NAME_AND_START_TIME  |    20M|       |    26 |
|*  7 |      INDEX RANGE SCAN           | A11_USER_NAME_AND_START_TIME  |     1 |       |     1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)<>0)
   3 - filter("A"."NAS_IP"="B"."NAS_IP" AND "A"."NAS_PORT"="B"."NAS_PORT" AND "A"."FRAME_IP"<>
              "B"."FRAME_IP"
   7 - access("A"."USER_NAME"="B"."USER_NAME" AND "A"."START_TIME"<"B"."START_TIME" AND "B"."S
              TART_TIME"<"A"."STOP_TIME"-.003472222222222222222222222222222222222222
[/php]

另外sort merge的结果出来了，单查询1小时19分钟完成。这个和以前比没太大变化，因为执行计划写TEMP还是要2GB以上，不同的只是表重排后执行相对快些。
[php]
---------------------------------------------------------------------------------
| Id  | Operation              |  Name          | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |   118 |       |   438K|
|*  1 |  FILTER                |                |       |       |       |       |
|   2 |   SORT GROUP BY NOSORT |                |     1 |   118 |       |   438K|
|   3 |    MERGE JOIN          |                |     1 |   118 |       |   438K|
|   4 |     SORT JOIN          |                |    20M|  1139M|  2958M|   219K|
|   5 |      TABLE ACCESS FULL | ADSL11_DETAIL  |    20M|  1139M|       | 23853 |
|*  6 |     FILTER             |                |       |       |       |       |
|*  7 |      SORT JOIN         |                |       |       |       |       |
|   8 |       TABLE ACCESS FULL| ADSL11_DETAIL  |    20M|  1139M|       | 23853 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)<>0)
   6 - filter("A"."START_TIME"<"B"."START_TIME" AND "B"."START_TIME"<"A"."STOP
              _TIME"-.003472222222222222222222222222222222222222 AND "A"."FRAM
              E_IP"<>"B"."FRAME_IP"
[/php]

为什么sort merge还有hash join慢或者发生过超慢，可能还是排序区域小了，因为似乎什么文章说排序区域大小对每个进程都一样，这方面我实在不太懂。为什么会想到优先用NL是因为看某文章说假如内表有索引的话，只有nested loop才会利用上，而我这个查询正好是极其需要的。