4.2 Controlling Plans on Oracle

Oracle currently offers two completely different optimizers, the rule-based optimizer (RBO) and the cost-based optimizer (CBO), and the methods for tuning on each differ.

The RBO is Oracle's original automated optimizer, back from the days of Oracle Version 6 and earlier. By rule-based, Oracle means that the optimizer uses only fixed properties of the tables, indexes, and SQL to guess an optimum execution plan from a set of simple rules of thumb (or heuristics) built into the automated optimizer. The RBO uses no data about the sizes of the tables or indexes, or about the distribution of data within those objects. It does use data on the fixed properties of the indexes: whether they are unique, which columns they cover, in which order, and how well those match up with the most selective-looking filter conditions and joins in the SQL. As tables grow and data distributions change, the RBO should go right on delivering the same plan indefinitely, as long as you don't alter the indexes (for example, from unique to nonunique) or change the table structure (for example, from an ordinary table to a partitioned table). However, at some future time, perhaps even in Oracle Database 10g, Oracle will drop all support for the rule-based optimizer, and cost-based optimization will become your only choice.

Since Oracle7, the RBO has been even more stable than before, because Oracle chose to freeze the RBO code beginning with Oracle7, except for rare, slight changes necessary to deliver functionally correct (as opposed to necessarily optimum) results. Therefore, an execution plan that is correct on the RBO today will likely stay unchanged until Oracle drops the RBO altogether. This is appealing from the perspective of stability, although the dark side of this stability is that the execution plans never get any better either.

Execution plans on the RBO never change to adapt to changing data distributions, and this is often cited as an argument to switch to the CBO. However, in my own experience, data-distribution change is the least of the reasons for cost-based optimization. In over 10 years, I have yet to find a single case in which it was important to use different execution plans for different real-world data distributions with the same SQL.

I have seen many cases in which one plan is not perfectly optimal for all real-world data distributions, but in all these cases, one robust plan exists that is at least nearly optimal across the board.

Another argument cited in favor of the CBO is that it can deliver parallel execution plans, plans that can bring multiple processors to bear on the SQL statement at once. I have not found this to be a compelling argument, since I have yet to find a real-world case in which the optimum SQL, with the optimum database design, required parallel execution for adequate performance. I expect some such cases exist in data-warehousing environments, which are not where most of my experience lies, I admit, but almost all cases in which parallel execution plans appear to shine are really covering up some mistake in database design, indexing, or application design, compensating for design deficiencies with horsepower. That, by itself, would not be such a bad thing; extra horsepower might be cheaper than fixing the application. However, parallel plans are usually in service of large batch processes, competing heavily for resources with online processes that are more critical to end users. Therefore, parallel plans often rob needed resources from other processes that are more critical.

These are the strongest arguments against using the RBO:

• It will become unavailable in some future release, perhaps during Oracle Database 10g, and you will not be able to use an older release forever.

• The CBO keeps getting better, while the RBO is stuck with all the old problems it has ever had.

• The CBO has a huge inherent advantage in the information available to it to calculate the best plan.

• The RBO cannot take advantage of features created since the CBO appeared in Oracle7, and in most cases the RBO will simply push queries that involve newer object types, such as bit-mapped indexes, off to the CBO. (See the following section, Section 4.2.1, for details about which features the RBO cannot handle.)

That said, the RBO does a surprisingly good job; its heuristics are well designed to get along with the tiny amount of information that the RBO uses to guess the best plan. In Chapter 6, I will describe properties of what I call a robust execution plan, one that behaves well across a wide range of data distributions. The RBO almost always delivers a robust plan when the necessary indexes are available and when the developer has not prevented use of an index with some index-disabling expression, as discussed earlier in this chapter. Given the right indexes, you can almost always get the best robust plan on either optimizer, with manual tuning. With automated tuning, the biggest advantage of the CBO is that it is more resourceful when dealing with imperfect indexing and nonoptimally written SQL; more often, it delivers at least an adequate plan in these cases, without manual tuning. When more than one robust plan is possible, the CBO is also more likely to find the best robust plan, while the RBO will pick one without knowing relative costs, unless you manually tune the SQL .

4.2.1 Controlling the Choice of Oracle Optimizer

It is unrealistic to optimize Oracle queries simultaneously for both the rule-based and the cost-based optimizers. Therefore, you should understand the factors that lead Oracle to choose which optimizer it applies, so that you can control those factors and get the optimizer you choose.

The RBO cannot handle certain object types and object properties that did not yet exist when Oracle froze the RBO code. However, rather than simply have its code error out, Oracle modified the RBO code just enough to let it recognize the cases it cannot handle and to have it pass those cases on to the CBO. Thus, even if you think you have set up your system for rule-based optimization, the following circumstances will absolutely force cost-based optimization:

• Bit-mapped indexes on any column of a table referenced in the SQL, even if those indexes are on columns the SQL does not touch.

• Function-based indexes in a table referenced in the SQL, if such and index is on an expression the SQL references.

• Partitioned tables touched by the SQL.

• Tables or indexes configured with parallel degree. The optimizer interprets these as a command to find parallel execution plans, which the RBO does not know how to do. As for bit-mapped indexes, indexes configured with parallel degree will disable use of the RBO on a table referenced by your SQL, even if the parallel-degree index is on columns the SQL does not touch.

CREATE INDEX Order_Ship_Date
ON Orders(Ship_Date)
PARALLEL 10;

ALTER INDEX Order_Ship_Date PARALLEL 1;

SELECT Index_Name
FROM ALL_INDEXES
WHERE Degree!=1;

If the tables and indexes involved in your SQL do not prevent using the RBO, Oracle chooses between the RBO and the CBO as follows:

In the last three steps of this decision cascade, Oracle chooses according to an optimizer_mode parameter, which you or your DBA sets. These are the four possible parameter values and how they affect the choice:

RULE

Oracle uses rule-based optimization.

ALL_ROWS

Oracle uses cost-based optimization with the goal of minimizing the cost of the whole query. This default version of cost-based optimization sometimes results in nonrobust plans (plans that use join methods other than nested-loops), with risks described in Chapter 6. However, the optimizer chooses these plans only when it calculates that they are faster than the best robust plans.

FIRST_ROWS

Oracle uses cost-based optimization with the goal of minimizing the cost of reaching the first rows from the query. In practice, this tends to favor robust, nested-loops plans similar to those plans the rule-based optimizer favors but built with much more knowledge of the data distributions and probable execution costs. The FIRST_ROWS optimization level creates the same effect as the OPTIMIZE FOR 1 ROW hint on DB2 and the OPTION(FAST 1) hint on SQL Server.

CHOOSE

Oracle uses cost-based optimization, as for the ALL_ROWS goal, unless no table or index involved in the query has optimization statistics available, in which case Oracle uses rule-based optimization.

Here's a quick way to check the instance-level parameter for optimizer_mode:

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'optimizer_mode';

When you have an execution plan in PLAN_TABLE, a quick way to see whether it is cost-based is to run the following query:

SELECT POSITION FROM PLAN_TABLE WHERE ID=0;

This returns the cost of the entire execution plan, in arbitrary units, when the plan is cost-based. When cost is not null, you have a cost-based plan.

4.2.2 Controlling Oracle Rule-Based Execution Plans

Most of the methods for controlling rule-based execution plans are the universal techniques of controlling plans, covered in the first section of this chapter. The primary Oracle-specific method of tuning under a rule-based default optimizer_mode is simply to switch modes to cost-based optimization, usually with a hint such as /*+ FIRST_ROWS */. In other words, you can always control a plan via hints, and hints (with the exception of the /*+ RULE */ hint) in a statement cause Oracle to use the CBO for that statement.

However, if you prefer not to use cost-based optimization, thus precluding the use of hints, one RBO-specific technique remains: in the FROM clause, list tables and their aliases in exactly the reverse order from the join order you want. This usually gives enough control of the join order, without using the techniques described earlier in Section 4.1.4. In particular, eligible, unique joins toward primary keys happen in the reverse order they are listed in the FROM clause, without changing the join conditions. For example, consider this query:

SELECT /*+ RULE */ E.First_Name, E.Last_Name, E.Salary, LE.Description,
       M.First_Name, M.Last_Name, LM.Description
FROM Locations LM, Employees M, Locations LE, Employees E
WHERE E.Last_Name = 'Johnson'
  AND E.Manager_ID=M.Employee_ID
  AND E.Location_ID=LE.Location_ID
  AND M.Location_ID=LM.Location_ID 
  AND LE.Description='Dallas';

Unlike the earlier version of this query in Chapter 3, which had the wrong order in the FROM clause, you now get the correct join order. In this correct execution plan, E joins to LE before joining to M or LM, as shown by the plan output:

SQL> @ex

PLAN
----------------------------------------------------------------------
SELECT STATEMENT
  NESTED LOOPS
    NESTED LOOPS
      NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID 4*EMPLOYEES
          INDEX RANGE SCAN EMPLOYEE_LAST_NAME
        TABLE ACCESS BY INDEX ROWID 3*LOCATIONS
          INDEX UNIQUE SCAN LOCATION_PKEY
      TABLE ACCESS BY INDEX ROWID 2*EMPLOYEES
        INDEX UNIQUE SCAN EMPLOYEE_PKEY
    TABLE ACCESS BY INDEX ROWID 1*LOCATIONS
      INDEX UNIQUE SCAN LOCATION_PKEY

When the RBO otherwise has no preference based on the conditions and indexes, the RBO joins tables by working from right to left in the FROM clause. However, this method offers only limited control by itself, because the RBO follows its other rules of thumb before considering the join order in the FROM clause. For example, the RBO always chooses to perform unique indexed reads and joins before doing indexed range scans, when it can.

4.2.3 Controlling Oracle Cost-Based Execution Plans

There are two main parts involved in tuning on the Oracle CBO:

• Providing the optimizer with good statistics about the tables and indexes, so it can calculate the costs of alternatives accurately. This is effectively a prerequisite to any manual tuning on a CBO.

• Adding hints to queries that the CBO fails to optimize well even with complete statistics about the tables and indexes that the queries reference.

4.2.3.1 Oracle cost-based optimizer prerequisites

Proving that a little knowledge is a dangerous thing, cost-based optimizers often do a terrible job if they do not have statistics on all the tables and indexes involved in the query. It is therefore imperative to maintain statistics on tables and indexes reliably, including regenerating statistics whenever table volumes change much or tables or indexes are rebuilt. It is safest to regenerate statistics periodically, during times that load is relatively quiet, such as nightly or at least weekly. The best way to generate and update statistics is with Oracle's DBMS_STATS package, documented at length in Oracle8i Supplied PL/SQL Packages Reference and Oracle9i Supplied PL/SQL Packages and Types Reference. Here is a simple example of using DBMS_STATS to generate statistics for a whole schema, Appl_Prod, sampling 10% of the data in the larger tables and cascading statistics collection to the indexes:

BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS ('Appl_Prod',10, 
      CASCADE => TRUE); 
END;
/

Often, queries include conditions on highly skewed distributions, such as conditions on special types, codes, or flags, when these columns have only a few values. Normally, the CBO evaluates selectivity of a condition based on the assumption that all nonnull values of a column are equally selective. This assumption generally works well for foreign and primary keys that join business entities, but it is inaccurate when the columns have permanent special meanings and certain meanings apply much more rarely than others.

For example, in an Orders table, you might have a Status_Code column with three possible values: 'CL' for closed (i.e., fulfilled) orders, 'CA' for cancelled orders, and 'OP' for open orders. Most orders, by far, would be fulfilled, once the application has been running for a few months. A steady, significant fraction of orders would end up cancelled, so that value would also eventually point to a large list of orders. However, as long as the business keeps up with incoming orders, the number of open orders would remain moderate and steady, even as data accumulates for years. Quite early, a condition specifying Status_Code='OP' would be selective enough to justify indexed access, if you had an index with that leading column, and it is important to enable the optimizer to realize this fact, preferably without a lot of manual tuning. Enabling the CBO to recognize when a column is selective requires two things:

• The SQL must mention the specific selective value, rather than use a bind variable, prior to Oracle 9i Database. Use of bind variables is commonly attractive, since it makes SQL more general and easier to share between processes. However, this need to hardcode especially selective values is the exception to that rule. If you use Status_Code=:1 instead of Status_Code='OP', prior to Oracle 9i, you will deny the CBO potential knowledge of the selectivity of the condition at parse time, when it does not yet know whether the bind variable :1 will be assigned a common or a rare Status_Code. Fortunately, in these cases, the usual reason to prefer using bind variables does not generally apply; since these special codes have special business meanings, it is unlikely that the SQL ever requires substituting a different value than the single selective value.

In Oracle 9i, Oracle introduced bind variable peeking, wherein Oracle checks the first value assigned to each bind variable (when a query sees its first hard parse) when choosing an execution plan. This eliminates the need to specify fixed values in place of bind variables, as long as all the values to be bound are of similar selectivity. However, if the bind variable will be bound sometimes to selective values and sometimes to nonselective values, you still must hardcode the values to obtain different plans in the two cases.

• You need to provide the CBO with special statistics that quantify how rare the uncommon code, type, or status values are, so it can know which values are highly selective.

Oracle stores special statistics on distribution when you request them, based on sorting the rows for a column and arranging the sorted list into a specified number of buckets that each contain the same number of rows. Since Oracle already knows that the range each bucket holds has the same number of rows, Oracle needs to know only the value-range endpoints in each bucket. In the current example, with 20 buckets, the first bucket might hold the range 'CA' to 'CA', and the second bucket might hold the range 'CA' to 'CL'. The next 17 buckets would hold the most common range, 'CL' to 'CL'. The last bucket would hold the range 'CL' to 'OP', which includes the rarest value. From this, Oracle can deduce that the selectivity of the column is 5-10% for the value 'CA', 85-95% for the value 'CL', and 0-5% for the value 'OP'. Since you want the optimizer to know more closely how selective the 'OP' value is, you would choose more buckets than this, perhaps the maximum of 254. (Oracle compresses the bucket information when so few values apply, so the large number of buckets should be inexpensive.) To create 254 buckets for the example case, in the schema owned by Appl_Prod, use this:

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS ('Appl_Prod','Orders', 
      METHOD_OPT => 'FOR COLUMNS SIZE 254 Status_Code'); 
END;
/

Generate the histogram statistics after you generate the general table statistics, because table-statistics generation deletes earlier histogram statistics.

4.2.3.2 General hint syntax

Oracle uses hints for manual control of cost-based optimization. Syntactically, these hints take the form of comments, like /*+ <Hint_String> */, immediately following the SELECT keyword. Oracle recognizes that this syntax encloses a hint, not a comment, by the + at the beginning and by the location of the hint, which must immediately follow SELECT. However, since these are comments from the point of view of standard SQL syntax, they do not interfere with parsing the SQL if the SQL is also to be executed on non-Oracle databases.

Oracle hints don't help get a fast execution plan on non-Oracle databases either, but unfortunately, it is not currently possible to share manually tuned SQL on multiple vendor databases and have the manual tuning work uniformly well on them all.

Each hint directly affects only the SELECT block that has the comment. Thus, to control the order of joins and index choices within a subquery, place the hint after the SELECT keyword that begins the subquery. But to affect the outer-query order of joins and index choices, place a hint immediately after the outer-query SELECT.

4.2.3.3 Approaches to tuning with hints

There are two basic extremes involved in tuning with hints:

• Use as little direction as possible to get the execution plan you want, or at least to get close enough to the plan you want for reasonable performance. This approach reasons that the CBO has more information than you have and should be left free to adapt to changing data distributions and to take advantage of improvements in Oracle with future releases. By leaving the CBO the maximum degrees of freedom, you maximize its power to optimize well for you in the future. However, until you try, you won't know how much direction the CBO will need if it did not get the plan right in the first place, so this approach is likely to be iterative, involving the addition of one hint at a time until the CBO delivers a good plan.

• If you did not get the plan you wanted from the CBO automatically, assume the CBO has made bad assumptions that will propagate to distort all of its calculations. Therefore, leave it with little freedom, specifying essentially the whole plan you want.

If you are confident in your chosen execution plan, as you should be if you apply the methods I describe later in this book, there is little reason to hold back from fully specifying that plan. I have yet to find a case where a well-chosen, robust execution plan needed to evolve to handle new data distributions or new database features. On the other hand, it is easy for SQL with a partially restricting set of hints to go wrong, especially if some table or index loses its statistics. When the CBO chooses incorrectly, the error that made the CBO choose incorrectly will likely propagate over the entire plan. For example, consider this query:

SELECT E.First_Name, E.Last_Name, E.Salary, LE.Description,
       M.First_Name, M.Last_Name, LM.Description
FROM Locations LM, Employees M, Locations LE, Employees E
WHERE E.Hire_Date > :1
  AND E.Manager_ID=M.Employee_ID
  AND E.Location_ID=LE.Location_ID
  AND M.Location_ID=LM.Location_ID

At parse time, when the optimizer does its work, it cannot know that the bind variable :1 will likely be set to a value in the current week, so it makes a conservative assumption about the selectivity of that condition on Hire_Date. Having made that assumption, it might not only forego using an index on Hire_Date (depending on the data distribution), but it might also further calculate that it will hit most of the rows of all the joined tables as well, and the CBO might choose full table scans with hash joins on them. Even if you instruct the CBO to use the index on Hire_Date, it still retains its initial assumption that the driving condition is unselective, and will likely retain its poor choices for the other joins and table-access methods. This is really no flaw in the optimizer; it cannot know what the application developer knows about the likely values to be assigned to the bind variable. However, the consequence is that, if you need to be any more specific than just specifying ALL_ROWS or FIRST_ROWS, chances are relatively high that the optimizer will need help across the board, to correct for some incorrect assumption somewhere.

ALL_ROWS and FIRST_ROWS hints are a safe way to begin optimization. If you are using the rule-based optimizer, you can safely try out these cost-based approaches with a hint, even before you do the work of finding the best execution plan. If the result is already fast enough, you might save yourself any further work. If your optimization is already cost-based, under either the ALL_ROWS or FIRST_ROWS mode, try the other one. If an optimizer_mode hint alone solves your problem, the optimizer is making reasonable assumptions and you can trust it.

4.2.3.4 Table-access hints

These are the main hints to control table-access methods:

INDEX( <Alias_Name> <Index_Name>)

This directs Oracle, when possible, to access the alias <Alias_Name> using the index named <Index_Name>. Repeat this hint for each index/alias combination you need to control.

FULL( <Alias_Name>)

This directs Oracle, when possible, to access the alias <Alias_Name> using a full table scan. Repeat this hint for each full table scan you require.

INDEX_DESC( <Alias_Name> <Index_Name>)

This directs Oracle, when possible, to access the alias <Alias_Name> using the index named <Index_Name>, reaching the rows in descending order (the reverse of the normal index-sorted order). Repeat this hint for each index/alias combination you need to control, although it is unlikely you will need it more than once in a query.

The INDEX and FULL hints are common and easy to use. The INDEX_DESC hint is useful only rarely, but it is occasionally vital to use. For example, if you want to know all about the last employee hired in April, you might use this query:

SELECT * 
FROM Employees E
WHERE Hire_Date>=TO_DATE('2003-04-01','YYYY-MM-DD')
  AND Hire_Date< TO_DATE('2003-05-01','YYYY-MM-DD')
ORDER BY Hire_Date DESC

You'll find the most recently hired employee you want at the top of the list of rows returned by this query. To avoid reading all the data for other employees hired in April, you might think to add a condition AND ROWNUM=1 to the query. However, this sometimes will not yield the desired result, because (depending on the data) Oracle will sometimes apply that condition before performing the descending sort. If Oracle uses a full table scan, it will return the first employee hired in April it finds in the table, likely the least recently hired. If it uses a simple index range scan on an index on Hire_Date, it will begin, as range scans generally do by default, at the low end of the index range, returning the first employee hired in April. However, the INDEX_DESC hint, with the index Employee_Hire_Date on the Hire_Date column, neatly solves the problem, returning the desired row with just a single logical I/O to the table:

SELECT /*+ INDEX_DESC(E Employee_Hire_Date) */ * 
FROM Employees E
WHERE Hire_Date>=TO_DATE('2003-04-01','YYYY-MM-DD')
  AND Hire_Date< TO_DATE('2003-05-01','YYYY-MM-DD')
  AND ROWNUM=1

Note that I removed the explicit ORDER BY clause, since it gives the false impression that it has effect, given the condition on ROWNUM.

The preceding example might strike you as risky code, apt to break functionally, for example, if someone drops or renames the index used. It is risky, and I recommend it only if the value of the performance improvement exceeds the cost of the risk of incorrect results. This is a clear case for SQL syntax that allows such top-n queries that take full advantage of the best indexed path. With current syntax, I haven't found a solution that is both optimal and functionally safe.

There are several other table-access hints that I have not described in this section, but I have never found them necessary.

4.2.3.5 Execution-order hints

These are the main hints to control the order of execution for joins and subqueries:

ORDERED

This directs Oracle, when possible, to join the tables in the FROM clause in the same order that they are listed.

This hint, unlike the others, usually requires that you alter the body of the SQL (or at least the FROM clause) to get the plan you want, since the hint refers to the FROM-clause order. Notice that the desired FROM-clause order will be precisely the opposite of the best FROM-clause order you would choose for rule-based optimization. That's because the RBO works from right to left, whereas this hint causes the CBO to work through the FROM clause from left to right.

LEADING( <Alias_Name>)

In the absence of an ORDERED hint, this selects the driving table, the first table in the join order. Although this gives less control over the join order than the ORDERED hint, it does not require modifying the FROM clause. Often, getting just the driving table correct is all you need to get at least close to the performance of the optimal plan. Later choices in the join order tend to matter less and will likely be well chosen by the optimizer, without your help.

PUSH_SUBQ

This hint instructs the optimizer to perform correlated subqueries at the first opportunity, as soon as the outer query reaches the join columns needed to evaluate them. Oracle's CBO normally performs correlated subqueries only after completing all the joins in the outer query.

The ORDERED and LEADING hints are common and straightforward to use. The PUSH_SUBQ hint is occasionally useful.

When it comes to subqueries, Oracle offers hint-based control only at the two extremes: executing subqueries as early or as late as possible. However, you can gain full control of when subqueries execute if you combine the PUSH_SUBQ hint with the earlier methods of postponing correlated joins. For example, consider the earlier query:

SELECT ... 
FROM Orders O, Customers C, Regions R
WHERE O.Status_Code='OP'
  AND O.Customer_ID=C.Customer_ID
  AND C.Customer_Type_Code='GOV'
  AND C.Region_ID=R.Region_ID
AND EXISTS (SELECT NULL
            FROM Order_Details OD
            WHERE O.Order_ID+0*C.Customer_ID=OD.Order_ID
              AND OD.Shipped_Flag='Y')

Without a hint, Oracle would execute the EXISTS check after joining all three outer-query tables. The point of the expression O.Order_ID+0*C.Customer_ID was to delay the EXISTS check until after the join to C, but not after the join to R. However, without any hint, all EXISTS conditions are automatically delayed until after all outer-query joins. To force the EXISTS condition to execute between the joins to C and R, use both the hint and the correlating-join-postponing expression:

SELECT /*+ PUSH_SUBQ */ ... 
FROM Orders O, Customers C, Regions R
WHERE O.Status_Code='OP'
  AND O.Customer_ID=C.Customer_ID
  AND C.Customer_Type_Code='GOV'
  AND C.Region_ID=R.Region_ID
  AND EXISTS (SELECT NULL 
              FROM Order_Details OD
              WHERE O.Order_ID+0*C.Customer_ID=OD.Order_ID
                AND OD.Shipped_Flag='Y')

Now, the PUSH_SUBQ hint causes Oracle to execute the EXISTS condition as early as possible, and the expression O.Order_ID+0*C.Customer_ID ensures that "as early as possible" doesn't come until after the join to C.

4.2.3.6 Join-method hints

These are the main hints to control the join methods:

USE_NL( <List_Of_Aliases>)

This directs Oracle, when possible, to join the tables indicated in the alias list by using nested loops. The alias list is without commasfor example, USE_NL(T1 T2 T3).

USE_HASH( <List_Of_Aliases>)

This directs Oracle, when possible, to join to the tables indicated in the alias list by using hash joins. The alias list is without commasfor example, USE_HASH(T1 T2 T3).

4.2.3.7 Example

Here's an example to illustrate the most frequently useful hints to yield complete control of an execution plan. I'll force the join order, the access method to every table, and the join method to every table. Consider the earlier example tuned for the RBO, shown at the end of Section 4.2.2. To fully force the same plan, but substitute a hash join for the first nested-loops join, with the employee locations read through the index on Description, use this query:

SELECT /*+ ORDERED USE_NL(M LM) USE_HASH(LE) INDEX(E Employee_Last_Name) 
           INDEX(LE Location_Description) INDEX(M Employee_Pkey) 
           INDEX(LM Location_Pkey) */ 
       E.First_Name, E.Last_Name, E.Salary, LE.Description,
       M.First_Name, M.Last_Name, LM.Description
FROM Employees E, Locations LE, Employees M, Locations LM
WHERE E.Last_Name = 'Johnson'
  AND E.Manager_ID=M.Employee_ID
  AND E.Location_ID=LE.Location_ID
  AND M.Location_ID=LM.Location_ID 
  AND LE.Description='Dallas'

This results in the execution plan, as shown here:

SQL> @ex

PLAN
----------------------------------------------------------------------
SELECT STATEMENT
  NESTED LOOPS
    NESTED LOOPS
      HASH JOIN
        TABLE ACCESS BY INDEX ROWID 1*EMPLOYEES
          INDEX RANGE SCAN EMPLOYEE_LAST_NAME
        TABLE ACCESS BY INDEX ROWID 2*LOCATIONS
          INDEX RANGE SCAN LOCATION_DESCRIPTION
      TABLE ACCESS BY INDEX ROWID 3*EMPLOYEES
        INDEX UNIQUE SCAN EMPLOYEE_PKEY
    TABLE ACCESS BY INDEX ROWID 4*LOCATIONS
      INDEX UNIQUE SCAN LOCATION_PKEY