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

biti_rainy

sorry ,我的疏忽，看错了

应该说，由于你把表的数据重新整理了之后，有序的数据存储在一起了，由于正好 oracle  920 对有序数据会做特别的处理，所以可能大大地降低了你的逻辑读。这是一点，另外一点就是数据的有序避免了额外过多的物理读。


NL 用索引没错，但是 NL 可能在大数据量连接的时候导致不必要的 物理读 和逻辑读
而你正好由于表数据的重新整理顺序减少了这个量
并且，我个人以为，你的磁盘的临时表空间的读写数据不是很好。 至于 sort merge ,你这里没有用到索引，如果用到索引应该会快一点。


也许这个问题到此该为止了。但我还是要申明一点：  NL 不适合大量数据join的场合，只是因为你重构了表数据顺序和索引一致，才能达到这样的效果。我想你的服务器的IO能力也可能不是够好。

biti_rainy

看我的一个 hash join的例子
sys@OCN>show  sga         

Total System Global Area  722540200 bytes
Fixed Size                   452264 bytes
Variable Size             301989888 bytes
Database Buffers          419430400 bytes
Redo Buffers                 667648 bytes
sys@OCN>select   bytes/1024/1024  from  user_segments  where   segment_name  =  'MEMBER';

BYTES/1024/1024
---------------
           2080

Elapsed: 00:00:00.03

sys@OCN>show parameter   pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1073741824
sys@OCN>show parameter  work

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

SQL> set  autotrace  trace   exp
SQL>
SQL> select  /*+ use_hash(a b)*/ a.login_id,count(*) dup_num,
sum(least(a.gmt_modified,b.gmt_create)-b.gmt_create) duptime_sum
from member a,member b
where a.login_id=b.login_id
and a.pen_name!=b.first_name
group by a.login_id
having count(*)!=0;
  2    3    4    5    6    7  
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57258 Card=24810 Byt
          es=2605050)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=57258 Card=24810 Bytes=2605050)
   3    2       HASH JOIN (Cost=52856 Card=496200 Bytes=52101000)
   4    3         TABLE ACCESS (FULL) OF 'MEMBER' (Cost=22141 Card=437
          4600 Bytes=87492000)

   5    3         TABLE ACCESS (FULL) OF 'MEMBER' (Cost=22141 Card=437
          4600 Bytes=371841000)

sys@OCN>select  /*+ use_hash(a b)*/ a.login_id,count(*) dup_num,
sum(least(a.gmt_modified,b.gmt_create)-b.gmt_create) duptime_sum
from member a,member b
where a.login_id=b.login_id
group by a.login_id
having count(*)!=0;

  2    3    4    5    6  


5078487 rows selected.

Elapsed: 00:03:13.09

Statistics
----------------------------------------------------------
          0  recursive calls
         23  db block gets
     964505  consistent gets
     575690  physical reads
          0  redo size
   88534739  bytes sent via SQL*Net to client
    1117758  bytes received via SQL*Net from client
     101571  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
    5078487  rows processed



表实际有2g大小，唯一和你不同的是记录数只有不到600万，只是因为行长度大一些。使用 hash join 完成时间大约是 3分多一点。根据我的经验，即使这个表大小增加到1000万，也不会超过10分钟。

这是我在我的系统中的测试，仅仅给你一个参考
我的环境是  linux  radhat  AS2.1 +  oracle  9204 , file system ext3  ,    EMC  cx200  raid10

SweetMemory

楼主也够聪明！把表中记录按索引排序大量降低了磁盘I/O，尤其是做NL连接时另一个表需要做索引范围扫描然后读取ROWID列表，在未排序的表中每个ROWID将指向不同的数据块，这些块会导致大量的I/O，排序之后做范围扫描I/O降了几个数量级。不过这种方法显然不适用于一般情况。
有一点我还是不明白你做HASH JION为什么就那么慢？莫非你磁盘的IO能力真的非常差吗？

biti_rainy

最后想请楼主明确一个问题

select  count(*) from  v$shared_server  ;  结果是不是 0
希望不是使用了 mts

wdq4587

最初由 SweetMemory 发布
[B]......在未排序的表中每个ROWID将指向不同的数据块，这些块会导致大量的I/O，排序之后做范围扫描I/O降了几个数量级。不过这种方法显然不适用于一般情况。
有一点我还是不明白你做HASH JION为什么就那么慢？莫非你磁盘的IO能力真的非常差吗？ [/B]

我这个1.5GB的表原始就是一次导入的，不存在rowid乱排的情况。磁盘IO能力应该不算太差吧，我从监视上看每秒150多次存取，正好是随机寻址的估计范围，顺序读取我没测，但应该在50MB/s以上的。

最初由 biti_rainy 发布
[B]最后想请楼主明确一个问题
select  count(*) from  v$shared_server  ;  结果是不是 0
希望不是使用了 mts [/B]

结果是1。

不好意思，这两天事情多，所以就没回帖，其实为了查这个问题，前几天别的事情进度已经拉下了（我不是专搞数据库或编程的），假如有时间的话，我还是愿意查个清楚的。起码我觉得在我这个特定运行环境情况下，Oracle对执行时间（代价）的估计有非常大的偏差。

我的系统环境是：Windows 2000 Server SP4，Oracle 9i 9.2.0.1.0，2GB内存，双2.8G CPU，硬盘为双73GB SCSI盘镜像（速度我估计为1万转），文件系统NTFS。

SQL> show sga

Total System Global Area 1074864892 bytes
Fixed Size                   455420 bytes
Variable Size             125829120 bytes
Database Buffers          947912704 bytes
Redo Buffers                 667648 bytes

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
pga_aggregate_target                 big integer 1610612736

不过奇怪的是PGA目标设大后执行时Oracle占用的内存也没增加，还是1GB。

SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
workarea_size_policy                 string      AUTO

你的对比查询和我的显然有些不同，你的IO肯定比我这个快，但这不是主要的（估计也就快1倍吧）。你的执行计划前两次全表扫描居然只要读372M和87M（不知道实际可要全表都扫过），而我这个两次都是1139M。我16M条记录你6M条记录，记录数少意味着要hash join的行数也少，但你的HASH JOIN空间只要52MB，内存轻松放下，而我这个要1371M，这个差距就太大了，时间上也主要就差在这里啊。

HASH JOIN的执行计划
[php]
-------------------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     1 |   118 |       | 92691 |
|*  1 |  FILTER              |                |       |       |       |       |
|   2 |   SORT GROUP BY      |                |     1 |   118 |       | 92691 |
|*  3 |    HASH JOIN         |                |     1 |   118 |  1371M| 92678 |
|   4 |     TABLE ACCESS FULL| ADSL11_DETAIL  |    20M|  1139M|       | 23853 |
|   5 |     TABLE ACCESS FULL| ADSL11_DETAIL  |    20M|  1139M|       | 23853 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT(*)<>0)
   3 - access("A"."USER_NAME"="B"."USER_NAME" AND "A"."NAS_IP"="B"."NAS_IP"
              AND "A"."NAS_PORT"="B"."NAS_PORT"
       filter("A"."START_TIME"<"B"."START_TIME" AND "B"."START_TIME"<"A"."ST
              OP_TIME"-.003472222222222222222222222222222222222222 AND "A"."
              FRAME_IP"<>"B"."FRAME_IP"
[/php]

wdq4587

查到了，我的hash_area_size 是
这个Oracle不是我装的。

SQL> show parameter hash_area_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
hash_area_size                       integer     1048576


是不是可以这样理解啊？开始我抱怨几天执行不完的查询是走Netsed Loop的（但按我估算最坏30小时应该可以执行完啊？为什么后来8小时又执行完了呢？那还没有重排表啊。），而后面做analyzer后用HASH JOIN但分配的空间太小所以就很慢？

biti_rainy

看来你的系统有设置 MTS
如果系统可以重新启动，请注释掉初始化参数里面的 dispatchers =  ...   这一行


如果不能，参考下面
如果你在client 通过 tnsnames.ora 登陆数据库的
在你的client 的 tnsnames.ora 中加上下面红色位置的这一行，然后再连接数据库做测试
BOPSCHA.DB.ALIBABA.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bopscha.hz.alibaba-inc.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (server =  dedicated)  
      (SERVICE_NAME = bopscha.db.alibaba.com)
    )
  )

我还是对这个结果有些不大服气   


BTW :  我的执行计划中为什么会显示那么小的字节数，可能跟我是采样分析有关。

d.c.b.a

表1大1小时，用hash比较好，对于两个大表，最好是关联列上有索引，进行merge join.

husthxd

都把注意力集中到调整sql上,能否通过建立物化视图去提升性能?

表数据的更新频率快吗?
可以考虑在下面的查询上建立物化视图.定时刷新.
select a.user_name,count(*) dup_num,
sum(least(a.stop_time,b.stop_time)-b.start_time) duptime_sum
from a11_detail a,a11_detail b
where a.user_name=b.user_name
and a.frame_ip!=b.frame_ip
and a.nas_ip=b.nas_ip
and a.nas_port=b.nas_port
and a.start_time<b.start_time and a.stop_time-1/288>b.start_time
/
或者不创建表,直接创建物化视图
create materialized view mv_a11_dupdial5min_sp as
select a.user_name,count(*) dup_num,
sum(least(a.stop_time,b.stop_time)-b.start_time) duptime_sum
from a11_detail a,a11_detail b
where a.user_name=b.user_name
and a.frame_ip!=b.frame_ip
and a.nas_ip=b.nas_ip
and a.nas_port=b.nas_port
and a.start_time<b.start_time and a.stop_time-1/288>b.start_time
group by a.user_name
having count(*)!=0
/

当然这是一种用空间换时间的做法.
刷新时候对系统性能也有所影响.

SweetMemory

我不知道他这种情况下，如果建立物化视图用增量更新的话，对系统性能影响会有多大，或者说是不是很大？物化视图每次的增量更新对性能的影响完全取决于更新的数据量吗？