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:
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_whereqt_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_BYTEqt_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,SQLqt_debug,EXTENDEDqt_no_debug Specifies whether the DataServer should print debugging information that it generates for the query to thedataserv.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_lookaheadqt_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:
Use startup parameters to override two other query-tuning defaults-INDEX-HINT and JOIN-BY-SQLDB. Table 4–7 describes these startup parameters.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |