I’m not very keen on bending the rules on production systems, I’d prefer to do things that look as if they could have happened in a completely legal fashion, but sometimes it’s necessary to abuse the system and here’s an example to demonstrate the point. I’ve got a simple SQL statement consisting of nothing more than an eight table join where the optimizer (on the various versions I’ve tested, including 12c) examines 5,040 join orders (even though _optimizer_max_permutations is set to the default of 2,000 – and that might come as a little surprise if you thought you knew what that parameter was supposed to do):
While creating a POC of a SQL rewrite recently I received a little surprise as I switched my query from serial execution to parallel execution and saw the optimizer’s estimated cost increase dramatically. I’ll explain why in a moment, but it made me think it might be worth setting up a very simple demonstration of the anomaly. I created a table t1 by copying view all_source – which happened to give me a table with about 100,000 rows and 1117 blocks – and then ran the query ‘select max(line) from t1;’ repeating the query with a /*+ parallel(t1 2) */ hint. From 22.214.171.124 here are the two execution plans I got:
That’s data that isn’t there until you look for it, sort of, from the optimizer’s perspective.
Here’s some code to create a sample data set:
create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select rownum id, mod(rownum-1,200) mod_200, mod(rownum-1,10000) mod_10000, lpad(rownum,50) padding from generator v1, generator v2 where rownum <= 1e6 ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; /
Now derive the execution plans for a couple of queries noting, particularly, that we are using queries that are NOT CONSISTENT with the current state of the data (or more importantly the statistics about the data) – we’re querying outside the known range.
“You can’t compare apples with oranges.”
Oh, yes you can! The answer is 72,731,533,037,581,000,000,000,000,000,000,000.
I was in Munich a few weeks ago running a course on Designing Optimal SQL and Troubleshooting and Tuning, but just before I flew in to Munich one of the attendees emailed me with an example of a statement that behaved a little strangely and asked me if we could look at it during the course. It displays an odd little feature, and I thought it might be interesting to write up what I did to find out what was going on. We’ll start with the problem query and execution plan:
There is a post from a good friend at Oracle entitled “Will the REAL SnapClone functionality please stand up?” and, as well-written and technically rich as the post is, I am particularly moved to comment on the very last and conclusive sentence in the post…
So with all of that, why would you look at a point solution that only covers one part of managing your Oracle infrastructure?
I’ve been told many times that the OMS for EM12c can take quite some time to start on Windows. Some told me it took anywhere from three to up to fifteen minutes and wanted to know why. I’ve done some research on the challenge and it is a complex one.
If you start moving objects around to try and reclaim space in a tablespace there are all sorts of little traps that make it harder than you might hope to get the maximum benefit with the minimum effort. I’ve written a couple of notes in the past about how to proceed and, more recently, one of the difficulties involved. This is just a brief note about a couple of ideas to make life a little easier.
Somewhere on this site is a post regarding how to validate against an XML schema…
Every so often our competitors like to spread false information about our solutions so as to gain unfair advantage. I wanted to address this for the Enterprise Manager Snap Clone functionality, so this blog post describes the Snap Clone solution as it currently stands (November 2014).
Let’s start by introducing Snap Clone functionality. I blogged on that a few months back, but a few months can be an eternity in software development terms, so here’s an update on the salient points of what Snap Clone does for you over the various EM releases: