Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## 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 “`qt_debug,SQL`” at connection it will override the `NO-DEBUG` default. If you additionally specify `NO-DEBUG` for a specific query, the `NO-DEBUG` for the query will override the connection setting, and no SQL will be written to the log file for that query. In this example, the DataServer will write 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 for more information.

> You override query-tuning defaults with the DataServer (`-Dsrv`) connection parameter when you connect to the ORACLE database. The `-Dsrv` switch accepts a comma separated list of options and values. To guarantee correct parsing, there can be no spaces in the list., as shown in the following syntax:

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

> Table 6–5 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 6–5: 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 for more information. Row minimum: 1. If the server performs the join, the minimum is 2. If `QT_BYTE` or `QT_ROW` is omitted, `QT_BYTE` is assumed. Default: 1024 bytes with standard cursors; 8192 with lookahead cursors.
> `qt_lookahead` `qt_no_lookahead` | Specifies whether the DataServer uses lookahead or standard cursors. Specify `qt_no_lookahead` for behavior that is consistent with OpenEdge. 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, as shown:

>     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 6–6 describes these startup parameters.

> Table 6–6: 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 OpenEdge behavior. Use `-nojoinbysqldb` at compile time. It has no effect at run time. Note: Server joins are never performed for dynamic queries.
> Get Previous (`-Dsrv` `srv-get-prev`) | The addition of this parameter allows for queries defined with `qt_no_lookahead` to correctly execute a `GET` `PREV` after a reposition. Without this switch, when a reposition fails to find the matching record in either the client or server cache, and new SQL is generated to satisfy the query, the resulting record is reset to the starting point of the query, rendering the `GET` `PREV` meaningless.

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