Over the course of the next hour, we are going to start with the basic framework of a complex query, and gradually take it apart looking at different optimizer options in isolation.
Once we’ve done that, we can put the query back together and examine the optimizer’s basic strategy, and what this means in terms of what is and is not possible.
Finally we note that the “best” execution path may be one that we choose because we have a solid understanding of the data. We may even decide on an execution path that the optimizer is literally unable to produce unaided.
So this is the basic structure of our query.
We have a simple join between two tables, with some simple filter conditions on both tables, and two subqueries, one operating against each table.
In it’s most general form, the predicates could be combined with a mixture of Ands and ORs; the subqueries could be INs, EXISTS, or their negation; or could simple be atithmetic comparisons on correlated, or non-correlated subqueries.
Here, to make the general structure more concrete, is an example with a couple of simple correlated existence tests.
We could imagine all sorts of ways in which the optimizer (or we) might choose to operate this query to get the result set with the minimum amount of work.
Before we look at the whole thing, though, let’s take parts of it in isolation.
We’ll start with a 10g feature – the qb_name() hint.
Every single query block (roughly speaking every subquery) in a SQL statement can be given a name with the qb_name() hint.
Once a query block has been named, we can make references to object in that query block from higher levels of the statement in the hints that we write. Every hint (probably) can have a new ‘first parameter’ which is the name of the query block to which it applies. The name has to be preceded with the @ sign.
Tables can then be qualified with the query block name (again with the @ sign) .
In this example, you’ll also notice the enhanced version of the leading() hint in 10g – you can specify every table in the query in the right order, and if there is a legal way to get to that order, Oracle will use it as the single join order it examines to optimize the query.
We haven’t included t4 in the leading() hint because it is set to “not unnest” and. So it is going to run as a late subquery.
Here’s the execution plan for our original query with the leading() hint from the previous slide included.
leading(t1@main, t3@main, t2@subq2)
Note that the order is t1, t3, then t2 with a semi-join, and t4 (no_unnest) as a filter.
One of the options for dbms_xplan.display() allows use to see table aliases and query block names – so we can see that we have a transformed block of three tables, plus one block which has not been transformed.
(Could we use no_query_transformation(@subq4) ? YES !!
Let’s go back to the transformations available for a single subquery).
In this case we could have a small volume of data in t1 that can be accessed very efficiently by an index on n2; and an efficient access path to find the corresponding rows in t2.
So Oracle converts the query into a nested loop join – stopping at the first success at t2 for every row it hits on t1.
But if there is no efficient access path into t2, the plan has to change. It could become a hash semi-join into t2.
However, we have an independent predicate on t2 that returns a few rows very efficiently, and we have an efficient way of getting into t1 based on the value of n1.
So the optimizer can generate a unique set of values from t2, then use those as the outer table in a nested loop join into t1.
In this case (which uses a completely different table structure) we have a unique index on (id_parent, id_child), and we are doing a classic: “most recent of” query.
The subquery is executed first, and the single result is used as an access predicate to allow Oracle a unique access into a unique index.
In this case, because of the OR clause, the optimizer cannot unnest the subquery – it has to pick up all the rows where n2 between 100 and 200, but also has to examine every other row in the table to check the subquery.
In principle, Oracle will run the subquery once for EVERY single row in the table (except the ones that have already passed the n2 test).
There is some aid from the problem of running a filter subquery for every row in the table. A feature (from 8.0) known as scalar subquery caching.
In general it is quite good to go for unnesting, because filter subqueries are unpredictable due to the hash algorithm used for caching.
How many times could this run ? Min 6, max 20,000. Response time 0.01 -> 150 seconds CPU. A small change in the actual data (a rogue value appearing) could make a massive difference in the number of times the query runs.
In 8i and 9i you get 256 values in the hash table. In 10g you get seem to get 1024 for numbers, but the table size is limited to 64KB in 10g, which means problems for unconstrained text values which deemed to be 4,000 bytes each.
Querying dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’) we can see the statistics for the last run of the most recent statement we executed. (You need to have serveroutput off for this to work, or the last statement will be the call to dbms_output.get_lines).
With the nested loop join, the data order of the driving column for the subquery s dictated by the first table – and this maximises the benefit of scalar subquery caching.
With the hash join, the data order of the driving column for the subquery is dictated by the second table. And this can mean we run the subquery more times.
……………….
© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Init.ora Parameter "_COMPLEX_VIEW_MERGING" Reference Note
- Understanding Bootstrap Of Oracle Database
- Understanding RAC Internals
- UNDERSTANDING CURSOR_SPACE_FOR_TIME WITH SAMPLE
- A Complete Understanding of RMAN Compression
- Troubleshooting and Understanding Applications Forms
- Understanding Device-mapper in Linux 2.6 Kernel
- Understanding and Reading System states dump
- Understanding Shared Pool Memory Structures Whitepaper
- Hash Joins Implementation and Tuning
最新评论