Canceling Long Running Queries in Oracle Applications 11i

Oracle strongly recommends that customers wishing to use the Cancel Query feature upgrade to Developer 6i patch 14 (Forms version 6.0.8.23.x) or later.

Customers using Developer 6i patch 13 (Forms version 6.0.8.22.x) must apply patch 2974236 to use all the cancel query features outlined in this document.

Oracle Applications does not support the Cancel Query feature for releases 10SC, 10.7NCA and 11.0 .

For information on the Cancel Query feature with earlier patch set releases, please see Metalink Note 241012.1 titled 'Canceling Long Running Queries in Earlier Versions of Oracle Applications 11i'

Canceling Long-Running Queries (Overview)
On occasion, a user may start a long-running query, grow tired of waiting, and close the browser. This section considers the implications of such an action, and describes a new feature that can help in this situation.

The middle-tier Forms Server process is basically single-threaded, and queries are normally executed in Blocking mode. If the client is killed, the Forms Server process still waits for the query to complete. This means that the Forms Server process will only time out when control has been returned from the database, at which point it will terminate when it can no longer communicate with the PC client. Cleanup will also take place for the database session.

The same principle applies in the case of a long-running transaction that calls a stored procedure from client-side PL/SQL. The key point is that in all such cases the query must run to completion before the middle tier process terminates and cleanup takes place.

If the query takes longer than about 5 seconds, a dialog box will appear that allows the user to cancel the query by clicking a Cancel button. This Cancel Query feature (sometimes called Non-blocking mode, as it is based on SQL*Net non-blocking functionality) is the only way to terminate a query without killing the Forms session.

This capability only applies to queries issued by form blocks or LOVs. It will not work for transactions such as stored procedure calls or queries executed from PL/SQL.
If you are using Oracle Applications 11i Release 7 (11.5.7) or later, or are using FND mini-pack D or later you must set the profile option FND:ENABLE_CANCEL_QUERY to Yes in order to enable this feature. This can be set at site, application, responsibility or user level. Setting this profile to Yes also adds a call to set_form_property in the standard APPCORE when-new-form-instance event, which sets the INTERACTION_MODE property to NON_BLOCKING.

If you are using Oracle Applications 11i prior to Release 7 (11.5.7) and FND mini-pack prior to D, you must apply patch 1675290, and follow the instructions in its readme file to be able use this feature.

Actions that will cause the dialog to appear:
For any block populated via a query issued by Oracle Forms directly, the dialog will appear if the time to fetch the records for the query exceeds the threshold.
When a query is run against a block that is part of a master-detail relation, the dialog may appear and disappear several times if multiple detail blocks are involved.
For any LOV which is based on a SQL statement, if the LOV is explicitly invoked by the user with an action such as Edit List of Values, the dialog will appear if the time to execute and fetch all records for the query exceeds the threshold.
With an operation that fetches many records, such as Go-Last Record or Action-Export, the records are fetched in batches of multiple rows at a time. The dialog will appear if the time to fetch all the records exceeds the threshold.
Queries issued from Find Windows will cause the dialog to appear if the threshold time is exceeded.
Actions that will not cause the dialog to appear:
Spreadtable blocks that are predominantly used by the CRM suite of products will not activate the Cancel Query feature. Java blocks, Java Beans and PLSQL queries that are not activated by forms will not activate this feature.
For a query run against a block that is part of a master-detail relation, the dialog will only appear if the time to populate a particular block exceeds the threshold.
LOVs that open automatically as a result of validation errors or non-unique entries.
Any LOV that has been coded as a user exit, including items such as concurrent request parameter LOVs or Flexfield segment LOVs. Oracle Applications user-exits call private Forms API’s to display an array built by the user-exit in a Forms LOV window. As the query is executed by the user-exit, Forms runtime has no control over it, so is unable to cancel it.
Any form processing involved with creating, committing, updating, deleting a record.

--------------------------------------------------------------------------------

Tuning Cancel Query
You must be on Forms 6i patchset 14 (6.0.8.23.x) or Forms 6i patchset 13 (6.0.8.22.x) with patch 2974236 applied, for all the options described below to be available. As cancel query introduces additional overheads you may wish to tune it to minimize any additional resource requirements.

As Cancel Query is enabled in Oracle Applications 11i by setting the profile FND_ENABLE_CANCEL_QUERY at the appropriate level, some customers may wish to enable cancel query for power users only due to the additional overheads. In this case the profile should be set at User level for these users only.

Cancel Query makes use of OCI level non-blocking queries. The middle tier Forms server process starts the query in non-blocking mode, then unlike a normal blocking query, control is returned to the forms code.

While the query is running, Forms polls the OCI layer until the query completes and records are returned. As well as polling to see if the database has finished executing the query, the forms server process also polls the Forms Java client to initially display the cancel query dialog, and then detect if the cancel button has been pressed. If the cancel button has been pressed by the end user, Forms will call the OCI function to terminate the query. At the time the query is terminated, some records may have already been fetched, so an incomplete record set may be returned in some cases.

While the query is running in non-blocking mode, the user is unable to do anything in the current forms UI except cancel the query. They cannot work in another form if they have multiple forms open.

Cancel query obviously imposes an additional overhead, as it needs to poll the database to see if the query has completed, and also poll the client to see if the user has cancelled it. You may experience the following issues -

Extra round trips between the forms server and the client. The Forms server polls the client once every second after the first 2 seconds by default. System Administrators can set environment variables to change the defaults. See below.

User Wait Time (i.e. the time the user has to wait for the query to complete and control returned to the user) may increase, particularly for queries which fetch a lot of records. Note that by default queries on Forms blocks only fetch enough records to display the first screen full, and fetch additional records as you scroll through. Therefore a query which potentially returns a million rows will not cause a problem because forms is smart enough to only fetch the first few rows. An LOV always fetches all records selected by the query.

CPU on the middle tier will increase. CPU will also increase on the client but that isn't normally a problem, as the client PC is not normally running anywhere near capacity. Middle tier CPU is more important. In cancel query we have to strike a balance between CPU usage on the middle tier and User Wait Time. Polling the database more often increases CPU usage, but reduces user wait time, particularly for queries which fetch a lot of records. The database polling interval is configurable - see The Database Polling Interval section below.

The following environment variables can be set to tune Cancel Query. Guidance is provided, but in some cases the optimal value is yet to be established, hence the reason for making everything configurable;


--------------------------------------------------------------------------------

Tuning round trips between the Middle Tier and the Client
Changes to the environment variables below will only affect Forms Server Listener implementations. Forms Servlet Listener users should go to the Forms Servlet Listener section below.

Minimizing network round trips between the middle tier and client is important, particularly if the bandwidth is limited, or the end user in on a WAN. Three environment variables have been introduced to tune the client round trips. Despite the names of the variables, they now control the round trips for all queries. The environment variables need to be set when the Forms server is started. They apply to all users with cancel query enabled. The defaults are set slightly differently for Oracle Applications users of the Forms runtime.

Initial Delay (FORMS60_LOV_INITIAL):
Specify the time in milliseconds
Default 1000 (2000 for Oracle Applications)

Minimum Polling Period (FORMS60_LOV_MINIMUM):
Specify the time in milliseconds.
Default 500 (1000 for Oracle Applications)

Network Latency Weighting (FORMS60_LOV_WEIGHT):
Specify the time in milliseconds.
Default 0 (16 for Oracle Applications)

FORMS60_LOV_INITIAL
This can be set between 1000 and 32000, and is the value in Milliseconds before the cancel query dialog initially appears. By setting this to 32000, it means the cancel query dialog will not appear until after 32 seconds have passed, and more importantly, no round trips will be generated between the Forms server and the client until after 32 seconds. If you set it to 32000, in most cases no extra network traffic will be generated by cancel query, except in the case of queries which really are candidates for being cancelled. range is 1000 to 32000. 5000 or 10000 would be a good starting point. Set to 32000 if your network is slow or heavily loaded.

The default value for Oracle Applications is set to 5000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.

You may alter the value for the FORMS60_LOV_INTIAL parameter as follows;

Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lov' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV initial' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-

On Unix:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)

FORMS60_LOV_INITIAL="10000"
export FORMS60_LOV_INITIAL

Source the environment, then restart the Forms Server process.

On Windows:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 10 seconds (10000 milliseconds) as an example)

FORMS60_LOV_INITIAL=10000

Source the environment, then restart the Forms Server process.

FORMS60_LOV_MINIMUM
This environment variable can be set to anything between 1000 and 32000 and is the value in Milliseconds between subsequent polling of the client from the middle tier. The more often you poll, the faster the query will be cancelled when you press the button, but at the expense of more network round trips. Although you can only set one value for all users, but you can set FORMS60_LOV_WEIGHT to automatically vary the time between polling based on network latency. The suggested range for FORMS60_LOV_MINIMUM is 1000 to 5000.

The default value for Oracle Applications is set to 1000 milliseconds.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.

You may alter the value for the FORMS60_LOV_MINIMUM parameter as follows;

Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovminimum' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV minimum' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-

On Unix:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 2000 milliseconds as an example)

FORMS60_LOV_MINIMUM="2000"
export FORMS60_LOV_MINIMUM

Source the environment, then restart the Forms Server process.

On Windows:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 2000 milliseconds as an example)

FORMS60_LOV_MINIMUM=2000

Source the environment, then restart the Forms Server process.

FORMS60_LOV_WEIGHT
This environment variable can be set between 0 and 32000. It defines a weighting factor, which takes account of the actual network latency to automatically adjust the polling period. You may have some users on the LAN with low latency, and other users on the WAN with high latency. All users may want to use cancel query, but the extra round trips will be more of a problem for the WAN, so this allows you to automatically decrease the frequency of the network round trips for users on slow networks and is utilized in the following equation;

x = minimum period (from FORMS60_LOV_MINIMUM) + weighting factor (from FORMS60_LOV_WEIGHT) * avg roundtrip time

Initially, there will be a certain amount of trial and error involved in finding the optimal value for this variable. We would suggest starting with the default value of 16, then increase/decrease by small amounts until the desired performance is obtained. If you do not wish to alter the interval then this value should be set to 0 (zero).

The default value for Oracle Applications is set to 16.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.

You may alter the value for the FORMS60_LOV_WEIGHT parameter as follows;

Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60lovweight' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 LOV weight' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-

On Unix:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 0 as an example)

FORMS60_LOV_WEIGHT="0"
export FORMS60_LOV_WEIGHT

Source the environment, then restart the Forms Server process.

On Windows:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 0 as an example)

FORMS60_LOV_WEIGHT=0

Source the environment, then restart the Forms Server process.


--------------------------------------------------------------------------------

Cancel Query with Long List LOV's
Longlist LOV's are a special case and do not use OCI non-blocking calls - it actually uses blocking calls at OCI level. Cancel query functionality is implemented between fetches, therefore it is only possible to cancel when a fetch completes and control is returned to the forms runtime internal code by the OCI layer. You cannot cancel if the initial execute and fetch takes an excessive time, or a subsequent fetch takes an excessive time.

In general longlist LOV queries should be tuned to perform adequately by Oracle development and the only time you need to cancel is when you enter inappropriate reduction criteria resulting in a long delay due to too many rows getting returned. In this case cancel query should work well.

FORMS60_BLOCKING_LONGLIST
This environment variable can be used to turn the cancel query feature on or off for Longlist LOV's. The default value is ‘FALSE’, which means cancel query is enabled for Longlist LOV's.

There is little reason to turn off cancel query for longlist LOV's. As the queries are always executed in blocking mode you will not save middle tier CPU. If network bandwidth is a real problem, but you still want cancel query for block queries you could turn it off.

The default value for Oracle Applications is set to FALSE.
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.

You may disable the cancel query functionality for Longlist LOV's as follows;

Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter 's_f60blocklist' in the adjacent field and press the 'Go' button.
Alter the value for the 'form 60 blocking longlist' parameter and press the 'Save' button.
Non-Autoconfig Users
To disable cancel query for Longlist LOV's, please follow the steps below:-

On Unix:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with;

FORMS60_BLOCKING_LONGLIST="TRUE"
export FORMS60_BLOCKING_LONGLIST

Source the environment, then restart the Forms Server process.

On Windows:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with;

FORMS60_BLOCKING_LONGLIST=TRUE

Source the environment, then restart the Forms Server process.


--------------------------------------------------------------------------------

Cancel Query with Standard LOV's
In theory cancel query should never need to be activated for standard LOV's. As there is no way to modify the query and re-execute it, any long running query is a performance problem, which needs to be fixed by Oracle Applications Development. Cancel query will not solve the underlying problem. Therefore from Forms patchset 14 we run all standard LOV's in blocking mode and offset some of the increase in middle tier CPU due to cancel query, with minimal loss of functionality. It will also reduce network round trips, but in general standard LOV queries will be quick, and will have completed before any client process round trips are generated.
FORMS60_ENABLE_LOV_CANCEL_QUERY
This environment variable is set to FALSE, which means cancel query is disabled for Standard LOV's (all standard LOV's will run in blocking mode). This parameter should not be changed.


--------------------------------------------------------------------------------

The Database Polling Interval
A new database polling algorithm is introduced in Forms patchset14, which should help to reduce the additional middle tier CPU used by cancel query. The new polling algorithm should use less CPU than all previous implementations.

By default we now poll every 100 milliseconds to check whether the query has completed, with sleeps in between. The more often we poll, the more CPU it uses. However if we don’t poll often enough, we will experience increases in user wait time, and in particular queries which fetch large numbers of rows, or forms which repopulate multiple detail blocks.

This is the reason we decided to disable cancel query for standard LOV’s by default (see above), as standard LOV’s which returned 1000+ records were affected most when we increased the polling interval. As we couldn't really see a need for cancel query on a standard LOV, it made more sense to disable it so we could run with a higher polling interval and reduce the middle tier CPU overhead.

FORMS60_NONBLOCKING_SLEEP
This variable allows you to alter the sleep period as required. For example, if this is set to "200" you will get a sleep period of 200 milliseconds between polling. This should be set as high as possible without impacting user-wait time. It’s not clear what the optimal value is at this time, hence the reason we made it configurable, but either 100 milliseconds (the default) or 200 milliseconds would be a good starting point. The optimal value probably be somewhere between 100 milliseconds and 400 milliseconds - any higher and it would probably start to impact user wait time.

The default value for Oracle Applications is set at 100 milliseconds
Autoconfig Users
Autoconfig users with TXK (FND) Autoconfig Template Roll Up M ( Patch 4709948 ) or later, should update this value through Autoconfig. If you are using an earlier version, you may also apply patch 3563654 to obtain this functionality.

You may disable the cancel query functionality for Longlist LOV's as follows;

Login to 'Oracle Applications Manager'
Select 'Site Map' -> 'Monitoring' Tab -> 'System Configuration' -> 'Applications Context'
Click on 'Edit Parameters' for the 'Applications' tier.
Select 'OA_VAR' from the 'Search' drop down menu, enter ' s_f60nonblockingsleep' in the adjacent field and press the 'Go' button.
Alter the value for the 'forms 60 non-blocking sleep' parameter and press the 'Save' button.
Non-Autoconfig Users
To alter the value for this environment variable please follow the steps below:-

On Unix:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.env' file with; (Using 200 milliseconds as an example)

FORMS60_NONBLOCKING_SLEEP="200"
export FORMS60_NONBLOCKING_SLEEP

Source the environment, then restart the Forms Server process.

On Windows:

Stop the Forms Server process.

Update the 'Oracle Forms 6.0 environment variables' section of your '$APPL_TOP/< SID >.cmd' file with; (Using 200 milliseconds as an example)

FORMS60_NONBLOCKING_SLEEP=200

Source the environment, then restart the Forms Server process.


--------------------------------------------------------------------------------

Forms Listener Servlet
If you are using the Forms Listener Servlet, then MaxBlockTime defined in formservlet.properties must be set to a value larger than the maximum cancel query polling interval. For example if;


FORMS60_LOV_INITIAL=10000
FORMS60_LOV_MINIMUM=20000
FORMS60_LOV_WEIGHT=0

Then set MaxBlockTime=25000 or higher.

If you set FORMS60_LOV_WEIGHT to a non-zero value, you will have to calculate the maximum polling interval based on the highest network latency. Set MaxBlockTime at least 5000ms higher than the maximum polling interval.

Failure to set MaxBlockTime to a value larger than the maximum polling interval will result in a loss of performance. If it's undefined (as in older versions of the configuration files), it defaults to 1000ms.

For the Forms Listener Servlet, the FORMS60_* environment variables are defined in the configuration file formservlet.ini

For further information on Forms Listener Servlet, see MetaLink Note id 201340.1 titled, 'Using Forms Listener Servlet with Oracle Applications 11i'.


--------------------------------------------------------------------------------

Open Enhancements (For Consideration)
Bug 823698 - Customize text/size of Cancel Query dialog and error message when query is cancelled
Bug 974350 - Mouse pointer flickers during non-blocking query on a Block/List of Values