1

Big amount (4.000.000+) of files (1-4Mb each) are loading into Oracle 10g for about several days. Table is allocated in big tablespace, which was created on special volume (labeled BIG_VOL) over hardware RAID (Adaptec 3805 , 8 HDD x 2Tb, raid level 6).

RedoLogs was moved on two another SSD drives. No archivelog. There are only DBF and control files on BIG_VOL.

Transaction closed (by COMMIT) after every loaded file.

Perfomance Monitor shows ~6-9Mb per second on writing for this BIG_VOL ! Simple copy any big file shows about 80+Mb per second!

Can I do something to improve speed?

Improve this question
6
  • 1
    How are you loading the data exactly? What sort of I/Os are going through (lots of block-sized I/O or lots of large writes)? How often are the redo logs rotating?
    – Mat
    Dec 26, 2014 at 9:48
  • Can you do an AWR report to see what the bottleneck actually is? Inserting using multiple processes will probably help scale.
    – Philᵀᴹ
    Dec 26, 2014 at 17:19
  • @Phil i create AWR report - awrrpt_1_2_5.html. as i understand it shows only that database limited by IO?
    – evg345
    Dec 31, 2014 at 0:57
  • @Mat Loader is win32 app (MyLoader.exe) via OCI uses INSERT INTO. Perfomance Monitor shows 800-1400 IO operation per second, so "lots of block-sized I/O"
    – evg345
    Dec 31, 2014 at 1:02
  • Don't commit after each row. That is going to be terribly slow. 4 million rows should work just fine in a single transaction. Why aren't you using sqlldr? There is nothing faster when it comes to bulk loading. And why are you using such an outdated Oracle version?
    – user1822
    Jan 2, 2015 at 11:02

2 Answers 2

Reset to default
1

There's some information missing that would be useful but my first approach would be to concatenate the files into a smaller number of large input files to cut down the file handling overhead. Look at the load method and check if the data profile supports the use of SQL Loader direct loads. If SQL loader is not appropriate maybe use external tables and add the APPEND hint to the INSERT.

You may be able to think about how the table is partitioned and if the partitioning is reflected in the files, i.e. each file being relevant to one partition. This might give you scope for making the load parallel based on the partition criteria.

The above assumes that you are inserting data but if you have updates in there as well then I would go for external tables with a merge statement. This has worked for me with 10g in the past. I had a shell script loading each file based on its suitability for each loading method. We loaded up to 80 million records in around 3 to 4 hours from, in our case, hundreds of files rather than millions. So taking a complete guess at 100 records per file you should be able to load them in less than a day.

Improve this answer
1
  • This is good advice and the best answer you've posted so far - it's good to have you on board but please more like this rather than any terser. You've obviously got the expertise to help here if you want to.
    – Jack Douglas
    Jan 2, 2015 at 18:36
0

any particular reason you are using raid 6? raid 6 has extra overhead in write operations as it has additional parity over raid 5. Also the number of indices on the table can potentially slow down inserts.

So to answer your specific question: 'Can I do something to improve speed?' Yes, move away from raid6 (to raid 1+0 or even try ASM with raw partitions) and reduce the number of indices on the table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.