在线等分析，oracle的insert插入非常慢

Question

现在有两台服务器一台是150，一台是144
昨天用150服务器上的plsql登录144的数据库并执行导入操作aa_distancehost_log，tmp_aa_distancehost_log均在144服务器上。
insert into tmp_aa_distancehost_log（log_datetime索引已建）
  select *
    from aa_distancehost_log t
   where t.log_datetime between
         to_date('2008-10-01 00:00:00 ', 'yyyy-mm-dd hh24:mi:ss') and
         to_date('2008-10-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
commit;在执行了大概1个小时后还在执行，不得已直接在plsql上中断连接，直到今早还是没有中断。不得已直接中断144服务器上的oracle进程。
但是现在150服务器上的oracle CPU占用一直在50%以上。
并且我现在把时间分一天天执行。insert 一天的数据就commit虽然非常慢但是总算导入了。
请高手分析下为什么144的服务器执行insert操作如此的慢，才200多万的数据。
还有150的机器为什么现在cpu占用率一直在50%以上

Answer 1

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 
可以通过alter system kill session ‘sid,serial#’来杀掉会话 
SELECT /*+ rule */ s.username, 
decode(l.type,'TM','TABLE LOCK', 
              'TX','ROW LOCK', 
              NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,dba_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT NULL 
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 
以下的语句可以查询到谁锁了表，而谁在等待。 
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, 
       o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM v$locked_object l,dba_objects o,v$session s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 
ORDER BY o.object_id,xidusn DESC 

Answer 2

linzhangs：导入一个月的日志（2008-10-01,2008-10-31），真有你的！  呵呵。

Answer 3

分天导入时也要编个循环，比如插入50条语句后，commit一次。

Answer 4

我知道这个的
alter system kill session ‘sid,serial#’来杀掉会话 ，
我杀掉后再执行插入仍然很慢。现在我分段一天天插还要1个小时才能执行完毕，但数据量只有250万条。我想问下是不有没有其他原因导致数据库执行非常缓慢的原因。

Answer 5

如果LZ只是insert into...而没有commit的话，那么回滚段中一直增加并保留这些修改的东西（甚至和操作系统不断地进行着页面的调进和调出）。直到你使用commit或是rollback！！！

Answer 6

我在本机执行的时候大概半个小时就可以了。可是在服务器执行，要那么久。服务器可是8G的内存，我本机只有1g。我想知道如果这250万数据一直插入，就相当于一直在内存中。那大概要占用多少内存。还有是不是undo表空间设置的太小的缘故呢。

Answer 7

同时，只是insert而没有commit会引起一堆大的DML阻塞，甚至死锁。严重地影响性能，这个楼主是知道的。

Answer 8

我知道的就是从v$session_wait查在等待哪些事件，然后再做分析处理150的机器上s1开始执行sql, 开另一个s2查看s1的等待事件，看看在等待些什么

Answer 9

>我在本机执行的时候大概半个小时就可以了。可是在服务器执行，要那么久。服务器可是8G的内存，我本机只有1g。是说：你从本机连到140运行是半小时，从150上连到140执行是很久？

Answer 10

我用同样的数据库在本机做了个试验，很快半个小时就搞定了。就一条语句也没有分段commit；看来服务器有问题。
BlueskyWide已经把看家的代码发给我了哈哈。我试下，现在得出去了，明天结贴。不行写过程50条commit一次。

Answer 11

我的服务器参数
SQL> show parameter cursor_sharing;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACTSQL>  show parameter shared_pool_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 16MSQL>  show parameter shared_pool_reserved_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 15938355SQL>  show parameter java_pool_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0SQL>  show parameter large_pool_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0SQL>  show parameter pga_aggregate_target;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 809MSQL> show parameter sga_max_size;NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2432MSQL> 帮忙分析下。哪个地方不合理，谢谢BlueskyWide大侠

Answer 12

什么版本的，shared_pool_size 才 16M？ 

Answer 13

你的redo文件多大一个，会不会小了

Answer 14

lz,建议你建立一个新表，索引外键主键，所有的约束都不要建立，然后执行脚本，看看用多少时间？
脚本：
create table tmp_aa_distancehost_log_temp as 
  select * 
    from aa_distancehost_log t 
  where t.log_datetime between 
        to_date('2008-10-01 00:00:00 ', 'yyyy-mm-dd hh24:mi:ss') and 
        to_date('2008-10-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'); 
commit; 

Answer 15

1.关闭索引
２．insert /*+ append */ into tmp_aa_distancehost_log
3.启用索引
执行完毕估计在３分钟内完成
通过v$session_longops监视，里面会显示所有执行超过６秒的操作，包括已经插入的记录数量