die Seilerwerks

This post was promised long ago, and I apologize for the tardiness. Some of you may recall my whining about seemingly unexplainable instance hanging since migrating our database to 64-bit hardware in September. Well, after some back and forth and hand-offs from one rep to another, we finally were given a possible explanation: Bug 4367986. The summary of the bug is “bind peeked parallel cursors do not share.” This basically means that parallel queries that use bind variables won’t share cursors. Not only does this defeat the purpose of using the bind variables, but it creates a new cursor for each parallel process. After a while, your cursor count will go up, just as mine did:

select sql_id, count(*)
from v$sql_shared_cursor
where bind_peeked_pq_mismatch='Y'
group by sql_id;

SQL_ID          COUNT(*)
------------- ----------
f3u64ru922snx        520
ckha07wkfaf8v          5
9g26upcqjh8kp          1
gdnga6d26vf4g         15

While I wasn’t able to choke and hang the instance in development, I was able to drive the count up as we saw in production.

We probably didn’t see this before our x86_64 migration because on our 32-bit instance, our parallel_max_servers was only set to 16. After migrating to the new hardware it was raised to 80 based on “the formula”. Dropping it to 0 obviously prevented the problem from coming up as well.

The bug is reportedly fixed in 10.2.0.4, which wasn’t released at the time we were finishing up the SR. There was only a one-off patch for 10.2.0.3, meaning we had to upgrade from 10.2.0.2. Well we did this, applied the patch, and haven’t had a reoccurrence of the problem since. That query listed above now happily returns no rows.

Of course there was also the issue of using bind variables in queries against partitioned tables. Greg Rahn had this to say:

Using PQ with binds can have other adverse effects, specifically if
the partition key is not provided as a literal. When the partition
key is a bind, the resulting plan will be a KEY-KEY plan (for
pstart/pstop) because w/o a literal value the optimizer can not tell
if there is any partition elimination since the literal value is not
provided at parse time. This often times results in a “wost case”
assumption, thus is it possible to have different plans even when the
bind and literal statements use the same values.

I would speculate that the overhead of parsing literals when using PQ
is minimal compared to the side effects it is causing (due to the bug)
and the potential of suboptimal plans. I personally would never mix
the two.

A week or so ago, I was working with a developer to find out why a SQL statement he was sending would sometimes work and sometimes return an invalid DATE format error. Part of the troubleshooting led us to examine the actual bind values being received by the server, just to make sure it was the same as being sent. I thought I was onto something when I saw that the fields of TIMESTAMP datatype actually were NULL:

SQL> select name, datatype_string, value_string
from v$sql_bind_capture
where sql_id='0wp5c2a3z82jr'
SQL> /

NAME DATATYPE_STRING VALUE_STRING
---- --------------- ------------
:1   TIMESTAMP       {NULL}
:2   VARCHAR2(32)    3WC
:3   VARCHAR2(32)    001
:4   TIMESTAMP       {NULL}
:5   NUMBER          8429721
:6   NUMBER          206

6 rows selected.

Unfortunately it was like this when the query both failed and succeeded. I banged my head against the wall for 30 more minutes before concluding that it must be a bug. Metalink agreed, and Note 444551.1 details the bug, which is not yet fixed and “should be fixed in 11.2,” affecting versions 9.2 to 11.1. Seems kind of long for something like this to still be out in the open, and it’s very annoying when you really want to see the value of a TIMESTAMP bind variable.

This is one of the many reasons you use bind variables.

Recently one of my developers found some articles by Arup Nanda while researching a query performance problem. The articles were title “Perils and Pitfalls in Partitioning,” parts 1 and 2.

In particular he was curious about this statement in part 2:

While using partitioning, should you use bind variables?

This is an interesting question. As we all know, use of bind variables eliminates the need to parse the cursors and makes it easier to reuse the cursors.

In case of partitions, however, using bind variables poses a problematic situation. Partition elimination and joins can occur only if the optimizer knows the filtering predicate in advance. The value of bind variables are not known until it’s time to execute, making the process of partition elimination or joins impossible. Therefore, to take advantage of these options, you should not use bind variables.

In Oracle 9i, the first parse of the statement, called hard parse, peeks into the value of the bind variable, and can effect these optimization options. But this occurs only with the hard parse; subsequent parses still go around the bind variable values.

I had come across this explanation before, about how Oracle will peek at the bind variable when it hard-parses a query, and then generate a potentially horrendous query plan geared specifically towards that first bound value. By this token, the general sentiment is that use of bind variables for queries against large, partitioned (warehouse-y) tables was probably not in the best interest. It was also given that such statements would be run infrequently compared to your normal set of OLTP tables where bind variables will save your bacon.

I did google more about it and found something indicating that it shouldn’t be an issue in our version of 10.2.0.2. Unfortunately, this event occurred 3-4 weeks ago, and I neglected to bookmark that source. I’ll follow-up in this space if I do get a final answer. Perhaps a loyal reader can steer me straight.

This December post from Jonathan Lewis doesn’t suggest that the issue was ever “fixed” (if you can call it a bug). Perhaps I was daydreaming or going through a chewing gum withdrawal delirium.

So sayeth John Paul Jones (not this one), so sayeth I.

I’ve been taking steps to make sure my developers know of the many benefits of using bind variables, and the punishments for not using bind variables. This is paying off in the newer code coming out, but it will take a while to re-write some of the older applications.

Today, while tracing another query, I saw that one of the back office applications that dealt with batch rating was using a query without bind variables. In the timeframe of 1 hour this morning, the same query with a different literal value was called over 21,000 times. The same app was bulk issuing two other queries using literals as well. Let’s just say that it was made certain that this needed to be changed.

This past Wednesday in #oracle, I asked how one views what values are being used in a SQL statement with bind variables. hali led me to v$sql_bind_capture. We played with some joins to v$sql and v$session and all was well. I immediately recognized the blog-worthiness of the discussion and tagged the concept for later blogging.

Well hali beat me to it. From now on I’ll just start blogging about things before I even have all of the information. For now you can read his post to see the usefulness of the v$ view. And leave comments about how he’s a dirty Scandinavian thief!

Jonathan Lewis has a great piece on the importance of using bind variables in INSERT statements as well, avoiding expensive check constraint revalidation.

Eddie Awad posted (in June) a nice flowchart of what Oracle does when it processes a SQL statement. Of particular import is the note in step 2 (parsing):

“If a similar SQL statement exists in the shared pool, Oracle skips the parsing step. A SQL statement is parsed once no matter how many times the statement is run. As you can see, parsing does many things and consumes time and resources. You should always aim at minimizing parsing when writing SQL.”

How do you minimize parsing? Bind variables! Now go re-write all of your applications to use them.

NOTE: I’m re-pasting this from an internal wiki page I wrote up for our new developers who may not be aware of such a thing as bind variables and how they can greatly improve performance and scalability.

Most know that using bind variables help greatly with performance when dealing with an often-used query, such as a customer lookup. Read Mark Rittman’s article, which does a great job of explaining all there is to be explained in that regard.

A quick summary that Oracle has to re-parse literal queries everytime, generating new query plans every time and storing that processed query in memory. With bind variables, only one such process, parse and memory storage takes place and that information gets re-used when that bind query is called again with different bound values. This saves on the CPU having to re-parse and re-plan the query, and saves on memory by not having to store yet another query plan.

To get an idea of how important DBAs consider the use of bind variables, read Tom Kyte’s thrashing of a user who was unlucky enough to incur his wrath.

Read about Using Bind Variables to Guard against SQL injection. SQL injection exploits are one of the most common attacks on database-driven websites, as attackers would exploit the fact that a SQL query such as user validation is built with literals, and they could turn this query:

SELECT * FROM usersWHERE username='dts'and password='foo'

into

SELECT * FROM usersWHERE username='dts'and password='foo' or 1=1

Using bind variables prevents such exploits from happening due to how the values are substituted.