Oracle Service Cloud returns a "Poor performing query" error when executing certain queries with the driver. This typically occurs when executing a query that requires the Oracle Service Cloud service to perform join operations on tables containing a large number of rows. You can work around this issue by doing the following:
To start the SQL engine server:
1. Configure the following connection options to the values provided:
Table 10. Poor Performing Query
Option
Value
ConfigOptions
Note: In addition to specifying the following recommended value, include any other value-pairs that you currently specify for ConfigOptions. These value-pairs should be specified within the curly brackets in a comma-separated list.
{NamedIDBehavior=2}
CreateDB
1 (ForceNew)
EnablePagingWithOrderByID
1 (Enabled)
Note: This is the default value for this option.
2. Connect to your Oracle Service Cloud instance. At connection, the driver deletes the current local map files and creates new files using the updated config option settings.
3. Change the value of CreateDB to your preferred setting before your next connection. We recommend specifying a value of 2 (NotExist).
4. Test your settings by executing a query that previously returned the error. Based on the results, choose one of the following:
If you receive the "Poor Performing Query" error, proceed to the next step.
If your query is successfully returned, skip to the end of the procedure.
5. Tune the driver's fetching behavior. Using the WSFetchSize option, decrease the number of rows the driver attempts to fetch until the "Poor Performing Query" error is no longer returned. For optimal performance, configure WSFetchSize so that the driver returns the maximum number of rows possible without returning the error. For example, if you receive the error fetching 500 rows, but not 499 rows, set WSFetchSize=499 for maximum throughput.
Be aware that decreasing the fetch size can increase the number of web service calls the driver makes to the Oracle Service Cloud service. If your query exceeds the statement call limit, increase the number of rows fetched using WSFetchSize or increase the statement call limit using the StatementCallLimit option to work around this issue.
Note: You will need to disconnect and reconnect for changes to connection option values to have effect.
Note: In the default setting, WSFetchSize=0, the driver uses the maximum page size allowed by the database to determine the number of rows it attempts to fetch for Oracle Service Cloud versions August 2014 or later. For versions earlier than August 2014, the driver attempts to fetch a maximum of 10,000 rows.
This configuration will allow you to avoid the "Poor performing query" error in subsequent connections. If you begin receiving the error again, tune the WSFetchSize connection option according to the guidelines provided in step 4.
If you receive the "Poor Performing Query" error, proceed to the next step.