fbpx

Teradata Tuning And The Pareto Principle

By Roland Wenzlofsky

December 4, 2018


Improve the Performance of your Teradata System

After executing the query below, you will probably be shocked by 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 in 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 only 13 queries caused 90% of the parallel inefficiency. 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;

__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

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

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

You might also like

>