Analyzing Spill To Disk

Query:

select t4.fragmentId
  , CASE 
      WHEN t4.scanType = 3 THEN 'HashAgg'
      WHEN t4.scanType = 17 THEN 'ExternalSort'
      ELSE 'Unknown'
      END `scanType`
  , sum(t4.records) `records`
  , sum(t4.batches) `batches`
  , sum(t4.records) / sum(t4.batches) `avgBatchSize`
  , count(t4.batches) `#fragment` 
  , CASE 
      WHEN t4.scanType = 3 AND t4.metrics.metricId = 6 THEN 'SpillMB'
      WHEN t4.scanType = 3 AND t4.metrics.metricId = 7 THEN 'SpillCycle'
      WHEN t4.scanType = 3 AND t4.metrics.metricId = 5 THEN 'NumSpills'
      WHEN t4.scanType = 3 AND t4.metrics.metricId = 3 THEN 'ResizeMS'
    ELSE 'SpillCount'
    END `metric`
  , sum(t4.metrics.longValue) `sumValue`
  , round(avg(t4.metrics.longValue),2) `avgValue`
  , max(t4.metrics.longValue) `maxValue`
FROM (
  SELECT t3.majorFId || '-XX-' || t3.minorOP.operatorId fragmentId
    , t3.minorOP.operatorType scanType
    , t3.minorOP.inputProfile.records `records`
    , t3.minorOP.inputProfile.batches `batches`
    , flatten(t3.minorOP.metric) `metrics`
  FROM (
    SELECT t2.majorFId majorFId
      , flatten(t2.minorFP.operatorProfile) minorOP
    FROM (
      SELECT t1.fp.majorFragmentId majorFId
        , flatten(t1.fp.minorFragmentProfile) minorFP
      FROM (
        SELECT flatten(t.fragmentprofile) fp
        FROM dfs.root.`/path/to/.sys.drill` t
        ) t1
        --dBug::where t1.fp.majorFragmentId in (13)
      ) t2
    ) t3
  --HashAgg(3)/ExtSort(17):
  WHERE t3.minorOP.operatorType IN (3, 17)
  ORDER BY t3.majorFId
    , t3.minorOP.operatorId
    , t3.minorOP.operatorType
  ) t4
  WHERE 
    --Show Relevant Non-Zero
    t4.metrics.longValue > 1 AND (
  --HashAgg(6,7,5,3)/ExtSort(0):
    ( scanType = 3 AND t4.metrics.metricId IN (3,5,6,7)) OR
    ( scanType = 17 AND t4.metrics.metricId IN (0))
    )
  GROUP BY t4.fragmentId
  , t4.scanType
  , t4.metrics.metricId
  ORDER BY t4.fragmentId
  , t4.metrics.metricId
;