6
Performance Tips

This chapter explains how to optimize distributed SQL statements, how to use partition views with Oracle Transparent Gateways, and how to optimize the performance of distributed queries.

This chapter includes the following sections:

• Optimizing Heterogeneous Distributed SQL Statements
• Using Gateways and Partition Views
• Optimizing Performance of Distributed Queries

Optimizing Heterogeneous Distributed SQL Statements

When a SQL statement accesses data from non-Oracle systems, it is said to be a heterogeneous distributed SQL statement. To optimize heterogeneous distributed SQL statements, follow the same guidelines as for optimizing distributed SQL statements that access Oracle databases only. However, you must consider that the non-Oracle system usually does not support all the functions and operators that Oracle9i supports.

The Oracle Transparent Gateways tell Oracle (at connect time) which functions and operators they do support. If the other data source does not support a function or operator, then Oracle performs that function or operator. In this case, Oracle obtains the data from the other data source and applies the function or operator locally. This affects the way in which the SQL statements are decomposed and can affect performance, especially if Oracle is not on the same machine as the other data source.

Using Gateways and Partition Views

You can use partition views with Oracle Transparent Gateways release 8 or higher. Ensure you adhere to the following rules:

• The cost-based optimizer must be used.

  Use hints or set the parameter OPTIMIZER_MODE to ALL_ROWS or FIRST_ROWS_K, or FIRST_ROWS.

• Indexes used for each partition must be the same.

  See the gateway-specific documentation to find out whether the gateway sends index information of the non-Oracle system to the Oracle Server. If the gateway sends index information to the optimizer, then make sure that each partition uses the same number of indexes and that you have indexed the same columns.

  If the gateway does not send index information, then the Oracle optimizer is not aware of the indexes on partitions. Indexes are, therefore, considered to be the same for each partition in the non-Oracle system. If one partition resides on an Oracle server, then you cannot have an index defined on that partition.

• The column names and column data types for all branches in the UNION ALL view must be the same.

  Non-Oracle system data types are mapped onto Oracle data types. Make sure that the data types of each partition that reside in the different non-Oracle systems all map to the same Oracle data types. To see how data types are mapped onto Oracle data types, execute a DESCRIBE statement in SQL*Plus.

Optimizing Performance of Distributed Queries

You can improve performance of distributed queries in several ways. These are:

• Choose the best SQL statement.

  In many cases, there are several SQL statements that can achieve the same result. If all tables are on the same database, then the difference in performance between these SQL statements might be minimal. But, if the tables are located on different databases, then the difference in performance might be more significant.

• Use the cost-based optimizer.

  The cost-based optimizer uses indexes on remote tables, considers more execution plans than the rule-based optimizer, and generally gives better results. With the cost-based optimizer, performance of distributed queries is generally satisfactory. Only on rare occasions is it necessary to change SQL statements, create views, or use procedural code.

• Use views.

  In some situations, views can be used to improve performance of distributed queries. For example:

  • Joining several remote tables on the remote database
  • Sending a different table through the network
  • Using procedural code
• Use procedural code.

  On some rare occasions, it can be more efficient to replace a distributed query by procedural code, such as a PL/SQL procedure or a precompiler program. This option is mentioned here only for completeness, not because it is often needed.