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…

 

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 Database 12c : EXPAND_SQL_TEXT, APPROX_COUNT_DISTINCT, Session Sequences and Temporary Undo

 

While I was away on the OTN APAC tour, I wasn’t really able to sink my teeth into anything, so inspired by Connor McDonald‘s session in Perth, I decided to write up a few bits and pieces in my free moments in the hotels and planes (when I had enough elbow room to type).

Here is what I’ve neatened up so far.

I’ve got a couple of other things that are part done, which will no doubt be coming out over this week…

Cheers

Tim…

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

 

I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. :) I’m thinking about Hooked on Monkey Fonics now…

At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? :)

I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. :)

Cheers

Tim…

JSON Support in Oracle Database 12c (12.1.0.2)

 

I spent a bit of time at OpenWorld looking at the JSON support in Oracle Database 12c. I started to write some stuff about it on the plane home and I spent the last two mornings finishing it off. You can see the results here.

I’ve tried to keep it light, since the documentation does a pretty good job at explaining all the variations of the syntax. I’ve also avoided trying to teach people about JSON itself. There is loads of stuff about that on the net already.

For the most part I think the JSON support looks pretty cool. During the process of writing the articles I did notice a few of things that I thought might confuse.

  • Using dot notation to access JSON in SQL seems like a neat solution, but each reference results in a query transformation that may well leave you with a whole bunch of function calls littered around your SQL. The end result is probably not what you want. I think it is probably better to avoid it and write all the direct function calls yourself, so you know exactly what the optimizer will do.
  • Typically the query transformations of dot notation result in a JSON_QUERY function call, but the optimizer can substitute a JSON_VALUE call if there is an index that it can take advantage of. That can be a little confusing when you aren’t expecting it. Once again, it might be better to avoid dot notation so as not to confuse.
  • If you are careful, the indexing of JSON data is pretty straight forward, but if you aren’t aware of how the query transformations work or you forget how very small changes in function parameters affect index usage, you can chase your tail trying to figure out why you aren’t able to use your indexes.

Until the REST APIs are released, the only way you can use this stuff is from the server side, so it’s not really something you can hand out to developers who are expecting to use just another document store. I had a play with the REST stuff during a hands-on lab at OpenWorld and it looked kind-of cool. When it’s released I’ll write an article about it and run it by some of the folks at work to see how they think it compares to other document databases…

Cheers

Tim…

PL/SQL New Features in Oracle Database 12c

 

I recently put some more PL/SQL new features articles live.

I’ve also posted a top-level new features article.

This contains a number smaller features as well as links to other articles on the site that discuss some of the new features in greater depth.

I’ve got a couple of PL/SQL books I’ve got to read and review, but I’ve been holding back because I wanted to get my take on this subject written before I was influenced by others. I guess I don’t have that excuse any more. :)

Cheers

Tim…

Temporal Validity, ACLs, External Tables, SQL*Loader and more in Oracle 12c

 

Some more 12c articles have trickled out over the last few days.

I kind-of mentioned this next thing in a post a few weeks ago, but didn’t name names. :) While writing an article about the PDB logging clause in 12.1.0.2 I noticed it didn’t work. I raised an SR with Oracle Support and they confirmed it was a bug. I was not planning to release the article until the bug was patched, but it came up in conversation recently and I decided it was better to release the article with a big fat warning on the top saying it doesn’t work, just so others are not as confused by this as I was. I’m still not sure it is the right thing to do, but what the heck…

When the bug is patched, I will revise the article and probably promote it to the front page of the website as a “new article”. For now it is lurking in the depths of my website. :)

The 12c journey continues…

Cheers

Tim…

A few more 12c articles

 

A few more 12c articles went live over the last few days…

The DMU and In-Database Archiving are from the OCP syllabus. The Invisible Columns stuff seemed like a natural thing to mention, when discussing the In-Database Archiving.

The 12c journey continues…

Cheers

Tim…

 

To PDB or not to PDB : The final decision

 

After yesterday’s to PDB or not to PDB post, I decided the answer was “to PDB”. Here’s what I did…

  • Installed the Oracle 12c (12.1.0.2) software. There is an installation article here, but all I had to do was a software-only installation because the OS already met all the prerequisites because of the existing 11.2.0.3 installation.
  • Upgrade the existing 11.2.0.3 instance. See here. I could have stopped at this point, but as I said I decided “to PDB”. :)
  • Created an empty CDB instance on the box using “dbca”.
  • Created a new PDB as a remote clone of the non-CDB instance, as described here.
  • Turned off the non-CDB instance.

Job done. So far it’s looking good. I’m going to do some messing about tomorrow to make sure it registers with Cloud Control properly and the backup schedule is sorted. Then I’ll give it to the folks to test their apps against.

Thoughts:

  • I flippin’ love the remote cloning of non-CDBs. I’ve played with it while writing the article about it, but seeing it happen on a real database was really exciting.
  • I think we all realise that this is version 1.1 of the multitenant architecture. The question is, is version 1.1 good enough at this point? The testing will determine that, not my excitement levels.
  • The testing will be based on our use of the DB. We are a small operation with quite simple needs. If we choose to go this route it will be because it is right for us. Depending on your usage, your experience may be different.
  • If things don’t work out with this POC, we will try with the non-CDB instance.

So it was kind-of exciting, fun and scary all rolled into one… :)

Cheers

Tim…

To PDB or not to PDB

 

I’m about to start a Proof of Concept (POC) for a 12c upgrade of one of our databases. The production database in question is running on Oracle Linux inside a VMware virtual machine, so the starting point I’ve been given for the POC is a clone of the whole VM…

Probably the biggest decision I’ve got to make is “to PDB or not to PDB” *. I mentioned it on Twitter earlier and got some conflicting opinions. I guess the pros and cons of the PDB approach go something like this in my head.

Pros:

  • The multitenant architecture is the future of Oracle. Depending on which rumours you believe, it’s possible that 12.2 will no longer allow the pre-12c style instances. Putting it off is delaying the inevitable.
  • As long as you only use a single PDB, there is no extra cost.
  • The multitenant architecture has some neat features related to cloning, especially remote clones. That potentially makes provisioning new environments pretty quick.
  • Even with a single PDB per CDB, there are potential advantages regarding patching and upgrades. Caveats apply as always.
  • I’m going to upgrade to a pre-12c style instance first anyway, so I will have a natural fallback position ready to go if I need it.
  • It would be good to invest the time up front to convert stuff now, rather than wait a few years to clean up the mess of CRON jobs and connections using SIDs, rather than services. This choice would force our hand.
  • If some of the technologies we are using are not going to “play well” with the multitenant architecture, I would rather know now than later.

Cons:

  • Using a PDB is definitely going to break a number of things for us, especially CRON jobs that run scripts using OS authentication. See here.
  • Once the decision has been made to “switch the multitenant architecture on”, it would be really easy for someone to create an extra PDB and incur additional licensing costs. As far as I’m aware, there is nothing to restrict the number of PDBs to 1, to prevent an uninitiated DBA from copying a script from the net and creating more. If someone knows an undocumented parameter for this I would be interested in knowing it. Note, “_max_pdbs” isn’t the answer here! :)
  • I’m going to upgrade to a pre-12c style instance first, so why add on the extra effort of cloning that to a PDB?
  • Why make life hard for yourself? You can use 12.1 as a half-way house and make the final step later.

I don’t think there is really a right or wrong answer in this debate. I could probably put forward a convincing argument in favour of either option. I’m leaning on the side of the “to PDB” choice. If this proves to be a no-go, then I’ll start a POC of a pre-12c style instance… :)

Despite my leaning for the PDB choice, I am interested to know what others think, especially those that have done something a bit more extensive than running this stuff on their laptop. :)

Cheers

Tim…

* I forgot to mention previously, we will almost definitely be going with a single PDB per CDB (the free option) initially. So this is not a “consolidate using multitenant” issue from the outset.