Skip to Main Content
  • Questions

  • Insert Into Large Tables Takes Longer Time

Breadcrumb

We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: September 04, 2024 - 1:48 am UTC

Last updated: September 04, 2024 - 2:33 am UTC

Version: 19c

Viewed 100+ times

You Asked

Looking for a recommendation related to large table inserts in Autonomous (ATP) database.

We have a sizable history table in our database, roughly 45 GB, which stores all transactions at the end of each day. Recently, we have been experiencing performance challenges when inserting data into this table.

We tried 2 approaches to fix the performance.

(1) /*+ APPEND */ hint in direct SQL.
(2) Bulk Collect and FORALL

Both approaches works fine for smaller data sets. But, the data is really big like 2 Million records (in certain days of a week) the inserts are taking more than 5 mins to run. Since our calling application has 5 mins limit on SQL executions, our entire package is failing.

Can you please help if we are missing anything in our approach or can try any other things for better performance?

Also, should we consider partitioning the table for faster DML?

If partitioning is something that we can consider, which field should we consider for partitioning (since the data in the table quite random, we thought of doing hash partition on line_id). Could you provide any suggestions? Furthermore, does the choice of field for hash partitioning carry any significance?

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/b852snk2k6919m1woble3hnlz

and Connor said...

Something is definitely wrong... here's a video which shows what you should typically expect on most hardware.



So the next thing to do would be to get a trace file so we can see where the time is being lost, ie

- exec dbms_monitor.session_trace_enable(waits=>true)
- run your inserts
- exec dbms_monitor.session_trace_disable

run tkprof on the resultant trace file (select * from v$diag_info).

That will show you where the time is being lost - if you need help with that, you can post the relevant bits here and we'll take a look

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.