Last Friday I wrote about a client query that could be rewritten in a much more optimized manner. However rewriting it was a little tricky, involving a hierarchical query and a column that needed to calculate a value based on it’s parent, grand-parent, great-grand-parent, etc. Raj posted a very smart solution that used sys_connect_by_path to access this information – which is exactly how I tried to do it at first. However after rethinking the query I realized that there was a better way to do it with analytics.

So here’s the approach that I took and how I arrived at the solution.

The Problem

The root problem was that I wanted a simple method to access any column value from an arbitrary level in the hierarchy above my current level. For example if you look at the TURNER record in the result set, his UPLINE_SAL column needs to be the sum of the SAL column from three different records:
› Read more

Dominic just posted a hint to the encyclopedia spine problem he posted last week. Sheesh… personally I think the hint totally gave it away. Or at least it gave away one solution.

Here’s what I came up with. I wonder if this is how Dominic solved the problem:
› Read more

Just a few days ago I was reading Dominic’s challenge to write a query that would give results like an Encyclopedia Spline. It apparently took over the entire day for another Dominic and I was planning to take it on myself too as soon as I had the time… but today another challenge came my way – but not just for entertainment, but for a real client. Basically I found myself looking at a 9000-word query (33 pages when I pasted it into MS Word). It was for a manufacturing client and it was pretty amazing… had been taking about 20-30 minutes but the guy added a single subquery and that plummeted the query to not even finishing in four hours.

Well we got the subquery thing rewritten and fixed the abysmal performance – but after analyzing the query I realized that the whole thing could be drastically improved by using a hierarchical query and SQL analytics. So I simplified the problem and ended up with a very interesting challenge for the day!

But before I share the challenge, let me give a brief overview of the original problem query. I analyzed it a bit and this was the basic structure:
› Read more

Just read Doug Burns’ interview from the Autumn 07 edition of AUSOUG’s Foresight magazine. It’s a fun read; Doug talks about how he got into technology and Oracle, the strengths and weaknesses of the Oracle RDBMS, and his addiction to the command line. But the part that jumped out at me the most was his comments about Oracle user groups – I wholeheartedly agree that they’re really important and I’m a huge supporter.

A strong user group is essential in my opinion. To have a strong user group, it’s important that it’s not organised for commercial gain, to argue the vendor’s case too vehemently (although talking up the product is okay if it’s a good one) or to be in the vendor’s pocket financially. However, it’s also important that the user group have a good relationship with the vendor so that there’s a circle of feedback that will hopefully improve both the product and service for the customer.

In the end, we all run into problems and it’s good for users to be able to get together, share their experiences and knowledge and passion for the software. I’d encourage as many people as possible to become involved. It’s a lot of fun and the more of us there are, the more we can achieve.

I know that there are lots and lots of DBAs in Chicago – you guys have no excuse not to come out to the user group meetings, especially when it’s 100% free and we meet right downtown!!! Let’s see you at the next meeting! (Probably in August; I think we’re shooting for about four per year right now.)

So we just had the spring COUG meeting. Two speakers this time, Charlie Garry from Oracle and Dean Richards from Confio. Oracle volunteered a meeting room; their offices are in the Sears Tower downtown. They also provided food. (Very cool!) Toward the end – after 5 or 6 people took off – I counted 24 attendees in addition to the two speakers. A lot of them seem to be DBA’s – I talked to one guy who was doing a four node RAC cluster on Sun Fire T2000’s (with Sun’s 8-core UltraSPARC T1 processors). But of course there was a good number of sales guys too… there was one guy in particular who really seemed interested in talking to me only about what he was selling. I have to be honest… sometimes that drives me nuts. I love talking tech stuff. I don’t mind learning about cool products and I don’t mind talking to sales guys. But when I’m ready to buy something, (1) I’m going to do my own looking around at all of the available options and (2) I’ll find you. Don’t worry, I’ve got like 10 copies of your business card. If only Oracle user groups could be like those really cool Linux user groups back in Michigan. Just a bunch of folks who meet up at a local coffee shop, order pizza and geek out about cool things you can do with computers.

So here’s my thoughts on the speakers. These are just my impressions; I’m sure that others would have different ideas… but FWIW:
› Read more

Well last week I had a few posts about controlfile recovery; one about recovering without a backup and one about recovering with a backup using RESETLOGS. In the second post I showed how when you restore a backup controlfile Oracle will always require you to recover then open the database with RESETLOGS.

Hemant Chitale pointed out that you do not always need to do a RESETLOGS when you open the database. If you recreate the controlfile rather than restoring a backup then Oracle allows you to open the database normally (assuming of course a normal database shutdown). Naturally you will lose any information in your controlfile (RMAN configuration and records, the incarnation table, etc) so I would personally prefer keeping the old file. But it’s certainly possible – so here’s a demo of Hemant’s suggestion on Oracle 10 release 2. Really it’s abit of a rehash; Hemant actually posted all of this himself on oracle-l last week. (And more – he also posted a scenario of losing both the controlfile and online logs.) He also mentioned it in his blog. So for more detail you can also check out email.
› Read more

I have to say that I really like the oracle-l mailing list. It tends to stay pretty focused on DBA stuff, there’s a low volume of spam and there are a lot of really smart guys who post there.

I was just catching up on some threads from last week and a comment about how to name redo logs caught my eye. I’m always interested in best practice type stuff and this is a pretty good tip. Basically the suggestion was to not give your online logs the file extension “log” – because… well everytime you write a script, what do you name the file with the output? xxxxx.log! Additionally, archived logs often have this same extension and someone (not you of course) could accidentally delete the online logs in the process of cleaning up archived logs. Or delete the archived logs in the process of cleaning up custom script log files. (For the same reason, it’s a good idea not to let archived logs have the extension “log” either.)

The whole thread is available on the oracle-l archives; look for the subject line “RMAN Recovery”.

Rick Weiss:

I finally get the chance to recover from a failure and it turns out to be the worst kind – DW (NOARCHIVELOG)

The cause of my problem was an erroneous rm *.log that whacked all the redologs by an analyst trying to clean out a bunch of Oracle Warehouse Builder ETL log files.

Jason Heinrich:

Funny, I just read an AskTom post yesterday where he recommended against naming your redo logs with the .log extension for this very reason.

I looked around on asktom a bit but I wasn’t able to find the question Jason referenced… but regardless, you should give your online logs a different extension like “rdo” or something. And archived logs should be named with an extension such as “arc”.

So I guess this is old news since they finished this implementation back in June last year and even presented at Open World. But this case study is just a goldmine of information and totally worth reviewing if you’re anywhere close to RAC, Data Guard, ASM, RMAN, or Flashback (and more I’m sure).

The big picture is that Fidelity migrated a 10TB data warehouse from DB2 to Oracle RAC. (Charles Kim was the lead on it.) Most of the data was unstructured PDF or XML stored in LOBs. Interestingly this is a perfect example of something that Robin Harris recently discussed on storagemojo… and then Kevin Closson picked up on his blog as well: that the future of storage is not IOPS or OLTP workloads; it’s capacity and unstructured data… think LOBs and sequential reads (or as Kevin pointed out, Oracle 11g Secure Files).

Update: Noticed that Nuno Suoto blogged just this morning about the same thing.

Kevin Closson is so right in his rants over structured vs unstructured data volumes! We have about 3TB of structured databases and nearly 20TB of unstructured data. All this is backed up daily and kept for indefinite periods on various schedules and sub-areas. All in all a total capacity of nearly 500TB!

› Read more

In response to a small discussion on the oracle-l mailing list last week I thought I’d put together a quick demo of exactly what I was referring to in my email. Basically I was discussing how even when you do a normal shutdown – leaving your database in a consistent state that doesn’t require recovery – if you lose your controlfiles then you still will need to execute the “recovery” commands and then open resetlogs.

Here’s the test, which I did on one of our lab systems…
› Read more

Little trick I’d seen once that might come in handy for someone.

Lets suppose that somehow you accidentally delete (“rm”) all copies of your current controlfile – and you don’t have a backup!! If the database is still running then don’t shut it down! There might actually be a way to recover the control file. Especially if you are using shared servers.

In unix when you “rm” a file it simply disconnects the inode from the directory. (The inode is the block on the disk with all the meta-information about the file.) If any process is still holding this file open then the inode – and in effect the file – will not be released as free space until all processes close the file.

So how does this help us recover a lost controlfile? Well if an Oracle process has the file open then that process can still access if even after you’ve deleted the file from unix.

I’ll give a quick demo:
› Read more