Category: Oracle

I love it when a plan comes together…

Yesterday I was trying to optimize a data transfer that took the best part of two hours to complete…

After a quick inspection I noticed a couple of things:

  • After the data was transfered from a remote database, it was updated 5 times to alter some of the values. A couple of quick DECODEs in the transfer query and these five updates were removed.
  • The transformation of some of the data was done by a really inefficient function. It was doing date arithmetic by selecting from dual, rather than just using a procedural assignment like, “variable := SYSDATE + 1”.

After rectifying both these issues the transfer time was reduced from nearly 2 hours to just 3 minutes.

I love it when a plan comes together… 🙂

Cheers

Tim…

PS. If only all optimizations were so easy…

I’m back…

With the operation and Christmas over, I’m back at work. Kind of an anti-climax…

Christmas was a bit weird. After an extended break with the knee thing, having time off for Christmas seemed a bit odd. In total, I’ve been out of the office for a little over 3 weeks.

Anyway, back to the real world…

Andrew Clarke spotted a gaff in one of the examples in my PL/SQL Tuning book. Click here to check it out. Sorry to anyone who has experienced an issue with this, and thanks to Andrew for pointing it out.

Cheers

Tim…

Update: I’ve notified the publisher of the corrections.

Forward to the Past…

I’ve recently been asked to do some work on some 8i instances and it got me to thinking. When a new release roles out you instantly see all the new bells and whistles, but many of the minor improvements go unnoticed. By the same token, when you’ve been working with newer versions for a while, you forget how many things you are using that were minor improvements on previous versions.

I started working on this 8i stuff and found a bunch of my scripts didn’t work as they relied on columns that were added to DBA and V$ views in 9i and 10g. That wasted some time.

I always use DBMS_XPLAN to get my execution plans, but it’s not present in 8i, so I had to start using utlxpls.sql, but it wasn’t quite what I wanted, so I had to modify it a bit. That wasted some more time.

I use some GUI tools for getting an overview of stuff, but some of these were not as backwards compatible as I would have liked. That wasted even more time.

The net product is, I ended up taking the best part of a day doing stuff that should have taken me a few minutes.

Now I like all the latest Oracle features as much as the next guy, but some of the most useful improvements don’t necessarily make it to the press releases. This experience makes me even more keen to see 11g. 🙂

Cheers

Tim…

UKOUG Bloggers Meetup…

It was cool to meet up with everyone again. Some people were at OpenWorld, some I’ve not seen since this time last year, and others were new faces. It’s good to meet-and-geek. 🙂

The subject of my blog post regarding forums came up a number of times. It seems everyone is suffering in the same way. One suggestion was that we, as a community, should try to get rid of as many Oracle forums as possible and encourage people to use the OTN forums. Effectively creating a single point of reference. I’m going to give this one some thought. Some days I really like having my forum, other days it’s the a right royal pain. I guess if the bad is out-weighing the good, closing it is an option.

Cheers

Tim…

The downfall of forums (part 2)…

It seems my previous post went some way to inspiring Eddie Awad’s recent post.

I’d just like to confirm that I don’t agree in using RTFM as an answer to a post. People should be guided in the right direction, rather than trampled on. My point is more that people need to take responsibility for themselves, part of which is reading the manuals before asking simple questions.

Cheers

Tim…

The downfall of forums…

I’ve had a few conversations recently about the state of numerous Oracle forums, including my own. It feels like most posters these days don’t even bother to open the manuals before asking a question. I can’t count the number of times I’ve been asked a question, that is answered by the first couple of paragraphs in the manual. It’s just lazy beyond words.

Now I’m not having a go at those individuals who struggle to understand the manuals. I realize the documentation is quite dry at times, often involves a lot of cross referencing and English is not everyones first language. Even so, sometimes it is quite apparent that no effort has been made to find the answers for themselves.

I suppose the general lack of effort out there means my skills are even more valuable, but it’s hardly a consolation.

I’m sure it wasn’t like this in the old days… 🙂

Cheers

Tim…