Tag: database

Fedora 23 and Oracle 11gR2/12cR1

A few months ago I mentioned doing some Fedora 22 installations. At the time I did some pre-emptive installations on the Alpha release of Fedora 23 also.

Now the final release of Fedora 23 is out, I’ve run through the articles again to make sure things are all ship-shape.

It’s pretty much as it was before, with the nice bonus that the “MATE Desktop” package group has been fixed. Happy days! 🙂

As always, installations of Oracle server products on Fedora are not a great idea, as explained here.

If you do like playing with this stuff, knock yourself out… 🙂

Cheers

Tim…

Databases Running in the Cloud

cloudI’ve been playing around with running databases in the cloud recently. It’s quite simplistic stuff, just to get a feel for it and investigate the possibilities of using it for some projects at work. Here’s what I’ve got so far.

Overview:

Oracle:

MySQL:

SQL Server:

It’s hard to differentiate between the cloud providers if you are just using them to provide a VM and self managing a system on it. It’s just another box provider.

In contrast the DBaaS offerings are much more interesting. I really like what Amazon are doing with RDS for Oracle/MySQL/SQL Server. I think these would work very well for *our* MySQL and SQL Server installations, which tend to be quite simple. I’m not sure I can live with some of the restrictions for RDS for Oracle, but that’s probably because I’m a snobby DBA type, who thinks he knows best. 🙂 The DBaaS for SQL Server on Azure is also really nice. You get less control than the RDS version, but maybe that’s a good thing.

You might have noticed I’ve not written much about Oracle Cloud yet. I should be getting a trial of the platform this month, so I will be able to fill in those gaps then.

Cheers

Tim…

Statistics Collection Enhancements on Oracle Database 12c

I’ve been having a play around with the enhancements to the statistics collection in 12c. I’ve put together this top-level post with links to all my other articles on this subject.

Here are the new articles it links to.

It also links to some of the stuff I put out previously for the Adaptive Query Optimization functionality, as that is statistics related.

In a totally unrelated incident, I wrote this thing about the new READ object privilege, but forgot to mention it on the blog.

The journey continues…

Cheers

Tim…

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…

 

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…

Roll up! Roll up! Nothing new to see here! (Database Web Services)

Over the years I’ve written loads of stuff about consuming and publishing web services directly from the database. I’ve been doing quite a bit of this at work recently and I realised how difficult it is to find all the pieces, since they are spread across multiple articles, spanning multiple database versions. In an attempt to give a single point of entry I’ve written this very brief article.

It’s really more of a links page. 🙂

If you are new to the idea of using the database for web services, it might come as a surprise what you can do without having to turn to the dark side (middleware). 🙂

Cheers

Tim…

Update: This is new on my website though. 🙂

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…

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…