Tag: 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…

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…

OpenWorld (Thursday)

Here goes…

“Making the Most of PL/SQL Error Management Features” by Steven Feuerstein – This presentation covered a number of aspects of error trapping available in PL/SQL, including new features in 10g and overall methodology. It was pitched more for beginners and those new to 10g, so there wasn’t a great deal for me. I guessed this from the title, but this was the only Steven Feuerstien presentation I could get to and I really wanted to hear him speak. He’s a great presenter and comes across as a really relaxed and “low temperature” guy. I hope in the future I’ll get to hear him speak on something a little more meaty.

“Sifting Through the ASHes: Advanced Database Performance Tuning Using Active Session History” by Graham Wood – Here we got an overview of how ASH works and what information you can get from it. Essentially, it’s pitched as filling the gap between AWR and SQL_TRACE. The line of argument goes, AWR isn’t realtime and SQL_TRACE is too expensive to have running for all sessions all the time. ASH doesn’t give you as much information as SQL_TRACE, but it’s running all the time, so you get realtime sampling not available from AWR. Pretty good for looking at current problems!

“Welcome to My Nightmare: The Common Performance Errors in Oracle Databases” by Michael Ault – Mike listed some of the major problems he has seen over the last few years of consulting and discussed possible solutions to them. Lots of the people near me were writing furiously, so I guess it went down well.

“Tuning SQL When You Cannot Change the Code” by Dan Hotka – Dan started at a furious pace, banging through lists of ways to affect execution plans for packaged or third party applications. Some of it was pretty obvious, some of it not so obvious. About half way through the hour slot he reached the end of his presentation, then it became apparent he had got the times mixed up and believed he only had 30 minutes. At this point the reason for the furious pace became obvious.

“Dell IT: Get Beyond Oracle Real Application Clusters and Start Deploying an Enterprise Grid Architecture” by Logan McLeod and Kirk McGowan – I advise people to look at some of the figures in this paper. These guys have a serious amount of servers and Oracle power. It makes most of our RAC look like hobby systems. I wish I had got to the technical session on this earlier in the week. Apparently they’ve automated RAC setup to the point where they can install and configure a whole RAC in approximately 30 minutes from booting the hardware to completion. Wild!

I got talking to a guy called Rick at the end of the last session and I ended up chatting to him during the whole of the two hour “It’s a Wrap!” party. He was making notes using Mind Mapper Pro, which I’ve seen before, but never used. We started talking about that, which turned into a general chat about writing, then I mentioned my website and finally we both went into full geek mode. Very cool! 🙂 It’s been really fun meeting and talking to all the people at the conference.

So now it’s over. I’ll probably write a Wrap-Up blog entry in a few days, once it’s all sunk in. So that’s goodbye to OpenWorld, but I still have a few more days in the USA!

Cheers

Tim…