5

what are Unnecessary-IO and Product Join Indicators in Teradata. How are these metrics determined for a query?

rogue-one
  • 11,259
  • 7
  • 53
  • 75

1 Answers1

3

Product Join Indicator is the ratio of CPU Seconds to IO for a query. Generally when the PJI is greater than 3 the query should be reviewed. Greater than 6 you may find the query is performing an unnecessary product join. To calculate the PJI using DBQL metrics: (AMPCPUTime * 1000) / TotalIOCount

Unnecessary IO Indicator is the ratio of IO to CPU Seconds. If the UII is greater than 3 the query should be reviewed to eliminate full-table scans and possibly redistribution steps. UII is a reasonable indicator to identify queries that may benefit from additional statistics to indexing improvements. To calculate the UII using DBQL metrics: TotalIOCount / (AMPCPUTime * 1000)

Rob Paller
  • 7,736
  • 29
  • 26
  • Thanks Rob, what constitutes a IOCount?. is it a single data block read? – rogue-one Jan 08 '14 at 13:53
  • If I recall correctly, TotalIOCount in DBQL is the AMP Level IO necessary to process the query. It should be both table and spool related IO at a block level. – Rob Paller Jan 08 '14 at 13:57