Przejdź do treści
Biblioteka skilli
Dane i analityka · Analiza

jfr-sql

/jfr-sql

Podpowiada schemat bazy JFR z profilu Jeffrey, nazwy pól zdarzeń i pułapki, które zwracają błędny wynik bez komunikatu.

Repozytorium
jeffrey ↗
Licencja
Apache-2.0
Ostatnia zmiana repozytorium
12.11.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 12.11.2026, bez odwołań do wycofanych modeli i API.

Licencja

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

Co robi i kiedy

Podpowiada schemat bazy JFR z profilu Jeffrey, nazwy pól zdarzeń i pułapki, które zwracają błędny wynik bez komunikatu.

Oryginalny opis (EN)

Load before the first jfr_executeQuery or jfr_queryEvents call, whichever skill is driving the analysis: the schema of a Jeffrey profile's JFR database (events, event_types, threads, stacktraces, frames), the field names per event type, and the traps that return a wrong answer without an error — events_raw's truncated fields, ->> binding tighter than casts, root-first stacks, the row cap. Use for what no flamegraph, trace, heap or jvm dashboard tool answers: how many distinct values a field takes, a distribution over time, a correlation between event types.

Jak działa

  1. Query events, never eventsraw
  2. Do not guess column names
  3. The tables
  4. Idioms that matter
  5. Event types worth knowing
  6. GC and JIT: try the jvm family first
  7. JIT compilation and deoptimisation
  8. Link what you name
  9. Results are capped
  10. One statement, and no way out of the database
Sekcje z nagłówków SKILL.md, w oryginale · 282 linii, 2 284 słów
Podgląd SKILL.md
---
name: jfr-sql
description: "Load before the first jfr_executeQuery or jfr_queryEvents call, whichever skill is driving the analysis: the schema of a Jeffrey profile's JFR database (events, event_types, threads, stacktraces, frames), the field names per event type, and the traps that return a wrong answer without an error — events_raw's truncated fields, ->> binding tighter than casts, root-first stacks, the row cap. Use for what no flamegraph, trace, heap or jvm dashboard tool answers: how many distinct values a field takes, a distribution over time, a correlation between event types."
allowed-tools: mcp__plugin_microscope_jeffrey__* mcp__jeffrey__*
---

# Querying a profile's JFR database

Each profile is one DuckDB database. Reach it with `jfr_executeQuery` (SELECT/WITH only, row-capped)
or `jfr_queryEvents` for the common "give me events of this type" case.
Tool names below omit the prefix your client puts in front of them — `mcp__plugin_microscope_jeffrey__` in Claude Code with the `microscope` plugin, `mcp__jeffrey__` in Codex or wherever the server is registered by hand as `jeffrey`, `mcp_jeffrey_` in Gemini CLI; the rest of the name is exact camelCase.

Prefer a purpose-built tool where one exists — `flamegraph_export` beats reconstructing a call tree
by hand, and the `heap_` family beats querying the heap index. SQL is for the questions they do not
cover: distributions over time, correlations between event types, cardinality of a field.

## Query `events`, never `events_raw`

`jfr_listTables` shows both. `events` is a view over `events_raw` that splices back the one large
string field the parser pools out of each row. Querying `events_raw` silently returns truncated JSON
in `fields`, with no error to warn you.

## Do not guess column names

Call `jfr_describeTable('events')` first. In particular the duration column is `duration` — not
`duration_ns`, not `duration_ms`.

## The tables

- **`events`** — one row per event. `event_type`, `start_timestamp` (TIMESTAMPTZ),
  `start_timestamp_from_beginning` (BIGINT millis since recording start — sargable, prefer it for
  relative time filters), `duration`, `samples`, `weight`, `weight_entity`, `stacktrace_hash`,
  `thread_hash`, `fields` (JSON).
- **`event_types`** — `name`, `label`, `description`, `categories`, `subtype`,
  `has_stacktrace`, `extras`, plus two columns worth knowing about because `jfr_listEventTypes`
  does not return them:
  - **`columns`** — a JSON array of `{field, header, type, description}`: the *declared fields* of
    that event type. `SELECT columns FROM event_types WHERE name = 'jdk.Deoptimization'` is the
    answer to "what is in `fields` for this type", instead of guessing at key names.
  - **`settings`** — how the recording was configured for that type. This is what settles "was
    this event switched off, or did it genuinely not happen": a threshold-gated type
    (`jdk.Compilation`, the I/O and monitor events) with no rows may simply have had nothing cross
    its threshold.
- **`threads`** — `thread_hash` (PK), `name`, `os_id` (null for virtual threads), `java_id`, `is_virtual`.
- **`stacktraces`** — `stacktrace_hash` (PK), `type_id`, `frame_hashes` (BIGINT array, ordered
  **root-first** — see the stacks idiom below), `tag_ids` (INTEGER array; `0` marks an idle stack,
  `1` an unsafe allocation — the tags the flamegraph's `excludeIdle` option filters on, matched with
  `list_has_any(tag_ids, [0])`).
- **`frames`** — `frame_hash` (PK), `class_name`, `method_name`, `frame_type`, `line_number`,
  `bytecode_index`, `hidden_class_id`.

Derived from the events when the profile carries Jeffrey Tracing, and empty otherwise:

- **`traces`** — one row per trace: `trace_id` (PK), `root_name`, `root_kind`, `root_event_type`
  (together the operation), `start_timestamp`, `start_timestamp_from_beginning`, `duration`,
  `span_count`, `error_count`.
- **`trace_spans`** — one row per span: `trace_id`, `span_id`, `parent_span_id`, `name`, `kind`,
  `status`, `error_type`, `start_timestamp_from_beginning`, `duration`, `self_duration`,
  `thread_hash`, `event_type`, `attributes` (JSON text).
- **`trace_notifications`** — what the application said inside a trace: `trace_id`, `span_id`
  (NULL when no span was open), `notification_id`, `start_timestamp_from_beginning`, `type`,
  `severity`, `category`, `source`, `attributes`, and `message_ref` into
  **`trace_notification_messages`** (`message_id`, `message_text`), which holds each distinct
  sentence once. Prefer `traces_notifications` for the grouped reading; SQL is for a question it
  does not shape, such as notifications per minute.

## Idioms that matter

- **Durations are nanoseconds.** `duration / 1000000` → milliseconds.
- **Event-specific data is JSON** in `fields`: `(fields->>'key')`, or `json_extract_string(fields, '$.key')`.
  Cast before comparing numerically: `CAST(fields->>'bytesRead' AS BIGINT) > 100`.
- **Parenthesise every `->>` outside a function call.** DuckDB binds `::`, arithmetic and `||`
  more tightly than `->>`, so `fields->>'size'::BIGINT` casts the key `'size'` (and fails with
  "Could not convert string 'size' to INT64") and `fields->>'end' - fields->>'start'` subtracts a
  document from a string. `->` is looser still, below comparisons, `AND` and `OR` too:
  `fields->'id' = 1` reads as `fields->('id' = 1)`. Write `(fields->>'size')::BIGINT` and
  `(fields->>'end') - (fields->>'start')`; inside `CAST(…)`, `SUM(…)` or any other call it is
  already safe.
- **Timestamps**: `epoch_ms(start_timestamp)` for epoch millis.
- **Stacks**: `UNNEST(frame_hashes)` and join to `frames`. The array is stored **root-first**: the
  first element is the thread entry point (`Thread.run`) and the **last** is the leaf that was
  actually executing. Taking the first element as "the hot method" is the mistake to avoid — it
  returns the entry point on every stack in the recording. Keep the position with
  `generate_subscripts(frame_hashes, 1)` beside the `UNNEST`, and order by it descending for the
  topmost-first reading a stack trace normally has:

  ```sql
  WITH positioned AS (
    SELECT UNNEST(frame_hashes) AS frame_hash,
           generate_subscripts(frame_hashes, 1) AS depth
    FROM stacktraces WHERE stacktrace_hash = ?
  )
  SELECT f.class_name, f.method_name FROM positioned p
  JOIN frames f USING (frame_hash) ORDER BY p.depth DESC
  ```

  For the leaf alone, `frame_hashes[-1]` — DuckDB lists are 1-based, and negative indices count
  from the end.
- **Hidden classes**: `class_name` never carries the per-run address of a JEP 371 hidden class
  (lambda proxy, method-handle form, indified string concat). The address lives in `hidden_class_id`,
  so `hidden_class_id IS NOT NULL` selects hidden frames — a null check, not a `LIKE`.
- **GROUP BY**: every non-aggregated column in the SELECT must appear in it.
  `SELECT event_type, COUNT(*) FROM events` is invalid; add `GROUP BY event_type`.

## Event types worth knowing

CPU `jdk.ExecutionSample`, `jdk.NativeMethodSample` · allocation `jdk.ObjectAllocationSample`,
`jdk.ObjectAllocationInNewTLAB`, `jdk.ObjectAllocationOutsideTLAB` · GC `jdk.GarbageCollection`,
`jdk.GCPhasePause`, `jdk.GCHeapSummary`, `jdk.YoungGarbageCollection`, `jdk.OldGarbageCollection`,
`jdk.G1GarbageCollection` · threading `jdk.ThreadPark`, `jdk.JavaMonitorEnter`,
`jdk.JavaMonitorWait` · I/O `jdk.FileRead`, `jdk.FileWrite`, `jdk.SocketRead`, `jdk.SocketWrite` ·
JIT `jdk.Compilation`, `jdk.Deoptimization`, `jdk.CompilerStatistics`, `jdk.CodeCacheStatistics` ·
tracing `jeffrey.Notification` (the application's own reports; its `fields` carry `traceId`,
`type`, `severity`, `message`), plus the span event types listed by `jfr_listEventTypes`.

`jfr_listEventTypes` gives the ones this profile actually recorded, with counts — use it rather than
assuming.

## GC and JIT: try the `jvm_` family first

`jvm_gc`, `jvm_safepoints` and `jvm_jit` render the dashboards the Jeffrey UI renders, from the
same tested builders, in one call. Everything below is the escape hatch for what they do not shape
— a distribution over time, a correlation with another event type, one field they do not carry.
Reproducing a dashboard here is slower and easier to get wrong.

`jdk.GarbageCollection` is one row per collection, carrying `gcId`, `name` (the collector, `…Full`
for a full GC), `cause`, `sumOfPauses` and `longestPause`.

**Rank by `sumOfPauses`, never by `duration`.** For ZGC, Shenandoah and G1's concurrent cycles the
event's `duration` spans phases the application ran straight through, so ordering by it reports
pauses that never happened. `sumOfPauses` and `longestPause` are the stop-the-world figures; use
`duration` only where they are absent.

```sql
SELECT fields->>'name' AS collector,
       fields->>'cause' AS cause,
       COUNT(*) AS collections,
       SUM(CAST(fields->>'sumOfPauses' AS BIGINT)) / 1000000.0 AS total_pause_ms,
       MAX(CAST(fields->>'longestPause' AS BIGINT)) / 1000000.0 AS worst_pause_ms
FROM events
WHERE event_type = 'jdk.GarbageCollection'
GROUP BY collector, cause
ORDER BY total_pause_ms DESC
```

Heap before and after each collection — `jdk.GCHeapSummary` is **two** rows per `gcId`, told apart
by `when`, so pivot rather than joining the table to itself:

```sql
WITH summaries AS (
  SELECT CAST(fields->>'gcId' AS BIGINT) AS gc_id,
         fields->>'when' AS phase,
         CAST(fields->>'heapUsed' AS BIGINT) AS heap_used
  FROM events WHERE event_type = 'jdk.GCHeapSummary'
)
SELECT gc_id,
       MAX(heap_used) FILTER (WHERE phase = 'Before GC') AS before_bytes,
       MAX(heap_used) FILTER (WHERE phase = 'After GC') AS after_bytes
FROM summaries
GROUP BY gc_id
ORDER BY gc_id
```

A live set that climbs across `after_bytes` is a retention problem — a heap dump answers it, this
recording does not.

Where a pause went inside one collection: `jdk.GCPhasePause` (children in `jdk.GCPhasePauseLevel1`
through `Level4`), grouped by `fields->>'name'` and filtered on `(fields->>'gcId')`.
`jdk.GCPhaseConcurrent` holds the phases outside the pause.

One row each, read before proposing a flag: `jdk.GCConfiguration`, `jdk.GCHeapConfiguration`,
`jdk.GCSurvivorConfiguration`, `jdk.GCTLABConfiguration`. Collector-specific pressure lives in
`jdk.ZAllocationStall` (threads that waited for memory — ZGC's latency symptom, not a pause),
`jdk.EvacuationFailed` and `jdk.TenuringDistribution`.

Nothing here names the code that produced the garbage. Once the pause budget shows GC matters, the
allocation flamegraph does — `flamegraph_export` on `jdk.ObjectAllocationSample` with
`useWeight: true`.

## JIT compilation and deoptimisation

`jvm_jit` already carries all of this aggregated. These are for the follow-up questions — a single
method's compilation history, deoptimisations bucketed by time, a join against another event type.

`jdk.Compilation` is one row per compilation: `method`, `compileLevel`, `compileId`, `codeSize`,
`isOsr`, `succeded` (JFR's own spelling — false means the method fell back to the interpreter).
Method-typed fields are flattened by the parser to `fully.qualified.Type#name`, in `jdk.Compilation`
and `jdk.Deoptimization` alike, so grouping by `fields->>'method'` gives a string that can be
grepped for in a checkout — no struct to unpack.
The event fires only above the recording's threshold setting, so no rows means nothing compiled
*slowly*; `jdk.CompilerStatistics` has the totals regardless (`compileCount`, `bailoutCount`,
`invalidatedCount`, `osrCompileCount`, `totalTimeSpent`).

```sql
SELECT fields->>'method' AS method,
       CAST(fields->>'compileLevel' AS INTEGER) AS level,
       duration / 1000000.0 AS compile_ms,
       CAST(fields->>'codeSize' AS BIGINT) AS code_size,
       fields->>'succeded' AS succeeded
FROM events
WHERE event_type = 'jdk.Compilation'
ORDER BY duration DESC
LIMIT 25
```

Deoptimisation is where the JIT turns into a latency problem. Group by method **and** reason: one
method deoptimised repeatedly ran interpreted for part of the recording, and the reason
(`unstable_if`, `class_check`, `null_check`) says what to look for in the source.

```sql
SELECT fields->>'method' AS method,
       fields->>'reason' AS reason,
       fields->>'action' AS action,
       COUNT(*) AS deopts
FROM events
WHERE event_type = 'jdk.Deoptimization'
GROUP BY method, reason, action
ORDER BY deopts DESC
LIMIT 25
```

`jdk.CodeCacheStatistics` and `jdk.CodeCacheFull` answer whether compilation stopped altogether —
a full code cache leaves the application at interpreted speed for the rest of the run — and
`jdk.CompilerQueueUtilization` shows the queues backing up, which reads as a slow warm-up rather
than a steady-state cost.

## Link what you name

Whenever a reply names something Microscope can open — the profile, a flamegraph, an operation, a
trace, an endpoint, a statement group, a GC, JIT or thread page, a heap class or object, a
differential, a catalogue row, a ledger entry — make the name a Markdown link marked with `↗`:
`[trace 7e34c5994dc96208 ↗](<its uiLink>)`, and in a table a column of `[↗ open](<its uiLink>)`.
Every link carries the mark — every profile named too, the analysed one and its baseline alike, and
a page named in passing ("the exceptions page"), a profile named under **Not assessed** or **Open
leads**, and a figure's source given as the page it was read off (`[slowest traces ↗](<its uiLink>)`) — the
links most often left bare: `[checkout candidate ↗](<its uiLink>)` — and never the bare URL. The `↗` is what makes it a link the
user sees: a terminal that renders links shows the text alone, clickable, with the URL behind it,
and an unmarked name reads as plain words. That holds in every reply, not only in a written report:
a finding, a ranked list, a follow-up offer, a sentence in passing. **Before you send a reply, check
it: every `](` closes a link text that holds `↗`.**

- Take the link from the answer the thing came from. A row with its own `uiLink` — a trace, an
  operation, an exemplar — links with that one; otherwise the answer's `uiLink` is the page that
  shows the row. Quote the `uiLinkNote` beside it when there is one.
- No link in hand for one of the profile's pages (GC, JIT, threads, the heap views, …) →
  `profiles_viewLink`, one cheap call; the profile itself → `profiles_link`.
- Never build, edit or guess a URL, and never fetch one: the link is for the user, and one typed by
  hand opens the wrong page or none.
- An area not yet analysed is not a thing and needs no link, and a result no page shows — a raw SQL
  query — has none; say so rather than inventing one.
- A tool's name is never link text. The link is the page, by its name — `[GC page ↗](<its uiLink>)` — and
  a tool stays in backticks beside it, plain. A tool you did not call — a follow-up on offer, a check under
  **Not assessed** — is named that way and not linked at all: `` `jvm_safepoints` `` alone, never
  `` [`jvm_safepoints`](<a link>) `` and never the profile's own link in place of a page you have no link for.

## Results are capped

`jfr_executeQuery` caps rows (`rowCap`, 1,000) and total characters, and says which in `truncation`:
`COMPLETE`, `ROW_LIMIT` or `OUTPUT_SIZE_LIMIT`. Its answer is JSON — `columns`, then `rows` with every
cell as a string and SQL `NULL` as `null`, and `returned`. `jfr_queryEvents` answers the same shape
with a `status` of `OK`, `NO_EVENTS` or `NO_MATCH`; an event type the profile never recorded is an
error. Aggregate in SQL rather than pulling rows back to count them.

## One statement, and no way out of the database

The `jfr_` family has no write tool: every call reads, and a profile's data is never rewritten
through MCP. Two rules follow from how the read tools are sandboxed, and both fail loudly rather
than silently:

- **One statement per call.** Anything after a semicolon is refused before the query runs. Send the
  `SELECT` on its own.
- **The engine has no filesystem.** The profile database is opened with DuckDB's external file
  access off, so `read_text`, `read_csv`, `read_parquet`, `glob` and `ATTACH` all fail. Nothing is
  lost for analysis — every table you need is in this database — but a query that reaches for one
  comes back with a permission error rather than a confusing empty result.

## Before writing a query against an unfamiliar event type

`jfr_describeTable` can only tell you that `events` has a JSON `fields` column. What is *inside* it
differs per event type, and the names are JFR's rather than Jeffrey's — `sumOfPauses` not `duration`,
`allocationSize` not `size`. **`jfr_describeEventType`** lists them with their labels and types, and
also says whether the type carries a stack trace, which is what decides whether it can be drawn as a
flamegraph at all.

Call it once for an event type you have not queried before. Guessing a JFR field name is how a query
comes back empty against a recording that holds exactly the data you asked for.

Wersje

RepozytoriumWynikLicencja
Ta strona · petrbouda/jeffrey100Apache-2.0
petrbouda/jeffrey100Apache-2.0

Migawka bazy z 13.11.2026 · wersja paczki c0fb89c6f124 (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100heap-sql
    Podaje schemat indeksu zrzutu sterty Jeffrey i pułapki zapytań SQL, które zwracają błędny wynik bez komunikatu o błędzie.
  • 100analyze-jfr
    Analizuje profil JVM z Jeffrey Microscope: procesor, alokacje, blokady, GC i konfigurację, by wyjaśnić, dlaczego aplikacja działa wolno.
  • 90jvm-profiling
    Profiluje usługi JVM przez async-profiler i JFR, rozdzielając dowody dotyczące CPU, alokacji i blokad, bezpiecznie na produkcji.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.