Try OpenEdge Now
skip to main content
DataServer for Oracle
Connecting the DataServer : Connecting a schema holder : Query tuning with connection and startup parameters
 

Query tuning with connection and startup parameters

In addition to controlling aspects of how the DataServer handles queries programmatically within ABL 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 Query tuning 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:
Table 41. Syntax
CONNECT db-name -U user-name -P password
-Dsrv query-tuning-option1,value1,
query-tuning-option2,value2.
The following table 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 42. 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 Caching records 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.
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.
Warning suppression(-Dsrv PRGRS_WARNING_SUPPRESSION)
*Specifies the Oracle warnings to be suppressed by the DataServer at connection time.
The DataServer treats and handles any warnings received from Oracle as errors. If you want your DataServer application to ignore Oracle warnings, you execute RUN STORED-PROCEDURE in your ABL editor to alter your Oracle session attributes to disable all the warnings or just the informational warnings in your DataServer connection. For more information on RUN STORED-PROCEDURE statement, see RUNSTORED-PROCEDURE statement.
However, if you want your DataServer application to overlook only certain warning conditions returned from Oracle, you can use the PRGRS_WARNING_SUPRESSION switch.
*In the PRGRS_WARNING_SUPRESSION switch, you can specify a maximum of 25 error codes as a comma-separated list ending with a semi-colon. Refer to the example in the section.
*The error codes specified in the PRGRS_WARNING_SUPRESSION switch are suppressed by the DataServers only if Oracle treats those codes as warnings.
*Errors codes added to the PRGRS_WARNING_SUPRESSION switch are unconditional, that is, the specified warning codes are suppressed by the DataServer irrespective of how the warning condition is produced by Oracle.
*Default: Disabled.
The parameter is disabled to allow for backward copatibility. So, by default, Oracle warnings are treated as errors by the Oracle DataServer.
The following example shows how to use the query-tuning options. The qt_cache_size may enhance performance as it increases the default size of the cache (of multiple records used by lookahead cursors) to 32K . In addition, the following example uses a query tuning logging switch that enables the DataServer to write an extended report on the SQL statements it executes:
CONNECT oradb -U user -P password
-Dsrv qt_cache_size,32000,qt_debug,EXTENDED.
The following example shows how to use the PRGRS_WARNING_SUPPRESSION parameter to specify Oracle warning codes, ORA-24347 and ORA-24348, from being treated by the DataServer applications as error conditions:
CONNECT oradb -U user -P password
-Dsrv PRGRS_WARNING_SUPPRESSION,24347,24348;
Use startup parameters to override two other query-tuning defaults, INDEX-HINT and JOIN-BY-SQLDB. The following table describes these startup parameters.
Table 43. 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.