3.11 Oracle's Optimizer and Heterogeneous Services

Oracle's optimizer can be used with Heterogeneous Services.

Heterogeneous Services collects certain table and index statistics information on the respective non-Oracle system tables and passes this information back to Oracle Database. The Oracle cost-based optimizer uses this information when building the query plan.

There are several other optimizations that the cost-based optimizer performs. The most important ones are remote sort elimination and remote joins.

• Example: Using Index and Table Statistics
  An example using index and table statistics.
• Example: Remote Join Optimization
  An example using the remote join optimization capability.
• Optimizer Restrictions for Non-Oracle Access
  An example of the optimizer restrictions for non-Oracle access.
• Explain Plan Consideration
  The explain plan operation can be distributed or sent to remote Oracle in the homogeneous Oracle distributed environment, while it is not possible in the heterogeneous environment.

3.11.1 Example: Using Index and Table Statistics

An example using index and table statistics.

Consider the following statement where you create a table in the Oracle database with 10 rows:

CREATE TABLE T1 (C1 number);

Analyze the table using the DBMS_STATS package. For example:

DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','T1');
DBMS_STATS.GENERATE_STATS ('SCOTT','T1');

The preceding example assumes the schema name is SCOTT and the table name is T1.

Create a table in the non-Oracle system with 1000 rows.

Issue the following SQL statement:

SELECT a.* FROM remote_t1@remote_db a, T1 b 
    WHERE a.C1 = b.C1;

The Oracle optimizer issues the following SQL statement to the agent:

SELECT C1 FROM remote_t1@remote_db;

This fetches all 1000 rows from the non-Oracle system and performs the join in the Oracle database.

If we add a unique index on the column C1 in the table remote_t1, and issue the same SQL statement again, the agent receives the following SQL statement for each value of C1 in the local t1:

...
SELECT C1 FROM remote_t1@remote_db WHERE C1 = ?;
...

Note:

? is the bind parameter marker. Also, join predicates containing bind variables generated by Oracle are generated only for nested loop join methods.

To verify the SQL execution plan, generate an explain plan for the SQL statement. First, load utlxplan in the admin directory.

Enter the following:

EXPLAIN PLAN FOR SELECT a.* FROM remote_t1@remote_db a, T1 b 
    WHERE a.C1 = b.C1;

Execute the utlxpls utility script by entering the following statement.

@utlxpls

OPERATION REMOTE indicates that remote SQL is being referenced.

To find out what statement is sent, enter the following statement:

SELECT ID, OTHER FROM PLAN_TABLE WHERE OPERATION = 'REMOTE';

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_STATS package.

3.11.2 Example: Remote Join Optimization

An example using the remote join optimization capability.

The following is an example of the remote join optimization capability of the Oracle database.

Note:

The explain plan that uses tables from a non-Oracle system can differ from similar statements with local or remote Oracle table scans. This is because of the limitation on the statistics available to Oracle for non-Oracle tables. Most importantly, column selectivity is not available for non-unique indexes of non-Oracle tables. Because of the limitation of the statistics available, the following example is not necessarily what you encounter when doing remote joins and is intended for illustration only.

Consider the following example:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname, f.ename, f.deptno FROM
   dept d,
   emp@remote_db e,
   emp@remote_db f
 WHERE e.mgr = f.empno
  AND e.deptno = d.deptno 
  AND e.empno = f.empno;
  
@utlxpls

You should see output similar to the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id    | Operation                           | Name  | Rows  | Bytes  | Cost
| Inst  |IN-OUT|
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT   |                |  2000   |   197K|   205 |
|*  1   | HASH JOIN          |                |  2000   |   197K|
205 |
|   2   | TABLE ACCESS FULL  | DEPT           |  21     |   462 |     2 |
|*  3   | HASH JOIN          |                |  2000   |   154K|
201 |
|   4   | REMOTE             |                |  2000   | 66000 
|    52 |
|   5   | REMOTE             |                |  2000   | 92000
|    52 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Hint Alias (identified by operation id):
-----------------------------------------------------------

   1 - sel$1 / D
   2 - sel$1 / D
   3 - sel$1 / F
   4 - sel$1 / F
   5 - sel$1 / E

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - access("E"."MGR"="F"."EMPNO" AND "E"."EMPNO"="F"."EMPNO")

Issue the following statement:

SET long 300
SELECT other FROM plan_table WHERE operation = 'REMOTE'; 

You should see output similar to the following:

OTHER
--------------------------------------------------------------------------------

SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"

3.11.3 Optimizer Restrictions for Non-Oracle Access

An example of the optimizer restrictions for non-Oracle access.

The following are optimizer restrictions for non-Oracle system access:

• There are no column statistics for remote objects. This can result in poor execution plans. Verify the execution plan and use hints to improve the plan.
• There is no optimizer hint to force a remote join. However, there is a remote query block optimization that can be used to rewrite the query slightly in order to get a remote join.

  The example from the previous section can be rewritten to the following form:

      SELECT v.ename, d.dname, d.deptno FROM dept d,
          (SELECT /*+ NO_MERGE */ 
           e.deptno deptno, e.ename ename emp@remote_db e, emp@remote_db f
               WHERE e.mgr = f.empno
               AND e.empno = f.empno;
          )
        WHERE v.deptno = d.deptno;
  

This example guarantees a remote join because it has been isolated in a nested query with the NO_MERGE hint.

3.11.4 Explain Plan Consideration

The explain plan operation can be distributed or sent to remote Oracle in the homogeneous Oracle distributed environment, while it is not possible in the heterogeneous environment.

Although Oracle Database Gateways return statistics information to the local Oracle database optimizer to generate the explain plan, it might not produce the exact explain plan when compared to a homogeneous Oracle distributed environment with similar data and index information.