Today's Smalltalk Daily looks at using statement caching against Oracle (version 9 and up) with ObjectStudio. If you're looking for a particular topic, you can find it with the Media Search application on our site.

The code used is below; To watch, click on the viewer:

"Statement Caching examples. Oracle does not support this feature until 9.0.0, so make 
sure you are using Oracle 9.0.0 and later."

"Information from Oracle: Statement caching refers to the feature that provides and 
manages a cache of statements for each session. In the server, it means that cursors 
are ready to be used without the need to parse the statement again.  It can be 
used with connection pooling, and will improve performance and scalability." 

"The following are two examples, one uses statement caching, the other does not."

"Statement Caching is off be default."
ObjectStudio.OracleDatabase useStatementCaching: false.

"Logon to the Oracle Server."
ObjectStudio.OracleDatabase 
	logOnServer: #'OracleDB' 
	user: #'username' 
	password: #'pwd' 
	alias: #'OracleDB'.


"Get the Oracle database instance."
db := ObjectStudio.Database accessName: #'OracleDB'.

"Set times to repeat."
loopCount := 100.

"Set the SQL to do the fetch."
sql :='select * from sys.all_tables where TABLE_NAME=''DUAL'''.

selectTime1 := [
1 to: loopCount do: [ :i|
        db execSql: sql.
    ].
] millisecondsToRun.

"Print out the miliseconds spent."
('Select without using statement caching: ' + selectTime1) out.	

db logOff.


"Turn on Statement Caching."
ObjectStudio.OracleDatabase useStatementCaching: true.

"Logon to the Oracle Server."
ObjectStudio.OracleDatabase 
	logOnServer: #'OracleDB' 
	user: #'username' 
	password: #'pwd' 
	alias: #'OracleDB'.

"Get the Oracle database instance."
db := ObjectStudio.Database accessName: #'OracleDB'.

selectTime2 := [
1 to: loopCount do: [ :i|
    db execSql: sql.
	]
] millisecondsToRun.

"Print out the miliseconds spent."
('Select using statement caching: ' + selectTime2) out.	

db logOff.

If you have trouble viewing that directly, you can click here to download the video directly

You can also watch it on YouTube:

Technorati Tags: smalltalk, oracle, objectstudio, database, statement caching