Here’s a thread from Oracle-L that reminded of an important reason why you still have to hint SQL sometimes (rather than following the mantra “if you can hint it, baseline it”).
I have a query that takes 77 seconds to optimize (it’s not a production query, fortunately, but one I engineered to make a point). I can enable sql plan baseline capture and create a baseline for it, and given the nature of the query I can be confident that the resulting plan will always be exactly the plan I want. If I have to re-optimize the query at any time (because it runs once per hour, say, and is constantly being flushed from the library cache) how much time will the SQL plan baseline save for me ?
The answer is NONE.
The first thing that the optimizer does for a query with a stored sql plan baseline is to optimize it as if the baseline did not exist.
This is a quick response to a question on an old blog post asking how you can adjust the high value if you’ve already got a height-balanced histogram in place. It’s possible that someone will come up with a tidier method, but this was just a quick sample I created and tested on 188.8.131.52 in a few minutes. (Note - this is specifically for height-balanced histograms, and it’s not appropriate for 12c which has introduced hybrid histograms that will require me to modify my “histogram faking” code a little).
I’ve already written about the 12cR3 to 12cR4 upgrade here. I did a few run through’s at home to practice it and it all seemed good.
Setting The Scene
Just to set the scene, for our production environment we run Cloud Control in a VMware virtual machine, using Oracle Linux 6.5 as the quest OS. With that setup, we can use a simple installation (DB and OMS on the same VM) and use VMware to provide our failover, rather than having to worry about multiple OMS installations and any DB failover technology etc. If there’s one thing I’ve learned about Cloud Control, it’s Keep It Simple Stupid (KISS)! As far as our managed servers go, most of our databases and all our middle tier stuff runs on VMware and Oracle Linux too. We have a handful of things still hanging around on HP-UX and Solaris, which will hopefully be migrated soon…
I’ve already talked about the recommendations we make for properly sizing an Enterprise Manager 12c and many already know about tuning a database, but let’s look at the tuning that may be a bit foreign to DBAs. We’ll start with Java.
We all know that it’s part of the EM12c architecture, but we often don’t realize that it requires attention to assist in Enterprise Manager running efficiently.
WebLogic 12cR3 was released towards the end of last week, so this weekend I had an install-fest.
I also did some minor amendments to some existing articles.
Those of you that have been around Enterprise Manager 12c for some time will recall the joy (for want of a better word!) we used to have configuring BI Publisher to work with Enterprise Manager. For those of you that haven’t looked at it yet, BI Publisher is the replacement for the now deprecated Information […]
There’s a neat little change to the Automatic Diagnostics Repository (ADR) in Oracle 12c. You can now track DDL operations and some of the messages that would have formerly gone to the alert log and trace files are now written to the debug log. This should thin out some of the crap from the alert log hopefully. Not surprisingly, ADRCI has had a minor tweak so you can report this stuff.
You can see what I wrote about it here:
Of course, the day-to-day usage remains the same, as discussed here:
There’s been some debate about how to get the parameters from a spfile. A spfile is a binary version of the parameter file of the Oracle database.
I added to the debate that my experience is that there are is some weirdness with using the strings command on the spfile. The discussion was on twitter, I didn’t add that doing that it most of the time meant it costed more time than I saved from using the “shortcut” of using strings on a spfile.
Let me show you what it means.
I’ve got a database with storage on ASM. Among other options, there are two simple methods to get the spfile from ASM:
You can get the spfile by logging on to the database, and create a pfile from the spfile, and create a spfile again:
At the Accenture Enkitec Group we have a couple of Exadata racks for Proof of Concepts (PoC), Performance validation, research and experimenting. This means the databases on the racks appear and vanish more than (should be) on an average customer Exadata rack (to be honest most people use a fixed few existing databases rather than creating and removing a database for every test).
Nevertheless we gotten in a situation where the /etc/oratab file was not in sync with the databases registered in the cluster registry. This situation can happen for a number reasons. For example, if you clone a database (RMAN duplicate), you end up with a cloned database (I sincerely hope), but this database needs to be manually registered in the cluster registry. This is the same with creating a standby database (for which one of the most used methods is to use the clone procedure with a couple of changes).
One of the quirky little anomalies of the optimizer is that it’s not allowed to select rows from a table after doing an index fast full scan (index_ffs) even if it is obviously the most efficient (or, perhaps, least inefficient) strategy. For example: