IQ buffer cache is too large, so the operating system is thrashing.
IQ buffer cache is too small, so 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. The buffer caches were 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.
Monitor paging to determine if thrashing is a problem.
To monitor IQ paging, run the IQ buffer cache monitor, as described in the Sybase IQ Performance and Tuning Guide section “Monitoring the buffer caches” in Chapter 5, “Monitoring and Tuning Performance.”
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. See the section “Monitoring the buffer caches” in Chapter 5, “Monitoring and Tuning Performance” of the Sybase IQ Performance and Tuning Guide.
If you monitor paging and determine that thrashing is a problem, you can also limit the amount of thrashing during the execution of a statement which includes a query 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 HASH_THRASHING_PERCENT permits more paging to disk before a rollback and decreasing HASH_THRASHING_PERCENT permits less paging before a rollback.
Queries involving hash algorithms that executed in earlier versions of IQ may now be rolled back when the default HASH_THRASHING_PERCENT limit is reached. The error "Hash insert thrashing detected." or "Hash find thrashing detected." (SQLState QFA43, SQLCode -1001047) is reported. Take one or more of the following actions to provide the query with the resources required for execution:
Relax the paging restriction by increasing the value of HASH_THRASHING_PERCENT.
Increase the size of the temporary cache (DBA only). Keep in mind that increasing the size of the temporary cache reduces the size of the main cache.
Attempt to identify and alleviate why IQ is misestimating 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.
Chapter 4, “Managing System Resources” in the Sybase IQ Performance and Tuning Guide
Chapter 5, “Monitoring and Tuning Performance” in the Sybase IQ Performance and Tuning Guide
“HASH_THRASHING_PERCENT option” and “HASH_PINNABLE_CACHE_PERCENT option” in Chapter 2, “Database Options” of the Sybase IQ Reference Manual
