Performance · Migrating To Amazon Redshift · aws/agent-toolkit-for-aws · Skills Docs

Performance · Migrating To Amazon Redshift · aws/agent-toolkit-for-aws · Skills Docs

-- Overall stats (one row; empty when logging off).
SELECT TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(COUNT(DISTINCT UserName) AS INTEGER)) || '|' ||
       TRIM(CAST(CAST(AVG(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) || '|' ||
       TRIM(CAST(CAST(MAX(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) || '|' ||
       TRIM(CAST(SUM(TotalIOCount) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl
HAVING COUNT(*) > 0;

-- Mix by StatementType (ETL vs BI → WLM queues).
SELECT TRIM(StatementType) || '|' || TRIM(CAST(COUNT(*) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl GROUP BY StatementType ORDER BY COUNT(*) DESC;

-- Top query-issuing users.
SELECT TRIM(UserName) || '|' || TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(CAST(SUM(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) AS rec
FROM DBC.DBQLogTbl GROUP BY UserName ORDER BY COUNT(*) DESC;

-- Concurrency/peak proxy: query count by hour-of-day.
SELECT TRIM(CAST(EXTRACT(HOUR FROM StartTime) AS INTEGER)) || '|' ||
       TRIM(CAST(COUNT(*) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl
GROUP BY EXTRACT(HOUR FROM StartTime)
ORDER BY EXTRACT(HOUR FROM StartTime);

-- System resource summary (one row; empty when logging off).
--   cpu_busy% = (CPUUExec + CPUUServ) / (CPUUExec + CPUUServ + CPUIdle) * 100   [VALIDATED]
-- NOTE: ResUsageSpma.MemSize is NOT physical node RAM (see validated note below) — do not
-- derive total RAM from it. Source RAM for sizing comes from node spec / operator.
SELECT TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(COUNT(DISTINCT NodeID) AS INTEGER)) || '|' ||
       TRIM(CAST(CAST(AVG((CPUUExec + CPUUServ) * 100.0
            / NULLIFZERO(CPUUExec + CPUUServ + CPUIdle)) AS DECIMAL(6,2)) AS VARCHAR(20))) || '|' ||
       TRIM(CAST(CAST(MAX((CPUUExec + CPUUServ) * 100.0
            / NULLIFZERO(CPUUExec + CPUUServ + CPUIdle)) AS DECIMAL(6,2)) AS VARCHAR(20))) AS rec
FROM DBC.ResUsageSpma
HAVING COUNT(*) > 0;

{
  "source": "teradata",
  "window": { "from": null, "to": null, "note": "DBQL retention window" },
  "query_stats": {
    "total_queries": 0, "distinct_users": 0,
    "avg_amp_cpu": 0.0, "max_amp_cpu": 0.0, "total_io": 0,
    "by_statement": [{ "statement_type": "Select", "count": 0 }],
    "by_hour": [{ "hour": 0, "count": 0 }],
    "top_users": [{ "user": "tester", "count": 0, "cpu": 0.0 }],
    "peak_hour": null, "peak_hour_count": 0
  },
  "resource_stats": {
    "sample_count": 0, "node_count": 2,
    "avg_cpu_busy_pct": null, "max_cpu_busy_pct": null
  },
  "representative_queries": [
    { "id": "q1", "sql": "...", "td_runtime_s": 0.0, "amp_cpu": 0.0, "io": 0 }
  ]
}

{
  "pairs": [
    { "id": "q1", "td_runtime_s": 0.0, "rs_runtime_s": 0.0, "delta_pct": 0.0, "status": "ok|regression" }
  ],
  "aggregate": { "queries": 0, "median_delta_pct": 0.0, "regressions": 0 },
  "notes": "warm-cache, concurrency-matched"
}