Archives of the TeradataForum

Message Posted: Thu, 13 Oct 2005 @ 12:40:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

All,

We have a certain class of queries which we monitor to make sure that response times stay lower than 20 seconds. Anytime a query exceeds this threshold, we investigate the offending SQL statement. In about 80% of the cases, the offending SQL contains a step utilizing a NUSI to get rows from the fact table (in our case, an AJI.) In almost all of these cases, the optimizer's estimates are way off*, and the use of a NUSI is a very poor decision. So, we ran a test using the same queries against a fact table without NUSIs. Though the new plans had higher CPU and IO costs, the wall-clock times were about the same or better (particularly better during times of heavy workload on the system.) Does this make sense? Is there anything specific to the NUSI access method which makes it particularly susceptible to performance problems on a busy system?

Any help would be greatly appreciated.

Thanks,

Clay

*Attempts to improve the optimizer's estimates have been ineffective

	<Prev	Next>		<<First	<Prev	Next>	Last>>