Category: Oracle

I’ve said it before and I’ll say it again, Oracle products don’t run properly on Tru64 anymore!

The latest issue came from an observation by a developer and involves problems with using NVL inside an EXISTS subquery. The following code is the test case I sent in my TAR to Oracle support.

CREATE TABLE tab1 (
code         VARCHAR2(10),
description  VARCHAR2(50)
);

ALTER TABLE tab1 ADD (
CONSTRAINT tab1_pk PRIMARY KEY (code)
);

INSERT INTO tab1 (code, description) VALUES ('1', 'ONE');
INSERT INTO tab1 (code, description) VALUES ('2', 'TWO');
INSERT INTO tab1 (code, description) VALUES ('3', 'THREE');

CREATE TABLE tab2 (
code         VARCHAR2(10),
tab1_code    VARCHAR2(10),
description  VARCHAR2(50)
);

ALTER TABLE tab2 ADD (
CONSTRAINT tab2_pk PRIMARY KEY (code)
);

ALTER TABLE tab2 ADD (
CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_code)
REFERENCES tab1(code)
);

CREATE INDEX tab2_tab1_fk_i ON tab2(tab1_code);

INSERT INTO tab2 (code, tab1_code, description) VALUES ('1', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('2', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('3', '1', 'ONE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('4', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('5', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('6', '2', 'TWO');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('7', '3', 'THREE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('8', '3', 'THREE');
INSERT INTO tab2 (code, tab1_code, description) VALUES ('9', '3', 'THREE');

COMMIT;

VARIABLE p1 VARCHAR2(1)
BEGIN
:p1 := '%';
END;
/

SELECT t1.code
FROM   tab1 t1
WHERE  EXISTS(SELECT rowid
FROM   tab2 t2
WHERE  t2.tab1_code = t1.code
AND    t2.code like :p1)
ORDER BY t1.code;

CODE
----------
1
2
3

3 rows selected.

SELECT t1.code
FROM   tab1 t1
WHERE  EXISTS(SELECT rowid
FROM   tab2 t2
WHERE  t2.tab1_code = t1.code
AND    t2.code like NVL(:p1,'%'))
ORDER BY t1.code;

CODE
----------
1
1
1
2
2
2
3
3
3

9 rows selected.

SELECT t1.code
FROM   tab1 t1
WHERE  EXISTS(SELECT rowid
FROM   tab2 t2
WHERE  t2.tab1_code = t1.code
AND    t2.code like COALESCE(:p1,'%'))
ORDER BY t1.code;

CODE
----------
1
2
3

3 rows selected.

From this you can see that then the bind variable is set to ‘%’ the query without the NVL works as expected, but the query using the NVL displays a row in the master table for each row found in the subquery. In comparison, the query using the COALESCE function works fine.

Now I realise that this is not a great query anyway, but you would still expect it to work!

Happy days in Tru64 land again 🙂

Once again I’m drawn to the inevitable conclusion that running Oracle products on anything but a tier-one platform is a bad idea. What’s more, judging by the recent arrival of Oracle 10g Release 2, there is now only a single tier-one platform. I don’t see a version for Solaris, HP-UX or Windows yet.

As for me, I’m still waiting for 10.1.0.4.0 to be released on Tru64. Release 2 is a long way off 🙂

Cheers

Tim…

I was recently asked to review a product called SQLdetective, so here it is.

Q. What’s it for?
A. It’s for developers and DBAs and anyone else who does anything with an Oracle database.

Q. What features does it have?
A. It has everything you’ve come to expect from a general purpose DBA/Developer IDE. I’m not even going to attempt to list them because it would take forever. Suffice to say, if you’ve seen it in another product, it’s probably here.

Q. What’s it look like?
A. You can look at a bunch of screen shots here. Like most current IDEs it’s a bit busy. Not as plain as PL/SQL Developer, but not as busy as TOAD. The icons are “different”, but I’m not sure if that’s a good or bad thing.

Q. What’s it feel like?
A. I seems quite quick and responsive, but it does some odd things, like when you highlight a table in the navigator the default operation is to show you a page of data. I doubt this would be the first thing I would want to see.

Q. What’s it cost?
A. The basic price is US$99, but if you want all the add-on modules it’s going to cost a total of US$198. So the cost depends on what you need. It’s difficult to compare prices because you rarely have to pay list prices and each IDE has different optional modules. It’s more expensive than the free version of TOAD, and now JDeveloper is free you can do all your PL/SQL development and debugging using that if you’ve got the hardware to run it 🙂

Q. Tim, would you buy it?
A. If I was forced to use an IDE I would use TOAD or JDeveloper for free. I have both loaded on my PC and I never use them. If I was forced to buy a license for an IDE I would consider this product. My company recently bought licenses for PL/SQL Developer, but I still use a text editor and SQL*Plus 🙂

Conclusion:

If you really want to pay money for an IDE give this one a try. It’s got a lot of stuff in it. Alternatively, write good intrumented code and save yourself the cash.

I guess I could just copy and paste this review about almost any of the current IDEs, but at least I’ve kept my promise…

Cheers

Tim…

Looks like Firefox 1.0.6 is out, but the update checker doesn’t know it… Again.

Got through a night without a server crash on the production RAC. Feels like the old days again 🙂

Cheers

Tim…

PS. Posted two more chapters to the publisher today. That leaves me with one new one to write and one to tighten up then I’m finished. Must crack on and get it sorted before I have a nervous break down cause by a complete lack of sleep 🙂

It’s been an “interesting” couple of days.

Since we moved to the new configuration we’ve have had some intermittent server crashes, including one last night where both nodes crashed simultaneously. We’ve applied an OS patch that is meant to solve the problem, but we’re still in the “fingers crossed” stage at the moment. It’s meant some busy nights and annoyed users, but we’re moving forward.

You’ve gotta love it…. Not!

Cheers

Tim…

This weekend our production system was switched from a 3-node to a 2-node RAC.

We were originally using a 2-node RAC (2 CPUs per node) and we added a third node because the system was struggling to cope with the workload. The third node helped us out in some ways, but it caused a lot of trouble in others. Ever since it’s inclusion it became impossible to take one node out of the RAC without bringing the lot crashing down, so much for high availability. In addition, a substantial proportion (about 30%) of the wait states on the system were due to inter-node communication. Now I expected with more nodes there would be more inter-node communication, but it seems a bit excessive. Heaven only knows what would happen in a 4-node cluster…

After a lot of banter with Oracle and HP we’ve finally decided to try a 2-node RAC again, but this time with 3 CPUs per node. OK, it’s actually 4 CPUs per node, but one CPU in each node is permanently offlined, so as not to affect our current Oracle licensing.

All the hardware modifications are complete and all tests indicate that the system is up and running normally. Of course the true test will happen tomorrow morning when the users log in and start to break things 🙂

The best news of all is that the move back to a 2-node cluster means that we can once again shut down one node at a time if we need to do maintenence. This is a big plus.

If everything goes quiet over the next few days it means that I’m fire-fighting and the switchover didn’t go well.

I’d be curious to see how many people out there are using RAC on more than 2 nodes. I’ve only done this on Tru64 with 1og Release 1, but I can say without a shadow of a doubt that it doesn’t work properly. I’m curious if this is Tru64 specific problem or if there is a fundamental flaw in RAC for clusters with more than 2 nodes.

Cheers

Tim…

You’ve got to take your hat off to Oracle. Just when you thought they couldn’t make their pricing model any more ridiculous they come up with this gem. I guess we should be grateful that they’ve conceded this much. Prior to this announcement a single dual core processor was charged the same as two individual processors, even though it didn’t have the same performance.

Of course nobody pays list price for Oracle, but it is used to calculate the support and updates costs, so it’s in their interest to keep it artificially high.

I always find the TPC pricings a laugh. Oracle sounds real cheap until you realize they’ve only included a 3 year license. I’ve worked with Oracle products for over 10 years and I’ve yet to work for a company that has bought Oracle this way. They have all bought perpetual licenses for some serious wonga! Maybe we’re living in the dark ages in the UK 🙂

We’ve got some 3rd party applications running on mySQL and they work really well. Nice and cheap too. Well actually it’s free. We’ve got a few 3rd party applications running against SQL Server and they do the job nicely too. We’re looking to switch another project from Oracle to SQL Server part way through the implementation after some confusion over the Oracle licensing costs. It doesn’t take many incidents like this within a company before some momentum builds up and people start opting for cheaper alternatives.

Whenever anyone mentions licensing costs to me I hide under the desk. I’m a grunt, not an accountant!

Cheers

Tim…

Once again Oracle provides a less than perfect service on the support front. Let’s take a look at my latest encounter.

20-JUN-2005 – I raised an iTAR because the CC and BCC lists of the UTL_MAIL.SEND procedure don’t work. Emails are sent properly for people listed in the RECIPIENTS list, but mail to CC and BCC lists never get sent. I also sent this example code:

BEGIN
UTL_MAIL.send(sender     => 'me@mycompany.com',
recipients => 'person1@yourcompany.com',
cc         => 'person2@yourcompany.com',
bcc        => 'person3@yourcompany.com',
subject    => 'UTL_MAIL Test',
message    => 'If you get this message it worked!');
END;
/

Assuming these were real email addresses person1 would receive a mail, while person2 and person3 would not. I was able to repeat this issue on Tru64 and Windows.

Within a couple of hours support requested an OWC (Oracle Web Conferencing) session to investigate further. Unfortunately I never received the email of the iTAR update so I didn’t reply.

13-JUL-2005 – The iTAR gets updated asking me for an OWC session again. This time I get an email so I respond saying I don’t think an OWC session is necessary as there is nothing to show. The sample code says it all. In this case the OWC session seemed like complete waste of both our time.

14-JUL-2005 – The iTAR is updated requesting an OWC session again. I say OK and connect. During the 10 minute conference (accompanied by a phone call) my only input was to show the sample code, which was already in the iTAR. First I’m told the issue can’t be progressed as I’m not on the latest patch, to which I reply that 10.1.0.3.0 is the latest patch for Tru64. After that the support guy searches and finds a generic bug listed as being fixed in 10.1.0.4.0. If this bug had been public I would have found it and not raised the iTAR in the first place.

The iTAR is now closed.

Now I realize that the majority of the time wasted here is down to me waiting for an email that never came, rather than checking the iTAR status directly. Obviously, if this had been an important issue for me I would not have let it drag on so long, but the whole process took nearly 4 weeks to inform me that my problem was an existing bug. I think that’s pretty shocking, especially since the bug was found using the information from the original iTAR, not the subsequent OWC session.

Conclusion – I assume the support people work to quotas. By replying to ask a followup question or request an OWC session they can tick a box to say they’ve responded. I’m sure the statistics relating to response times at Oracle support make very impressive reading, but I believe the truth is very different.

I don’t have a problem with the support people themselves. Some are great and some are not. I just think the support process sucks! We pay a ridiculous amount of money for what I can only describe as a crappy service. These days I raise iTARs in an attempt to improve the product/documentation, not because I expect to get an answer. I’m more likely to do that by visiting a free forum or searching on Google.

I suppose I should be grateful. At least you get an answer to DB support requests. That’s more than can be said for AS10g support requests! We close those out of boredom 🙂

Cheers

Tim…