Unexpectedly Long Loads or Queries

Long loads or queries may cause issues.

Possible Causes

IQ buffer cache is too large, so the operating system is thrashing.
IQ buffer cache is too small, so SAP Sybase IQ is thrashing because it cannot fit enough of the query data into the cache.
You attempted to set IQ buffer cache sizes so that total memory requirements on your system exceed total system memory. Consequently, buffer caches have been therefore automatically reduced to their default sizes.
User-defined functions or cross-database joins requiring CIS intervention.
Missing HG or LF index on columns used in the WHERE clause and GROUP BY clause.

Action

Monitor paging to determine if thrashing is a problem.

To monitor IQ paging, run the IQ buffer cache monitor.
To monitor operating system paging, use the UNIX vmstat utility or other platform-specific tools, or the Windows Performance Monitor.

Reset your buffer sizes as needed.

You can also limit the amount of thrashing during a query execution that involves hash algorithms. Adjusting the HASH_THRASHING_PERCENT database option controls the percentage of hard disk I/Os allowed before the statement is rolled back and an error is returned.

The default value of HASH_THRASHING_PERCENT is 10%. Increasing the value permits more paging to disk before a rollback, and decreasing the value permits less paging before a rollback.

Queries involving hash algorithms that executed in earlier versions of SAP Sybase IQ may now be rolled back when the default HASH_THRASHING_PERCENT limit is reached, and you may see either of these messages:

Hash insert thrashing detected.
Hash find thrashing detected. (SQLState QFA43, SQLCode -1001047)

To provide the query with the resources required for execution, perform one or more of these actions:

Relax the paging restriction by increasing the value of HASH_THRASHING_PERCENT.
Increase the size of the temporary cache (DBA only). Increasing the size of the temporary cache reduces the size of the main buffer cache.
Attempt to identify and alleviate why SAP Sybase IQ is incorrectly estimating one or more hash sizes for this statement.
Decrease the value of the database option HASH_PINNABLE_CACHE_PERCENT.

To identify possible problems with a query, generate a query plan by running the query with the temporary database options QUERY_PLAN = 'ON' and QUERY _DETAIL = 'ON', then examine the estimates in the query plan. The option QUERY_PLAN_AFTER_RUN = 'ON' provides additional information, as the query plan is printed after the query has finished running. The generated query plan is in the message log file.