Who's online

There are currently 0 users and 41 guests online.

Recent comments


Oakies Blog Aggregator

Simple SQL with and without Inline Views

October 21, 2011 (Forward to the Next Post in the Series) Sometimes it is interesting to take a step back from some of the more difficult to construct SQL statements, and just put together something simple.  The following request recently arrived through an ERP mailing list: “I want to find the last ship date for all the [...]

Friday Philosophy – Should I Be a Twit?

Something I have been pondering for a while now is should I join in with the “happening crowd” and sign up to Twitter? I know, I’m two or three years behind the times on this, but more and more people who I like have signed up – even Doug Burns now uses twitter and he used to be negative about it in the same way as I. I’ve asked a few of these friends what they think.

I’ve always resisted the whole micro-blogging world, probably due to the comments made by people about how much dross is tweeted and what a time sink it can be, something the people I asked all mentioned. And partly, being candid, because I know one of my faults is to shoot my mouth off before engaging my brain, especially if annoyed. If it takes 20 seconds to do a tweet, I shudder to think some of the things I might have put out there in the heat of the moment or when in the pub. Pub talk is fine, so long as it is kept in the pub. With a blog, it takes me so long to write them I tend to calm down before sending, if I am angry.

Also, tweets are not my style. I don’t know if you have noticed but I can be a little verbose {this means I spout several sentences where 5 words would do}. Can I be succinct enough to say anything anyone else would want to read?

That’s the writing of tweets, what about the following? I could just be a passive follower. But how many? This is part of the advice given to me by Neil Chandler and also Doug, to only follow a few people so that you do not just drown in an endless feed of stuff. I guess that anything good gets re-tweeted and so you see stuff not just by those you follow but the best bits of what they follow? Neil also suggested that part of what makes twitter work is joining in, don’t just be passive.

Something about Twitter that does bug me a lot, and this is just part of the whole texting/smartphone/constant communication thing of modern life, is when people you are spending social time with just sit staring at their bloody phone. I just find that really rude and I also think it’s a bit depressing when you see three or four people in the pub or restaurant, all staring at their smartphones and not communicating with each other. You might as well stay in bed. Alone. {Oh good grief what a terrible thought! Do couples who do social media now just sit in bed with their phones in front of them, ignoring their supposed love of their life?}

But of course there are advantages to Twitter. There is a lot of interesting stuff that goes on only in the twitter world and some of the tweets I have seen have been hilarious. It’s far more lightweight than blogging, something you can do in a quiet moment. Though Doug suggests this is why you get so much dross about travelling, people catch up on twitter when bored and also tweet then. Niall Litchfield made a very interesting point to me in that it allows you to keep up on a large range of topics and see new ideas and thoughts more. I like that. In fact, Niall sent me an excellent list of reasons for and against, which I’ll {almost} finish on.

From Niall:


Reasons for :

- I get vicarious updates from a large number of sources on a large number of subjects. (Oracle,SQL,Science,Politics)
- It can suit my short and acerbic style from time to time.
- More people are doing it
- It indirectly promotes me (albeit with a somewhat misleading image)
- I find stuff I wouldn’t otherwise have found
- I have engaged with product managers I probably wouldn’t have done

Reasons not:
- time wasting
- addictive
- it promotes me in a misleading way
- it can annoy others

I like it because I’m an information junkie and like to keep abreast of stuff in several areas. It can be a colossal waste of time and irritate immensely.


I think I might well give it a go and that leads to my last thought. If I am going to enter the world of twitter, I am going to have to get a new phone. My current one is so basic that it does little more than just make and receive calls. But a single charge lasts a week.

Tuning Blog Entries

Organizing  some of my past blog entries on Oracle database performance tuning and analysis. I’ll add more notes as well as a separate section on SQL tuning in particular.






VST – vistual sql tunning




Why Are My Indexes Still Valid Solution (A Second Face)

I’ve been so busy lately, I just haven’t had any spare time to post. For now, the quick answer to the last quiz is that the second table was indeed an Index Organized Table (IOT). One of the nice benefits of an IOT is that when re-organised, unlike a Heap Table, all indexes remain valid, [...]

APAC OTN Tour: Beijing, China

My flights to China were rather uneventful. The Birmingham to Dubai leg was delayed by an hour due to fog in Dubai. I had a 4 hour connection in Dubai originally, so the delay was no big deal.

Arriving in Beijing was a little unnerving. I misplaced the Chinese version of the hotel address, but had the English version. Finding someone to translate it proved very difficult and as it turned out they translated it incorrectly. Fortunately I found a cached version of the address on my iPad, so that saved by bacon. The second hitch was that I couldn’t get cash with by debit cards. Just a flat-out refusal from any ATM in China. Arrrggghhh! Fortunately, I was able to get cash advances using my credit card. I’m going to pay through the nose for it, but at least I can survive.

Today is the first day of the conference and I had a morning slot. For the English speakers, we had one screen showing our slides in English and one showing the Chinese translation. I was asked to speak more slowly than usual (kinda difficult for me) and as a result I had to reduce the content somewhat. I did a run through last night to make sure my timing was OK with this adjustment.

The conference has a single track, so you get a room full of people from different technical areas. This is always a little unnerving as you worry about the relevance of your material to audience. Here are a couple of photos of the audience I took while I was setting up.


Everything seemed to go OK. I hope they understood my accent. :)

With a bit of luck I will get to see some of Beijing over the next couple of days. I’m behing the Great Firewall of China, so some sites (Facebook and Twitter) are blocked completely and many other sites (like Gmail and any other Google related services) seem to come and go. For the time being my blog seems active. If I lose access to it I will update things once I get to New Zealand.



Little Things Doth Crabby Make – Part XV. Oracle SPARC SuperCluster Is Much Faster Than IBM Power Systems! No Squinting Allowed!

Since I missed Oracle Openworld 2011 I wasn’t able to attend the keynotes. I have, however, taken the time to view each of them in playback from video archives. After viewing the keynote delivered by Oracle Corporation’s CEO Larry Ellison, I felt compelled to read some additional literature relevant to the IBM-smashing claims made by Mr. Ellison during his segment focused on Oracle SPARC SuperCluster. A simple Google search brought me to where I see the following graphic:














It has been a long time since my last installment in the Little Things Doth Crabby Make series so it’s high time I do so.  Here is what I see at the “sun-beats-ibm” webpage:










In case the fine-print disclaimer is too small, here’s what I see (bold font added by me):

Sources for Comparison of Systems:

Systems cost based on server, software and comparable storage list prices (without discounts), as well as third party research. Performance comparison based on Oracle internal testing together with publicly available information about IBM Power 795 TurboCore system with highest processor speed commercially available (4.25 GHz) as of Sept 28, 2011


That makes me crabby. I shouldn’t have to squint, should I?

Filed under: oracle

Want to Know More about Oracle’s Core?

I had a real treat this summer during my “time off” in that I got to review Jonathan Lewis’s up-coming new book. I think it’s going to be a great book. If you want to know how Oracle actually holds it’s data in memory, how it finds records already in the cache and how it manages to control everything so that all that committing and read consistency really works, it will be the book for you.

{Update, Jonathan has confirmed that, unexpected hiccups aside, Oracle Core: Essential Internals for DBAs and Developers should be available from October 24, 2011}

{Thanks to Mike Cox, who let me know it is already available to be reserved at Amazon}

Jonathan got in touch with me around mid-May to say he was working on the draft of his new book, one that would cover “how does Oracle work”, the core mechanics. Would I be willing to be one of his reviewers? Before anyone comments that there is not likely to be much about core Oracle that I know and Jonathan does not, he did point out that he had already lined up someone to be his technical reviewer, ie someone he expected to know as much as he and help spot actual errors. The technical reviewer is the most excellent Tanel Poder, who posted a little mention of it a couple of months back.

I was to act more like a typical reader – someone who knew the basics and wanted to learn more. I would be more likely to spot things he had assumed we all know but don’t, or bits that did not clearly explain the point if you did not already know the answer. ie an incomplete geek. I figured I could manage that :-) .

It was a lot harder work than I expected and I have to confess I struggled to supply back feedback as quickly as Jonathan wanted it – I was not working but I was very busy {and he maybe did not poke me with a sharp stick for feedback soon enough}. As anybody who has had to review code specifications or design documents will probably appreciate, you don’t just read stuff when you review it, you try and consider if all the information is there, can it be misunderstood and, if you find that you don’t understand a section, you need to work out if the fault is with you, with the way it is written or with what is written. When I read a technical {or scientific} document and I do not fully understand it, I usually leave it a day, re-read it and if it still seems opaque, I just move on. In this case I could not do that, I had to ensure I understood it or else tell Jonathan why I thought I did not understand it. If there are sections in the end book that people find confusing, I’ll feel I let Jonathan down.

Just as tricky, on the one hand, as I’ve been using Oracle for so long and I do know quite a lot about Oracle {although clearly not enough in the eyes of the author :-) } I had to try and “not know” stuff to be able to decide if something was missing. On the other, when I wanted to know more about something was I just being a bit too nerdy? I swung more towards the opinion that if I wanted to know more, others would too.

I have to say that I really enjoyed the experience and I learnt a lot. I think it might change how I read technical books a little. I would run through each chapter once to get the feel of it all and then re-read it properly, constantly checking things in both version 11 and 10 of Oracle as I read the drafts and would not let myself skip over anything until I felt I really understood it. As an example, I’ve never dug into internal locks, latches and mutexes much before and now that I’ve had to learn more to review the book, I have a much better appreciation of some issues I’ve seen in the wild.

Keep an eye out for the book, it should be available by the end of this year and be called something like “Oracle Core” {I’ll check with Jonathan and update this}. I won’t say it will be an easy read – though hopefully a little easier as a result of my input – as understanding things always takes some skull work. But it will certainly be a rewarding read and packed full of information and knowledge.

The Social Development Database

I’ve always been fascinated by development databases — more so sometimes than huge, heavily utilized production ones. Mainly because I’ve seen how the beginnings of a performance problem, or the start of an elegant solution takes shape within a development database. It’s one of the reasons why I love high levels of visibility through full DDL-auditing within development. I love to SEE what database developers are thinking, and how they are implementing their ideas using specific shapes of data structures.

One of the concepts I’d love to see is a “river of news” panel within development tools to see what is going on within a development database. Some of the good distributed source code control systems do this now.

Here’s a good example of what I mean:

Imagine if there was a panel in SQL*Developer or TOAD that looked something like this:

This wouldn’t be all that hard to implement if full DDL-auditing was turned on…

Happy Birthday Val…

It’s my mom’s 70th birthday today. Happy birthday Val.

I’m flying to China today, so I’ve set this post to publish automatically while I’m in transit. :)



Debugging PL/SQL and Java Stored Procedures with JPDA

In 2003 I published a paper entitled Debugging PL/SQL and Java Stored Procedures with JPDA. Its aim was to describe how to debug PL/SQL and Java code deployed into the database with JDeveloper 9i. Two weeks ago a reader of my blog, Pradip Kumar Pathy, contacted me because he tried, without success, to do something similar with JDeveloper 11g, WebLogic 11g and Oracle Database 11g. Unfortunately I was not able to help him. The reason is quite simple, since 2004 I’m an Eclipse user…

Few days later Pradip contacted me again to let me know that, at last, he succeeded. Here you find his notes…

  1. Grant the required privileges
  2. GRANT DEBUG CONNECT SESSION to &&schema_name;

  3. Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
  4. Write some Java code to let the database engine connect JDeveloper (this piece of code must be executed before calling the PL/SQL stored procedure)
  5. private static void remotePLSQLDebug(Connection con) throws SQLException
      SystemProperties systemProperties = null;
      systemProperties = SystemProperties.getInstance();
      CallableStatement callState = null;
      String port = systemProperties.get("plsqldebug.port").trim();
      String storedCall = "call dbms_debug_jdwp.connect_tcp('localhost'," + port + ")";
      callState = con.prepareCall("{" + storedCall + "}");
  6. Configure the plsqldebug.port parameter in the application properties
  7. Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
  8. Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
  9. Execute the Java code of step 3; as a result the database engine connects the JPDA listener and a connection process is created (Figure 7)
  10. When the PL/SQL stored procedure is executed, JDeveloper is able to debug it (open the PL/SQL stored procedure in the “Database Navigator” for that purpose)
  11. Once the debugging is over detach the connection process

Thank you Pradip to share your findings!