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:
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