Best Practices for Working with Non-Oracle Databases

Oracle Database Gateway performance is affected by several factors, including network speed, available memory, amount of data being transferred from one database to the other, and the number of concurrent sessions. Some of these factors can be adjusted for better performance.

You can achieve better performance by following these best practices:

• Reduce Post Processing

• Tune the Non-Oracle Database

• Set the Relevant Initialization Parameters

• Check the Location of the Oracle Database Gateway Installation

• Ensure Adequate Memory

• Consider Case Differences

Reduce Post Processing

Performance can be affected negatively if there is a large amount of post processing. Ensure that as much of each SQL statement as possible is processed on the non-Oracle database to achieve better performance. If parts of the WHERE clause are missing from the SQL that is sent to the non-Oracle database or if joins are split, then the query will be post processed.

Follow these best practices to reduce post processing:

• Avoid SQL functions in the WHERE clause, if possible. SQL functions in WHERE clauses affect the performance of the gateway. If you are using Oracle functions that do not have an equivalent in the non-Oracle database, then the gateway compensates for it. The data is retrieved from the non-Oracle database and the function is applied on the Oracle database.

• Use hints to improve the query plan. Look at the gateway trace file or explain plan to determine the SQL being sent to the non-Oracle database. For example, if the SQL statement includes joins of tables on the non-Oracle database, and they are being post processed, then you can use hints to cause the joins to be performed on the non-Oracle database.

Tune the Non-Oracle Database

Performance can be affected negatively if the optimizer does not have enough information to generate an optimal plan. The Oracle optimizer uses table and index statistics of the non-Oracle database to determine the most optimal path to access the data in the non-Oracle database. If this information is missing or inaccurate, then the access path is not optimal. Defining indexes on the non-Oracle database improves the performance of the gateway. See Tuning SQL Statements for more information about the Oracle optimizer.

Set the Relevant Initialization Parameters

Customizing the following initialization parameters can improve performance:

• HS_RPC_FETCH_SIZE

• HS_FDS_FETCH_ROWS

• HS_LANGUAGE

For example if you are sure that the data you are accessing in the non-Oracle database is in the same character set as the one used by the Oracle database, then set the HS_LANGUAGE initialization parameter to the character set of the Oracle database.

Check the Location of the Oracle Database Gateway Installation

The location of the Oracle Database Gateway installation might affect performance. For example, if CPU is a constraint, then do not install the Oracle Database Gateway on the same computer systems where Oracle databases are running.

Ensure Adequate Memory

Ensure that there is enough memory on the computer system where the Oracle Database Gateway is running. There are several factors that affect memory requirements. These include the SQL statement being processed, the number of concurrent sessions, the number of open cursors, and the number of columns in the table being accessed.

Consider Case Differences

When you are working with non-Oracle databases, remember that an Oracle database defaults characters to uppercase unless you surround identifiers with double quotation marks. For example, to refer to the Sybase table named emp, enter the name with double quotation marks, as in the following example:

SELECT * FROM "emp"@SYBS;

However, to refer to the Sybase table called emp owned by user Smith from an Oracle application, enter the following:

SELECT * FROM "Smith"."emp"@SYBS;

If the Sybase table named emp is owned by SMITH, a table owner name in uppercase letters, then you can enter the owner name without double quotation marks, as in the following examples:

SQL> SELECT * FROM SMITH."emp"@SYBS;
 
SQL> SELECT * FROM smith."emp"@SYBS;

SQL> SELECT * FROM SmItH."emp"@SYBS;