Oracle Scratchpad

April 28, 2009

Strategic Hints

Filed under: CBO,Execution plans,Hints,Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 7:19 pm BST Apr 28,2009

I try to avoid hinting SQL if possible as it is very hard to do well, but there are a few hints that give an overview of how a query should operate without trying to control the detail of what the optimizer does. These are the hints that I call the “strategic hints” (possibly “query block hints” would be a better name – but there may be hints at the query block level that I wouldn’t qualify as strategic), and an example came up on the comp.databases.oracle.server newsgroup recently that looks like an ideal example of how a couple of them could be used.

I have a query that looks like:

select ...
from
	tab1,
	tab2,
	tab3,
	tab4,
	view
where
	{filter on tab1 that reduces result set to 1 row}
	{ many other clauses...}
and	view.xx = tab1.xx
and	view.yy = tab2.yy
and	view.zz = tab2.zz
;

If I remove (comment out) the view and the where clauses lines related to it, the response time is immediate; if I run the select on the view alone, with “view.xx = value”, “view.yy = value”… as returned by the previous statement, the response time is also immediate.

I’d like the optimizer to see that computing my first result set, and then joining the view on these xx/yy/zz resulting from my “first” statement should be optimal, but the whole query together takes more than 2 minutes to complete

The developer has identified a good strategy for dealing with this query in a two-stage process – and the optimizer is not doing it automatically (possibly because it literally can’t, thanks to the “left deep” strategy that it adopts – my guess is that it has merged the view into the four-table join and re-ordered the tables in the resulting query block in an unsuitable way).

So we can take the description we’ve been given, and impose it at a high level on the query. Without looking at the exact query I couldn’t guarantee that what I suggest would work  – but the following may be the simplest and best way to address the requirement:

We’ve already been told that Oracle can work out the four-table join efficiently; we know that it can re-calculate the view efficiently for each set of values from the four-table join – so we describe exactly that high-level strategy in four hints: 

select
	/*+
		ordered
		no_merge(v1)
		no_merge(v2)
		push_pred(v2)
	*/
	...
from
	(
		select ...
		from
			tab1,
			tab2,
			tab3,
			tab4,
		where
			{ many clauses... }
	)	v1,
	view	v2
and	v2.xx = v1.xx
and	v2.yy = v1.yy
and	v2.zz = v1.zz
;

The purpose of these four hints is as follows:

  • Don’t try to merge the view with the four table join (no_merge x 2 )
  • Work out the join first, and the view second (ordered – but could use leading())
  • For each row from the join recreate the view (push_pred)

I’ve stuck with the hint syntax that will work in 8i, 9i – rather than using qb_name() and fully qualified hints which would be appropriate for 10g and 11g.  

I may have missed something important in the initial description, and there may be some important version-dependent detail that has to be considered – but this approach is a very solid starting point for getting Oracle to do the right thing with minimum risk of getting a silly execution plan at some random time in the future.

2 Comments »

  1. Could you please explain push_pred in bit more details here ?

    Comment by Vikas — May 2, 2010 @ 9:26 am BST May 2,2010 | Reply

    • I used the “search” facility in the right hand panel to check for “push_pred” appearing anywhere in the blog, and then added a link from this posting to the most appropriate hit.

      Comment by Jonathan Lewis — May 2, 2010 @ 10:39 am BST May 2,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers