profiling-statement-fingerprints
Ranks and analyzes statement fingerprints using aggregated SQL statistics from crdb_internal.statement_statistics to identify slow, resource-intensive, or error-prone query patterns. Use when investigating historical performance trends, identifying optimization opportunities, or diagnosing recurring slowness without DB Console access.
소스 정보
- 저장소
- cockroachdb/copilot-plugin
- 최근 소스 활동
- 2026년 7월 1일 02:07
- 감지된 SKILL.md 언어
- 영어
- 스타
- 0
- 포크
- 0
설치 방법
기본적으로 소스를 먼저 확인하는 Prompt가 선택됩니다. 직접 명령으로 전환하거나 로컬 사본을 다운로드할 수도 있습니다.
소스 파일 검토
설치 여부를 결정하기 전에 SKILL.md와 SkillsMP에 표시된 보조 파일을 읽어 보세요.
파일 탐색기
4 개 파일SKILL.md 표시 중
SKILL.md
소스 지침 · 읽기 전용 미리보기- name
- profiling-statement-fingerprints
- description
- Ranks and analyzes statement fingerprints using aggregated SQL statistics from crdb_internal.statement_statistics to identify slow, resource-intensive, or error-prone query patterns. Use when investigating historical performance trends, identifying optimization opportunities, or diagnosing recurring slowness without DB Console access.
- compatibility
- Requires SQL access with VIEWACTIVITY or VIEWACTIVITYREDACTED cluster privilege. Uses crdb_internal.statement_statistics (production-safe). Execution statistics fields are sampled and may be sparse.
- metadata
- {"author":"cockroachdb","version":"1.0"}
# Profiling Statement Fingerprints
Analyzes historical statement performance patterns using aggregated SQL statistics to identify slow, resource-intensive, or error-prone query fingerprints. Uses `crdb_internal.statement_statistics` for time-windowed analysis of latency, CPU, contention, admission delays, and failure rates - entirely via SQL without requiring DB Console access.
**Complement to triaging-live-sql-activity:** This skill analyzes historical patterns; for immediate triage of currently running queries, see [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md).
## When to Use This Skill
- Identify slowest statement fingerprints over past hours/days/weeks
- Find queries with high CPU consumption, contention, or admission waits
- Investigate performance regressions or plan changes
- Locate full table scans or missing indexes via index recommendations
- Analyze resource consumption by application or database
- SQL-only historical analysis without DB Console access
**For immediate incident response:** Use [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md) to triage currently running queries and cancel runaway work.
**For transaction-level analysis:** Use [profiling-transaction-fingerprints](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/profiling-transaction-fingerprints/SKILL.md) to analyze retry patterns, commit latency, and statement composition at the transaction boundary.
**For background job monitoring:** Use [monitoring-background-jobs](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/monitoring-background-jobs/SKILL.md) for long-running schema changes and automatic jobs excluded from statement statistics.
## Prerequisites
- SQL connection to CockroachDB cluster
- `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility
- Statement statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true`
**Check collection status:**
```sql
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; -- Should return: true
```
See [triaging-live-sql-activity permissions reference](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges).
## Core Concepts
### Statement Fingerprints vs Live Queries
**Statement fingerprint:** Normalized SQL pattern with parameterized constants (e.g., `SELECT * FROM users WHERE id = $1` vs `SELECT * FROM users WHERE id = 123`)
**Key differences:**
- **Time scope:** Historical hourly buckets vs real-time current state
- **Granularity:** Aggregated pattern statistics vs individual execution instances
### Time-Series Bucketing
**aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions)
**Data retention:** Capped by row count, not time. `sql.stats.persisted_rows.max` (default 1,000,000) bounds the persisted statement+transaction rows; older buckets are compacted once the cap is reached. Effective wall-clock window depends on workload diversity.
**Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'`
### Aggregated vs Sampled Metrics
| Metric Category | JSON Path | Scope | Use Case |
|-----------------|-----------|-------|----------|
| **Aggregated** | `statistics.statistics.*` | All executions | Latency, row counts, execution counts |
| **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | CPU, contention, admission wait, memory/disk |
**Critical:** Always check sampled metrics presence: `WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL`
### JSON Field Extraction
**Operators:**
- `->`: Extract JSON object (returns JSON)
- `->>`: Extract as text (returns text)
- `::TYPE`: Cast to specific type
**Examples:**
```sql
metadata->>'db' -- Database name
(statistics->'statistics'->>'cnt')::INT -- Execution count
(statistics->'statistics'->'runLat'->>'mean')::FLOAT8 -- Mean latency (seconds)
(statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 -- CPU (convert nanos to seconds)
```
**Units:** Latency = seconds, CPU/admission = nanoseconds (÷ 1e9), Memory/disk = bytes (÷ 1048576 for MB)
See [JSON field reference](references/json-field-reference.md) for complete schema.
## Core Diagnostic Queries
### Query 1: Top Statements by Mean Run Latency
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
metadata->>'query' AS query_text,
(statistics->'statistics'->>'cnt')::INT AS execution_count,
(statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,
(statistics->'statistics'->'runLat'->>'max')::FLOAT8 AS max_run_lat_seconds,
(metadata->>'fullScan')::BOOL AS full_scan,
metadata->'index_recommendations' AS index_recommendations,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 > 1.0 -- > 1 second mean latency
ORDER BY (statistics->'statistics'->'runLat'->>'mean')::FLOAT8 DESC
LIMIT 20;
```
**Focus:** Slowest queries; check `full_scan` and `index_recommendations` for optimization opportunities.
### Query 2: Admission Control Impact
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
metadata->>'query' AS query_text,
(statistics->'statistics'->>'cnt')::INT AS execution_count,
(statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 / 1e9 AS mean_admission_wait_seconds,
(statistics->'statistics'->'runLat'->>'mean')::FLOAT8 AS mean_run_lat_seconds,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
AND (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 > 0
ORDER BY (statistics->'execution_statistics'->'admissionWaitTime'->>'mean')::FLOAT8 DESC
LIMIT 20;
```
**Interpretation:** High admission wait = cluster at resource limits (CPU, memory, I/O). Ratio > 1.0 (wait > runtime) indicates severe queueing.
### Query 3: Plan Hash Diversity
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
metadata->>'query' AS query_text,
COUNT(DISTINCT plan_hash) AS distinct_plan_count,
array_agg(DISTINCT plan_hash ORDER BY plan_hash) AS plan_hashes,
SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '7 days'
GROUP BY fingerprint_id, metadata->>'db', metadata->>'query'
HAVING COUNT(DISTINCT plan_hash) > 1
ORDER BY COUNT(DISTINCT plan_hash) DESC, SUM((statistics->'statistics'->>'cnt')::INT) DESC
LIMIT 20;
```
**Interpretation:** Multiple plans indicate instability from schema changes, statistics updates, or routing changes. Performance can vary significantly between plans.
### Query 4: High Contention Statements
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
metadata->>'app' AS application,
substring(metadata->>'query', 1, 150) AS query_preview,
(statistics->'statistics'->>'cnt')::INT AS execution_count,
(statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds,
ROUND(
((statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9) /
NULLIF((statistics->'statistics'->'runLat'->>'mean')::FLOAT8, 0) * 100, 2
) AS contention_pct_of_runtime,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
AND (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 > 0
ORDER BY (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 DESC
LIMIT 20;
```
**Interpretation:** >20% contention = transaction conflicts, hot row access. Remediate with batching, transaction boundary changes, or schema redesign.
### Query 5: High CPU Consumers
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
substring(metadata->>'query', 1, 150) AS query_preview,
(statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9 AS mean_cpu_seconds,
(statistics->'statistics'->>'cnt')::INT AS total_executions,
ROUND(
((statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 / 1e9) *
(statistics->'statistics'->>'cnt')::INT, 2
) AS estimated_total_cpu_seconds,
(metadata->>'fullScan')::BOOL AS full_scan,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
AND (statistics->'execution_statistics'->'cpuSQLNanos'->>'mean')::FLOAT8 > 0
ORDER BY estimated_total_cpu_seconds DESC
LIMIT 20;
```
**Focus:** `estimated_total_cpu_seconds` shows cluster impact. High mean CPU often correlates with `full_scan = true`.
### Query 6: Memory and Disk Spill Detection
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
substring(metadata->>'query', 1, 150) AS query_preview,
(statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb,
(statistics->'execution_statistics'->'maxMemUsage'->>'max')::FLOAT8 / 1048576 AS max_mem_mb,
(statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb,
(statistics->'execution_statistics'->'maxDiskUsage'->>'max')::FLOAT8 / 1048576 AS max_disk_mb,
metadata->>'stmtType' AS statement_type,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL
AND (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 > 0 -- Has disk spills
ORDER BY (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 DESC
LIMIT 20;
```
**Interpretation:** Disk usage > 0 = memory spill (~100-1000x slower than in-memory). Common for large aggregations, sorts, hash joins. Fix with indexes or increased `sql.distsql.temp_storage.workmem`.
### Query 7: Error-Prone Statements
```sql
SELECT
fingerprint_id,
metadata->>'db' AS database,
substring(metadata->>'query', 1, 150) AS query_preview,
(statistics->'statistics'->>'cnt')::INT AS total_executions,
COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) AS failure_count,
ROUND(
COALESCE((statistics->'statistics'->>'failureCount')::INT, 0)::NUMERIC /
NULLIF((statistics->'statistics'->>'cnt')::INT, 0) * 100, 2
) AS failure_rate_pct,
aggregated_ts
FROM crdb_internal.statement_statistics
WHERE aggregated_ts > now() - INTERVAL '24 hours'
AND (statistics->'statistics'->>'cnt')::INT > 10
AND COALESCE((statistics->'statistics'->>'failureCount')::INT, 0) > 0
GitHub에서 보기이 SKILL.md는 매우 커서 SkillsMP가 여기에는 첫 섹션만 미리 보여줍니다. GitHub에서 보기
