Tuning Distributed Queries

The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

You have several options for designing your application to optimize query processing. This section contains the following topics:

Using Collocated Inline Views
Using Cost-Based Optimization
Using Hints
Analyzing the Execution Plan

Using Collocated Inline Views

The most effective way of optimizing distributed queries is to access the remote databases as little as possible and to retrieve only the required data.

For example, assume you reference five remote tables from two different remote databases in a distributed query and have a complex filter (for example, WHERE r1.salary + r2.salary > 50000). You can improve the performance of the query by rewriting the query to access the remote databases once and to apply the filter at the remote site. This rewrite causes less data to be transferred to the query execution site.

Rewriting your query to access the remote database once is achieved by using collocated inline views. The following terms need to be defined:

Collocated

Two or more tables located in the same database.
Inline view

A SELECT statement that is substituted for a table in a parent SELECT statement. The embedded SELECT statement, shown within the parentheses is an example of an inline view:
```
SELECT e.empno,e.ename,d.deptno,d.dname
   FROM (SELECT empno, ename from
         emp@orc1.world) e, dept d;
```
Collocated inline view

An inline view that selects data from multiple tables from a single database only. It reduces the amount of times that the remote database is accessed, improving the performance of a distributed query.

Oracle recommends that you form your distributed query using collocated inline views to increase the performance of your distributed query. Oracle Database cost-based optimization can transparently rewrite many of your distributed queries to take advantage of the performance gains offered by collocated inline views.

Using Cost-Based Optimization

In addition to rewriting your queries with collocated inline views, the cost-based optimization method optimizes distributed queries according to the gathered statistics of the referenced tables and the computations performed by the optimizer.

For example, cost-based optimization analyzes the following query. The example assumes that table statistics are available. Note that it analyzes the query inside a CREATE TABLE statement:

CREATE TABLE AS (
                 SELECT l.a, l.b, r1.c, r1.d, r1.e, r2.b, r2.c
                 FROM local l, remote1 r1, remote2 r2 
                    WHERE l.c = r.c 
                    AND r1.c = r2.c 
                    AND r.e > 300
                );

and rewrites it as:

CREATE TABLE AS (
                 SELECT l.a, l.b, v.c, v.d, v.e
                 FROM (
                        SELECT r1.c, r1.d, r1.e, r2.b, r2.c
                         FROM remote1 r1, remote2 r2
                         WHERE r1.c = r2.c 
                         AND r1.e > 300
                      ) v, local l
                 WHERE l.c = r1.c
                );

The alias v is assigned to the inline view, which can then be referenced as a table in the preceding SELECT statement. Creating a collocated inline view reduces the amount of queries performed at a remote site, thereby reducing costly network traffic.

How Does Cost-Based Optimization Work?

The main task of optimization is to rewrite a distributed query to use collocated inline views. This optimization is performed in three steps:

All mergeable views are merged.
Optimizer performs collocated query block test.
Optimizer rewrites query using collocated inline views.

After the query is rewritten, it is executed and the data set is returned to the user.

While cost-based optimization is performed transparently to the user, it is unable to improve the performance of several distributed query scenarios. Specifically, if your distributed query contains any of the following, cost-based optimization is not effective:

Aggregates
Subqueries
Complex SQL

If your distributed query contains one of these elements, see "Using Hints" to learn how you can modify your query and use hints to improve the performance of your distributed query.

Setting Up Cost-Based Optimization

After you have set up your system to use cost-based optimization to improve the performance of distributed queries, the operation is transparent to the user. In other words, the optimization occurs automatically when the query is issued.

You need to complete the following tasks to set up your system to take advantage of cost-based optimization:

Setting Up the Environment
Analyzing Tables

Setting Up the Environment

To enable cost-based optimization, set the OPTIMIZER_MODE initialization parameter to CHOOSE or COST. You can set this parameter by:

Modifying the OPTIMIZER_MODE parameter in the initialization parameter file
Setting it at session level by issuing an ALTER SESSION statement

Issue one of the following statements to set the OPTIMIZER_MODE initialization parameter at the session level:

ALTER SESSION OPTIMIZER_MODE = CHOOSE;
ALTER SESSION OPTIMIZER_MODE = COST;

See Also:

Oracle Database Performance Tuning Guide for information on setting the OPTIMIZER_MODE initialization parameter in the parameter file and for configuring your system to use a cost-based optimization method

Analyzing Tables

For cost-based optimization to select the most efficient path for a distributed query, you must provide accurate statistics for the tables involved. You do this using the DBMS_STATS package or the ANALYZE statement.

Note:

You must connect locally with respect to the tables when executing the DBMS_STATS procedure or ANALYZE statement. For example, you cannot execute the following:

ANALYZE TABLE remote@remote.com COMPUTE STATISTICS;

You must first connect to the remote site and then execute the preceding ANALYZE statement, or the equivalent DBMS_STATS procedure.

The following DBMS_STATS procedures enable the gathering of certain classes of optimizer statistics:

GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS

For example, assume that distributed transactions routinely access the scott.dept table. To ensure that the cost-based optimizer is still picking the best plan, execute the following:

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS ('scott', 'dept');
END;

See Also:

Oracle Database Performance Tuning Guide for information about generating statistics
Oracle Database PL/SQL Packages and Types Reference for additional information on using the DBMS_STATS package

Using Hints

If a statement is not sufficiently optimized, then you can use hints to extend the capability of cost-based optimization. Specifically, if you write your own query to utilize collocated inline views, instruct the cost-based optimizer not to rewrite your distributed query.

Additionally, if you have special knowledge about the database environment (such as statistics, load, network and CPU limitations, distributed queries, and so forth), you can specify a hint to guide cost-based optimization. For example, if you have written your own optimized query using collocated inline views that are based on your knowledge of the database environment, specify the NO_MERGE hint to prevent the optimizer from rewriting your query.

This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL. Because this type of distributed query cannot be rewritten by the optimizer, specifying NO_MERGE causes the optimizer to skip the steps described in "How Does Cost-Based Optimization Work?".

The DRIVING_SITE hint lets you define a remote site to act as the query execution site. In this way, the query executes on the remote site, which then returns the data to the local site. This hint is especially helpful when the remote site contains the majority of the data.

See Also:

Oracle Database Performance Tuning Guide for more information about using hints

Using the NO_MERGE Hint

The NO_MERGE hint prevents the database from merging an inline view into a potentially non-collocated SQL statement (see "Using Hints"). This hint is embedded in the SELECT statement and can appear either at the beginning of the SELECT statement with the inline view as an argument or in the query block that defines the inline view.

/* with argument */

SELECT /*+NO_MERGE(v)*/ t1.x, v.avg_y
   FROM t1, (SELECT x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
   WHERE t1.x = v.x AND t1.y = 1;

/* in query block */

SELECT t1.x, v.avg_y
   FROM t1, (SELECT /*+NO_MERGE*/ x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
   WHERE t1.x = v.x AND t1.y = 1;

Typically, you use this hint when you have developed an optimized query based on your knowledge of your database environment.

Using the DRIVING_SITE Hint

The DRIVING_SITE hint lets you specify the site where the query execution is performed. It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.

Following is an example of a SELECT statement with a DRIVING_SITE hint:

SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com
   WHERE emp.deptno = dept.deptno;

Analyzing the Execution Plan

An important aspect to tuning distributed queries is analyzing the execution plan. The feedback that you receive from your analysis is an important element to testing and verifying your database. Verification becomes especially important when you want to compare plans. For example, comparing the execution plan for a distributed query optimized by cost-based optimization to a plan for a query manually optimized using hints, collocated inline views, and other techniques.

See Also:

Oracle Database Performance Tuning Guide for detailed information about execution plans, the EXPLAIN PLAN statement, and how to interpret the results

Preparing the Database to Store the Plan

Before you can view the execution plan for the distributed query, prepare the database to store the execution plan. You can perform this preparation by executing a script. Execute the following script to prepare your database to store an execution plan:

SQL> @UTLXPLAN.SQL

Note:

The utlxplan.sql file can be found in the $ORACLE_HOME/rdbms/admin directory.

After you execute utlxplan.sql, a table, PLAN_TABLE, is created in the current schema to temporarily store the execution plan.

Generating the Execution Plan

After you have prepared the database to store the execution plan, you are ready to view the plan for a specified query. Instead of directly executing a SQL statement, append the statement to the EXPLAIN PLAN FOR clause. For example, you can execute the following:

EXPLAIN PLAN FOR
   SELECT d.dname 
   FROM dept d
      WHERE d.deptno 
      IN (SELECT deptno 
          FROM emp@orc2.world
          GROUP BY deptno
          HAVING COUNT (deptno) >3
          )
/

Viewing the Execution Plan

After you have executed the preceding SQL statement, the execution plan is stored temporarily in the PLAN_TABLE that you created earlier. To view the results of the execution plan, execute the following script:

@UTLXPLS.SQL

Note:

The utlxpls.sql can be found in the $ORACLE_HOME/rdbms/admin directory.

Executing the utlxpls.sql script displays the execution plan for the SELECT statement that you specified. The results are formatted as follows:

Plan Table
-------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
| SELECT STATEMENT          |          |       |      |        |       |      |
|  NESTED LOOPS             |          |       |      |        |       |      |
|   VIEW                    |          |       |      |        |       |      |
|    REMOTE                 |          |       |      |        |       |      |
|   TABLE ACCESS BY INDEX RO|DEPT      |       |      |        |       |      |
|    INDEX UNIQUE SCAN      |PK_DEPT   |       |      |        |       |      |
-------------------------------------------------------------------------------

If you are manually optimizing distributed queries by writing your own collocated inline views or using hints, it is best to generate an execution plan before and after your manual optimization. With both execution plans, you can compare the effectiveness of your manual optimization and make changes as necessary to improve the performance of the distributed query.

To view the SQL statement that will be executed at the remote site, execute the following select statement:

SELECT OTHER 
FROM PLAN_TABLE 
   WHERE operation = 'REMOTE';

Following is sample output:

SELECT DISTINCT "A1"."DEPTNO" FROM "EMP" "A1" 
   GROUP BY "A1"."DEPTNO" HAVING COUNT("A1"."DEPTNO")>3

Note:

If you are having difficulty viewing the entire contents of the OTHER column, execute the following SQL*Plus command:

SET LONG 9999999