Android Perfetto Series 11: PerfettoSQL, Trace Processor, and Regression Dete...

Word count: 7kReading time: 43 min

 2026/05/04

When handed a trace of UI jank, most people’s first instinct—including mine a few years ago—is to open Perfetto UI, select a time range, inspect the main thread, RenderThread, and CPU states, then take a few screenshots and write a conclusion. That works for the immediate problem, but struggles with the follow-up questions: does another trace support the same conclusion? What about another device? After a fix ships in the next version, how do we verify that the problem has not returned?

Part 11 covers PerfettoSQL and Trace Processor. The aim is straightforward: turn judgments made in the UI into queries that others can check, then use Python to run them across multiple traces. You do not need to become a SQL expert first. Treat a trace as a set of timestamped tables, and translate your visual assessment into a query you can run repeatedly.

The article follows one path: understand Trace Processor, express an assessment as a query over a defined time window, run it from the command line, process multiple traces with Python, and finally turn the results into stable metrics and Trace Summary output. Each step includes SQL you can reuse.

Perfetto Series Catalog

From Screenshot Conclusions to Queries Others Can Check

Part 03 covered basic Perfetto UI operations, Part 04 explained how to open large traces, and Parts 09 and 10 included SQL examples for CPU and Binder analysis. This article does not repeat those topics. It adds one thing: how to move from “this looks like the cause this time” to “we can measure it by the same standard next time.”

You can start here directly, but it helps to have four things ready: a .perfetto-trace file, the target app’s package name, a target time window you can select in the UI, and a working local trace_processor. The SQL below revolves around these four inputs.

The path from manual assessment to an automated report breaks down into these steps:

Form an assessment in Perfetto UI
  -> Express the assessment as a PerfettoSQL query
  -> Execute the query with Trace Processor on the command line
  -> Process multiple traces in batches with Python
  -> Produce stable results with Trace Summary or a custom report

Turning UI assessments into repeatable queries

Diagram: fix the window, target, and quality conditions before using SQL results for batch comparisons.

There are three common engineering uses:

Ad hoc investigation: find a suspicious interval in the UI, then use SQL to extract its time, thread, process, duration, and underlying evidence.
Domain-specific analysis: inspect dozens of traces for the same class of problem, such as scrolling jank, tap response, or slow camera opening, and produce the same diagnostic fields.
Regression detection: run the same queries for every version and put before/after results into reports or dashboards.

The UI remains valuable. SQL preserves the assessments made there so they can be checked and compared in bulk.

What Is Trace Processor?

Trace Processor is Perfetto’s analysis engine. It parses files such as .perfetto-trace, Chrome traces, and simpleperf protobufs into a unified set of SQL tables. Many tracks in Perfetto UI are also obtained by querying these tables and views.

Part 04 used trace_processor_shell --httpd to open large traces. Current official documentation calls the user-facing command trace_processor: the downloaded file is a Python wrapper that retrieves and caches the native Trace Processor executable for your platform on its first run.

First, install the command. These commands download trace_processor and confirm that it can enter the interactive SQL environment.

1
2
3

curl -LO https://get.perfetto.dev/trace_processor
chmod +x ./trace_processor
./trace_processor interactive trace.perfetto-trace

Older documentation and scripts often use ./trace_processor trace.perfetto-trace directly. This default interactive mode still works, but new scripts should use explicit subcommands to make their purpose clear.

For command-line batch processing, focus on query. It loads the trace, executes SQL, and prints the results without opening the UI:

1 2	./trace_processor query trace.perfetto-trace \ "SELECT ts, dur, name FROM slice LIMIT 5;"

For large traces, the UI can still use a local Trace Processor to reduce parsing work in the browser. The current subcommand syntax is:

1	./trace_processor server http trace.perfetto-trace

This command only starts the local HTTP RPC server. Perfetto UI in the browser must still connect to that local Trace Processor at the address and port shown in the output before the local process takes over parsing.

Older entry points such as trace_processor_shell and --httpd retain compatibility support; prefer subcommands in new scripts. Two different ways of performing the same action can leave a team unsure which one is recommended six months later.

Think of a Trace as a Set of Tables

PerfettoSQL is the SQL dialect provided by Trace Processor. You do not need to memorize the entire schema to begin. Start with a few common table types:

Type	Common tables	Purpose
Slices	`slice`	Events with a start time and duration, such as ATrace, Track Event, and some imported or derived slices
Counters	`counter`, `counter_track`	Values that change over time, such as CPU frequency, memory, power, and application counters
Threads and processes	`thread`, `process`	Thread names, process names, tid, pid, utid, and upid
Tracks	`track`, `thread_track`, `process_track`	Ownership of events such as slices and counters; visible UI tracks do not necessarily map one-to-one to these tables
Scheduling states	`thread_state`, `sched`	`thread_state` records thread states; `sched` records the Running intervals when a thread actually occupies a CPU
CPU samples	`perf_sample`, `cpu_profile_stack_sample`	Sample fact tables for CPU profiling
Stack dimension tables	`stack_profile_*`	Stack information such as callsites, frames, and mappings, usually queried together with sample tables
Integrity	`stats`	Collection-quality information, including data loss, parsing errors, and buffer problems

The easiest distinction to miss is tid/pid versus utid/upid. System tids and pids can be reused, and a single trace can contain multiple threads with the same name. Trace Processor uses utid/upid to identify unique thread and process entities within that trace. Prefer utid/upid in scripts rather than relying solely on thread names.

Learn the time units early: ts and dur are generally in nanoseconds. The two conversion directions have different precision. In time.conversion, time_from_ms() performs multiplication and is exact for expressing thresholds; dur > time_from_ms(5) is easier to read than a bare 5e6. By contrast, time_to_ms() performs integer division ($nanos / (1000 * 1000), returning LONG), truncating 1.6 ms to 1. It is therefore suitable only for locating timestamps or coarse bucketing. Duration columns used in reports or before/after comparisons should use ROUND(dur / 1e6, 3), or submillisecond differences will disappear from the report. counter.value has no universal unit: interpret it using counter_track, the relevant module, and the collection configuration. CPU frequency, memory, power rails, and application counters cannot all be compared using the same AVG definition.

Another distinction matters: names such as thread_slice refer to views provided by the slices.with_context standard-library module, not raw schema tables. The module joins slices, tracks, threads, and processes for you, which is why you can query process_name, thread_name, and is_main_thread directly. When copying these queries, do not omit the preceding INCLUDE PERFETTO MODULE ... statement.

Classify SQL failures before changing anything. If INCLUDE PERFETTO MODULE fails or reports a missing module, your local Trace Processor is often too old and its standard library does not yet include that module; run ./trace_processor --version before deciding to upgrade. For no such table or no such column, first suspect a missing INCLUDE or schema evolution, and check column names with PRAGMA table_info(table_name);. A successful query returning zero rows is a third case: the data source may not have been collected, or the window may be wrong. That is not a syntax problem; return to stats and the collection configuration. These three cases need different responses, so do not immediately rewrite the SQL itself.

Scheduling states also depend on the collection configuration. To analyze thread_state, Running, Runnable, and wakeups, the trace needs ftrace scheduling events, particularly sched/sched_switch, sched/sched_wakeup, and sched/sched_waking. Running analysis mainly depends on switch events; explaining the relationship between wakeups and Runnable also requires wakeup/waking events. If these events are missing, the conclusion is “insufficient evidence,” not “no scheduling problem.”

CPU profiling has similar limits. perf_sample and cpu_profile_stack_sample contain sampled observations, not a complete execution record. Sampling frequency, sampling window, unwinding, symbolization, and lost-sample statistics all affect the conclusion. If the sample tables are absent, report “CPU profile not collected,” not “no CPU hotspot.”

Expressing a Complete Analysis in SQL

Consider a common scenario: you notice a clear main-thread stall within a target window in the UI and want to turn that assessment into a query others can check. The window may come from a UI selection, Track Event marker, input event, or the start and end of a frame/CUJ.

Collection Quality Determines the Strength of the Conclusion

Check collection quality before writing any conclusion. stats includes ordinary counters as well as errors and data loss. An automated gate should not treat every nonzero item as failure. First list the items that directly limit the conclusion, then decide per data source whether to recollect, downgrade confidence, or restrict the impact to particular metrics.

The official statistics definitions use types such as kError, kDataLoss, and kInfo, but query values in stats.severity are lowercase strings. This query retains the original name/idx/severity/source/value fields and adds an impact hint. A generic entry point should not classify every error or data_loss item as fatal to the entire trace.

SELECT
  name,
  idx,
  severity,
  source,
  value,
  CASE
    WHEN severity = 'data_loss' AND LOWER(name) LIKE '%ftrace%' THEN 'sched_or_ftrace_degraded'
    WHEN LOWER(name) LIKE '%frame_timeline%' THEN 'frame_timeline_degraded'
    WHEN LOWER(name) LIKE '%track_event%' THEN 'track_event_degraded'
    WHEN severity IN ('error', 'data_loss') THEN 'metric_dependency_check_required'
    ELSE 'configuration_or_parser_context'
  END AS metric_impact_hint
FROM stats
WHERE value != 0
  AND (
    severity IN ('error', 'data_loss')
    OR LOWER(name) LIKE '%loss%'
    OR LOWER(name) LIKE '%drop%'
    OR LOWER(name) LIKE '%parse%'
    OR name IN ('ftrace_setup_errors', 'traced_buf_chunks_overwritten',
                'traced_buf_chunks_discarded', 'traced_flushes_failed',
                'traced_final_flush_failed')
  )
ORDER BY severity, source, name, idx;

If the results include data_loss, parser errors, buffer overruns, or packet loss, qualify subsequent conclusions. ftrace loss directly affects sched/thread_state/wakeup assessments, but does not necessarily invalidate all app Track Events. Missing FrameTimeline data affects frame metrics, but may not affect Binder or memory queries. Preserve the detailed stats in the report, then decide for each metric whether to recollect, exclude, or downgrade.

When optimizing the collection configuration, broaden the filter to value != 0 and inspect the full stats table. Informational items such as buffer size, write volume, and ftrace counters can also explain why data was lost. Part 12 covers the full classification of data loss; here, the aim is to make the check part of the automation entry point.

For frame metrics, also check whether FrameTimeline data exists. Long slices are not a definition of slow frames. FrameTimeline’s actual/expected frames, jank types, and overruns are the data needed to answer frame-level regression questions:

SELECT 'actual' AS table_name, COUNT(*) AS row_count
FROM actual_frame_timeline_slice
UNION ALL
SELECT 'expected' AS table_name, COUNT(*) AS row_count
FROM expected_frame_timeline_slice;

This query always returns two rows. A row_count = 0 does not necessarily mean the trace is broken; the Android version, collection configuration, or scenario may not support that data. Report “frame metrics unavailable” rather than substituting long RenderThread slices for a slow-frame conclusion.

Fix the Target Window First

The target window cannot remain “a range selected in the UI.” A script needs explicit start_ts/end_ts values, and subsequent long-slice, scheduling-state, and frame queries must use the same window. The most reliable source is a Trace.beginSection() or Track Event marker emitted by the app itself:

INCLUDE PERFETTO MODULE slices.with_context;
INCLUDE PERFETTO MODULE time.conversion;

SELECT
  'app_marker' AS window_source,
  name AS window_name,
  ts AS start_ts,
  ts + dur AS end_ts,
  time_to_ms(ts) AS start_ms,
  time_to_ms(ts + dur) AS end_ms,
  ROUND(dur / 1e6, 3) AS duration_ms,
  process_name,
  thread_name
FROM thread_slice
WHERE process_name = 'com.example.app'
  AND name = 'feed_scroll'
  AND dur > 0
ORDER BY ts
LIMIT 10;

If the window comes from a frame, CUJ, input event, or UI selection, first express it using the same fields. For example, select a target frame from FrameTimeline and use ts and ts + dur as the window. If the query returns zero rows, the report should say metric_status=unavailable and degrade_reason=missing_frame_timeline, rather than falling back to long RenderThread slices as frame metrics:

INCLUDE PERFETTO MODULE time.conversion;

SELECT
  'frame_timeline' AS window_source,
  name AS window_name,
  ts AS start_ts,
  ts + dur AS end_ts,
  time_to_ms(ts) AS start_ms,
  time_to_ms(ts + dur) AS end_ms,
  ROUND(dur / 1e6, 3) AS duration_ms
FROM actual_frame_timeline_slice
WHERE dur > 0
ORDER BY dur DESC
LIMIT 10;

To keep the examples short, the queries below still hardcode a window in a target_window CTE. Production scripts should pass window_source/window_name/start_ts/end_ts from metadata or a preceding query and include those fields in the report.

Do Not Find Threads by `main` Alone

Many examples simply use thread.name = 'main'. That looks convenient for one app, but can select unrelated threads in a system trace because every app may have a main thread. First list the threads in the target process to establish their upid/utid identities:

INCLUDE PERFETTO MODULE time.conversion;

WITH candidate_processes AS (
  SELECT
    p.name AS process_name,
    p.upid,
    p.pid,
    COUNT(th.utid) AS thread_count,
    MAX(th.is_main_thread) AS has_main_thread,
    time_to_ms(MIN(th.start_ts)) AS first_thread_start_ms
  FROM process p
  JOIN thread th USING (upid)
  WHERE p.name GLOB 'com.example.app*'
  GROUP BY p.name, p.upid, p.pid
)
SELECT
  cp.process_name,
  cp.upid,
  cp.pid,
  cp.thread_count,
  cp.has_main_thread,
  cp.first_thread_start_ms,
  th.name AS thread_name,
  th.utid,
  th.tid,
  th.is_main_thread
FROM thread th
JOIN candidate_processes cp USING (upid)
ORDER BY cp.process_name, th.is_main_thread DESC, th.name;

A NULL first_thread_start_ms means that the trace did not record the thread’s creation time, commonly because the thread already existed when recording began. Do not treat NULL as zero or infer that the thread does not exist.

Replace com.example.app with your package-name prefix. Multiprocess apps may include :remote, :push, WebView sandboxes, and isolated processes. For UI rendering issues, prioritize the process hosting the Activity, FrameTimeline, or app marker. For a cross-process application path, retain multiple upid values rather than collapsing them into one “total app duration.”

Long Slices Identify Suspects Only

The PerfettoSQL standard library provides prejoined views that avoid many error-prone JOINs. This SQL includes slices.with_context and time.conversion to find thread slices longer than 5 ms within the target window.

The times in target_window are trace timestamps, still in nanoseconds. In practice, replace them with start and end times derived from a UI selection, Track Event, or input event.

INCLUDE PERFETTO MODULE slices.with_context;
INCLUDE PERFETTO MODULE time.conversion;

WITH target_window(start_ts, end_ts) AS (
  VALUES (123000000000, 123500000000)
),
target_slices AS (
  SELECT
    s.ts,
    s.dur,
    s.upid,
    s.utid,
    s.name,
    s.thread_name,
    s.process_name,
    MAX(0, MIN(s.ts + s.dur, w.end_ts) - MAX(s.ts, w.start_ts)) AS overlap_dur
  FROM thread_slice s
  JOIN target_window w
  WHERE s.process_name = 'com.example.app'
    AND (s.is_main_thread = 1 OR s.thread_name = 'RenderThread')
    AND s.dur > 0
    AND s.ts < w.end_ts
    AND s.ts + s.dur > w.start_ts
)
SELECT
  upid,
  utid,
  time_to_ms(ts) AS ts_ms,
  ROUND(overlap_dur / 1e6, 3) AS overlap_ms,
  ROUND(dur / 1e6, 3) AS original_dur_ms,
  name,
  thread_name,
  process_name
FROM target_slices
WHERE overlap_dur > time_from_ms(5)
ORDER BY overlap_ms DESC;

This query corresponds to “find long main-thread tasks” in the UI. It cannot directly establish a root cause or replace slow-frame metrics. It lists suspicious work in the target window: the thread, the slice, its overlap with the window, and the original slice duration.

Runnable Is Not Running

A long slice may contain prolonged execution, or it may span time spent waiting. Inspect scheduling states next. thread_state.state = 'Running' represents intervals actually occupying a CPU. States such as R and R+ mean the thread is runnable but is not executing on a CPU; R+ typically means it remains runnable after preemption.

Before scheduling analysis, check whether thread_state/sched data exists within the same window. If the target thread cannot be matched, or stats contain ftrace loss/drop/error, report insufficient_sched_evidence or sched_evidence_grade=weak:

WITH target_window(start_ts, end_ts) AS (
  VALUES (123000000000, 123500000000)
),
target_threads AS (
  SELECT th.utid
  FROM thread th
  JOIN process p USING (upid)
  WHERE p.name = 'com.example.app'
    AND (th.is_main_thread = 1 OR th.name = 'RenderThread')
)
SELECT 'thread_state_rows' AS check_name, COUNT(*) AS row_count
FROM thread_state ts
JOIN target_threads t USING (utid)
JOIN target_window w
WHERE ts.ts < w.end_ts
  AND ts.ts + ts.dur > w.start_ts
UNION ALL
SELECT 'sched_rows' AS check_name, COUNT(*) AS row_count
FROM sched s
JOIN target_threads t USING (utid)
JOIN target_window w
WHERE s.ts < w.end_ts
  AND s.ts + s.dur > w.start_ts;

The next query remains within the same target window and separates Running, Runnable, S, and D for the main thread and RenderThread:

WITH target_window(start_ts, end_ts) AS (
  VALUES (123000000000, 123500000000)
),
target_threads AS (
  SELECT th.utid, p.name AS process_name, th.name AS thread_name
  FROM thread th
  JOIN process p USING (upid)
  WHERE p.name = 'com.example.app'
    AND (th.is_main_thread = 1 OR th.name = 'RenderThread')
),
state_in_window AS (
  SELECT
    t.utid,
    t.process_name,
    t.thread_name,
    CASE
      WHEN ts.state = 'Running' THEN 'running'
      WHEN ts.state GLOB 'R*' THEN 'runnable'
      WHEN ts.state = 'S' THEN 'interruptible_sleep'
      WHEN ts.state = 'D' THEN 'uninterruptible_blocked'
      ELSE 'other_' || ts.state
    END AS state_bucket,
    MAX(0, MIN(ts.ts + ts.dur, w.end_ts) - MAX(ts.ts, w.start_ts)) AS overlap_dur
  FROM thread_state ts
  JOIN target_threads t USING (utid)
  JOIN target_window w
  WHERE ts.dur > 0
    AND ts.ts < w.end_ts
    AND ts.ts + ts.dur > w.start_ts
)
SELECT
  utid,
  process_name,
  thread_name,
  state_bucket,
  ROUND(SUM(overlap_dur) / 1e6, 3) AS overlap_ms
FROM state_in_window
GROUP BY utid, process_name, thread_name, state_bucket
ORDER BY thread_name, overlap_ms DESC;

S commonly occurs during normal sleep or futex waits; D is closer to an uninterruptible wait. Before attributing a cause, examine the blocked reason, io_wait, and waker fields in thread_state, together with the schema version. In scenarios such as tap response, the first scrolling frame, or the first launch frame, a long Runnable interval means CPU contention or scheduling delay also needs investigation, but cannot establish the cause alone. Thread running time is a thread-level metric. To explain CPU utilization and cluster behavior, separately inspect sched.cpu, CPU idle, CPU frequency, and capacity within the same window.

Move the Query to the Command Line

Save the same SQL as queries/slow_slices.sql and run it with Trace Processor:

1
2
3

./trace_processor query \
  -f queries/slow_slices.sql \
  trace.perfetto-trace

At this point, the analysis has moved from screenshots to a text file. A text file can be reviewed, reused, included in CI, and sent to colleagues for verification.

Batch Analysis with the Python API

You can inspect one trace carefully in the UI; multiple traces call for a script. The script needs to do four things: read the trace, read its matching .json metadata file, run quality gates, and emit stable fields for the same target window. Pay particular attention to TraceProcessor(trace=...), tp.query(...), and the report’s column names.

1	pip install perfetto

A regression platform should not depend on downloading tools on first use. CI should pin the Python perfetto package version and prewarm or cache the native Trace Processor. TraceProcessorConfig(bin_path=...) can point directly to a preinstalled binary, avoiding an on-demand download on the CI machine’s first run. Record trace_processor --version, the TraceConfig id, and the SQL package version in metadata. Otherwise, a tool upgrade can change the parsing assumptions between before and after.

import csv
import json
from pathlib import Path

from perfetto.trace_processor import TraceProcessor

APP_PROCESS = "com.example.app"

QUALITY_DETAIL_QUERY = """
SELECT
  name,
  idx,
  severity,
  source,
  value
FROM stats
WHERE value != 0
  AND (
    severity IN ('error', 'data_loss')
    OR LOWER(name) LIKE '%loss%'
    OR LOWER(name) LIKE '%drop%'
    OR LOWER(name) LIKE '%parse%'
    OR name IN ('ftrace_setup_errors', 'traced_buf_chunks_overwritten',
                'traced_buf_chunks_discarded', 'traced_flushes_failed',
                'traced_final_flush_failed')
  )
ORDER BY severity, source, name, idx;
"""

FRAME_TIMELINE_QUERY = """
SELECT
  (SELECT COUNT(*) FROM actual_frame_timeline_slice) AS actual_count,
  (SELECT COUNT(*) FROM expected_frame_timeline_slice) AS expected_count;
"""

SLOW_SLICE_QUERY_TEMPLATE = """
INCLUDE PERFETTO MODULE slices.with_context;
INCLUDE PERFETTO MODULE time.conversion;

WITH target_window(start_ts, end_ts) AS (
  VALUES ({start_ts}, {end_ts})
),
target_slices AS (
  SELECT
    s.process_name,
    s.upid,
    s.thread_name,
    s.utid,
    s.name AS slice_name,
    MAX(0, MIN(s.ts + s.dur, w.end_ts) - MAX(s.ts, w.start_ts)) AS overlap_dur
  FROM thread_slice s
  JOIN target_window w
  WHERE s.process_name = {process_name}
    AND (s.is_main_thread = 1 OR s.thread_name = 'RenderThread')
    AND s.dur > 0
    AND s.ts < w.end_ts
    AND s.ts + s.dur > w.start_ts
)
SELECT
  process_name,
  upid,
  thread_name,
  utid,
  slice_name,
  COUNT(*) AS slice_count,
  ROUND(SUM(overlap_dur) / 1e6, 3) AS overlap_total_ms,
  ROUND(MAX(overlap_dur) / 1e6, 3) AS max_overlap_ms
FROM target_slices
WHERE overlap_dur > time_from_ms(5)
GROUP BY process_name, upid, thread_name, utid, slice_name
ORDER BY overlap_total_ms DESC
LIMIT 20;
"""

STATE_QUERY_TEMPLATE = """
WITH target_window(start_ts, end_ts) AS (
  VALUES ({start_ts}, {end_ts})
),
target_threads AS (
  SELECT th.utid, p.name AS process_name, th.name AS thread_name
  FROM thread th
  JOIN process p USING (upid)
  WHERE p.name = {process_name}
    AND (th.is_main_thread = 1 OR th.name = 'RenderThread')
),
state_in_window AS (
  SELECT
    t.process_name,
    t.thread_name,
    ts.utid,
    CASE
      WHEN ts.state = 'Running' THEN 'running_ms'
      WHEN ts.state GLOB 'R*' THEN 'runnable_ms'
      WHEN ts.state = 'S' THEN 'sleeping_ms'
      WHEN ts.state = 'D' THEN 'uninterruptible_blocked_ms'
      ELSE 'other_state_ms'
    END AS state_bucket,
    MAX(0, MIN(ts.ts + ts.dur, w.end_ts) - MAX(ts.ts, w.start_ts)) AS overlap_dur
  FROM thread_state ts
  JOIN target_threads t USING (utid)
  JOIN target_window w
  WHERE ts.dur > 0
    AND ts.ts < w.end_ts
    AND ts.ts + ts.dur > w.start_ts
)
SELECT
  process_name,
  thread_name,
  utid,
  state_bucket,
  ROUND(SUM(overlap_dur) / 1e6, 3) AS overlap_ms
FROM state_in_window
GROUP BY process_name, thread_name, utid, state_bucket;
"""

def sql_string(value):
    return "'" + value.replace("'", "''") + "'"

def load_metadata(trace_path):
    metadata_path = trace_path.with_suffix(".json")
    if not metadata_path.exists():
        return {}
    return json.loads(metadata_path.read_text())

def quality_summary(rows, frame_row):
    data_loss_sources = []
    ftrace_loss_count = 0
    for row in rows:
        name = row.name or ""
        severity = row.severity or ""
        if severity == "data_loss":
            data_loss_sources.append(f"{row.source}:{name}[{row.idx}]")
        if "ftrace" in name.lower() and (
            severity in ("error", "data_loss")
            or "loss" in name.lower()
            or "drop" in name.lower()
        ):
            ftrace_loss_count += 1

    frame_available = (frame_row.actual_count or 0) > 0 and (frame_row.expected_count or 0) > 0
    if ftrace_loss_count:
        grade = "weak"
        reason = "ftrace_loss"
    elif data_loss_sources:
        grade = "needs_metric_scope_check"
        reason = "data_loss"
    elif rows:
        grade = "needs_metric_scope_check"
        reason = "stats_findings"  # Includes parser errors, setup notices, and overwrite counters
    elif not frame_available:
        grade = "frame_metrics_unavailable"
        reason = "missing_frame_timeline"
    else:
        grade = "no_selected_stats_findings"
        reason = ""

    return {
        "quality_grade": grade,
        "degrade_reason": reason,
        "data_loss_sources": ";".join(data_loss_sources),
        "ftrace_loss_count": ftrace_loss_count,
        "frame_timeline_available": frame_available,
    }

quality_dir = Path("quality")
quality_dir.mkdir(exist_ok=True)

with open("report.csv", "w", newline="") as fp:
    writer = csv.DictWriter(
        fp,
        fieldnames=[
            "trace_name",
            "group",
            "scenario",
            "window_source",
            "window_name",
            "window_start_ms",
            "window_dur_ms",
            "quality_grade",
            "degrade_reason",
            "data_loss_sources",
            "ftrace_loss_count",
            "frame_timeline_available",
            "sched_coverage",
            "process_name",
            "upid",
            "thread_name",
            "utid",
            "slice_name",
            "slice_count",
            "overlap_total_ms",
            "max_overlap_ms",
            "running_ms",
            "runnable_ms",
            "sleeping_ms",
            "uninterruptible_blocked_ms",
        ],
    )
    writer.writeheader()

    for trace_path in Path("traces").glob("*.perfetto-trace"):
        metadata = load_metadata(trace_path)
        process_name = sql_string(metadata.get("process_name", APP_PROCESS))
        start_ts = int(metadata["window_start_ts"])
        end_ts = int(metadata["window_end_ts"])
        if end_ts <= start_ts:
            raise ValueError(f"Invalid target window: {trace_path}")

        slow_slice_query = SLOW_SLICE_QUERY_TEMPLATE.format(
            start_ts=start_ts,
            end_ts=end_ts,
            process_name=process_name,
        )
        state_query = STATE_QUERY_TEMPLATE.format(
            start_ts=start_ts,
            end_ts=end_ts,
            process_name=process_name,
        )

        with TraceProcessor(trace=str(trace_path)) as tp:
            quality_rows = list(tp.query(QUALITY_DETAIL_QUERY))
            frame_row = next(iter(tp.query(FRAME_TIMELINE_QUERY)))
            slow_rows = list(tp.query(slow_slice_query))
            state_rows = list(tp.query(state_query))

        (quality_dir / f"{trace_path.stem}.stats.json").write_text(json.dumps(
            [{key: getattr(row, key) for key in ("name", "idx", "severity", "source", "value")}
             for row in quality_rows], ensure_ascii=False, indent=2), encoding="utf-8")
        quality = quality_summary(quality_rows, frame_row)
        state_by_thread = {}
        for row in state_rows:
            state_by_thread.setdefault((row.utid, row.thread_name), {})[row.state_bucket] = row.overlap_ms or 0

        if not slow_rows:
            slow_rows = [None]

        for row in slow_rows:
            states = state_by_thread.get((row.utid, row.thread_name), {}) if row else {}
            # Treat absent states as 0 only when this thread's state intervals cover the full window.
            # With missing or partial coverage, retain observed values and leave missing states blank.
            covered_ms = sum(states.values())
            full_coverage = bool(states) and abs(covered_ms - (end_ts - start_ts) / 1e6) <= 0.01
            sched_coverage = "full" if full_coverage else ("partial" if states else "missing")
            def state_value(key):
                value = states.get(key, 0 if full_coverage else None)
                return round(value, 3) if value is not None else ""

            writer.writerow({
                "trace_name": trace_path.name,
                "group": metadata.get("group", "unknown"),
                "scenario": metadata.get("scenario", "unknown"),
                "window_source": metadata.get("window_source", "metadata"),
                "window_name": metadata.get("window_name", ""),
                "window_start_ms": round(start_ts / 1_000_000, 3),
                "window_dur_ms": round((end_ts - start_ts) / 1_000_000, 3),
                **quality,
                "sched_coverage": sched_coverage,
                "process_name": row.process_name if row else metadata.get("process_name", APP_PROCESS),
                "upid": row.upid if row else "",
                "thread_name": row.thread_name if row else "",
                "utid": row.utid if row else "",
                "slice_name": row.slice_name if row else "",
                "slice_count": row.slice_count if row else 0,
                "overlap_total_ms": round(row.overlap_total_ms, 3) if row else 0,
                "max_overlap_ms": round(row.max_overlap_ms, 3) if row else 0,
                "running_ms": state_value("running_ms"),
                "runnable_ms": state_value("runnable_ms"),
                "sleeping_ms": state_value("sleeping_ms"),
                "uninterruptible_blocked_ms": state_value("uninterruptible_blocked_ms"),
            })

metadata.json must provide window_start_ts/window_end_ts as integer nanoseconds. The script stops if they are missing; it must not guess the window. frame_timeline_available only means that frame records exist somewhere in the trace, and no_selected_stats_findings only means the selected statistics raised no alarms. Neither proves that the target window is complete. sched_coverage is also only a check of state-interval coverage and must still be considered alongside ftrace loss. When there are no slow slices, the summary row has no thread identity, so its scheduling columns remain blank.

This script is still a starting point, but already has three properties needed for long-lived scripts: the same windowed SQL, the same thresholds, and the same output structure. quality/*.stats.json preserves the queried stats details, while the CSV expresses reasons for downgrading through quality_grade/degrade_reason/data_loss_sources. SLOW_SLICE_QUERY_TEMPLATE only considers the main thread and RenderThread in the target window, avoiding the mistake of treating long background-thread tasks elsewhere in the trace as evidence of UI jank.

As the number of traces grows, replace the handwritten loop with the official BatchTraceProcessor. It reuses the same PerfettoSQL and can return results separately for each trace, or combine them into a table with source information through query_and_flatten():

1	pip install perfetto pandas

import glob
from perfetto.batch_trace_processor.api import BatchTraceProcessor

QUERY = """
INCLUDE PERFETTO MODULE slices.with_context;
INCLUDE PERFETTO MODULE time.conversion;

WITH target_window(start_ts, end_ts) AS (
  VALUES (123000000000, 123500000000)
),
target_slices AS (
  SELECT
    process_name,
    upid,
    thread_name,
    utid,
    name AS slice_name,
    MAX(0, MIN(ts + dur, w.end_ts) - MAX(ts, w.start_ts)) AS overlap_dur
  FROM thread_slice
  JOIN target_window w
  WHERE process_name = 'com.example.app'
    AND (is_main_thread = 1 OR thread_name = 'RenderThread')
    AND ts < w.end_ts
    AND ts + dur > w.start_ts
)
SELECT
  process_name,
  upid,
  thread_name,
  utid,
  slice_name,
  COUNT(*) AS slice_count,
  ROUND(SUM(overlap_dur) / 1e6, 3) AS overlap_total_ms
FROM target_slices
WHERE overlap_dur > time_from_ms(5)
GROUP BY process_name, upid, thread_name, utid, slice_name
ORDER BY overlap_total_ms DESC
LIMIT 20;
"""

files = glob.glob("traces/*.perfetto-trace")

with BatchTraceProcessor(files) as btp:
    result = btp.query_and_flatten(QUERY)
    print(result)

This kind of batch processing suits lab regression testing and large collections of field traces. Memory and concurrency must be controlled: before loading hundreds of large traces at once, try a small sample, check memory consumption and query duration, then scale up.

From Ad Hoc Queries to Stable Metrics

Ad hoc SQL is for exploration; stable metrics are for long-term comparison. Before integrating a query into an engineering workflow, define at least four sets of constraints:

Output contract: fix column names, units, and sort order. dur > time_from_ms(5) is better suited to long-lived scripts than a bare 5e6. A name such as overlap_total_ms tells the reader that the value has already been intersected with the target window.
Identity: use utid/upid for multiprocess scenarios. Thread names are readable labels, not unique identifiers; main, RenderThread, and Binder:* can all repeat.
Target window: taps, launches, the first scrolling frame, and camera opening cannot be assessed solely through whole-trace totals. Include window_source/window_name/window_start_ms/window_dur_ms in the report.
Data quality: every trace needs detailed stats and downgrade fields. quality_grade/degrade_reason/missing_sources/fallback_used serves a regression gate better than a single total.

For complex JOINs, look for standard-library views first. thread_slice, process_slice, and thread_or_process_slice reduce basic mistakes. Prefer Trace Summary for long-term projects: the official direction is v2 summary for structured output, rather than starting a new solution around the older v1 metrics.

A useful test is whether the query result can go directly into a version comparison. If someone still has to explain that “this column was called A last time and B this time,” it is not yet a stable metric.

Another easily missed requirement is how to interpret a metric when something goes wrong. “Cumulative duration of main-thread slices over 5 ms” only describes long tasks among the slices; it cannot independently prove jank. “Total Runnable time” only indicates that the thread spent time waiting for CPU and does not replace CPU frequency, load, or scheduler evidence. If FrameTimeline is missing, the report should say metric_status=unavailable, fallback_used=false, and degrade_reason=missing_frame_timeline. If ftrace loss is detected, scheduling metrics should carry sched_evidence_grade=weak.

Nested slices overlap in time. SUM(overlap_dur) is a cumulative slice quantity, not nonoverlapping wall-clock time or CPU time. Nor can parent and child slices simply be added together to calculate thread utilization.

CPU frequency, CPU idle, thermal conditions, and cluster/CPU placement within the same target window fit better as explanatory variables in the report. They usually do not establish a cause on their own, but can explain why the same application slice is slower in one trace.

Regression Questions Require Multiple Traces

A single trace helps locate a problem in one captured occurrence. Multiple traces are needed to answer whether an optimization improves performance consistently. Many performance reports suffer less from the traces themselves than from how the evidence is organized: lots of screenshots and strong conclusions, but no fixed configuration, sample count, data-loss check, or consistent before/after statistics.

For multiple-trace analysis, first fix the input contract. The minimum set is trace_name/scenario/group/device/build/config_id; duration_ms/thermal_state/trace_processor_version/sql_package_version helps rule out environmental and tool drift. Each batch should include at least these fields:

Field	Example	Purpose
`trace_name`	`after-run03.perfetto-trace`	Locate the original file
`scenario`	`feed_scroll_120hz`	Distinguish scenarios
`group`	`before` / `after`	Compare versions
`device`	`Pixel_8`	Aggregate by device model
`build`	`UP1A.xxx`	Identify the system version
`config_id`	`ui_jank_v4`	Confirm consistent TraceConfig
`duration_ms`	`30000`	Assess the collection window
`thermal_state`	`nominal`	Rule out thermal interference
`trace_processor_version`	`v49.0`	Rule out parser or standard-library changes
`sql_package_version`	`perf-v3`	Confirm consistent query logic

These fields can come from filenames, metadata, or a test platform, or be stored in the case package’s metadata.json. Without them, the numbers are difficult to interpret.

The minimum output of a batch script can be a CSV suitable for pasting into an issue:

trace_name,group,scenario,window_name,quality_grade,degrade_reason,ftrace_loss_count,frame_timeline_available,thread_name,slice_name,slice_count,overlap_total_ms,runnable_ms,conclusion_level,next_action
before-run01.perfetto-trace,before,feed_scroll_120hz,feed_scroll,strong,,0,true,main,inflate,4,38.41,2.10,info,compare_after
before-run02.perfetto-trace,before,feed_scroll_120hz,feed_scroll,strong,,0,true,RenderThread,DrawFrame,3,51.02,4.33,info,compare_after
after-run01.perfetto-trace,after,feed_scroll_120hz,feed_scroll,weak,ftrace_loss,1,true,main,inflate,5,71.82,18.40,needs_retrace,recollect_trace
after-run02.perfetto-trace,after,feed_scroll_120hz,feed_scroll,strong,,0,true,RenderThread,DrawFrame,2,25.77,1.92,improved,keep_monitoring

Do not reduce quality problems to one total. ftrace loss directly affects sched/thread_state/wakeup conclusions, but does not necessarily invalidate all app Track Events. Missing FrameTimeline data affects frame metrics, but may not affect Binder or memory analysis. Retain at least name/source/idx or aggregate by data source, then let regression gates decide whether to recollect, exclude, or downgrade according to each metric’s dependencies.

Do not judge regressions solely by averages, either. Keep the median, p95, worst value, and raw value from each run:

Metric	Direction	Suggested assessment
FrameTimeline overrun / jank rate	Lower is better	Inspect `p95_frame_overrun_ms`, `worst_frame_overrun_ms`, `jank_count`, `jank_rate`, and the `jank_type` distribution; use `valid_frame_count` to state the sample size
launch duration	Lower is better	Define its start and end separately; do not mix it with frame duration
jank count / jank rate	Lower is better	Retain total frame count, valid frame count, window duration, and proportion
slow binder count	Lower is better	Examine both the count and whether events cluster in the same phase
CPU Running time	Depends	Interpret alongside scenario actions and frame windows
RSS/PSS/unfreed native allocations	Usually lower is better	Inspect peaks, growth slope, and whether values fall again
battery current / power rail	Depends	Meaningful only for A/B comparisons on the same device under the same conditions

Five before/after runs can reveal obvious regressions, but cannot establish a 1% or 2% change. State sample counts and environmental conditions explicitly. Downgrade conclusions when samples are insufficient, thermal states differ, or quality gates are not clean.

Use Trace Summary for Long-Term Metrics

Direct SELECT queries are flexible, but their output schema changes with the query. Platforms, dashboards, and CI need a stable structure. Trace Summary is designed to place metrics into a unified TraceSummary protobuf.

The current command-line entry point is the summarize subcommand. If you downloaded the trace_processor wrapper earlier, keep using that entry point.

The following minimal spec.textproto demonstrates Trace Summary using a memory metric. Memory is chosen simply to keep the example short. Once the SQL for long slices, frames, Binder, or other metrics stabilizes, those can be migrated in the same way.

metric_spec {
  id: "memory_per_process"
  dimensions: "process_name"
  value: "avg_rss_and_swap"
  unit: BYTES
  polarity: LOWER_IS_BETTER
  query {
    table {
      table_name: "memory_rss_and_swap_per_process"
    }
    referenced_modules: "linux.memory.process"
    group_by {
      column_names: "process_name"
      aggregates {
        column_name: "rss_and_swap"
        op: DURATION_WEIGHTED_MEAN
        result_column_name: "avg_rss_and_swap"
      }
    }
  }
}

Run this summary spec and request the memory_per_process metric:

1
2
3

./trace_processor summarize \
  --metrics-v2 memory_per_process \
  trace.perfetto-trace spec.textproto

The source-tree command trace_processor_shell summarize expresses the same operation. The older --summary --summary-spec ... --summary-metrics-v2 ... form remains supported, but new scripts should prefer subcommands so they are easier to follow alongside the official documentation.

Trace Summary suits long-term metrics: memory, launch, frames, Binder, CPU, and power data tracked across versions. For ad hoc troubleshooting, direct SQL remains useful. When migrating frame metrics, output app FrameTimeline metrics separately from SurfaceFlinger/display metrics; do not treat long RenderThread slices as frame duration.

A summary spec must at least define the metric id, dimensions, and value. An individual metric_spec can declare unit and polarity directly—for example, BYTES for memory with LOWER_IS_BETTER. For templates, value_column_specs can declare units and directions separately for multiple value columns. Consumers of the protobuf then know how to sort, flag, and compare values.

Another capability useful for platform integration is metadata_query_id. If the spec also defines a metadata query returning key / value, the command line can use --metadata-query, and the Python API can pass metadata_query_id. This carries fields such as device, build, scenario, and config_id alongside the summary, so later aggregation does not have to guess from filenames.

Start with Python + CSV if that fits the project. Once fields stabilize, consumers are established, and metrics need long-term maintenance, migrate the key metrics to Trace Summary.

Common Pitfalls

Treat time columns such as ts/dur as nanoseconds by default. Use report names such as dur_ms/overlap_ms, and time_from_ms() for thresholds to avoid unit mistakes. For durations, use ROUND(x / 1e6, 3), not time_to_ms(), which truncates to whole milliseconds. Check track and module definitions separately for counter/value fields.
A slice with dur = -1 is an event that remained open when the trace ended. Handle it before intersecting windows or calculating SUM(dur); the examples in this article filter with dur > 0. If the target event itself is unclosed, explain it separately rather than counting it as an ordinary long slice.
main and RenderThread are not unique. Filter process_name/upid first, then thread_name/utid. Include upid/utid in reports to avoid collisions between thread names.
pid/tid can be reused. Prefer upid/utid in scripts, retaining tid/pid as auxiliary fields when correlating with system logs.
A visible UI track does not necessarily correspond to a single raw table. State the data source in the report—for example, thread_slice, thread_state, or actual_frame_timeline_slice—to avoid confusing the presentation layer with the schema.
R and R+ mean Runnable; only Running means actual execution. Separate running_ms/runnable_ms/sleeping_ms/uninterruptible_blocked_ms in reports instead of merging them into “thread busy.”
If stats reports data loss, avoid strong conclusions. Keep name/source/idx and degrade_reason, and assess the impact according to metric dependencies.
Schemas evolve. When a batch script fails, first use PRAGMA table_info(table_name); to confirm that columns exist. Retain trace_processor_version/sql_package_version in the report.
Do not compare metrics when before and after use different TraceConfigs. Output config_id and recollect first if they differ.
Keeping only the report, without the original trace, configuration, SQL, and metadata, prevents later reproduction. At minimum, retain the trace filename, configuration id, SQL version, and window source.

Conclusion

Do not treat screenshots as conclusions. Screenshots help you spot problems; SQL, metadata, quality gates, and repeatable output turn the assessment into regression evidence.

For the next trace you receive, follow this order: check stats, fix the target window and upid/utid, then write long slices, Running/Runnable, frame metrics, and downgrade reasons into CSV or Trace Summary. SQL makes an assessment checkable, but cannot replace collection quality or scenario definition. With the wrong window, lost trace data, or changed configuration, even elegant queries produce untrustworthy numbers.

I have automated this “stats health check → fixed window → SQL evidence → repeatable output” workflow in my open-source project SmartPerfetto. PerfettoSQL queries like those in this article have become more than two hundred scenario-specific SQL skills and evidence rules connected to an AI agent runtime. Given a trace, it automatically checks its health, selects a scenario strategy, and produces conclusions with evidence grades. Start there to experience the complete workflow; see SmartPerfetto Six-Week Update for an overview of the current version’s capabilities.