Progress
DataServer
for ORACLE Guide


Query Tuning with Connection and Startup Parameters

In addition to controlling aspects of how the DataServer handles queries programmatically within 4GL statements, you can control the same aspects through startup and connection parameter options.

NOTE: Startup and connection parameters override query-tuning defaults. Options set in the QUERY-TUNING phrase take precedence over startup and connection parameters. For example, if you specify NO-DEBUG in a query, specifying qt_debug, SQL at connection overrides the NO-DEBUG default, but does not override the NO-DEBUG option that you specified for the query. The DataServer writes a report that includes all the SQL it generates for the application, except for the query with the NO-DEBUG option. See the "Query Tuning" section in "Programming Considerations," for more information.

You override query-tuning defaults with the DataServer (-Dsrv) connection parameter when you connect to the ORACLE database. This is the syntax:

SYNTAX
CONNECT db-name -U user-name -P password
  -Dsrv query-tuning-option1,value1,
  query-tuning-option2,value2. 

Table 4–6 describes the query-tuning options that you can specify with the -Dsrv parameter. Unless otherwise indicated, these options apply at compile and run time.

Table 4–6: Connection Query-tuning Options
Option
Description
qt_bind_where
qt_no_bind_where
Specifies whether the DataServer uses ORACLE bind variables for values in WHERE clauses.
Specify qt_no_bind_where to use literals.
Use at run time only.
Default: qt_bind_where
qt_cache_size integer QT_BYTE
qt_cache_size integer QT_ROW
Specifies the size of the cache (in bytes or records) for information used by lookahead or standard cursors.
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. If a join returns a 500-byte record, you need a cache of 1000 bytes.
Row maximum: The number of records that can fit in 65535 bytes. See the "Caching Records" section in "Programming Considerations" for more information.
Row minimum: 1. If the server performs the join, the minimum is 2.
Default: 1024 bytes with standard cursors; 8192 with lookahead cursors
qt_debug,SQL
qt_debug,EXTENDED
qt_no_debug
Specifies whether the DataServer should print debugging information that it generates for the query to the dataserv.lg file.
Specify qt_debug,SQL to print the SQL that the DataServer executes against the ORACLE DBMS.
Specify qt_debug,EXTENDED to print information in addition to the SQL statements executed by the DataServer, such as cursor statistics.
Specify qt_debug,option to override the NO-DEBUG default.
In addition to SQL and EXTENDED, there are other options that can assist you in analyzing performance. See Table 4–8.
Default: qt_no_debug
qt_lookahead
qt_no_lookahead
Specifies whether the DataServer uses lookahead or standard cursors.
Specify qt_no_lookahead for behavior that is consistent with Progress.
Default: qt_lookahead, except with statements that use an EXCLUSIVE lock

The following example shows how to use the query-tuning options to enhance performance. The multiple records that the lookahead cursors require are stored in a 32K cache. In addition, the DataServer writes an extended report on the SQL statements it executes:

CONNECT oradb -U user -P password
  -Dsrv qt_cache_size,32000,qt_debug,EXTENDED. 

Use startup parameters to override two other query-tuning defaults-INDEX-HINT and JOIN-BY-SQLDB. Table 4–7 describes these startup parameters.

Table 4–7: Query-tuning Startup Parameters 
Startup Parameter
Description
Index Hint
(-noindexhint)
Specifies that the DataServer not provide index hints to the ORACLE DBMS. Generally index hints improve performance, but ORACLE’s responses to hints vary between releases.
Use -noindexhint to test whether performance for a query improves when the DataServer executes it without hints.
Use -noindexhint at compile or run time.
Server Join
(-nojoinbysqldb)
Specifies that the client evaluates and performs queries that have joins. This might slow performance, but provides results that are consistent with Progress behavior.
Use -nojoinbysqldb at compile time. It has no effect at run time.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095