 |
 |
Archives of the TeradataForum
Message Posted: Sun, 18 Nov 2007 @ 11:14:07 GMT
| 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.
| |
