Improve Query Performance to Oracle

I recently had an issue where we were migrating a large Oracle database into Dynamics which required a significant amount of lookups back to Oracle for synchronization keys between both systems.

When we moved the system between different database environments we started to see the following errors.

“ORA-12801: error signaled in parallel query server P001\nORA-12853: insufficient memory for PX buffers: current 1632K, max needed 80640K\nORA-04031: unable to allocate 65560 bytes of shared memory (\”large pool\”,\”unknown object\”,\”large pool\”,\”PX msg pool\”)”  

As a developer, I get very worried when code changes are required between environments when all other variables stay the same (i.e., database, code, etc).  In this case however, we had been lucky that we had not run into this problem in DEV.

Where I was dynamically constructing the query on the fly, Oracle saw this as a new query being built every time (despite the only thing changing was the value in the WHERE Clause).  On their own these queries were fine, but when running about 50,000+ lead to some issues.

To get around the above error we leveraged the OracleParameter syntax as follows.

OracleCommand oraCommand = new OracleCommand("SELECT user FROM test.USER_LOOKUP WHERE user = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", userId));

Once implemented, we noticed a huge shift in performance and no more parallel query errors.  We had a lot of classes to change but were able to implement the change in a little under a day.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s