We’ve all been there. You have an application bought from some vendor that has less than adequate knowledge about the Oracle Database. The solution underperforms even though the SQL itself looks OK. You dig into it and it turns out the solution retrieves a lot of data but each fetch gets too few rows from the database and thus the latency of getting more data is 99% of the time it takes.
The result is of course that there is no way to tune this in database, fixing it by finetuníng the app-server is not possible either. The only way to improve it is to fix the fetching. But you have no access to the source code and the vendor is not interested to help at all.
I had this happen to a friend that contacted me some months ago. I have been asked to not mention the name of the person nor the company, so shoutout to my mysterious good friend X. 🙂
Anyway, the deal was that Oracle look suspisiously slow compared to alternatives the same code could run against. Some inspection showed that virtually identical SQL was used. Not believing Oracle would be slower for pure data extraction we looked into what really happened. I turns out it did many more network trips.
How can we get around this without changing the code? I found a nice little feature for OCI-drivers that allows defaulting quite a few things. While the code defined a very small array to use it did not configure prefetching.
In the TNS_ADMIN location we can place an XML-file with contents like the following. The file should be named oraaccess.xml.
<?xml version="1.0" encoding="ASCII" ?>
<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>10240</rows>
</prefetch>
</default_parameters>
</oraaccess>As you can tell, in this case we only set a default for prefetch. This means it pulls a number of rows and then each array-fetch gets some of them until there are no more to get locally and another prefetch is made from the database.
You can read more on this and other possible settings in the docs. That one is for version 21, but this functionality has been available since 12c.
This helped improve the performance significantly and Oracle was once again the preferred solution. The response time was more than cut in half.
I’m petty enough that I recommended them not sharing it with the vendor. If they wanted the solution without participating in the work, they ought to pay a lot for it. Like no support fees for five years or some such.
What else could this be used for? Quite a lot. You can specify statement caching, you can set FAN (Fast Application Notification) and ONS (Oracle Notification Service), connection pool settings for UCP (Universal Connection Pool) as well as settings per connection alias to allow different connection strings to get different defaults.
One more way I think this can be used is to allow for fast testing of different settings. The typical way to test some of this is of course to make code changes and get it to a test environment. With this one can just plug in a setting in the file and run it without needing any code changes.