Oracle插入非常缓慢

Question

我有以下的Oracle查询，执行速度很快（几秒钟内完成）：

select contract_id_fk as ct,
       max(trip_id) as tid,
       max(consumed_mileage) as cum
from trip
where to_date > to_date('20-12-2016','DD-MM-YYYY')
and contract_id_fk is not null
and vehicle_id_fk is not null
and trip_stop_status is null
group by contract_id_fk

“trip”表有大量行（超过2000万）。现在，我想使用以下方式将此查询结果插入表中：

INSERT INTO lst
select contract_id_fk as ct, 
       max(trip_id) as tid, 
       max(consumed_mileage) as cum 
from trip
where to_date > to_date('20-12-2016','DD-MM-YYYY') 
and contract_id_fk is not null 
and vehicle_id_fk is not null 
and trip_stop_status is null 
group by contract_id_fk

这太慢了。非常缓慢，以至于交易超时（在我的情况下超过30秒）。您有任何想法为什么这么慢，并且如何进行优化吗？

- Pascal Ognibene

---

你是如何运行“SELECT”查询的（使用SQLPlus、某个工具等）？请尝试使用“select count(*) from (yourSelect)”并观察发生了什么。 - Aleksej

尝试添加追加提示 - /*+ append */。可能有很多原因（硬件、操作系统配置、表格配置）。选择返回多少行？表上有多少索引？ - OldProgrammer

如果原始表中有2000万行，则可能有数百万个结果行要写入表“lst”。输出固有比输入慢，而且您必须检查约束、更新索引等。您所描述的相对时间似乎并不出奇。 - John Bollinger

在插入之前，您能否先执行CTAS操作呢？ - Migs Isip

你能否提供一个针对插入语句的 SQL 监视器报告？ - BobC

Answer 1

很难确定原因，可能有多种原因，例如：

• 内存不足或重做日志大小不够
• 子优化表设置（PCTFREE等）
• 硬件性能问题

我建议两件事：

• 检查等待事件是什么
• 尝试使用APPEND提示进行插入

INSERT /*+ APPEND */ INTO lst
select contract_id_fk as ct,
max(trip_id) as tid, max(consumed_mileage) as cum
from trip where to_date > to_date('20-12-2016','DD-MM-YYYY')
and contract_id_fk is not null and vehicle_id_fk is not null and
trip_stop_status is null group by contract_id_fk

- ms32035

---

谢谢，我会尝试使用APPEND关键字。请问有什么提示可以帮助我检查等待事件吗？ - Pascal Ognibene

请查看 V$SESSION_EVENT。 - ms32035

Answer 2

尝试使用光标选择，并在循环中插入。像这样;

DECLARE
       CURSOR SCURSOR IS
        select contract_id_fk as ct, 
           max(trip_id) as tid, 
           max(consumed_mileage) as cum 
        from trip
        where to_date > to_date('20-12-2016','DD-MM-YYYY') 
        and contract_id_fk is not null 
        and vehicle_id_fk is not null 
        and trip_stop_status is null 
        group by contract_id_fk ;
    BEGIN
       FOR RECS IN SCURSOR
       LOOP
            INSERT INTO lst 
            SELECT RECS.ct , RECS.tid , RECS.cum FROM DUAL;
            COMMIT;
       END LOOP;
    END;

- demiradam

Answer 3

检查缓存区，我认为问题可能在于缓存区 或尝试收集表统计信息

- Abdelhamid Elnagar

---

欢迎来到StackOverflow！您所说的“cashe”是指cache吗？ - xKobalt

Answer 4

因为仍在回答这个将近4年历史的问题...IO饱和度缓慢的根本原因是（AWS上的RDS实例...）太多的分组/排序。这更像是数据库建模问题而不是其他任何问题。感谢所有尝试帮助的人！

- Pascal Ognibene