Hello
I want to improve the performance of one of the insert sqls. My insert statement has a 'VALUES' clause and uses bind variables to do the inserts.
What I gathered from reading other blogs on how to improve the inserts are the following
1. Removing and rebuilding indexes (All my tablespaces are locally managed, so the indexes are not fragmented)
2. Parallel Inserts (This causes blocking issues, so I cant use this method)
3. Disabling Primary Keys
4. Putting the table in nologging (The append_values hint already does this by default)
5. Using direct path insert (The append_values hint uses the direct path insert by default)
5. Using Append_values hint (since my database is 10g, and since my insert contains referential integrity constraints and VALUES clause I cannot use this hint)
6. Using Host arrays (This requires the declaration of array variables, which is not advisable in my case)
7. Using bulk Inserts (This requires the code changes in my sql statement, which is also not advisable)
8. Using bigger data block sizes (my current value of the parameter db_block_size is 16k)
among these possible methods of improving my insert statement, using bigger data block sizes seems to be my only option.
my question is, can I really make use of the bigger block size, to improve my inserts? I read that using multiple block sizes for different objects in the same database is not recommended, with transportable tablespaces being the exception and to change the block size, we have to recreate the entire database (not possible in my case, since the database is in production).
Is there a way to change the block size of the database with out having to recreate it? If there is a way, what is it? Will I be able to change the block size back to it's original value (16k), without affecting the database?
The table currently has 16,246,909 rows
The number of minutes took for the statement to finish each time it is run. is as follows
(55 50 68 74 87 88 66 68 95 95 104 115 121)
as you can see, the performance of the query detoriated each and every time the sql is executed.
(the following is the general syntax of the insert statement)
Insert into <table name> values (:1, :2, :3 etc)
this insert statement inserts records into 18 columns
If there are any other way to improve the insert sql please do let me know
Thank You in advance.
Edited by: 969224 on Dec 12, 2012 9:28 AM