Search

Top 60 Oracle Blogs

Recent comments

December 2018

Transitive Closure

This is a follow-up to a note I wrote nearly 12 years ago, looking at the problems of transitive closure (or absence thereof) from the opposite direction. Transitive closure gives the optimizer one way of generating new predicates from the predicates you supply in your where clause (or, in some cases, your constraints); but it’s a mechanism with some limitations. Consider the following pairs of predicates:

Virtual Patching or Good Security Design instead?

I got an email from someone recently who asked me about virtual patching for Oracle as they were running an out of date version of Oracle and were thinking that virtual patching maybe a good solution to make their database....[Read More]

Posted by Pete On 19/12/18 At 01:32 PM

UKOUG “Lifetime Achievement Award” Speaker Award

I was recently very honoured and flattered to have received the “Lifetime Achievement Award” Speaker Award from the UKOUG. I have only managed to get to the excellent UKOUG Tech conferences on a couple of occasions, so it was both a thrill and a surprise to be so honoured. Unfortunately, I wasn’t able to make […]

NULL predicate

People ask me from time to time if I’m going to write another book on the Cost Based Optimizer – and I think the answer has to be no because the product keeps growing so fast it’s not possible to keep up and because there are always more and more little details that might have been around for years and finally show up when someone asks me a question about some little oddity I’ve never noticed before.

The difficult with the “little oddities” is the amount of time you could spend trying to work out whether or not they matter and if it’s worth writing about them. Here’s a little example to show what I mean – first the data set:

Oracle VPD as a safeguard for DML

A new blog post on the Databases at CERN blog about using VPD Row-Level Security (DBMS_RLS) as a safeguard for the privileged users who need to bypass the application and run SQL directly: https://db-blog.web.cern.ch/blog/franck-pachot/2018-12-oracle-vpd-safeguard-dml

Little things worth knowing: the latest public-yum-ol7.repo configuration file enables an upgrade to UEK R5

For a little while now I have been using Ansible for all installation/configuration tasks I do in the lab. I can’t really be bothered to do these things by typing commands anymore: once you get the hang of Ansible, you can develop an urge to automate everything. As part of my playbook installing the Oracle database on Oracle Linux, I replace /etc/yum.repos.d/public-yum-ol7.repo with the current version from Oracle’s server to make sure I have the latest and greatest software available.

It’s all downhill past 30

Yes, it sounds like a lamentation of the aging process, and from my own experience, it is definitely true that the wheels started falling off for my knees, back and plenty of other body parts once I turned 30. But that is perhaps a legacy of too much alcohol, not eating well in my youth and failing to stretch rather than any particular chronological milestone Smile.

But this post is not about that. This one is about the magical 30 character limit on identifiers that we finally lifted in version 12c. For static SQL, this is obviously a no impact change – you either define your tables and columns with longer names or you don’t, and the static SQL you write reflects that position.

CPU Capacity planning from OEM metrics

The CPU used by your Oracle Database is expensive because it is the metric used by licensing. The more you can control and know what you need, the more freedom you will have to optimize the costs. With instance caging, available in all editions, you can put a soft limit. This means that:

  • you run on a limited number of threads and after a while, this gives a good idea of what you really need. You can forecast the capacity for a future consolidation.
  • you monitor ‘resmgr: cpu quantum’ and if activity is high you can decide to scale-up immediately, throttle some services, or do some query/design tuning.

In order to set instance caging, you need to define a value for CPU_COUNT according to the past activity. This post is the detail behind the following tweet:

Extreme Nulls

This note is a variant of a note that I wrote a few months ago about the impact of nulls on column groups. The effect showed up recently on a client site with a little camouflage that confused the issue for a little while, so I thought it would be worth a repeat.  We’ll start with a script to generate some test data:

Linux grub2 boot loader manipulation

This post is about how to manage grub2 in an easy way.

grub1

In the past, which is before linux EL7, the boot loader was grub, the grand unified bootloader (version 1). Things were very simple; if you installed another kernel (using rpm) it would add an entry to grub’s configuration in /boot/grub/menu.lst. If you wanted to change grub to boot that newly installed kernel by default you edit /boot/grub/menu.lst and set ‘default’ to the number, counting from zero, of the newly installed kernel, in the order of the kernels listed. If you wanted a certain option set for booting the kernel, you added it to the kernel line.