Skip to content

Posts from the ‘Performance’ Category

Teradata – SQL Performance Issues diagnostics 101

Basic 101 for Teradata sql performance issue:

1) Look at explain plan for the sql. eg:
explain
select * from dbc.tables;

2) For more details on explain plan and get suggestions on missing stats enable helpstats at session level and the run explain plan. eg:
diagnostic helpstats on for session;
explain
select * from dbc.tables;

3) For more detailed explain with recomendations enable verboseplain at session level and the run explain plan. eg:
diagnostic verboseexplain on for session;
explain
select * from dbc.tables;

Max Concurrent jobs in Netezza

In Netezza we can run maximum of 48 concurrent jobs in parallel, provided system have sufficient resources avilable to handle these transactions. Default value is also 48 but can be changed if needed by updating the registry key gkMaxConcurrent. To check the currently configured maximum concurrent limit run:

nzsystem showRegistry | grep gkMaxConcurrent
host.gkMaxConcurrent = 48

How to change this setting?

To change the max concurrent jobs for your system:
1) Pause the system by issuing “nzsystem pause”
2) System will prompt for confirmation. Select yes.
Are you sure you want to pause the system (y|n)? [n] y
3) Run below command to change the change maximum concurrent jobs say 24 in this example.
nzsystem set -arg host.gkMaxConcurrent=24
4) System will prompt for confirmation. Select yes
Are you sure you want to change the system configuration (y|n)? [n] y
5) Resume the system for the change to take effect by issuing “nzsystem resume’
6) Verify the new setting by running
nzsystem showRegistry | grep gkMaxConcurrent
host.gkMaxConcurrent = 24