Top-N Queries and the Multithreaded Model in Oracle 12c…

A couple of new 12c articles have rolled off the press. Both of them small, but interesting to me.

These resulted in amendments to a couple of old articles.

I wonder how many people will start using the multithreaded model and still try and kill sessions on the operating system using the UNIX/Linux process ID? 🙂

Cheers

Tim…

WITH Clause Enhancements in Oracle 12c…

After doing a number of 12c installations, I decided it was time to write something new. I figured I’d pick something easy to start off with, so here is the first thing off the press.

Over the last year I’ve heard a few speakers talk about these enhancements, but I got myself into a right pickle while I was working through this. If you caught the first draft of this article you would see I made a complete mess of it and jumped to all the wrong conclusions. Having worked through it again with fresh eyes, it all became clear. Of course, if you didn’t see the first draft, then ignore what I said. The article has always been perfect. 🙂

I think I will carry on doing some of the simple stuff to build my confidence before I can tackle something big, like pluggable databases. 🙂

Cheers

Tim…

Update: Note to self. Searching the 11gR2 docs for “PRAGMA UDF” is unlikely to result in you finding this 12c feature. Who’da thunk it? Thanks to Tom Kyte for pointing me to the correct doc reference. I’ve altered the searches on my homepage to default to the 12c docs, so hopefully I won’t be such a Muppet next time. 🙂

How can we make Oracle Database 12cR2 the best release ever?

Oracle will be releasing Oracle Database 12cR1 at some point this year. Many companies will avoid this release, opting to wait for 12cR2, their reasoning being it will be more stable and, as a terminal release, will have a longer support life-cycle. Since 12cR2 is what most businesses care about, what can we do to make it as good as it can possibly be? Here are a few thoughts…

  • For a start, you’ve got to use 12cR1 in your organization to understand what it does well and what is a bit crappy about it. If you don’t figure this out during 12cR1, chances are your wishlist will not be ticked off in 12cR2. I’m not saying launch straight into production, but don’t just place your head in the sand either.
  • Report every bug you find, even those that already have resolutions in MOS. Lots of people, including myself, complain about how buggy the Oracle database has become as the functionality has expanded. Workload in any organization has to be prioritized. Chances are, the things that people shout about the most are the things that Oracle will get done. If we all make our feelings known by logging every bug that affects us and explain why/how it is impacting on us, it can only help the cause.
  • Report bugs in the documentation. The Oracle docs have improved a lot over the years, but there is always room for improvements. In the old days you had to report faults in the documentation as SRs. Now, you just need to add comments directly in the documentation, which is a lot quicker and easier. When you see a problem, report it. If you think something is missing, ask for it. Once again, if enough people shout about something, it may affect the priority.
  • File enhancement requests. You often come across things that almost solve your problem, but not quite. If that’s the case, file an enhancement request on MOS. If enough people ask for stuff it will probably get added. Even better if you have an example of how cool that feature is in another DB engine. 🙂
  • Get involved in beta programs. This is not always as easy as it sounds. I would love to have been involved in the 12cR1 beta program, but I was not considered worthy. 🙂 If your company can get involved in beta programs it’s a good way to help improve and shape the product. The more issues logged during the beta testing period, the more likely the final product will live up to your expectations.
  • Make sure Oracle salespeople know and understand your priorities. At the end of the day, Oracle exist to make money. In most commercial companies the sales department have a disproportionate influence on the direction of the products. At every opportunity, make it know what *your* priorities are. I love the new and geeky stuff, but to be honest, most of the work I see people doing requires little more than what was available in Oracle 7. If bug fixes and stability are more important to you than new functionality, make it known.

I have no knowledge of the internal workings of Oracle as a company, so I can’t guarantee these suggestions will have any impact, but I think of this the same way as I do about voting in elections. If you don’t voice your opinions, you don’t really have any right to complain. If Oracle know exactly what their customers want and don’t deliver, they only have themselves to blame if everything goes wrong. If we as customers don’t make our opinions known, we shouldn’t be surprised if Oracle keep chasing the next buzzword, rather than doing what matters to us.

Cheers

Tim…

Oracle Database on Oracle Linux 6.1…

I mentioned the day before Open World I put a Virtual RAC on Oracle Linux 6.1 article live. Although the procedure was complete, some of the screen shots were from an old article as I didn’t have time to redo them before my flight. 🙂 I’ve just run through the procedure again and taken new screen shots. As a result, I’ve allowed the article to display on the front page of the website, which is why you will see it listed as a new article there.

This kinda rounds out the whole Oracle on 6.1 stuff as there has been a single instance installation guide out for ages and more recently the Cloud Control installation, which references it.

Remember, it’s still not certified yet, but it’s coming.

Cheers

Tim…

Update: It’s finally certified. See here.

Oracle Database 11gR2 on OL6 / RHEL6: Certified or Not?

There seems to be a little confusion out there about the certification status of Oracle Database 11gR2, especially with the release of the 11.2.0.3 patchset which fixes all the issues associated with RAC installs on OL/RHEL 6.1.

Currently, 11gR2 is *NOT* certified on OL6 or RHEL6. How do I know? My Oracle Support says so! Check for yourself like this:

  • Log on the My Oracle Support (support.oracle.com).
  • Click the “Certifications” link.
  • Type in the product name, like “Oracle Database”
  • Select the product version number, like “11.2.0.3.0”.
  • Select the platform, like “Linux x86_64” or a specific distro beneath this.
  • Click the “Search” button.

From the results you will see that Oracle Database 11.2.0.3 is certified on OL and RHEL 5.x. Oracle do not differentiate between different respins of the major version. You will also notice that it is not currently supported on OL6 or RHEL6.

Having said that, we can expect this certification really soon. Why? Because Red Hat has submitted all the certification information to Oracle and (based on previous certifications) expects it to happen some time in Q4 this year, which is any time between now and the end of the year.

With a bit of luck, by the time I submit this post MOS certification will get updated and I will happily be out of date… 🙂

Cheers

Tim…

Update: It’s finally certified. See here.

Two New Articles: UDEV and Database Triggers…

I’ve recently put a couple of new articles about old subjects on the website. In both cases, the articles were initiated by forum questions, but the explanations became too painful in the format of a forum post so they graduated into articles…

  • UDEV SCSI Rules Configuration In Oracle Linux 5 : For those of you that like to follow my Virtual RAC guides, but don’t like using ASMLib, you can use this article and replace ASMLib with UDEV.
  • Database Triggers Overview : This is really a primer on database triggers. I’ve focussed mostly on simple DML triggers, since this is what the vast majority of trigger-related questions I’m asked relate to. Consider it the “minimum” you should know before you write a database trigger.

Cheers

Tim…

Oracle Database SQL Expert (1Z0-047)…

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

  • Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
  • Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
  • The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
  • Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
  • Several of the questions included the “ANY” and “ALL” comparison conditions, which are barely mentioned in the documentation (here)*. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in MySQL so I knew what they were for, which was fortunate.
  • There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself. 🙂

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it. 🙂

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

* Updated thanks to Pierre’s comment.

Oracle Database File System (DBFS) in 11gR2…

I’ve been having a play with the DBFS functionality in 11gR2.

For the most part it is extremely simple. If you are working on Linux then it’s a really neat solution. On any other platform you are limited to using the dbfs_client tool, which is currently lacking a lot of functionality. Even so, it’s a good first step.

I am having a bit of trouble with the “/etc/fstab” mounting. I’ve included it in the article, with a warning that it isn’t working for me. If anyone has got it to work I would really appreciate some input on what I’ve got wrong. The documentation is a little sparse on this subject even with some digging around it’s proving difficult.

I’ll probably be witing another article on a different aspect of DBFS soon.

Cheers

Tim…