4:00 AM
Deep Dey
I have been doing large inserts on a couple of tables in my Oracle database and the performance has been degrading by the minute.
Answer:
The degradation in performance 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 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.
Example:
ALTER TABLE sales
EXCHANGE PARTITION new_sales WITH sales_jul_2010;
If you don't have partitioning, another option would be to drop indexes and/or constraints before the insert and re-create them 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 the input data is valid.
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. 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 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.
Answer:
The degradation in performance 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 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.
Example:
ALTER TABLE sales
EXCHANGE PARTITION new_sales WITH sales_jul_2010;
If you don't have partitioning, another option would be to drop indexes and/or constraints before the insert and re-create them 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 the input data is valid.
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. 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 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.
0 comments:
Post a Comment