Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sun, 18 Nov 2007 @ 11:14:07 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Cache in teradata?
From:   Wu, Judy

Anomy.Anom wrote:

  In case of oracle if we run same query twice in short interval of time, the time taken to produce the results second time may be significantly lesser. This is because of Cache concept in Oracle. Does anything similar happen in Teradata? If I run same query twice will I be seeing a better performance when it is run the second time??  

  I understand that we do have dictonary cache which may speed up the creation of plan but I don't it should affect performance by more then 4-5 secs. Do you see any other reason for same query taking 1min during one run and 15-16 secs during second run?  

This would depend on what type of query you are running and the workload of the system in which a given query is running. If a query is involved long IO, such as full table scan on a big table on a quite system, I would see much short elapse time for the 2nd run (right after the 1st run) due to most of data read from disk still staying at FSG cache (haven't yet swapped out yet from the 1st read). The other parts of the cache become relative insignificant in this case compring with the big amount of data cache in FSG available for your 2nd run of the same query.

For the short query, plan will be cached for the 2nd run. The plan cach is a big deal for a short query such as primary index access due to recompilng a plan needing some IO to access DBC dictionary tables.

If you are really interested in how the first and 2nd run difference, use RSS data to look at the logical io and physical io and the rest of other attributes of performance counters.

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020