Today's Smalltalk Daily looks at using Statement Caching against Oracle (version 9 and up) with VisualWorks. 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:

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

conn := OracleConnection new. 

"Verify whether the loaded OCI supports Statement Caching."
conn supportStatementCaching.

"Set to use statement caching."
conn useStatementCaching: true.

conn environment: 'ORACLEDB'; 
        username: 'username'; 
        connect: 'password'. 

"Get Statement Cache Size, the default is 20."
conn getStatementCacheSize.

"Set Statement Cache to desired size."
conn setStatementCacheSize: 30.

"Verify the new Statement Cache Size."
conn getStatementCacheSize.


t1 := Time millisecondsToRun:[

       " | conn sess ansStrm |"

     100 timesRepeat: [
		sess prepare:  'select * from sys.all_tables where TABLE_NAME=''DUAL'''.
		sess execute.
		ansStrm := sess answer.
		res := ansStrm upToEnd.
	].
].
Transcript 
	cr; 
	show: 'Time spent when using Statement Caching: ', t1 asFloat printString.

conn := OracleConnection new. 

"Set to use statement caching."
conn useStatementCaching: false.
conn environment: 'ORACLEDB'; 
        username: 'username'; 
        connect: 'password'. 

t2 := Time millisecondsToRun:[
       " | conn sess ansStrm |"
     100 timesRepeat: [
		sess prepare:  'select * from sys.all_tables where TABLE_NAME=''DUAL'''.
		sess execute.
		ansStrm := sess answer.
		res := ansStrm upToEnd.
	].

].

Transcript 
	cr; 
	show: 'Time spent without using Statement Caching: ', t2 asFloat printString.

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, database, visualworks, statement caching