The 12c version has introduced a new features called “OCI Client-Side Deployment Parameters” this feature gives you the possibility to override some OCI parameters without even changing your application code.
Let’s make a simple test case using Python and the cx_oracle module (which use the OCI interface) for connecting to the oracle database.
You can find this module for your python version and the database 12c version here.
This is the code of our test case:
#!/usr/bin/python2.7 import cx_Oracle connection = cx_Oracle.connect ('test','test','testdb') for i in range (0,1000000) : cursor = connection.cursor () cursor.execute ('select 1 FROM dual') cursor.close () sql="select a.name name, b.value value from v$statname a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%parse% or a.name like '%session cursor%'" cursor = connection.cursor () cursor.execute(sql) rows = cursor.fetchall() for name,value in rows: print "%s : %d"%(name,value) connection.close ()
So basically, we execute this simple “select 1 from dual” 1 million times by opening and closing the cursor during each iteration.
Then we display our session statistics regarding parsing.
After executing that script and check the result :
session cursor cache hits : 16961 session cursor cache count : 7 parse time cpu : 176 parse time elapsed : 641 parse count (total) : 1000010 parse count (hard) : 2
The time consumed for parsing was around 6 seconds and as you can see was mainly soft parsing (only 2 hard parses have been performed for the two cursors opened in our code).
This is even better due to the fact that this cursor was already cached in our Private SQL Area(as observed by checking the session cursor cache hits statistic), so the soft parse was even more softer:)
But wait!! why the session session cursor cache hits is so small after 1M executions.
Adding those lines in the code :
cursor.execute ('select 1 FROM dual') sql="select a.name name, b.value value from v$statname a, v$mystat b where a.statistic# = b.statistic# and ( a.name = 'session cursor cache hits')" cursor.execute(sql) rows = cursor.fetchall() for name,value in rows: print "%s : %d"%(name,value)
You will see the following result :
session cursor cache hits : 65525 session cursor cache hits : 65527 session cursor cache hits : 65529 session cursor cache hits : 65531 session cursor cache hits : 65533 session cursor cache hits : 65535 session cursor cache hits : 1 session cursor cache hits : 3 session cursor cache hits : 5 session cursor cache hits : 7
So i guess that this statistic value is stored in a 16 bits integer and cannot store more than 65535.
I found the confirmation in this Christian’s post so this is not fixed yet in 126.96.36.199.
As i said earlier, with 12c you can now set override your application OCI parameters by using a file named oraaccess.xml.
This XML file needs to be in your TNS_ADMIN directory to be taken in account.
You could find an example in this directory $ORACLE_HOME/dbms/demo/oraaccess.xml.
So now let’s check if that works with the cx_oracle module.
You can set a number of clients parameters like arraysize, client result cache and in our case statement cache.
We didn’t implement the statement cache feature in our code but we will set the value for client statement cache to 1.
<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess" xmlns:oci="http://xmlns.oracle.com/oci/oraaccess" schemaLocation="http://xmlns.oracle.com/oci/oraaccess http://xmlns.oracle.com/oci/oraaccess.xsd"> <default_parameters> <prefetch> <rows>50</rows> </prefetch> <statement_cache> <size>1</size> </statement_cache> <result_cache> <max_rset_rows>100</max_rset_rows> <!-- 'K' or 'k' for kilobytes and 'M' or 'm' for megabytes --> <max_rset_size>10K</max_rset_size> <max_size>64M</max_size> </result_cache> </default_parameters> </oraaccess>
So now we run the python script again and check our result.
session cursor cache hits : 2 session cursor cache count : 6 parse time cpu : 0 parse time elapsed : 0 parse count (total) : 11 parse count (hard) : 0 parse count (failures) : 0 parse count (describe) : 0
As you can see there is no parse time anymore as the client uses its statement cache automatically.
In conclusion soft parsing is better but no parsing at all is possible even by not touching the code.