DEA-C01 Data Operations and Support Practice Question

Which TWO are valid approaches to troubleshoot a slow Amazon Redshift query? (Choose two.)

⚠ Common exam trap

DEA-C01 often tests the difference between diagnostic steps (EXPLAIN, STV_LOCKS) and remediation or unrelated actions (VACUUM, encryption, DISTSTYLE change) — candidates who pick remediation steps as 'troubleshooting' fall into the trap.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Check for table locks using STV_LOCKS.

Option A is correct because STV_LOCKS is the Amazon Redshift system view that shows current table locks, and a query can appear slow simply because it is blocked waiting on a lock held by another transaction, so checking it is a valid troubleshooting step. Option C is correct because the EXPLAIN command returns the query execution plan, revealing expensive operations such as sequential scans, nested loops, or large data redistributions and broadcasts, which directly identifies why a query is slow. Option B is not a troubleshooting approach for slow queries; enabling cluster encryption is a security configuration and does not affect query performance. Option D is not a diagnostic step but a maintenance action, and running VACUUM blindly is not a valid troubleshooting approach since it can be costly and should follow diagnosis. Option E is likewise a remediation action that changes the table's distribution style, not a way to investigate the cause of slowness, and it should only be applied after identifying a distribution problem.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Check for table locks using STV_LOCKS.
Why this is correct
STV_LOCKS exposes active table locks that block query progress, directly addressing the slow-query constraint. When another transaction holds a lock on a target table, dependent queries queue and stall; inspecting this view identifies the blocking session so it can be resolved. This is a valid, documented Redshift troubleshooting approach.
✗
Enable encryption on the cluster.
Why it's wrong here
Encryption protects data at rest and adds no diagnostic insight into query execution; slow queries are investigated via EXPLAIN plans, STL/SVL system views and workload management queues. It is tempting because encryption is a valid cluster security setting, but it is the correct answer for compliance, not performance troubleshooting.
✓
Use the EXPLAIN command to review the query execution plan.
Why this is correct
EXPLAIN returns the Redshift query execution plan, exposing sequential scans, missing sort keys and costly redistribution or broadcast steps. Reviewing this plan identifies the specific operation causing the slow runtime, directly addressing the troubleshooting scenario.
✗
Run VACUUM on the table.
Why it's wrong here
VACUUM reclaims space from deleted rows and re-sorts unsorted data, so it only speeds queries when the table is heavily unsorted or bloated. A slow query caused by skew, missing statistics or poor distribution remains slow after VACUUM. It is tempting because routine maintenance genuinely improves scan performance on churned tables.
✗
Alter the table to change DISTSTYLE to KEY.
Why it's wrong here
Changing DISTSTYLE to KEY redistributes rows across nodes, which only helps when the current distribution causes skew or forces broadcast traffic for joins. It cannot fix a slow query caused by missing sort keys, stale statistics or full-table scans, and rebuilding a large table is disruptive. It suits persistent join-key skew.

Go deeper

DEA-C01 Data Operations and Support Practice Question

Why each option matters

Option-by-option breakdown

Related to this question