Archives of the TeradataForum

Message Posted: Mon, 28 Nov 2011 @ 19:07:13 GMT


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

Subj:		Re: Varying AMPCPUTime and TotalIOCount in DBQL

From:		Attila_Finta

Thank you everyone for your insights and suggestions. My comments:

... Explain Plan had been changed or remain the same at the different times?

Explain plans were identical.

That would happen if query that you are running would have a table(s) that are being updated, or inserted into several times a day.

No, did not happen between queries.

... also the last time the stats were refreshed.

Stats were refreshed after last insert/update. All queries were run after that.

I recommend to logoff and relogin the session while running the query each and every time. As I guess if you run the same query in same session than it will sum up the I/O and CPU Count.

I don't see that summation happening. See DBQL metrics below.

When you run queries, the results of the initial IO reads are stored in cache. When possible, subsequent runs of the query will access the data from cache rather than re-reading it from disk. Reads from cache reduce the TotalIOCount and AMPCPUTimes. The values of these two totals will vary based on how much of the data has been aged out of the cache by other work executing on the system.

When the query was re-run within a few minutes I can see a slight decrease, but that doesn't explain most of the variations:

     Start   NumOf    MaxAMP   AMP     Max   Total   Spool   Num
     _Time   Active   CPU      CPU     Amp   IO      Usage   Result
             AMPs     Time     Time    IO    Count   Rows
     -----   ------   ------   ----    ---   -----   -----   ------
      8:34     1       0.00     0.00    70     81    11776    29
     10:27     1       0.00     0.01    71     82    11776    29
     11:08     1       0.00     0.00    74     89    11776    29
     11:58     1       0.00     0.00    77     93    11776    29
     14:42     1       0.03     0.04    78     95    11776    29
     14:55     1       0.00     0.01    75     90    11776    29
     17:17     1       0.01     0.01    79     96    11776    29
     20:21     1       0.00     0.00    70     81    11776    29
     23:21     1       0.00     0.00    79     96    11776    29
     23:23     1       0.00     0.00    71     82    11776    29

It seems to me that somehow the other workload on the server affects the efficiency of the query. But I don't understand how or why. Any other thoughts on this?

Attila Finta


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

Attachments

Library

Quick Reference