Yesterday, we had an issue to increase the speed of insert statements. We had to insert about 500K record to database as quickly as possible. We were inserting at a rate of 10 records/sec. Well, I was thinking the following approaches to gain speed-
1. Use a larger data block size: By defining large (i.e. 16k or 32k) blocksizes for the target tables, we can reduce I/O because more rows fit onto a block before a "block full" condition (as set by PCTFREE) unlinks the block from the freelist.
SQL> DROP TABLESPACE web_key_tbs INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TABLESPACE web_key_tbs DATAFILE '/mnt/extra/test1.dbf' SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED blocksize 16K;
2. Increase the size of UNDO tablespace -
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/mnt/extra/test_undo1.dbf' SIZE 200M BLOCKSIZE 16K;
SQL> alter system set undo_tablespace='UNDOTBS' scope=both;
SQL> DROP TABLESPACE UNDOTBSP1 INCLUDING CONTENTS AND DATAFILES;
3. APPEND into tables: By using the APPEND hint, it ensures that Oracle always grabs fresh data blocks by raising the high-water-mark for the table.
SQL> insert /*+ append */ into customer values ('hello',';there');
4. Table into NOLOGGING mode: If possible, take the database to NO ARCHIVE LOG mode. Putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.
SQL> SELECT logging FROM user_tables WHERE table_name = 'LOGIN';
SQL> ALTER TABLE login NOLOGGING;
Again, to enable logging -
SQL> ALTER TABLE login LOGGING;
5. Disable/drop indexes: It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.
6. Parallelize the load: We can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL "APPEND" option.
Unfortunately, I was not be able to apply most of these tricks, the insertions were done by a third party application. I only tried the first two options - which did not help :-(
I could not even managed to analyze the wait events to find the exact reason of waiting. That third party software, which did those inserts, uses Oracle Call Interface (OCI) - so statements were not available to the database.
One more thing- database was runnnig on NOARCHIVELOG mode.
One helpful url -
http://www.dba-oracle.com/t_optimize_insert_sql_performance.htm
2 comments:
10 records/s looks quite low. On a Dell PowerEdge server with Windows 2003, 32 bit and 4 GB RAM, in our firm we insert up to 6 Million rows/hour, i.e. 1,666 rows/s.
This is our peak load, but we can sustain it if needed. Our application generates a "moderate" 1000 rows/s.
Your optimizations are right, but most improvements depend on the way you load data (ODP.NET in our case) and how your tables are indexed/structured (we insert with all indexes on - no indexes disabled).
Thanks for your comments. The machines were good enough - They had Intel's Quad processor with 4GB memory. As we used a third party software and called their APIs to insert into database, we did not have much control on their application. That third party software also performs some sort of indexing (as a part of their searching process) in file systems while inserting into databse.
Post a Comment