Doug Burn’s sent me an email a couple of days ago about a question that had appeared on his blog in response to a comment I made several months ago.
I’ve followed up with an answer, of course, but reading the blog reminded me that some interesting stuff had come up about direct path reads and writes, as well as some comments on parallel query. So I thought I’d publish a link to the item so you could see the whole discussion.
Here’s a surprising anomaly that showed up in a question on the OTN forums a little while ago. Consider a simple query that uses a hash join between two tables.
pq_distribute(t2 hash hash)
from t1, t2
where t2.n1 = t1.n1
and t2.small_vc = t1.small_vc
When it runs serially the join completes in memory and the only I/O you see comes from the two tablescans. When the query runs parallel something causes a spill to the temporary tablespace.
When Oracle tries to optimize a complex SQL statement one of the first steps it takes is to transform it into something simpler. The ideal target (from the optimizer’s perspective) is to eliminate any subqueries and in-line views so that your SQL statement ends up as a list of tables in a single from clause. Every in-line view and subquery is a separate query block, and the optimizer really wants to turn the whole thing into a single query block. Unfortunately this isn’t always possible, and sometimes it isn’t even a good idea - which is why 10g introduced ‘cost base query transformation’.
Here’s a quirky little set of results from trace files. First a 10391 at level 64 – this shows the way in which an object was broken into granules for a parallel tablescan. I’ve just listed the first few granules: (more…)
Or as Doug Burns put it on his blog: how can I tell the actual DOP used for my parallel query”
As Doug points out, you first have to ask “When are you asking the question”. Immediately after you have run the query you could check v$pq_tqstat to see what happened, but apart from that you have very little hope of finding out in retrospect what happened in a specific query.
In the 9i Database Performance Tuning Guide and Reference part no: A96533-01, there is a convenient list of features that “require the CBO” on page 1-15, with the note
“Use of any of these features enables the CBO, even if the parameter OPTIMIZER_MODE is set to RULE.”
The list is as follows:
If you look at a 10053 trace file, you will find references to Resc and Resp (sometimes rsc and rsp) lurking in the background whenever the trace file mentions Cost. When you get to the 10gR2 trace file, you see these described in the “Legend” section as: (more…)
Looking back at some of my previous posts I was reminded how easy it is to overlook one important feature when trying to comment on another. In particular, in this case, a short posting on indexed access paths omitted any mention of parallel execution until a comment from David Aldridge jogged my memory. So here’s an important thought about parallel execution.