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.

I’m sure you all know to use DBMS_STATS to gather statistics for your tables and indexes. The DBMS_STATS.GATHER_TABLE_STATS() method has as its first three parameters OWNNAME, TABNAME, and PARTNAME, specifying the schema owner name, table name, and table partition name, respectively.

I had been assuming that specifying a partition name via the PARTNAME parameter would mean that I’d be gathering stats for just that partition. Wrong! You also need to specify GRANULARITY => PARTITION if that is your goal. Otherwise the PARTNAME is simply ignored and global (table-wide) statistics will be gathered instead.

Thanks to Wolfgang Breitling for the cluestick.

I came across this post about Oracle partitioning licensing a couple weeks ago (yes I’m catching up with all the blogging). In it, Mathias basically doesn’t think that partitioning should be separately licensed at all, and included in ALL versions of Oracle.

For those of you who haven’t had the pleasure:

To use partitioning, you have to pay for the Enterprise Edition of the database at $40,000 per CPU and then pay an additional $10,000 for the partitioning option. That is a lot of money if all I really need is SE One (at $5,000 per CPU) with Partitioning. Unfortunately, that is not an option.

As we used to shout at band camp, “$10,000? WHAT A SCREW!” Same thing goes for the Diagnostic Pack, one of the greatest inventions of mankind.

I’d at least settle for making partitioning included in EE and a pay-for-play option in SE. Of course, then who would pay for all of this?

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.