Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 18 May 2001 @ 17:14:10 GMT

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

Subj:   Re: Monitoring long-running queries
From:   Thomas F. Stanek

There are a couple of ways to see what is happening with a query.

The easiest is by using PMON, a Teradata Utility that will show the actual consumption of a request in real time. This GUI tool can be executed either directly from your workstation or through Teradata Manager. There is some setup involved if you have not yet used it.

Another alternative is to query the DBC.ACTG table. There is a user view called DBC.AmpUsage that you can use. The DBC.ACTG table records the CPU and IO being consumed by a user. technically, it is recorded by Username and Accountname, so if you are taking advantage of the Account column (i.e. using literals, ASE variables, PSF) you may be able to get more detailed information. The primary key of this table is UserName, AccountName, and AMP. By querying this table several times for your UserName over time, you will begin to see the progress being made in terms of the system resources used. If you are not seeing any increase in resources used over time, you may be blocked by another request.

Using the HELP INDEX command on the target table will tell you very quickly if you have begun to populate the new table with any rows. This command returns an approx. number of rows in the table. If the approx. row count is zero, the system is still doing the scan of the table or performing the aggregation.

You can also, query the DBC.ALLSPACE view to get a sense of the spool being used by the query. Although, if you are doing mutiple requests concurrently using the same username, this will only tell you the aggregate spool used.

select sum(currentspool)
froim dbc.allspace
where username eq 'yourid'

It's also possible that the query is taking a long time because it is blocked for some reason. PMON would indicate if that is an issue.

Another possibility is that the system is very busy and your request is not getting very many CPU cycles.

In short, there are a hundred different reasons why a query might take longer than expected. Hopefully, these suggestions will give you a better indication of what is happening.


Thomas F. Stanek
TFS Consulting

  <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: 28 Jun 2020