Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   NUSI Access Performance Issues
 
From:   cbarrineau

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016