Adaptive Query Optimization in Oracle Database 12c

Over the holiday period I finally decided to tackle the Adaptive Query Optimization stuff in Oracle 12c, which resulted in these articles.

Adaptive Query Optimization is really a collection of different features, hence the links page to bring them all together. Some of the features are just renamed and slightly modified versions of stuff from previous releases. Other bits are totally new. I’ve sat through a number of talks about this stuff over the last few years and the sound-bites make it seem quite obvious and straight forward. That’s because the individual pieces, for the most part, are quite straight forward. The trouble comes when they all start to interact, at which point things can get quite confusing. While I was doing the demos for the features, I had to chop and change quite a bit. Sometimes things didn’t happen when I thought they should. Other times things did happen when I thought they shouldn’t. I think much of it was down to my misunderstanding, but that just goes to show how tricky some of this can be… That’s without adaptive cursor sharing and SQL plan management thrown into the mix…

As I keep saying, these articles will probably get revised over time as I get a better understanding of this stuff. I have a feeling I won’t really get to grips with it until we are live on 12c for a number of projects and people start throwing real situations at me to investigate.

Cheers

Tim…

 

“a SQL” or “an SQL”?

This is an age old question and of course the answer depends on how you say “SQL”.

  • … a Structured Query Language statement…
  • … a Sequel statement…
  • … an Es Queue El statement… (I say it this way)

Different people say it different ways. Most of the time I don’t notice, but I just read something by another writer and kept seeing “a SQL …” and it was freaking me out. Then I realised I always write “an SQL …”, which reads “an sequel …” to some people, which sounds really stupid. :)

According to the Oracle docs it is sequel, so “a SQL …” is the correct way.

“SQL (pronounced sequel) is the set-based, high-level declarative computer language…”

The MySQL docs go the other route.

“The official way to pronounce MySQL is My Ess Que Ell (not my sequel), but we do not mind if you pronounce it as my sequel or in some other localized way.”

According to Wikipedia, it doesn’t matter either way.

“The original standard declared that the official pronunciation for “SQL” was an initialism: /ˈɛs kjuː ˈɛl/ (“es queue el”).[11] Regardless, many English-speaking database professionals (including Donald Chamberlin himself[37]) use the acronym-like pronunciation of /ˈskwəl/ (“sequel”),[38] mirroring the language’s pre-release development name of “SEQUEL”

If one of the designers can’t make his mind up, what hope to we have? :)

Anyway, when you are reading my stuff and you see “an SQL …” everywhere, it’s correct for the way I say SQL. :)

Cheers

Tim…

PS. All the other typos are just plain typos because I’m practically illiterate. :)

PPS. PL/SQL is a lot simpler as it is “a PL/SQL …” regardless of how you say it. :)

Update: The general consensus from comments, twitter and emails seems to be:

  • When you write it, it should be “a SQL …”, regardless of how you say it. Taking me back to school, Paul Steffensen said, “if it starts with a vowel it’s an, otherwise it’s a”. In my reply, I mentioned that “an Es Queue El …” does start with a vowel. :) That being said, “a SQL …” goes with the basic written language, the sequel version and the full wording of the acronym, so it fits more of the cases.
  • If, like me, you say “Es Queue Ell”, you should probably say “an Es Queue Ell” when speaking, but still write it “a SQL …”
  • Andrew Taylor said, “just to be on the safe side I say *some* SQL.” Pure genius!
  • In many cases, you can probably reword the statement so the “a” is not necessary. This feels like a cop out, but it might be what I end up doing. :)

MobaXterm 7.4

MobaXterm 7.4 was released today. The downloads and changelog are in the usual place.

This release has a bunch of upgrades of the core components, bug fixes and support for Windows 10 Technical Preview.

Happy upgrading!

Cheers

Tim…

The Black Hole

I was re-watching Who’s Afraid of the Big Black Hole on YouTube and I got all nostalgic about the 1979 film The Black Hole. A couple of clicks on Google Play and I bought and watched it. Flippin’ awesome!

Considering this is 35 years old, it’s stood the test of time pretty well. There are certainly a few dodgy effects on show, but most of the film looks fine. This is post-Start Wars after all. There are some scenes where people can seemingly survive in space without spacesuits, but even that could be explained away based on the tech used by the main ship I guess. :)

We definitely need more space-based Sci Fi. Can I have Guardians of the Galaxy 2 and a new FireFly series please?

Cheers

Tim…

 

The Hobbit : The Battle of the Five Armies

I’ve just got back from watching The Hobbit : The Battle of the Five Armies.

I didn’t see the previous film at the cinema because I couldn’t be bothered. I watched it on a plane some time later. When my friends texted me to ask if I wanted to see this film I started to reply no, then remembered it’s ages since I got to watch a film at the cinema with friends (I usually go on my own), so I said yes…

Spoilers below. Don’t read anymore if you care.

Basically, this feels like a montage of clips from the previous two films with some bits of L.O.T.R footage thrown in for good measure. I’m so sick of seeing a handful of people defeat an army of bigger, stronger, faster and more well armed people (orcs). I can get my head around elves being much faster and more agile. I can kind-of get my head around dwarves being solid and stronger than they look. I can’t deal with humans being able to brute-force block an attack by a giant troll because … physics!

When I see scenes of walking I think, “Not more friggin’ walking!”. When “the eagles” inevitably came I couldn’t help myself from exclaiming, “Oh for f*cks sake!” The two trilogies could have been condensed into two episodes of, “There’s a nasty thing we don’t like. Let’s send the eagles to solve our problems for us!”

As you’ve probably guessed by now, I was not very impressed with the film. This Hobbit trilogy should have been a single film. Two at the most. Making this into three films embodies everything I hate about the film industry! It was just filler shot after filler shot. Peter Jackson needs to get his head out of his ass and learn to make a film with some decent pacing.

I’ve come close to giving this a “toilet rating”, which has been reserved for Twilight films up until now…

In order to end on a positive note…

  • Benedict Cumberbatch voices were good.
  • Lee Pace looks so beautifully sinister as Thranduil. They could have made more of his part, rather than adding in Legolas in a ham-fisted attempt to appeal to the fans of the L.O.T.R. trilogy.
  • A couple of minutes of Cate Blanchett is always welcome.

Cheers

Tim…

PS. Like the other L.O.T.R. and Hobbit films, I think this is probably quite good to watch when you are sitting with a group from friends chatting and only paying it half your attention. I’m sure I’ll end up buying the DVD for this very purpose…

PPS. My friends liked it.

PPPS. It’s about 13 years since the L.O.T.R. was made, so I guess it’s about time for the Hollywood reboot of the series…

Happy Christmas from oracle-base.com

Happy Christmas to everyone out there, whether they observe the whole Christmas thing or not.

I often take a look at the website stats on occasions like this to see who is hanging about and this is what I can see at 08:00 UK time on Christmas day morning…
ChristmasMorningStats

What’s top of the list as far as content is concerned? Well that will be Data Pump of course! :)

Have a good day everyone and I hope 2015 is even better for you than 2014 was!

Cheers

Tim…

PS. FYI this is just a real-time snapshot of the people who were active as I checked. :)

Skin Game

Skin Game by Jim Butcher is the 15th book in The Dresden Files series.

For a quick recap, Harry Dresden is a wizard for hire. Look him up in the Chicago Yellow Pages. Magic is real. Ghosts are real. Fairies are tall, so beautiful it’s almost painful to look at them, and are so divorced from mortals that they seem evil. All the gods you’ve ever heard of are real. All the things that go bump in the night are real too…

In this book Harry is forced to work with some of his enemies to pull a bank job with a difference!

I love Harry Dresden and I love The Dresden Files. The books are always fast and furious, sometimes a little predictable, but in a good way, and ultimately really fun. I think every book has also had moments that have been genuinely emotional and inspirational.

If you liked the previous books you will love this. If Harry Dresden is not your kind of guy, there is something wrong with you… :)

Cheers

Tim…

Oracle REST Data Services (ORDS) 3.0 on Tomcat 7

What started out being a quick dabble with Oracle REST Data Services (ORDS) 3.0 spawned a bunch of different things…

First, I needed a fresh APEX installation to play around with. Most of the APEX stuff I’ve been using recently has been against Oracle 11.2 databases and I follow this kind of method. Standard stuff…

That’s fine, but the default installation of APEX in Oracle 12c multitenant environments splits APEX between the CDB and the PDBs. On the surface that sounds like a good thing. Installing APEX multiple times sounds like a waste right? In practice, this “shared APEX” situation is a complete pain in the ass and I hate it. So the first thing I now do on a new installation is to remove the shared APEX stuff. That spawned this note.

It’s actually a pretty important decision, because Oracle don’t support removal of APEX from the CDB once it has PDBs, so you will be faced with a bunch of unplugs and administration if you change your mind later. Having to decide up front if you may want to do something later is a total fail in my book. I hope Oracle scrap this shared APEX setup. I would prefer to see people create a new PDB from the seed, install APEX into it and use that as a seed for new PDBs. Effectively building their own gold-image seed… That’s my little rant over. :)

Next thing I needed was a Tomcat installation. Despite the ORDS 3.0 documentation suggesting Tomcat 7, I started with Tomcat 8. Word to the wise, it doesn’t work, at least not by just following the ORDS install docs anyway.

Since I was having problems with the ORDS installation on Tomcat 8, I switched back to the recommended Tomcat 7. This wasn’t so much a case of RTFM as a case of BTFM. The “B” is for Believe. :)

Once I switched to Tomcat 7, the ORDS installation went just fine. Here is a note about that…

So now we are four articles in and I’m just about able to start the thing I wanted to actually test. :) Like I said, a quick dabble spawned a bunch of different things… :)

Cheers

Tim…

The importance of backups and disaster recovery plans

Having tweeted, “When was the last time you practised the recovery of each of your production databases?”, a few days before, I was presented with a very real DR situation for my website.

On Wednesday my website went down while I was at my office Christmas lunch. I restarted it from my phone and it was all up, but I got a message saying one of the RAID1 disks was dead. I do nightly file system and database backups, but I took another backup anyway because I couldn’t remember what had changed during the day. In the evening I contacted the people hosting my dedicated server and asked them to swap out the dead disk. Several hours and several phone calls later it became obvious the data had “magically” disappeared from the second disk in the pair. Of course, it was nothing to do with the hosting company… I think the way I would describe them is a useless bunch of ****s.

I ended up initiating a re-image of the server. Their website says this should take about an hour. After a bit over 2 hours I gave up waiting and went to bed…

I woke up the next morning and started the process of rebuilding the server. Fortunately, I have a DR plan containing all the stuff I need to do to rebuild the server. The plan had one minor flaw, it included some references to my own articles. :) Luckily, these were available via Google Cache. I’ve now copied any relevant information into my DR plan, so when this happens next time I will be even more prepared. :)

Having a full set of file system and database backups meant I was confident I would get it sorted. The biggest problem was upload speed. Home broadband is great for download, but pretty terrible for upload, so my initial plan to just push all the backups to the server then start recovering it had to be revised. Instead I cherry-picked the most recent file system backup and pushed that first. Once on the server, the backup unzipped in a few seconds and the main part of the website was back. I then started to push the most recent DB backups and loaded those. I’ve altered the DR plan so next time I can easily do this, prior to a blanket “rsync” of all the backups back to the server. That should speed up the recovery somewhat.

Lessons people need to learn:

  • Having regular backups is good. (No shit Sherlock!) If you are on this blog you are probably an Oracle person, so this should not come as a surprise. I just wonder how many people don’t bother with them for their blog etc. In my case I self-host, so it is all down to me.
  • You’ve got to have a DR plan. It’s amazing how many bits of software you install over time. It’s also surprising how many odd little commands and config entries you put in over time. Unless you are tracking these manually, or through some admin tool, you are going to have a nightmare getting back. I didn’t remember half of the things I did during the setup of this stuff, but luckily I wrote it all down.
  • You’ve got to practice your DR plan. You don’t know if it works unless you try it. I moved my stuff on to this server a few months ago, so I was pretty confident the plan was viable. I do occasionally build a copy of my website on a VM to make sure the backups are working OK. Obviously, this was a pretty good “test”. :)
  • Remember to back up your backup scripts. My backups were pretty good, but I did forget to include the backup scripts in the backup, so I had to reinvent those. It wasn’t a big deal, but it was a silly mistake.
  • If you are looking for quick recovery of your blog/website, you probably don’t want to rely on backups sitting on your home servers/storage as the broadband upload speed (at least in the UK) is pretty terrible. Maybe investing in a cloud backup solution would be a better idea.

I think everything is sorted now. If not, I’ll pick it up as I go along and add the fix into the plan. :)

Sorry for any inconvenience. :)

Cheers

Tim…

Fun : Endorse my “new” skills…

For a bit of fun I’ve added the following “new” skills to my LinkedIn profile.

It would be really cool if we could get them to the top 4 in the list of my endorsed skills. Please make it a happy holiday by endorsing these. :)

Remember, this only works if you actively don’t endorse the other things!

Cheers

Tim…

PS. I’ve written before about how I think LinkedIn endorsements are a joke. It’s like believing all someone’s Facebook friends are really friends. IMHO if anyone uses LinkedIn endorsement as a measure of ability and employability they are a moron!

CrocodileWrestling

Update : I think some people have mistakenly assumed I was in some way trawling for real endorsements. I really wasn’t. Please endorse the joke ones and avoid or “unsub” the real ones. I want to get these joke ones to the top of the list. :)