1

I insert data from external table to my table that is range partitioned and have two local indexes.

My case,

I have to insert records under 60 seconds for each flat file because new one comes.

A flat file consists +5 M records and 2 GB.(volume : Totally 5 billion records daily ) Additionally I do some sort operations before insert on external table select.

My environment is on Oracle ExaData X-5 12.2 version.

There are many process doing insert to same table simultaneously so I can not use append hint. I can use parallel and nologging hints.

I have .exe that manages all this process. It gets flat files from source then combines them if there is one more flat files then moves combined file to true directory for external table and calls a procedure to insert data from external table to my table. Lastly changes flat file with next one.

There is one .exe for each different flat file.

All select operation takes 35-40 seconds from external table but insert takes too much times 50-60 seconds.

Can you give me some useful advices?

mehmet sahin
  • 802
  • 7
  • 21
  • You've made a series of statements and asked for advice, but you haven't explained what you problem is. Please edit your question and add an explanation of the issues you're facing. Thanks. – Bob Jarvis - Слава Україні Oct 20 '18 at 21:48
  • 1
    Does each file contain records which insert into one partition or many? – APC Oct 21 '18 at 06:22
  • There are several ways of using parallel hints for an insert. `insert /*+ parallel */` has the same effects as the `insert /*+ append */` that you wanted to avoid for concurrency reasons. Given that you want to avoid those, I'm not sure what benefit you are getting from `nologging`. – William Robertson Oct 21 '18 at 10:02
  • Regarding grabbing the data before the external table changes, I'm struggling a bit to picture how the load process works, but you might dump the contents into an interval-partitioned staging table to free up the external table. – William Robertson Oct 21 '18 at 10:05
  • @APC there is range partition on date column so flat files generally inserts same partition – mehmet sahin Oct 21 '18 at 11:51
  • @BobJarvis I edited question. Problem is about on performance. Explained process takes 100 seconds and I must improve performance and increase duration to under 60 seconds. Thanks. – mehmet sahin Oct 21 '18 at 11:54
  • How are we supposed to help with this, without any information on your table structure and the way you are inserting that data? – GolezTrol Oct 21 '18 at 12:30
  • @GolezTrol My target table is configured as 'nologging' and range partitioned on date column hourly and have two local indexes. and has 4 column as 1 date and 3 number – mehmet sahin Oct 21 '18 at 12:46
  • And how do you do the insert? – GolezTrol Oct 21 '18 at 12:58
  • @GolezTrol Insert /*+ parallel(8) nologging */ into target_table select * from ext_table – mehmet sahin Oct 21 '18 at 13:09
  • Is the requirement to load each file in 60s; or to load 5 billion records in a day. In other words, does a single file's data need to be available in 60s? – BobC Oct 21 '18 at 17:36
  • You also say you have multiple processes doing this concurrently. Are these all reading different file and inserting into the same table? – BobC Oct 21 '18 at 17:38
  • @BobC the requirement is to load each single flat file under 60s. All concurrent process reads different file and load same table. Thanks. – mehmet sahin Oct 21 '18 at 18:07
  • What if you built the external table using all the files that you are trying to load using multiple processes. Then you could use direct path, and increase the parallelism. – BobC Oct 21 '18 at 18:33
  • @BobC each flat file type has different structure and I load only 3 same column from each ones. So I can not combine them but I combine same structured files. If I load with append hint , 'Enq TM Contention' occured for other sessions. – mehmet sahin Oct 21 '18 at 18:38
  • 2
    Your insert statement can combine the select from the different external tables. The motivation is to have oracle perform the parallelism, rather than doi it manually with multiple processes. That would allow the direction path option and mitigate the TM lock . – BobC Oct 21 '18 at 18:46
  • I will try your advice if there is not any limitation for doing this approach. Thanks. – mehmet sahin Oct 21 '18 at 18:50

0 Answers0