insert into 效率极其慢, 3000多条数据新增所需 19 分钟

Question

 服务器CPU：E7-4820 @2.0GHz
      内存：64GB
      硬盘空间：80G
Oracle SGA：12G
Oracle 表空间自动增加最小5M最大200M 问题是插入数据时, 3000条左右数据, 新增所需19分钟, 表中有7个字段, 并且表中有一个索引, 索引包含表中所有字段, 没有触发器.
在查询出 3000 条数据时, 只需2秒就可以查出, 但插入数据用时19分钟.oracle 服务重启后正常, 开2天以上就会发生问题.查询卡在语句的位置：
[oracle@localhost ~]$ top -d 1
top - 13:59:12 up 26 days,  2:12,  2 users,  load average: 1.06, 1.02, 1.00
Tasks: 217 total,   2 running, 215 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.2%us,  0.0%sy,  0.0%ni, 74.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  65970232k total, 22548692k used, 43421540k free,   241380k buffers
Swap:  4063224k total,        0k used,  4063224k free, 21245188k cached  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                
12123 oracle    20   0 25.7g 270m 260m R 100.0  0.4  27:31.21 oracle                                                                
12416 oracle    20   0 15144 1340  968 R  1.0  0.0   0:00.10 top                                                                    
    1 root      20   0 19348 1552 1224 S  0.0  0.0   0:19.25 init                                                                   
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.08 kthreadd                                                               
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.58 migration/0                                                            
    4 root      20   0     0    0    0 S  0.0  0.0   0:06.81 ksoftirqd/0                                                            
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0                                                            
......                                                        
[oracle@localhost ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 9 13:59:22 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn /as sysdba
Connected.
SQL> select addr,spid from v$process where spid='12123';   ADDR             SPID
---------------- ------------------------
00000006A18691E0 12123SQL> select sid,sql_id from v$session where paddr='00000006A18848A0';       SID SQL_ID
---------- -------------
       437 6kj86grnufd3cSQL> select sql_text from v$sql where sql_id='6kj86grnufd3c';SQL_TEXT
--------------------------------------------------------------------------------
INSERT /*+APPEND*/ INTO BBTJSUMMARY_NEEDSHOTV2 (FSTATIONCODE, FSTATDATEYM, FSERV
ERSTATUS, FBACTALIAS, YZWZ_Y16, YZWZ_Y1, YZWZ_Y14) SELECT /*+parallel*/ F.FSTATI
ONCODEL3 FSTATIONCODE, :B1 , V.FSERVERSTATUS, V.FBACTALIAS, SUM(V.YZWZ_Y16) YZWZ
_Y16, SUM(V.YZWZ_Y14) YZWZ_Y14, SUM(V.YZWZ_Y14) YZWZ_Y14 FROM BBTJSUMMARY_NEEDSH
OTV2 V INNER JOIN DCSTREETFULL F ON (V.FSTATIONCODE = F.FSTATIONCODEL5) WHERE FS
TATDATEYM = :B1 GROUP BY FSTATIONCODEL3, V.FSERVERSTATUS, V.FBACTALIAS
SQL> 请各位大神帮帮小弟分析一下原因, 感激不尽, 100分奉上.

Answer 1

可以做个ash或awr，检查下对应的等待事件
很可能在于磁盘写入，检测数据文件所在磁盘的IO及IOPS

Answer 2

IOPS测试结果： 标红的地方是重点？
[root@localhost fio-2.0.7]# fio -filename=/dev/mapper/VolGroup-lv_root -direct=1 -iodepth 1 -thread -rw=randrw -rwmixread=70 -ioengine=psync -bs=16k -size=10G -numjobs=30 -runtime=100 -group_reporting -name=mytest -ioscheduler=noop
mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K, ioengine=psync, iodepth=1
...
mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K, ioengine=psync, iodepth=1
fio 2.0.7
Starting 30 threads
fio: os or kernel doesn't support IO scheduler switching
中间省略
fio: os or kernel doesn't support IO scheduler switching
Jobs: 30 (f=30): [mmmmmmmmmmmmmmmmmmmmmmmmmmmmmm] [100.0% done] [25935K/11190K /s] [1583 /683  iops] [eta 00m:00s]
mytest: (groupid=0, jobs=30): err= 0: pid=25226
  read : io=3085.8MB, bw=31560KB/s, iops=1972 , runt=100122msec
    clat (usec): min=68 , max=706522 , avg=12757.15, stdev=24100.46
     lat (usec): min=69 , max=706522 , avg=12757.41, stdev=24100.46
    clat percentiles (usec):
     |  1.00th=[  187],  5.00th=[  258], 10.00th=[  532], 20.00th=[ 2896],
     | 30.00th=[ 4448], 40.00th=[ 5920], 50.00th=[ 7264], 60.00th=[ 9024],
     | 70.00th=[11584], 80.00th=[15936], 90.00th=[25984], 95.00th=[41216],
     | 99.00th=[98816], 99.50th=[142336], 99.90th=[321536], 99.95th=[428032],
     | 99.99th=[626688]
    bw (KB/s)  : min=   23, max= 3073, per=3.42%, avg=1077.98, stdev=631.43
  write: io=1320.5MB, bw=13505KB/s, iops=844 , runt=100122msec
    clat (usec): min=174 , max=640436 , avg=5589.21, stdev=22350.70
     lat (usec): min=181 , max=640443 , avg=5597.27, stdev=22350.73
    clat percentiles (usec):
     |  1.00th=[  207],  5.00th=[  231], 10.00th=[  249], 20.00th=[  282],
     | 30.00th=[  326], 40.00th=[  446], 50.00th=[  700], 60.00th=[ 1032],
     | 70.00th=[ 2448], 80.00th=[ 3632], 90.00th=[ 6688], 95.00th=[17024],
     | 99.00th=[117248], 99.50th=[177152], 99.90th=[272384], 99.95th=[305152],
     | 99.99th=[342016]
    bw (KB/s)  : min=   20, max= 1788, per=3.42%, avg=462.15, stdev=298.84
    lat (usec) : 100=0.02%, 250=6.28%, 500=13.45%, 750=3.45%, 1000=3.27%
    lat (msec) : 2=4.90%, 4=12.34%, 10=29.41%, 20=15.33%, 50=8.21%
    lat (msec) : 100=2.29%, 250=0.88%, 500=0.15%, 750=0.02%
  cpu          : usr=2.92%, sys=11.03%, ctx=8804460, majf=0, minf=42232
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=197490/w=84507/d=0, short=r=0/w=0/d=0Run status group 0 (all jobs):
   READ: io=3085.8MB, aggrb=31559KB/s, minb=31559KB/s, maxb=31559KB/s, mint=100122msec, maxt=100122msec
  WRITE: io=1320.5MB, aggrb=13504KB/s, minb=13504KB/s, maxb=13504KB/s, mint=100122msec, maxt=100122msec
  
  
  IO读写测试：
  [root@localhost fio-2.0.7]# hdparm -Tt /dev/mapper/VolGroup-lv_root/dev/mapper/VolGroup-lv_root:
 Timing cached reads:   9498 MB in  2.00 seconds = 4755.31 MB/sec
 Timing buffered disk reads:  756 MB in  3.00 seconds = 251.70 MB/sec
[root@localhost fio-2.0.7]# [root@localhost fio-2.0.7]# time dd if=/dev/mapper/VolGroup-lv_root of=/test.dbf bs=8k count=300000
记录了300000+0 的读入
记录了300000+0 的写出
2457600000字节(2.5 GB)已复制，27.8951 秒，88.1 MB/秒real    0m28.704s
user    0m0.077s
sys     0m9.944sAWR报告不知道怎么弄上来。

Answer 3

条件允许的话，插入语句可以提出来执行一下select count(1) from(
SELECT /*+parallel*/ F.FSTATI
ONCODEL3 FSTATIONCODE, :B1 , V.FSERVERSTATUS, V.FBACTALIAS, SUM(V.YZWZ_Y16) YZWZ
_Y16, SUM(V.YZWZ_Y14) YZWZ_Y14, SUM(V.YZWZ_Y14) YZWZ_Y14 FROM BBTJSUMMARY_NEEDSH
OTV2 V INNER JOIN DCSTREETFULL F ON (V.FSTATIONCODE = F.FSTATIONCODEL5) WHERE FS
TATDATEYM = :B1 GROUP BY FSTATIONCODEL3, V.FSERVERSTATUS, V.FBACTALIAS);看看这句要执行多久。
insert ..
select ...
先找出慢在查询，还是插入阶段。根据等待事件可以更方便判断

Answer 4

感觉就是 select 时，用的时间比较长。单拿出来跑跑看

Answer 5

这个问题一般从等待事件入手,可以做10046或者
直接根据 session查v$active_session_history,看等待什么或是谁阻塞~