Do you have any tips on Oracle performance tuning?

I have been doing large inserts on a couple of tables in my Oracle database and the performance has been degrading by the minute.

The performance degradation of your Oracle database could be caused by many things which makes it difficult to generalise. However, once you've ruled out possible causes such as an extra load from other users or batch programs or another developer's query using all the available resources, and are convinced that it's solely your insert that has a problem then there are several things you can do.

If you're using the Enterprise Edition of Oracle with the partitioning option you could do a partition exchange - you populate a table with the new data, create a new empty partition and then swap them. This is commonly used to update Oracle data warehouses with the latest transactions from the OLTP system.

EXCHANGE PARTITION new_sales WITH sales_jul_2010;

If you don't have partitioning, another option would be to drop all the indexes and disable referential integrity constraints on your table before the insert and re-create the indexes and re-enable the constraints once the insert has finished. Obviously this runs the risk that your inserted data will contain duplicates or invalid data so you have to ensure that the input data is valid.

Another trick is to disable logging on the table for the duration of the insert. This means that Oracle doesn't create redo information about the data inserted by the direct-path method. There are a number of caveats and conditions relating to the NOLOGGING clause (of the ALTER/CREATE TABLE/SPACE/INDEX etc statements) so you would be wise to check the nologging clause in the Oracle Database SQL Language Reference.

Another option would be use to use the PARALLEL hint on the SQL insert statement. This causes multiple processes to be spawned to carry out the insert. SMP systems benfit most from this. However, if your system is already cpu-bound this won't help.

Finally you could use the APPEND hint in your SQL statement to force a direct-path load of the data, bypassing the Oracle buffer cache. This causes the new rows to be created above the high-water mark of the table which means that any empty space existing in the table won't be re-used. This obviously has the potential to use more space then a normal INSERT statement but would be quicker when inserting a large amount of data.

That's a few tips for improving the performance of your insert statement, you might also find this question and answer on Oracle sql performance of interest as well as this one oracle performance tuning.

See our Oracle training page if you'd like formal training either on-line or in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.