CODEWITHSUNDEEP
sqloracle-databasesql-insert
Pascal Ognibene
Pascal Ognibene

Reputation: 111

oracle insert into very slow

I have the following Oracle query, that executes quickly (in a few seconds):

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

the 'trip' table has a large number of row (more than 20 million) Now, I want to insert the result of this query in a table, using:

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

This is extremely slow. So slow that the transaction times out (more than 30 seconds in my case). Any idea why this is so slow, and how it could be optimized?

Upvotes: 4

Views: 27538

Answers (4)

demiradam
demiradam

Reputation: 11

Try to select with the cursor, and insert in the loop. Like that;

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;

Upvotes: 1

Pascal Ognibene
Pascal Ognibene

Reputation: 111

As they are still answers being made for this almost 4 years old question ... The root cause for the slowness for a saturation in IO (RDS instance on AWS...) plus way too many group by/sort. More a database modeling problem than anything else. Thank you for all those that tried to help!

Upvotes: 0

Abdelhamid Elnagar
Abdelhamid Elnagar

Reputation: 1

check buffer cashe, i think the problem may be at buffer cashe or try to gather table statistics

Upvotes: 0

ms32035
ms32035

Reputation: 159

It's very hard to say what's the reason, as there could be multiple things, such as:

  • insufficient memory, or redo log size
  • suboptimal table settings (PCTFREE etc)
  • hardware performance issue

Two things I'd recommend:

  • check what are the wait events
  • try insert with APPEND hint
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

Upvotes: 1

Related Questions