Bind variable peeking is executed, that is, Oracle only decides on an execution plan once it knows the first value of the bind variable. The phenomenon only occurs as of Oracle Version 9.2 in connection with SAP R3 Release 6.1 or higher, because the Oracle C-interface Version 8 (OCI8) is used there. Use%TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g.%TextIn(:1)%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms. Here are some resolutions that discusses this issue in Metalink – Oracle support site.
Not long ago I did note that quite many developers are using in Oracle database packages SQL statements as the dynamic SQL. They have learned in somewhere that the bind variables will make the query run faster and save some perfomance. To make clear some basics about Oracle database I wrote the following examples. Please don’t understand me incorrectly the bind variables are fast with dynamic SQL but only in external programming languages like ProC, C or Java. To store your code into a database the dynamic SQL is needed only in some extreme circumnstanses. To keep your queries as a static way in your prodecures and packages will save the compiling time before executing and any missing column or table will be raised as an Oracle exception during you are compiling the code.
To make sure all SQL queries have the same “starting point” and none of them will be cached empty the share_pool. Do it before running any of the following queries. You may need to use your SYS user to execute it.
The first PL/SQL anonymous block will be executed 50000 times with SQL query “SELECT rownum INTO v_cnt FROM dual WHERE rownum = 1;” and to make sure we will get the closest timing we will execute the block 5 times. To measure the execution time we are using SYSDATE and not some fancy procedure that can have bugs. In other words we will keep the block as simple as possible.
Bind In Peoplesoft Portal
The output shows execution time in seconds as 4, 4, 4, 5 and 4 and that makes in average total: 4.2.
The second code is written as a dynamic SQL query without the bind variables and we will execute the same statement as above.
The SQL output is very different from the first anonymous block and the times are 9, 9, 10, 9 and 9. That makes the average total: 9.2.
The third anonymous block is a dynamic sql with the bind variables and the query statement is same as previous examples.
The third output shows 4, 5, 4, 4 and 5 and the average is 4.4. The last average is not so far from the first anonymous block with a static SQL but still in long run it will make some difference and even less sense makes to write all your queries dynamically into the database procedures or packages.
The following group of examples are done with Oracle INSERT statement and to try them on your database you would need to create a new table so it wouldn’t break anything existsing.
The first insert is done to make sure the table has needed tablespace size taken and the INSERT statements would NOT loose any time by extending the tablespace. Once the insert is done we will delete all lines using TRUNCATE TABLE command and don’t worry about the COMMIT the TRUNCATE TABLE statement does commit.
Bind In Peoplesoft Jobs
Now we are ready to execute the first INSERT examples and this is static SQL inserting into just created my_oracle_test table and it does it 150000 times. We are keep measuring the time as on the example above and after the anonymous block is done it will truncate the table data. Once again I am running the test 5 times to get the average time.
The output shows 15.99, 16.99, 14.99, 15.99 and 16.99 and that makes the average 16.19.
The second INSERT anonymous block is with a dynamic SQL and WITHOUT the bind variables. The insert statement will stay the same and we will do truncate on the rows after the block is done.
The second insert is way slower than the static SQL and the times are 107, 107, 105, 105 and 105. The average in seconds is 105.8.
The third INSERT is a dynamic SQL and with the bind variables. The rest of code has left as on the previous INSERT examples above.
The SQL output shows times as 15.99, 16.99, 16.99, 15.99 and 15.99. The average is 16.36 and again the timing is not so far from the static SQL (16.19) but we cannot say that a dynamic SQL with the bind variables is quicker than a static SQL.
In short, you should keep your code in database as static and don’t write it as dynamic unless this is something extreme and can’t be written any other way. When you are using C or Java application USE bind variables calling out the database procedure or in SQL queries.
See Also:
Oracle SelectOracle SubstrOracle InstrHome