Every now and then, silver bullets work. For as long as I have been at Jobster, we have had issues with high CPU loads on our Oracle 10g server. We’ve been slowly working on it, finding inefficient queries and pages and fixing them. But on Monday, we really were on the edge. Our CPU usage was over 90%.We spent the day trying to find a reason, turning different features off to help reduce the load none seemed to solve it. Until we discovered that Ruby on Rails ActiveRecord did not actually send parameterized queries.
How QP’s Work
Database queries are processed in steps. The first step is to parse the SQL into a tree representation for the database system in question. Next, the query optimizer applies rules, statistics, and voodoo magic to reorganize the relational algebra into a more efficient execution plan. Finally, the execution plan is used to fetch the data to satisfy the query.It is expensive to parse queries, optimize, and produce an execution plan. CPU expensive. So one major performance enhancement is to cache query plans to skip all the steps and go right to execution. This happens if the exact same query is executed or if the query is parameterized on field values that are easy to apply at run time (like a condition in a WHERE clause).
Solution
Unfortunately, ActiveRecord doesn’t do parameterized SQL. Instead, it produces a full literal query each time. Very expensive if you do it over and over and over. Even when the query is 90% the same except for one value (like a user id).We found a hint on an Oracle setting for CURSOR_SHARING. We set it to SIMILAR which means that Oracle will now take our literal SQL and attempt to automatically parse the literals out of the query and create a parameterized version. This allows it to find a match for the plan.It worked. Not just a little but enormously well. We dropped CPU usage down to around 20% with the same traffic load. I think I spent the rest of the day trying to figure out if we did something wrong. It’s pretty rare to change one setting and have this big of an effect.So, if you run RoR + Oracle, take a look at this setting. It is particularly useful because ActiveRecord produces tons of queries that are very similar. And ask Oracle why this isn’t the default behavior.
Comments 2
although they really should support bind variables….it’s one of the biggest performance hits out there (besides using rules-based vs. cost-based optimization).
Back in the day I remember that was the first thing I looked at when looking at some n-tier app’s performance. A lot of people didn’t use them and simply tuning their top 10 queries would often reduce DB load by 90% or so.
Posted 30 Oct 2006 at 11:28 ¶Totally agree. We are working on putting in real bind support. It seems particularly critical for Rails because ActiveRecord fits parameterized SQL very well.
Posted 30 Oct 2006 at 12:18 ¶Post a Comment