A Handful of Queries is killing your Teradata System

0
886
terdata system

teradata system

Improve the Performance of your Teradata System

After executing the query below, you will probably be shocked how only a few queries are consuming a significant part of the Teradata System resources. Performance tuning of these queries can make a huge difference for the overall system health.

The overall system health itself highly depends on the parallel efficiency of your Teradata System. If we can find out the resources consumed by queries with bad parallel efficiency, we can quickly improve the overall performance within just a few days.

The query below gives you the following information for the current date about:

  • How many steps cause 5% of the parallel inefficiency
  • How many steps cause 10% of the parallel inefficiency
  • How many steps cause 25% of the parallel inefficiency
  • How many steps cause 50% of the parallel inefficiency
  • How many steps cause 90% of the parallel inefficiency
  • The number of actions included in the analysis

The shocking result on a test site: 1,1,1,1,13,1414

We can say that 90% of the parallel inefficiency was caused by only 13 queries. By optimizing these queries could have an enormous impact on the overall system performance. The picture on your site will probably be quite similar.

After having the big picture you can dig into the details per query available in the DBC.DBQLOGTBL. The important measures you can reuse from below query are: CPU_IMPACT and WASTED_CPU.

select ‘NUMBER_OF_QUERIES’ (TITLE ”),
  min(l5) “TOP5 Percent”,
  min(l10) “TOP10 Percent”,
  min(l25) “TOP25 Percent”,
  min(l50) “TOP50 Percent”,
  min(l75) “TOP90 Percent”,
  max(rnk) “Queries”
from
(
  select
  case when x < 5.00 then null else rnk end l5
,case when x < 10.00 then null else rnk end l10
,case when x < 25.00 then null else rnk end l25
,case when x < 50.00 then null else rnk end l50
,case when x < 90.00 then null else rnk end l90
,rnk
, Total_CPU
, CPU_IMPACT
from
(
select
sum(Wasted_CPU) over (order by Wasted_CPU desc ) TOTAL_WASTE
, sum(Wasted_CPU) over (order by Wasted_CPU desc rows unbounded preceding) TOTAL_WASTE_OLAP
, TOTAL_WASTE_OLAP *100.00 / TOTAL_WASTE x
, sum(1) over (order by Wasted_CPU desc rows unbounded preceding) rnk
, Total_CPU
, CPU_IMPACT
from
(
select
AMPCPUTime Total_CPU
, (MaxAMPCPUTime * (hashamp () + 1)) CPU_IMPACT
, CPU_IMPACT – Total_CPU Wasted_CPU

from dbc.dbqlogtbl where
starttime(date) = date
and ampcputime>0
) x
) y
) z
group by 1;

Our Reader Score
[Total: 9    Average: 5/5]
A Handful of Queries is killing your Teradata System written by Roland Wenzlofsky average rating 5/5 - 9 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here