Flashback Data Archive Enhancements in Oracle Database 12c

Having played around with Flashback Data Archive in 11g, I figured I would get through the 12c enhancements pretty quickly. I didn’t account for the fact I’m a donkey and can’t see the wood for the trees. Luckily, I know some people who aren’t stupid and they gave me a nudge in the right direction, allowing me to spot my silly mistake. Thanks Bjoern and Connor:)

So after lots of wasted time, here is the article.

For the most part, it’s an evolution, but the new bits are pretty darn cool. I guess a lot of people will focus on two main things:

  • It is now a free feature, provided you don’t use compression, available in all versions. The change to use no compression by default has been back-ported to 11.2.0.4, so it’s free there too, which is nice!
  • The contents of contexts can now be stored in the flashback archive, so you can have access to the USERENV and custom context values that were set in the session when the DML was processed. This makes it possible to replace all those crappy old audit triggers with FDA!

There’s some other stuff in there that’s kind-of nice too. I think it’s worth checking it out, especially at its new price. :)

Cheers

Tim…

 

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. :)

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…

Fedora 21 : Oracle 11g and 12c Installations

Fedora 21 has arrived and it’s now delivered in three focussed flavours (Workstation, Server and Cloud). This of course resulted in the usual articles from me.

As always, read the warnings before you start down this path.

From an Oracle installation perspective, it’s almost identical to Fedora 20. I chose to use the server flavour and install the “MATE Desktop” package group. I suspect others may prefer to start with the workstation release. Either way it should be fine.

As I suspected, switching my main desktop from Fedora to the MacBook means I care significantly less about this release than before, but I still have some upgrades I’ll need to plug through.

Cheers

Tim…

Oracle ACE Program : Thanks Folks!

As you will know from my previous post, I picked up the “UK Speaker Award” at UKOUG Tech 14. I’ve already done a bunch of thank you messages, but I’d just like to take a minute and thank the Oracle ACE Program for the part they have played in this.

Many of you will know, I was invited to the ACE program because of my online contributions to the Oracle community. Prior to joining the program I had not done any public speaking and had not been part of any user groups. I hadn’t even attended a SIG or a conference before. The first conference I ever attended was Oracle OpenWorld in 2006, where I picked up the “Oracle ACE of the Year” award. It was only from that point on that I started to present.

If I had not been invited to the program, I doubt I would have ever tried to present at an Oracle event. Even if I had taken the plunge, I doubt I would have done enough presenting to come away with a speaker award like this. The amount of people I’ve met over the years and the amount of advice they have given me on presenting has been invaluable.

I know I’m not the best presenter out there and I know I’ve still got a long way to go, but I honestly don’t believe I would have come this far without the help of the Oracle ACE Program, so many thanks to everyone involved in the program, both organisers and ACEs alike, for all your help over the last  8 years. It is very much appreciated!

Cheers

Tim…

UKOUG Tech 14 : Monday

The day started with a taxi ride into New Street station to get the 06:01 train to Liverpool Lime Street. I was a little dissapointed that the train was a sprint train, so there were no tables and no power sockets. When the ticket guy came through I asked about an upgrade to first class and it was only £25 for the return journey, so I paid the extra to get a table and a power socket. Two 90 minute journeys are so much more bearable when you can actually do something useful on them. From Lime Street, it was a quick taxi ride to the ACC where I picked up my pass and headed straight to the room for my first session.

I was up in the first block of speakers at 09:00 for my virtualisation session. I expected a very small crowd for a virtualisation session in the first slot of an Oracle tech conference. I got into double figures, so I was happy. I got some questions at the end also, which is always nice.

After chatting to Peter Scott and Richard Foote about Richard’s fake tan and botox, I went to the speaker lounge and spent some time chatting to a variety of people. I also got my Christmas present from my dad. I was kind-of surprised and the size of the cheque, but I promised not to tell the wife or daughter how much he gave me! They are jealous types.

Next up was Robyn Sands speaking about “Why Solid SQL Still Delivers the Best ROI”. Being a database guy I guess it’s pretty obvious I’m going to agree entirely with what Robyn says. :) A few of the key points included:

  • Good database and application design pays off many times over.
  • Set-based processing in the database will always out pace row-by-row processing, either in the database or in the middle tier, for large data sets.
  • If you can’t use set-based processing, array processing typically beats row-by-row processing.
  • Threading to fake parallelism is not a great replacement for true database parallelism, but it can give advantages in some circumstances.

There are always exceptions to the rules, but bad programming gives bad performance. The test harness used for the demos is really neat. It really does hammer home the point in a very clear way!

SpeakerAwardSmallI chatted through lunch and didn’t actually make it to the food. :) After lunch it was off to the keynotes, where the UKOUG gave out some awards. I picked up a “UK Speaker Award” for last years session on “Improving the Performance of PL/SQL Function Calls from SQL”. The award was based on the speaker evaluations, which makes it feel kind-of special. I wasn’t too happy with my performance during that particular session last year, but obviously some of the folks in the audience felt different. Just goes to show you can’t be objective about your own performance. I’ll no doubt act all blasé about it, like it does’t matter, but I’ll secretly get a t-shirt printed to wear around the house. :) Thanks to everyone who filled in the evaluations. It is very nice to get a pat on the back like this from time to time…

The keynote overran and I was sitting near the front, so I was a bit nervous about leaving early. As a result, I was the last person to arrive at my session. My second session was an introduction to Analytic Functions, which seemed popular. The room was full and Tom claimed he was turned away. I think he was on the wind-up! It’s always better to have a smaller room that’s full, that a large room that feels empty. :) I was a bit “giddy” during this session. I think it was a combination of things including lack of food, adrenalin, excitement over the award, having a few friends in the audience and it being my last presentation of the year. I remember it feeling like a fun session for me. Not totally sure how that translates to the audience reaction though. As an example of my “giddiness”, about 3/4 of the way through the session I noticed Dawn on the front row, right in front of me, stopped and said something like, “Oh. You’re here!”. I must have looked like I was on crack! :)

During the Analytics session I try to break things down to the simplest possible terms, so this is more like a teaching session than a conference presentation. Analytics is something you just have to get your hands dirty with. No amount conference presentations will actually make you feel comfortable with them, so rather than trying to get heavy, I’m trying to get people over the first hurdle and get them started. The response from this session is usually pretty good, but I’m always worried someone will miss the word “introduction” mentioned in the abstract and turn up expecting some deep dive type session. It’s always good when you learn stuff during sessions you present and I learnt a couple of things thanks to Stew Ashton. Happy days!

After another chatting session in the speaker lounge, it was off to see dad speak about “AWR: Looking Beyond the Wait Events and Top SQL”. This meant missing Tom’s “AskTom” session, but family comes first! :) I really liked this session. The main point dad was making was people often jump into the top-5 wait events and try to fix the problem, without doing the necessary prep work first. Guilty! Instead, people should look at the information in the AWR report and try to build a picture of the system and application architecture, before trying to fix the problem. In many cases, this process will actually highlight the problems. This is not an exhaustive list, but the sort of things he highlighted were:

  • Check the spec of the hardware at the top of the report. Specifically the core count.
  • Check the number of sessions. Is it excessive compared to the core count. Ideally it should be 1-10 times the core count. More than that may represent a problem.
  • Is the number of sessions increasing during the sample time (start compared to end)? Is this normal or a session leak?
  • Is the number of open cursors excessive? Is it growing? Is this a cursor leak, which suggests bad application code?
  • On the load profile, is the DB CPUs per second is higher than the number of cores, you are CPU bound. This will probably make other waits “over-report” as they are really CPU bound. Maybe the contents of the top 5 waits is an artefact of being CPU bound. Can you trust them?
  • If there are a high number of rollbacks compared to transactions (commits), the application is doing lots of work only to throw it away. Bad application design.
  • Check out the init.ora settings for non-default and hidden parameters that look suspicious. Are these necessary or legacy?

Only after you’ve built up this picture, should you jump to the top 5 waits and the top SQL sections. Do they confirm your picture of the system built up from the previous investigation?

I’m pretty sure I will end up at the same conclusions when looking at an AWR report, but I think this approach is much better than the way I do things, so I will try to adapt and see how it goes. If you didn’t manage to see the session, you should download the slides and check them out. I think there is a lot of value there. Well done dad!

After the last session we headed off to the exhibition hall to socialise for a bit. I talked to a whole bunch of people, which is the best bit about conferences. I also got my first food of the day, a chocolate fish from the Dbvisit stand. :) I spent quite a bit of time talking to Jacco Landlust, who is always a great source of info. His advice has been incredibly valuable in plugging up the gaping holes in my middle tier knowledge.

At about 19:40 I shot off to the station and got on the 20:04 train home. A table and a power socket again. Awesome. I also managed to scrounge and extra strong mint of someone, which was the second meal of the day. :) I got to Birmingham at about 21:45 and stuffed some food down my face before getting a taxi home.

So that’s the last conference of the year. Let’s see what 2015 brings…

Thanks to my company for paying for the train ticket and letting me have the day off work as a “training day”, rather than holiday, especially having just got back from a three week “holiday” doing the OTN APAC tour. :) Thanks to the UKOUG for inviting me again and making sure everything ran smoothly. Thanks to all the attendees this year and to everyone who filled in their evaluations from last year. Cheques are in the mail. :) I’ll do a separate post about the ACE Program

Cheers

Tim…

UKOUG Tech14 : It’s nearly here…

TECH14_ResourcePk_IS_v1I’ve literally just picked up my train tickets for Monday’s trip to Liverpool for UKOUG Tech14!

I’m only going to be there for Monday, so I’ve got a lot to pack in. I’ll be doing 2 sessions, at 09:00 and 14:30, so if you don’t want to come to my sessions, but want to meet up to chat, grab me before or after them. Geeking out is much more important than the sessions. :)

I thought I might be able to make the Sunday stuff this year, which I usually have to miss because of my nephew’s birthday party (got to get your priorities right). I thought I might make it this year because his birthday is on Wednesday (today), but they decided to do the party on the weekend following, rather than the weekend preceding, so no luck. One of these years the stars will align and I’ll get to do the whole event!

Anyway, I’m looking forward to meeting up with everyone again. Hopefully I will get to see the wife and kid. Not sure if dad (and master of social media) is going to make it this year…

Cheers

Tim…