Search

Top 60 Oracle Blogs

Recent comments

December 2013

12c Adaptive Optimization – Part 2 – Hints

This is the second post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization. The first post is here: 12c Adaptive Optimization – Part 1. Since there were several comments and questions about hints and how they interact with Adaptive Plans, I decided to limit this 2nd post to that topic.

Q: Regarding turning off the adaptive optimization (particularly adaptive joins), will there also be a hint to disable it for a particular SQL?
Q: can we pick and choose SQL’s not to run this collector for

A: There are no specific hints to enable or disable Adaptive Plans as of 12.1.0.1. However, the OPT_PARAM hint does work with both the OPTIMIZER_ADAPTIVE_FEATURES parameter and the “_optimizer_adaptive_plans” parameter.

Why Oozie?

Thats a really frequently asked question. Oozie is a workflow manager and scheduler. Most companies already have a workflow schedulers – Activebatch, Autosys, UC4, HP Orchestration. These workflow schedulers run jobs on all their existing databases – Oracle, Netezza, MySQL. Why does Hadoop need its own special workflow scheduler?

As usual, it depends. In general, you can keep using any workflow scheduler that works for you. No need to change, really.
However, Oozie does have some benefits that are worth considering:

Bitmap join indexes

Here’s another of my “draft” notes that needs some exapansion and, most importantly, proof.

I have a fact table with a “status id” column that shows a massive skew. Unfortunately I have a dimension table that holds the “status code”, so (in theory, at least) I have to do a join from the statuses table to the fact table to find rows of a given status. Unfortunately the join hides the skew:

select  f.* 
from    facts f, statuses s
where   s.code = 'C'
and     f.status_id = s.status_id
;

The optimizer knows that the status_id column on the facts table has a highly skewed distribution and will create a histogram on it, but it can’t know which status code corresponds to which status_id, so the histogram doesn’t help in calculating the join cardinality.

Will a bitmap join index help ? Answer – NO.

Differences

Here’s an offering in my “drafts for someone else to finish” suite of postings; it’s one I’ve been meaning to retest and publish for quite some time. It’s a possible answer to a question that comes up occasionally on the OTN database forum: “How do I resynchronize two tables that are supposed to be copies of each other?”

I suppose it’s possible to interpret this question in a couple of ways, but sometimes it means – “anything in table 1 should also be in table 2, anything in table 2 should also be in table 1, and where a row exists it should be exactly the same in both tables”. There are two “philosophical” problems attached to the task, of course – first, how do you decide that two rows that are currently different from each other are supposed to be the same; second how do you decide which bits of which version hold the correct values.

String Aggregation of “Huge” Strings via XML

My goal was initially to concatenate all rows generated via the package DBMS_FEATURE_USAGE_REPORT in one big HTML (XML?) document instead of the by default generated...
class="readmore">Read More

Be aware of these environment variables in .bashrc et al.

This is a quick post about one of my pet peeves-statically setting environment variables in .bashrc or other shell’s equivalents. I have been bitten by this a number of times. Sometimes it’s my own code, as in this story.

Background

Many installation instructions about Oracle version x tell you to add variables to your shell session when you log in. What’s meant well for convenience can backfire. Sure it’s nice to have ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH, CLASSPATH etc set automatically without having to find out about them the hard way. However, there are situations where this doesn’t help.

Parallel Execution – 3

It’s finally time to take a close look at the parallel versions of the execution plan I produced a little while ago for a four-table hash join. In this note I’ll examine the broadcast parallel distribution. First, here’s a list of the hints I’m going to use to get the effect I want:

	/*+
		leading(t4 t1 t2 t3)
		full(t4) parallel(t4, 2)
		use_hash(t1) swap_join_inputs(t1) pq_distribute(t1 none broadcast)
		full(t1) parallel(t1, 2)
		use_hash(t2) swap_join_inputs(t2) pq_distribute(t2 none broadcast)
		full(t2) parallel(t2, 2)
		use_hash(t3) swap_join_inputs(t3) pq_distribute(t3 none broadcast)
		full(t3) parallel(t3, 2)
		monitor
	*/

If you compare this set of hints to the hints I used for the serial plan you’ll see that the change involves two features:

Buffer Pins

Sometimes you get some questions on OTN lead to very geeky investigations. Here’s one that came up a while ago that started with a reasonable observation about recursive subquery factoring, then devolved into a real geek-attack question about buffer headers (x$bh) and buffer pins (x$kccbf).

I contributed a couple of ideas and some basic SQL to the discussion but never got around to doing anything concrete. If anyone has time and is sufficiently curious to play around I’d be interested to see what you did and what conclusions you came to.

 

 

12c Subqueries

When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set: