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.

EM Cloud Control 12c : The 24 Hour DBA

I think I’ve lived through all the ages of Enterprise Manager. I used the Java console version back in the days when admitting you used it got you excommunicated from the church of DBA. I lived through the difficult birth of the web-based Grid Control. I’ve been there since the start of Cloud Control. I’ll no doubt be there when it is renamed to Big Data Cloud Pixie Dust Manager (As A Service).

I was walking from the pool to work this morning, checking my emails on my phone and it struck me (not for the first time) that I’m pretty much a 24 hour DBA these days. I’m not paid to be on call, I’m just a 9-5 guy, but all my Cloud Control notifications come through to my phone and tablet. I know when backups have completed (or failed). I know when a Tnsping takes too long. I know when we have storage issues. I know all this because Cloud Control tells me.

Now you might look on this as a bad thing, but being the control freak I am, I prefer to get a message on a Sunday telling me something is broken, hop on the computer and fix it there and then, rather than coming in on Monday to a complete sh*t-storm of users complaining. I’m not paid to do it, but that’s the way I roll.

While walking down memory lane I was thinking about all the scripting I used to do to check all this stuff. Endless amounts of shell scripts to check services and backups etc. I don’t do hardly any of that these days. Cloud Control handles all that.

We are a pretty small Oracle shop, but I think life would be a whole lot more difficult without Cloud Control. I’ve mentioned this a number of times, but it’s worth saying again… If you have more than a handful of Oracle databases, you really should be using Cloud Control these days. It’s as simple as that.

Just in case you are wondering, this is how our infrastructure looks this morning… :)

em-all-green

Cheers

Tim…

Invoker Rights in Oracle Database 12c : Some more articles

I wrote about the Code Based Access Control (CBAC) stuff in Oracle Database 12c a while back.

I’ve recently “completed the set” by looking at the INHERIT PRIVILEGES and BEQUEATH CURRENT_USER stuff for PL/SQL code and views respectively.

It’s pretty cool, but I’m not sure how much of it I will see in the wild as it will require developers to do a bit more thinking, rather than doing what they’ve always done… :)

Cheers

Tim…