fbpx

How to get SQL Query Stats in Teradata

By Nitin Srivastava

April 8, 2014


Several parameters can help us in understanding SQL Query Performance in Teradata.
I consider AMPCPUTime, TotalIOCount, SpoolUsage as three main parameters to determine SQL Query performance.
Say, you are executing multiple queries in Teradata sequentially. You might think the query which took the most time is weak, but this may not be true for all the cases. However, if you refer to above mentioned three parameters to decide the worst query, you will be correct for most cases.
There are two tables in DBC which give us this required information: DBQLOGTBL and DBQLSQLTBL.
To get SQL Query Stats, you can use the below-mentioned query:

SEL
TB1.queryband,TB1.NumResultRows,TB1.NumSteps,TB1.TotalIOCount,
TB1.AMPCPUTime,TB1.ParserCPUTime,TB1.NumOfActiveAMPs,TB1.MaxCPUAmpNumber,
TB1.MinAmpIO,TB1.MAxAmPIO,TB1.MaxIOAmpNumber,TB1.SpoolUsage,
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QueryID = TB2.QueryID
AND
TB1.ProcID = TB2.ProcID
AND
TB1.SessionID=12345;

You can add or remove columns per your requirement. However, the ones highlighted are essential parameters for determining any Query Performance in Teradata.
If the AMPCPUTIME is high, you have to tune your query to make sure it performs well.

Three points to consider while running the query mentioned above:

a) You may not see results immediately after running your SQL queries. There are a few minute delays when query information comes to DBQL tables.

b) The query mentioned above may take some time to give output. The reason behind it is the ‘NOT SO PROPER’ Index columns for these two tables. When we check the PRIMARY INDEX columns for both the tables, we observe that the PI is the same. Both the tables have ProcID, CollectTimeStamp as PI. However, the value for CollectTimeStamp can be different for the same query in both the tables. Hence joining by the second column is not advisable. Therefore, you cannot leverage PI ultimately here; thus, the query may need a long time to give results.

c) To get the SessionID, just run SEL SESSION; command in the same session you are running your queries.

So now on, never say that query which took the maximum time is the worst. Fetch the Query DBQL stats and check the worst query yourself.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Nitin Srivastava

Nitin Srivastava holds engineering degree in Computer Science. He has 5+ years of experience in Teradata SQL Development and Query Optimization. He has worked for Telecom,Health Care, Banking Clients across the globe.

    • FROM The docs:

      Before Teradata Database writes DBQL data to Data Dictionary tables, it holds the data in cache until either:
      1.The cache is full.
      2.You end query logging.
      3.The number of seconds has elapsed that you define in the DBS Control utility field DBQLFlushRate.
      Note: You can select a rate from 1 to 3,600 seconds.However,Teradata recommends a flush rate of at least 10 minutes (600 seconds), which is the default. Less than 10 minutes can impact performance.
      4.You flush the DBQL cache manually.

  • You will need to start query logging then end it to flush the cache, or you will get nothing using your query

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >