Analyzing SQL Traces

Analyzing SQL Traces

A specialized skill for analyzing Perfetto browser traces (individually or comparatively) to detect performance bottlenecks and generate codebase refactoring or instrumentation recommendations.

1. Prerequisites & Context

• Treatment Traces (one or more .pb files).
• Control Traces (optional, one or more .pb files for comparison).
• Target Slice or Metric Window (e.g., Startup.FirstWebContents.FirstContentfulPaint, OmniboxEditModel::OpenMatch).
• Analysis Mode Input: Select either descendants (to analyze child slices of a specific target) or window (to analyze all slices overlapping a metric window).

⚠️ Safety & Sandbox Compliance (Zero-Grant Rule)

To prevent triggering unnecessary user permission/access grant prompts:

• ALWAYS write all intermediate and final outputs to the parent E2E session's unified analysis directory inside the workspace: out/e2e_nla_run_{parent_session_id}/analysis/ (where {parent_session_id} is passed by the Orchestrator).
  • Raw data / comparison reports: out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_data.txt (Mode A, Text flamegraph) out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_report.md (Mode A, Markdown report) out/e2e_nla_run_{parent_session_id}/analysis/comparison_report.md (Mode B, Markdown report) out/e2e_nla_run_{parent_session_id}/analysis/comparison_flamegraph.txt (Mode B, Text flamegraph)
  • Structured JSON: out/e2e_nla_run_{parent_session_id}/analysis/trace_analysis_results.json
  • Markdown Dispatch Report: out/e2e_nla_run_{parent_session_id}/analysis/trace_analysis_dispatch_report.md
• NEVER execute shell utilities like mkdir, ls, touch, or rm to manage these files.
• ALWAYS rely on the internal Python APIs inside trace_analyzer.py or trace_comparator.py to programmatically create directories and manage files silently.
• ALWAYS run the scripts with vpython3 agents/skills/analyzing-sql-traces/scripts/trace_analyzer.py or vpython3 agents/skills/analyzing-sql-traces/scripts/trace_comparator.py to avoid extra permmision grant prompts.

2. Core Workflow

Step 1: Determine the Analysis Mode & Run Extraction

Mode A: Single-Group Analysis (Only Treatment Traces Provided)

First, run the trace analyzer to produce an aggregated text flamegraph:

vpython3 agents/skills/analyzing-sql-traces/scripts/trace_analyzer.py \
  --traces {path/to/treatment_trace_*.pb} \
  --target "{focus_slice_or_metric}" \
  --mode {descendants|window} \
  --format text \
  --output out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_data.txt

Second, run the trace analyzer to produce a markdown report with cumulative redundancy analysis:

vpython3 agents/skills/analyzing-sql-traces/scripts/trace_analyzer.py \
  --traces {path/to/treatment_trace_*.pb} \
  --target "{focus_slice_or_metric}" \
  --mode {descendants|window} \
  --format markdown \
  --output out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_report.md

Read the generated out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_data.txt and out/e2e_nla_run_{parent_session_id}/analysis/raw_trace_report.md using view_file.

Mode B: Comparative Analysis (Both Control and Treatment Traces Provided)

First, run the trace comparator to generate the tabular comparative report:

vpython3 agents/skills/analyzing-sql-traces/scripts/trace_comparator.py \
  --control {path/to/control_trace_*.pb} \
  --experiment {path/to/treatment_trace_*.pb} \
  --target "{focus_slice_or_metric}" \
  --mode {descendants|window} \
  --format markdown \
  --output out/e2e_nla_run_{parent_session_id}/analysis/comparison_report.md

Second, run the trace comparator to generate the high-level comparative text flamegraph (use --min-dur to filter out minor slices, e.g., $\ge 5.0\text{ ms}$):

vpython3 agents/skills/analyzing-sql-traces/scripts/trace_comparator.py \
  --control {path/to/control_trace_*.pb} \
  --experiment {path/to/treatment_trace_*.pb} \
  --target "{focus_slice_or_metric}" \
  --mode {descendants|window} \
  --format text \
  --min-dur 5.0 \
  --output out/e2e_nla_run_{parent_session_id}/analysis/comparison_flamegraph.txt

Read the generated out/e2e_nla_run_{parent_session_id}/analysis/comparison_report.md and out/e2e_nla_run_{parent_session_id}/analysis/comparison_flamegraph.txt using view_file.

Advanced Filtering & Aggregation Options (Optional)

Both scripts (trace_analyzer.py and trace_comparator.py) support optional flags to refine slice selection when multiple events share the same name:

• Aggregation Mode (--aggregate): If the target slice can be called multiple times, use this flag to aggregate all occurrences (cumulative durations and self-times) into a single merged call tree.
• Slice Argument Filtering (--arg-key <key> and --arg-value <value>): To analyze only a specific call out of multiple occurrences, filter by its arguments (e.g. --arg-key "task.posted_from.file_name" --arg-value "content/browser/browser_main_loop.cc"). Note: The --arg-value parameter supports SQL LIKE operator syntax (e.g. %google.com/search% to perform prefix or wildcard substring matches).
• Parent Bounding Target (--boundary-target <name>): Restricts the target slice search to only those occurrences that fall chronologically within the execution time windows of a specified parent/boundary event (descendants mode only). Use with --boundary-arg-key <key> and --boundary-arg-value <value> to target specific parent navigation/workflow windows.

Step 2: Apply Cognitive Principles

Open and read the mandatory reasoning guide to evaluate the results, focusing on browser logic and filtering out infrastructure noise: file:///.agents/skills/analyzing-sql-traces/references/cognitive_principles.md

Step 3: Run Arbitrary SQL Queries (Follow-up Analysis)

If you need custom details or want to perform follow-up analysis not covered by the default trace analyzer/comparator (e.g. searching for specific args, getting stats on specific threads, custom joins), ALWAYS run the arbitrary query script query_trace.py rather than creating a custom script yourself.