This chapter suggests ways to optimize distributed SQL statements and improve the performance of distributed queries.
This chapter includes the following sections:
Note:
For information about general data transfer performance, see Section 4.2, "Optimizing Data Transfers Using Bulk Fetch" and Section 4.3, "Optimizing Data Loads Using Parallel Load"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 Oracle supports.
The Oracle Database gateways tell Oracle (at connect time) which functions and operators they support. If the non-Oracle 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 computer as the other data source. However, performance can be improved if you use the bulk fetch and bulk load features.
You can improve the performance of distributed queries by using the following strategies:
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 may be minimal. If the tables are located on different databases, then the difference in performance may be more significant. Also, note that the best SQL statement may change from one release to the next.
Use the query optimizer.
The query optimizer uses indexes on remote tables, considers more execution plans than the rule-based optimizer, and generally gives better results. With the query 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
Retrieve data from the remote table in parallel
Use procedural code.
On 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.