You Asked
Hi Tom,
We have an application to load data into database, It uses insert statements to insert data into tables.
Each table contains nearly 220 colums.
insert into table1(col1,col2,col3,col6,col7,col8................col220)values(1,2,3,6,7,8,...220);
insert into table1(col1,col2,col3,col5,col7,col8................col220)values(1,2,3,5,7,8,...220);
insert into table1(col1,col2,col3,col5,col7,col8................col200)values(1,2,3,5,7,8,...200);
insert into table1(col1,col2,col4,col5,col7,col9................col200)values(1,2,4,5,7,8,...200);
insert into table1(col1,col2,col4,col6,col7,col9................col200)values(1,2,4,6,7,9...200);
The application trying to insert each record into table1 in different formats as shown above depending on application logic(Please observe some column values are not inserting on each insert )
I have observed Oracle generating different SQL_ID's Due to change in columns...It directly impacting insert performance.
Could you please advise me how to fix this issue.
Thanks & Regards
Krishna.
and Chris said...
Each unique statement will get its own SQL_ID. So if you want to avoid having a stack of statements, you need single insert that sets all the columns. If a column doesn't have a value, set it to null.
The easiest way to do this is with bind variables! For example:
insert into t (c1, c2, c3, c4, ..., c219, c220) values (:b1, :b2, :b3, :b4, ...,:b219, :b220);
Set the bind variable values before running the insert.
It doesn't look like you're using these currently. So you'll need to change the application to do this.
The lack of bind variables is probably the real cause of your performance problems. So instead of one SQL_ID/column combination,
every insert is a new statement! This will lead to a lot of parsing...
To make things worse, you're at risk of SQL injection too:
https://blogs.oracle.com/sql/entry/what_is_sql_injection_and