You won’t have been developing in Oracle for too long before you find the need to populate data in your application tables. Moving from across the various environments from development to production means that you will want the ability to create data easily so manually typing data using a GUI is out of the question. Besides, how many errors do you think you’ll make trying to type in a list of postcodes and suburbs?
For relatively small amounts of data the simple INSERT statement is more than adequate for loading data. Creating scripts for reference tables can be a tedious though. Thankfully GUI development tools such as Dell (Quest) TOAD and Oracle SQL Developer have the ability to export data as INSERT statements. Once a table has been loaded in the development environment it can be easily exported as INSERTs for inclusion as part of the application deployment scripts.
Things begin to wrong when developers think this a good strategy for large tables. Those same GUI tools that made it easy to export small reference table data as INSERTs also make it easy to export large tables, running into the tens of thousands of rows. While there are better ways to load data it can be too late to do anything about it by the time it reaches the DBA for deployment. Not only can the script take a while to run but it will flood the SGA with one-off SQL statements.
Both problems can be mitigated with the CURSOR_SHARING parameter though. Somewhat simplistically, changing the parameter from the default value of EXACT to FORCE will replace the literal values in the insert statements with bind variables. Since CURSOR_SHARING is a session modifiable setting it can be changed with:
ALTER SESSION SET cursor_sharing = FORCE
So how effective is CURSOR_SHARING? Let’s do a test using 10,000 inserts into an empty table with CURSOR_SHARING set to EXACT, i.e. no forced bind variable substitution. The inserts are all along the lines of:
Insert into INS_TEST (ID,PADDING) values (1,'ZLwgVwYBfDopIlMbvjQjtWNfVfFrKP');
The full scripts can be downloaded using the links at the end of the this post.
SQL>SET ECHO ON
SQL>VARIABLE start_time NUMBER
SQL>ALTER SYSTEM FLUSH SHARED_POOL
2 /
System altered.
SQL>TRUNCATE TABLE ins_test
2 /
Table truncated.
SQL>ALTER SESSION SET cursor_sharing = EXACT
2 /
Session altered.
SQL>EXEC :start_time := dbms_utility.get_cpu_time
PL/SQL procedure successfully completed.
SQL>SET TERMOUT OFF
-- 10,000 insert statements executed here but not shown
SQL>EXEC dbms_output.put_line ('Insert duration (cursor_sharing=exact): ' || TO_CHAR(dbms_utility.get_cpu_time - :start_time))
Insert duration (cursor_sharing=exact): 1288
PL/SQL procedure successfully completed.
So, our inserts too 12.7 seconds, which is quite good for my small notebook albeit equipped with a fast processor and an SSD.
Now let’s see what happens if we set CURSOR_SHARING to FORCE, which substitutes literal values with bind values in our insert statements:
SQL>TRUNCATE TABLE ins_test
2 /
Table truncated.
SQL>ALTER SESSION SET cursor_sharing = FORCE
2 /
Session altered.
SQL>EXEC :start_time := dbms_utility.get_cpu_time
PL/SQL procedure successfully completed.
SQL>SET TERMOUT OFF
-- 10,000 insert statements executed here but not shown
SQL>EXEC dbms_output.put_line ('Insert duration (cursor_sharing=force): ' || TO_CHAR(dbms_utility.get_cpu_time - :start_time))
Insert duration (cursor_sharing=force): 626
PL/SQL procedure successfully completed.
SQL>
SQL>ALTER SESSION SET cursor_sharing = EXACT
2 /
Session altered.
Using FORCE for CURSOR_SHARING halved our execution time; from 12.7 to 6.3 seconds. A very nice benefit from a single line change to the deployment script!
So, what about our SGA? Running the following query after each test:
SELECT AVG(executions)
FROM v$sql
WHERE sql_text LIKE 'Insert into INS_TEST%' ESCAPE '\'
gives us a result of 1 when CURSOR_SHARING is EXACT and 10,000 when FORCE, showing the CURSOR_SHARING is behaving exactly as we want.
One last point to note: always change CURSOR_SHARING back to the default value immediately at the end of the script! This is just good practice.
Download the scripts for this post.
Mark Hoxey 