23 April 2008

Bind Variables and Parallel Queries Do Not Mix

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 "worst 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.

18 April 2008

Don: 1 vs. 1Z0-043: 0

Passed the 1Z0-043 exam today, otherwise known as the OCP exam. Just need some other jazz like my hands-on course requirement form to be processed and then I should get my badge and gun and key to the executive washroom.

Thanks to Bradd Piontek for reminding me of how silly it all is, inspiring me to pass it even more so that I wouldn't look even more foolish failing a silly exam.

Some Comic Humor For You

married to the sea comic

From Married To The Sea, one of my favorite web comics.

14 April 2008

ALTER TABLE doesn't like synonyms

Something new I found out today. When performing an ALTER TABLE statement (in this case to add a column) in another schema for which you have a synonym (public or private), you must refer to it with the schema qualifier, e.g. HR.EMPLOYEES rather than just EMPLOYEES. It seems the DDL statement doesn't bother itself with synonyms. Probably a good thing, as DDL is nothing to be taken lightly and it's best to fully spell out what it is you are mucking about with.

In the example, my user has been granted the ALTER ANY TABLE privilege, as well as SELECT on HR.EMPLOYEES. I've made a private synonym in my own schema, also named EMPLOYEES. Let's see what happens:

SQL> alter table employees add foo number;
alter table employees add foo number
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter table hr.employees add foo number;


Table altered.