Tuning Distributed Queries

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:

1. All mergeable views are merged.

2. Optimizer performs collocated query block test.

3. 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 cannot 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 must complete the following tasks to set up your system to take advantage of cost-based optimization:

• Setting Up the Environment

• Analyzing Tables

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

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

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;

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. To view the results of the execution plan, execute the following script:

@utlxpls.sql

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

SET LONG 9999999