Progress
DataServer
for ORACLE Guide
Query Tuning
In addition to the standard approach of using selection criteria to refine access to data, you can further influence how the DataServer executes a query through the Progress QUERY-TUNING phrase. How you structure a query determines how efficiently you access a database. Efficient use of the ORACLE RDBMS enhances the performance of DataServer applications. You can include the QUERY-TUNING phrase in these Progress statements:
Place the QUERY-TUNING phrase after the last record phrase. For example, place it near the end of the statement where you also place block modifier phrases such as BREAK, ON ERROR, and TRANSACTION. Separate multiple query-tuning options by a single space. The QUERY-TUNING options have equivalent startup parameters. You cannot use the startup parameters to override the QUERY-TUNING settings.
Table 2–13 describes the query-tuning options.
Table 2–13: Query-tuning Options Option Description ARRAY-MESSAGENO-ARRAY-MESSAGE Specifies whether the DataServer sends multiple result rows in a single logical network message, thereby reducing network traffic.
Default: ARRAY-MESSAGE, if the query uses a lookahead cursor BIND-WHERENO-BIND-WHERE Specifies whether the DataServer uses ORACLE bind variables for values in WHERE clauses. Using bind variables typically improves performance, but ORACLE provides unexpected results for some operations, such as a MATCHES on an indexed field and a trailing wildcard or comparisons of CHAR fields that use ORACLES blank-padding rules.
Specify NO-BIND-WHERE to use literals. Using NO-BIND-WHERE in queries that do comparisons (MATCHES, BEGINS on an indexed field) can improve performance.
Default: BIND-WHERE CACHE-SIZE integer BYTECACHE-SIZE integer ROW Specifies the size of the cache for information (in bytes or records) used by lookahead or standard cursors. If you have two Progress statements that cause the DataServer to generate identical SQL code except that the second statement specifies a smaller cache size, the DataServer reuses the larger cache from the first statement if the cursor is still available. Reusing cache and cursors improves performance.
Byte maximum: 65535 bytes
Byte minimum: Specify the number of bytes contained in a single record. For joins, specify the number of bytes contained in two joined records. By default, the DataServer sizes the cache to accommodate one record or, for a join, two joined records. For example, if a join returns a 500-byte record, you need a cache of at least 1000 bytes.
Default: 1024 bytes with standard cursors; 8192 with lookahead cursors
Row maximum: the number of records that can fit in 65535 bytes. See the "Caching Records" section for more information.
Row minimum: 1 for a single table; 2 for a join DEBUG EXTENDEDDEBUG SQLNO-DEBUG Specifies whether the DataServer should print debugging information that it generates for the query to thedataserv.lg
file.
Specify DEBUG SQL to print the SQL that the DataServer executes against the ORACLE DBMS.
Specify DEBUG EXTENDED to print additional information, such as cursor statistics.
There are additional options for collecting advanced statistics with DEBUG. See the "Analyzing Performance" section in Connecting the DataServer."
Default: NO-DEBUG HINT string1 string2 string3 Specifies the ORACLE hint syntax that the DataServer passes directly to the ORACLE DBMS as part of the query. This allows you to control which hints are passed as opposed to the index hints that the DataServer passes when appropriate.
When you have to specify an index name in the hint syntax, use the name defined in the ORACLE database. Because the DataServer generates aliases for ORACLE tables using names from T0 through T9, use these aliases to refer to tables in the hint syntax.
The DataServer passes the opening symbols (/*+) and closing symbols (*/). For example, to pass the /*+ORDERED*/ hint syntax, you specify only HINT "ORDERED".
Passing incorrect hint syntax, inappropriate hints, or conflicting hints will not return an error but might give you unpredictable results. See ORACLE documentation for information on hint syntax. INDEX-HINTNO-INDEX-HINT Specifies whether the DataServer should provide index hints to the ORACLE DBMS. Generally index hints improve performance, but ORACLE’s responses to hints vary between releases.
Specify NO-INDEX-HINT to test whether performance for a query improves when the DataServer executes it without hints. See the "Indexes" section for more information on index hints.
By default, the DataServer passes index hints. You can turn off the default globally at compile time or run time by specifying the -noindexhint startup parameter when you start a Progress session. Use INDEX-HINT to retain the behavior for individual queries.
Default: INDEX-HINT JOIN-BY-SQLDBNO-JOIN-BY-SQLDB Specifies whether the DataServer allows the ORACLE DBMS to perform a join, which usually improves performance.
Default: JOIN-BY-SQLDB
You can turn off the JOIN-BY-SQLDB default globally at compile time by specifying the -nojoinbysqldb startup parameter when you start a Progress session. The -nojoinbysqldb parameter does not override the explicit use of JOIN-BY-SQLDB in the QUERY-TUNING phrase. LOOKAHEADNO-LOOKAHEAD Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as fit in the allocated cache (CACHE-SIZE), which limits the number of database accesses thereby improving performance.
Using lookahead cursors results in behavior that is different from Progress because the client does not see any changes made to the records in the cache. Specify NO-LOOKAHEAD for behavior that is consistent with Progress.
Default: LOOKAHEAD, except with FIND statements and statements that use an EXCLUSIVE lock ORDERED-JOIN Specifies that the DataServer embed the ORDERED hint syntax in the SQL it generates. REVERSE-FROM Specifies that ORACLE join tables in the reverse order in which they appear in the FROM clause. The DataServer generates a new SQL FROM clause with the tables in reverse order.
REVERSE-FROM is independent of the ORDERED-JOIN option.
NOTE: All of the query-tuning options take effect at both compile and run time except for the INDEX-HINT, JOIN-BY-SQLDB, and NO-JOIN-BY-SQLDB options, which apply only at compile time.The following example shows how to use the QUERY-TUNING phrase to enhance performance. It includes a join which the DataServer instructs ORACLE to perform by default. The QUERY-TUNING options specify that no lookahead cursors will be used. In addition, the DataServer will write an extended report on the SQL statements it executes:
This example shows how to use the QUERY-TUNING phrase to manage cache size so that the DataServer can reuse cursors and cache, thereby improving performance. The phrase also passes a hint to the ORACLE optimizer to choose the cost-based approach to optimize the statement for best response time. Finally, the DEBUG EXTENDED option causes the DataServer to report on the SQL statements it executes:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |