COMMON REASON FOR NZ QUERY PERFORMANCE ISSUE

-          Not having correct distribution keys resulting in certain data slice storing more data resulting in data skew performance of a query depends on the performance of the slice storing most data for the tables involved in query.

-          Even if the data is distributed uniformly, not taking into account processing patterns in distribution results is process skew.  For e.g. Data is distributed by month but if the process looks for a month of data,  then the performance of the query will be degraded since the processing needs to be handled by a subset of SPUs and not all of the SPUs in the system.

-          Performance gets impacted if a large volume of data (fact table) gets re-distributed or broadcast during query execution.

-          Not having zone maps of columns since the data types are not the ones for which NZ can generate Zone Maps like numeric (x,y),char,varchar etc.

-          Not having the table data organized optimally for multi table joins as in the case of multi-dimensional joins performed in a data warehouse environment.

For job long running analysis:

 -          Identify if queries are being queued and the long running queries which is causing the queries to be queued through nzadmin tool/NPS or nzseesion command.

-          Long running queries can also be identified if query history is being gathered using appropriate history configuration (Tidal job history runtime).

-          For long running queries generate query plans using the “EXPLAIN” command.  Recent query execution plans are also stored in “.pln” files in the data. <ver> /plans directory under the /nz/data directory.

-          There might a chance like outdated for stats collection and grooming tables & create / change the distribution keys. 

-          Creation of materialized views and modifying or using organization keys.

-          If the analysis is performed in a non – development environment(QA),  it is key to make sure that the statistics reflect the values expected or in production.

Comments

Post a Comment

Popular posts from this blog

Database Size : Calculation in Teradata

SELECT DatabaseNameI, PermSpace, SpoolSpace, TempSpace,CreateTimeStamp FROM DBC.DBase WHERE DatabaseNameI = '[databasename]'; Permanent Space: Permanent space is the maximum amount of space allocated to the user/database to hold data rows. Perm space is used for database object (permanent tables, indexes etc) creation and to hold their data. The amount of permanent space is divided among the number of AMPs.Whenever per AMP limit exceeds the allocated space of AMP, 'No more room in database' error message is generated. Spool Space: Spool space is the unused permanent space which is used by the system to keep the intermediate results of the SQL query. Users without spool space cannot execute any query. Data is active up to the current session only. Spool space is divided among the number of AMPs. Whenever per AMP limit exceeds the allocated space, the user will get a spool space error. Temporary Space: Temporary space is the unused permanent space whic...
Read more

The difference between SET and MULTISET tables is?

The difference between SET and MULTISET tables is? SET tables  – SET tables did not allow duplicate values in the table. If not specified in the DDL of the table then Teradata will create table as default SET. A SET table force Teradata to check for the duplicate rows every time a new row is  inserted  or  updated  in the table. This is an overhead on the resource if we need to insert massive amount of rows. MULTISET tables  – MULTISET tables allow duplicate values in table. Remember that SET table causes an additional overhead of checking for the duplicate records. So we need to follow few points to save Teradata from this additional overhead. ·      If you are using any  GROUP BY  or  QUALIFY  statement on the source table then it’s highly recommended to define target table as MULTISET. As GROUP BY and QUALIFY will remove the duplicate records from the source. · ...
Read more

Hadoop Commands

hadoop archive The  hadoop archive  command creates a Hadoop archive, a file that contains other files. A Hadoop archive always has a  *.har  extension. hadoop classpath The  hadoop classpath  command prints the class path needed to access the Hadoop jar and the required libraries. hadoop daemonlog The  hadoop daemonlog  command gets and sets the log level for each daemon. hadoop distcp The  hadoop distcp  command is a tool used for large inter- and intra-cluster copying. hadoop fs The  hadoop fs  command runs a generic file system user client that interacts with the MapR file system. hadoop jar The  hadoop jar  command runs a program contained in a JAR file. Users can bundle their MapReduce code in a JAR file and execute it using this command. hadoop job The  hadoop job  command enables you to manage MapReduce jobs. hadoop mfs The  hadoop mfs  command displays directory inf...
Read more