SQL Plan Directives: they’re always good… except when they’re bad!

The new Oracle 12c optimizer adaptive features are just great and work well out of the box in most cases.

Recently, however,  I’ve experienced my very first problem with SQL Plan Directives migrating a database to 12c, so I would like to share it.

Disclaimer 1: this is a specific problem that I found on ONE system. My solution may not fit with your environment, don’t use it if you are not sure about what you’re doing!

Disclaimer 2: despite I had this problem with a single SPD, I like adaptive features and I encourage to use them!!

Problem: a query takes a sub-second in 11gR2, in 12c it takes 12 seconds or more.

V_TAB_PROP is a very simple view. It just selects a central table “TAB” and then takes different properties by joining  a property table “TAB_PROP”.

To do that, it does 11 joins on the same property table.

On the property table, TAB_PROP_ID and PROP_ID are unique (they compose the pk), so nested loops and index unique scans are the best way to get this data.
The table is 1500Mb big and the index 1000Mb.

This was the plan in 11g:

In 12c, the plan switches to adaptive, and half of the joins are converted to hash joins / full table scans:

However, the inflection point is never reached. The execution keeps the default plan that has half of the joins HJ and the other half NL.

The problem in this case is the SQL Directive. Why?

There are to many distinct values for TAB_ID and the data is very skewed.

The histogram on that column is OK and it always leads to the correct plan (with the adaptive features disabled).
But there are still some “minor” misestimates, and the optimizer sometimes decides to create a SQL Plan directive:

The Directive instructs the optimizer to do a dynamic sampling, but with a such big and skewed table this is not ok, so the Dynamic sampling result is worse than using the histogram. I can check it by simplifying the query to just one join:

What’s the fix?

I’ve tried to drop the directive first, but it reappears as soon as there are new misestimates.
The best solution in my case has been to disable the directive, an operation that can be done easily with the DBMS_SPD package:

I did this on a QAS environment.
Because the production system is not migrated to 12c yet, it’s wise to import these disabled directives in production before the optimizer creates and enables them.

Off course, the directives can’t be created for objects that do not exist, the import  has to be done after the objects migrate to the 12c version.

Because the SQL Plan Directives are tied to specific objects and not specific queries, they can fix many statements at once, but in case like this one, they can compromise several statements!

Monitoring the creation of new directives is an important task as it may indicate misestimates/lack of statistics on one side or execution plan changes on the other one.

Oracle RAC and the Private Cloud. And why small customers are not implementing it. Not yet.

Cloud. What a wonderful word. Wonderful and gray.
If you are involved in the Oracle Community, blogs and conferences, you certainly care about it and have perhaps your own conception of it or ideas about how to implement it.

My Collaborate 2015 RAC SIG experience

During the last Collaborate Conference, I’ve “tried” to animate the traditional RAC SIG Round-Table  with this topic:

In the last few years, cloud computing and infrastructure optimization have been the leading topics that guided the IT innovation. What’s the role of Oracle RAC in this context?

During this meeting leading RAC specialists, product managers, RAC SIG representatives and RAC Attack Ninjas will come together and discuss with you about the new Oracle RAC 12c features for the private cloud and the manageability of RAC environments.

Join us for the great discussion. This is your chance to have a great networking session!

Because it’s the RAC SIG meeting, most of the participants DO HAVE a RAC environment to manage, and are looking for best practices and ideas to improve it, or maybe they want to share their experiences.

I’ve started the session by asking how many people are currently operating a private cloud and how many would like to implement it.

With my biggest surprise (so big that I felt immediately uncomfortable), except one single person, nobody raised the hand.


I’ve spent a very bad minute, I was almost speechless. I was actually asking myself: “is my conception of private cloud wrong?”. Then my good friend Yury came in help and we started the discussion about the RAC features that enable private cloud capabilities. During those 30 minutes, almost no users intervened. Then Oracle Product Managers (RAC, ASM, QoS, Cloud) started explaining their point of view, and I suddenly realized that

when talking about Private Cloud, there is a huge gap between the Oracle Private Cloud implementation best practices and the small customers skills and budgets.

When Oracle product managers talk about Private Cloud, they target big companies and advice to plan the infrastructure using:

  • Exadata
  • Full-pack of options for a total of 131k per CPU:
    • Enterprise Edition (47.5k)
    • Multitenant (17.5k)
    • Real Application Clusters (23k)
    • Partitioning (11.5k)
    • Diagnostic Pack (7.5k)
    • Tuning Pack (5k)
    • Lifecycle Management Pack (12k)
    • Cloud Management Pack (7.5k)
  • Flex Cluster
  • Policy Managed Databases
  • Quality of Services Management
  • Rapid Home provisioning
  • Enterprise Manager and DBaaS Self Service portal

The CapEx needed for such a stack is definitely a show stopper for most small-medium companies. And it’s not only about the cost. When I gave my presentation about Policy Managed Databases at Collaborate in 2014, and later about Multitenant and MAA at Open World, it was clear that “almost” nobody (let’s say less than 5%, just to give an idea) uses these new technologies. Many of them are new and, in some cases, not stable. Notably, Multitenant and QoS are not working together as of now. Qos will work with the new resource manager at PDB level only in release 12.2 (and still not guaranteed).

For the average company (or the average DBA), there is more than enough to be scared about, so private cloud is not seen as easy to implement.

So there’s no private cloud solution for SMBs?

It really depends on what you want to achieve, and at which level.

Based on my experience at Trivadis, I can say that you can achieve Private Cloud for less. Much less.

What a Private Cloud should guarantee? According to its NIST definition, five four things:

  1. On-demand self-service.
  2. Broad network access.
  3. Resource pooling.
  4. Rapid elasticity.
  5. Measured service.

Number 5 is a clear field of EM, and AWR Warehouse new feature may be of great help, for free  (but still, you can do a lot on your own with Statspack and some scripting if you are crazy enough to do it without Diagnostic pack).

Numbers 3 and 4 are a peculiarity of RAC, and they are included in the EE+RAC license. By leveraging OVM, there are very good opportunities of savings if the initial sizing of the solution is a problem. With OVM you can start as small as you want.

Number 1 depends on standards and automation already in place at your company. Generally speaking, nowadays scripting automatic provisioning with DBCA and APEX is very simple. If you’re not comfortable with coding, tools like the Trivadis Toolbox make this task easier. Moreover, nobody said that the self-service provisioning must be done through a web interface by the final user. It might be (and usually is) triggered by an event, like the creation of a service request, so you can keep web development outside of your cloud.

Putting all together

You can create a basic Private Cloud that fits perfectly your needs without spending or changing too much in your RAC environment.

Automation doesn’t mean cost, you can do it on your own and keep it simple. If you need an advice, ideas or some help, just drop me an email (myfirstname.mylastname@trivadis.com), it would be great to discuss about your need for private cloud!

Things can be less complex than what we often think. Our imagination is the new limit!