Paradise Delayed

boseAlas for promised pleasures!

My wireless headphones turned out to be a bit of a disaster. Not that there was much wrong with their form or sound, but the padding round the earpiece turns out to be attached to the main headphone structure by the flimsiest piece of plastic ‘skirting’ you’ve ever seen. One brush with a jacket, or one removal from their carrying bag that brushes the wrong way, and the padding pops loose. And getting it to re-attach took me (on my first try) over 5 hours. Second time, I managed it in about 10 minutes. Third time, I gave up and asked ToH to try… and about 6 minutes later, they were usable once more.

Such fiddliness I cannot deal with -and I already mentioned last time that one had to be super-careful with the way you moved your arms lest a waft of air trigger (best case) the music playback to stop or (worst case) the headphones to ask my phone to redial the last number it called.

Back to the shop they went this afternoon. I wasn’t sorry to see them go, though I am surprised that Sennheiser produced such a weirdly disfunctional product.

In their place (and some $60 cheaper), I got a pair of Bose SoundLink on-ear headphones. Once bitten, twice shy: no recommendation or glowing plaudits on these as yet. But I am assured that their padding attaches more robustly than their Sennheiser forebears managed; the pairing with my non-Apple phone was straightforward; and the sound quality is excellent -neutral and clear, and no excessive bass at all. Sound leakage to the outside world appears also to be minimal. Fingers crossed they turn out to be what I was actually after all this time.

The other bit of bad news this week was that large dollar figures don’t buy you the level of service they once did: turns out the car showroom can’t cope with vehicles being picked up on Saturdays, and would we mind doing it on Monday instead? So we are new-Mercedes-less until tomorrow afternoon -which I am a bit annoyed about.

I suppose one just has to learn to cope, however…

 

Impulse Drive

Almost two years ago, I needed a new USB hard disk and accordingly got driven to the local computer bits-and-pieces store to get one. It happens to be located a couple of blocks down the road from the nearby Mercedes car dealership. So, since I had my hard drive, ToH decided that a bit of window-shopping in the Merc showroom wouldn’t be amiss.

A week later, we took delivery of our convertible E200.

It was perhaps the single most expensive hard drive I’d ever purchased, but I’ve enjoyed being driven with the roof down many, many times since, so I figure it was worth it in the end.

Today we popped out to see if we could find any plausible replacements for some of our interior doors, as you do. It happens that the door showroom is quite near to the hard disk drive shop. And thus…

merc2

I wasn’t exactly dressed for the occasion, but after all: it was a bit of an impulse buy! It’s pretty much the same car as before (an E250 convertible, instead of an E200), but with a sports pack and some nice interior trim features we didn’t have before. It also allegedly does a fancy trick of adjusting its speed automatically to keep a set distance from the car in front of you. Since I still don’t have an Australian driving license, however, such niceties of function are lost on me… I just wanted the roof to fold back and the thing to look pretty darn’d sexy. (It’s true I could use a life, I fear).

We pick her up properly on Saturday next.

And I have been forbidden from shopping near car dealerships ever again 🙂

Un-Entangled

I live about 100kms from the city, and that requires a commute. Since everyone and their dog these days thinks that [shrieking]|[snoring]|[broadcasting their lastest hip-hop to everyone in the carriage] is fine and dandy, I’ve been forced to plug in some headphones and try to drown out the racket with some Bach, Handel, Queen or whatever else I happen to have copied to my phone’s SD card.

square_louped_cx_215_blue_01_sq_music_portable_sennheiserFor a couple of years, I’ve made do with the headphones you see at the left.

Sennsheiser CX 215 in-ear headphones.

They have provided what I consider to be excellent sound quality, but their rubbery ear-bud thingies (I am sure there’s a more technical term for them!) have long had a tendency to pop off at the drop of a hat. I have been extraordinarily lucky, in that every time that’s happened, I’ve been able to find the missing rubbery bit… but if I hadn’t been quite so fortunate, I’d have been the proud owner of one usable earpiece and a plastic stalk that was no use to anyone.

Additionally, I have a laptop carry bag that gets carried over my shoulder. If I’m wearing the earpieces and then put the laptop carry strap on, I end up with a phone that gets knotted. Do the wake-up-in-a-hurry-when-arriving-at-your-stop routine that is all-too-common on a hot afternoon after a long day at work and it’s fair to say that I often walk off the train looking a bit like this:

tangle1Not a good look and I’ve been dropping heavy hints to ToH for a long time that some sort of wireless headphone arrangement would suit me better (and make me look considerably less stupid).

Yesterday, for no objective reason I could discern, I was finally granted my wish and my head is now the proud carrier of these:

bluetoothheadphonesSennheiser Urbanite XL Wireless (i.e., Bluetooth), light on the head, comfortable over the glasses. And no cables!! Woo-hoo. Entanglement problems solved at a stroke! (Pairing with my Android phone was an absolute doddle).

I suppose I really wanted wireless and noise-reduction, but apart from a few (extremely pricey) exceptions, you tend either to get wireless or active noise-reduction, but seldom the two together. Not in my price bracket, anyway…

As it happens, the noise reduction bit is really irrelevant: the ear cups effectively block out an enormous amount of noise all by themselves. Which is the whole point of the exercise, I feel!

The sound quality is (entirely subjectively, of course) a lot better than the ear-buds I was using: a lot more bass (which worried me to start with, because I’m keen on listening to classical music as-is, without boosting this or that), but on listening carefully (and comparatively, with the home hi-fi), it’s merely sounding what’s actually there, rather than artificially boosting it.

The right ear-cup has a “panel” bit which you can tap to advance a track, or slide to increase/decrease the volume. It’s frankly too bloody complicated for me to work out: I just play what I want and pick the volume I want on the phone, and that suits me fine. Occasionally, if I raise my arms to yawn (it happens!), I suppose I must brush it and cause playback to abruptly cease, which is a bit annoying. Short version: the headphone control is a bit over-sensitive to movement, quite fiddly to use as Sennheiser intended and more trouble than it’s worth; but for the way I intend to use them, I can live with it.

Battery life is allegedly 25 hours after a 2-hour charge, which is plenty for my purposes. I can even be listening to a full orchestra in bed, right next to ToH, who swears that nothing at all can be heard: sound ‘bleed’ is practically non-existent, in other words. Which means I can show my fellow commuters rather more consideration than most of them show me.

And, more importantly, I can wake up with a start and walk off the train looking vaguely like a normal human being once more… which is as it should be!

 

 

Heffalumps and Woozles

heffalumpsThe article about using clooney.sh to configure Solaris as a platform on which to run Oracle 11gR2 or 12c is finally complete.

Which means you have the perfect environment in which to replicate my recent bug-hunting expedition!

Take an 11gR2 (i.e., 11.2.0.3 or 11.2.0.4) database running on Solaris (or Linux or Windows, come to that), and create a non-SYS user with DBA privileges:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create user hjr identified by hjr;

User created.

SQL> grant dba to hjr;

Grant succeeded.

Now, connected as that new non-SYS user, here is a little script to create and populate two tables, one partitioned and one not (so you need the Enterprise Edition for this, otherwise you can’t use partitioning):

create table ABC (key  NUMBER(10) not null);
insert into abc values (10);
insert into abc values (11);
insert into abc values (9);

create table DEF (key  NUMBER(10) not null)
partition by range (key)
(partition p0 values less than (10),
 partition p1 values less than (11),
 partition p2 values less than (12),
 partition p3 values less than (13));

alter table ABC add primary key (key) using index;
create index DEF_IX on DEF (KEY) local;
 
insert into def values (10);
insert into def values (11);
commit;

Table ABC contains three rows; table DEF contains 2 -and they happen to share values with two of the rows in the ABC table. There’s an index on both tables, too: on the partitioned table, the index is locally partitioned.

Now, create a little procedure that does nothing more than join the two tables and select for values greater than or equal to something:

create or replace procedure codetest (v_k IN number)
is
  v_txt varchar2(1000);
begin
  select
  abc.key
    into v_txt
    from abc, def
  where abc.key = def.key
    and abc.key >= v_k
    and rownum < 2;
  dbms_output.put_line(v_txt);
end;
/

So you can execute that procedure like so:

SQL> set serveroutput on
SQL> exec codetest(10);
10

PL/SQL procedure successfully completed.

SQL> exec codetest(11);
11

PL/SQL procedure successfully completed.

So both values 10 and 11 work fine, and the little select statement contained within the procedure correctly fetches both values as required. So far, so normal.

Now do this:

alter index DEF_IX MODIFY PARTITION p1 unusable;

That renders the p1 partition of the index on the DEF table unusable. Note that partition p1 would hold values “less than 11 (and greater than 9)”, so no way, no how is value 11 involved in that partition. Now run the procedure again, selecting for value 11:

SQL> exec codetest(11);
BEGIN codetest(11); END;

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "SYS.CODETEST", line 5
ORA-06512: at line 1

Though partition p1 cannot be relevant for the query, the query fails declaring that “not all variables bound” …a classic error that you often get when submitting SQL queries using bind or substitution variables (along the lines of “select * from emp where ename=:B1”), and when you’ve forgotten to assign a value to one of the variables.

But there are no bind or subtitution variables in this code, except in the sense that v_k is one… and that definitely has had a value assigned to it. So why on Earth is the ORA-01008 being raised?

When this happened on one of my production databases, Artem (my esteemed co-DBA) and I puzzled about it for ages. We suspected corruption (but DBMS_REPAIR said all was fine). We re-wrote the select part of the PL/SQL code every which way we could think of, just in case some syntactical issue was tripping up the optimizer. We did this, we did that… but then Artem did the following:

SQL> alter index DEF_IX REBUILD PARTITION p1;

Index altered.

SQL> select status from dba_ind_partitions where index_name='DEF_IX';

STATUS
--------
USABLE
USABLE
USABLE
USABLE

(So we know that all the index partitions are fine once more). And:

SQL> alter index DEF_IX MODIFY PARTITION p1 unusable;

Index altered.

SQL> exec codetest(10);
10

PL/SQL procedure successfully completed.

SQL> exec codetest(11);
11

PL/SQL procedure successfully completed.

So this time, we’ve marked the same partition as before as unusable before we do anything else, and then we run the procedure selecting for value 10… which is involved in the index partition we’ve just invalidated. And yet the code still works! And, even more surprisingly, if the procedure worked fine on value 10, it goes on to work fine on value 11 too -the same value it had previously failed at in the presence of the same unusable index partition.

So then we immediately did this:

SQL> alter system flush shared_pool;

System altered.

SQL> exec codetest(11);
BEGIN codetest(11); END;

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "SYS.CODETEST", line 5
ORA-06512: at line 1

Just by flushing the shared pool, the code reverts back to its previous behaviour, where it raises an ORA-01008 for value 11 …the same value it just worked for!

I’ve no way of knowing precisely what is going on, but it seems pretty apparent we have a bug here -but one that took a lot of hunting down! The ingredients needed to make the bug reveal itself would seem to be:

  • Joining a non-partitioned table to a partitioned table
  • The non-partitioned table has values less than any of the values in the partitioned tables
  • The two tables share at least two values
  • You query to retrieve the higher of the two values in the partitioned table that overlap/match with values in the non-partitioned table. In this case, the overlapping values were 10 and 11 -query for 10 and the ORA-01008 never arises. Query for 11 in the presence of unusable index partitions and the non-existence of a previously-compiled shareable cursor and the ORA-01008 errors come thick and fast.
  • And your query must use “>=”. If it selects for “<=” or “=”, then the index invalidation never causes the ORA-01008 to appear, no matter what index partitions are unusable or what value you select for.
  • You have to have the initialization parameter skip_unusable_indexes set to TRUE (which is the default in 11g). If it’s set to FALSE, then the existence of unusable index partitions never causes an ORA-01008 to be raised under any circumstances.
  • You’re not doing this as SYS. SYS seems to be immune from whatever code-path causes the ORA-01008. Non-SYS users can be victims of it!

If you get all those conditions met, then marking an index partition unusable seems to cause the internal, recursive SQL (that’s generated under the hood whenever you make use of the database) to be using a bind variable to which no value has been assigned. The ORA-01008 is therefore “correct” in the sense that there’s genuinely a bind variable somewhere that’s missing a value, but it’s a problem with Oracle’s own internal SQL, not the code you’re running.

If you happen to query for value 10, you don’t encounter the problem in the presence of an unusable index partition -and the cursor that’s created (and stored in the library cache) when your query is successfully parsed is then available for use by the procedure when you then make it query for value 11. So if you’ve successfully queried for value 10, you can successfully query for value 11 (or any other value), because a parsed cursor is already at your disposal. But flush the shared pool, and that cursor is wiped -so that when you now select for value 11, a new cursor has to be compiled… and the problem with the recursive SQL needed to make that happen then rears its ugly head once more.

Happily, the bug would seem to have been fixed in 12.1.0.2, but 11.2.04 remains supported for a good few years yet! Therefore I’ll be raising an SR soon enough, and we’ll see what Oracle has to say about it. I’ve already raised an Oracle forums question about it, and got some useful suggestions which got us on the right track (though quite a few more comments about the quality of my code than were strictly warranted, I feel! Why do people look at a forest and see trees quite so often?)

There are so many variables that make the difference between the execution of the procedure fail or succeed, though, that this truly did feel like Pooh and Piglet felt when hunting Woozles. And at the end, I felt as if I’d fallen in a Heffalump trap for Poohs (or Piglets):

“I have been Foolish and Deluded,” said he, “and I am a Bear of No Brain at All.”
“You’re the Best Bear in All the World,” said Christopher Robin soothingly.
“Am I?” said Pooh hopefully. And then he brightened up suddenly.