perfetto-sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Guidelines and Hints

指南与提示

Idempotency: Ensure queries are idempotent to prevent "already exists" errors during multiple executions.

For Perfetto objects, always use

CREATE OR REPLACE

CREATE OR REPLACE PERFETTO TABLE

CREATE OR REPLACE PERFETTO VIEW

CREATE OR REPLACE PERFETTO FUNCTION

CREATE OR REPLACE PERFETTO MACRO

For SQLite Virtual Tables (such as

SPAN_JOIN

CREATE OR REPLACE

is not supported. Explicitly drop them first:

DROP TABLE IF EXISTS my_table; CREATE VIRTUAL TABLE my_table USING SPAN_JOIN(...);

For standard SQLite indexes, prepend
```
DROP INDEX IF EXISTS index_name;
```
.

```
SPAN_JOIN
```
will crash if intervals within the same input table overlap. Always use the
```
PARTITIONED {column}
```
(for example,
```
PARTITIONED upid
```
) clause to isolate intervals.
Intermediate tables fed into a
```
SPAN_JOIN
```
must be materialized using
```
CREATE PERFETTO TABLE
```
, not
```
CREATE VIEW
```
.
Trace Boundaries (
dur = -1
): Slices or thread states that don't finish before the trace ends are recorded with
```
dur = -1
```
. When calculating a bounding box (for example,
```
ts + dur
```
) or summing durations (
```
SUM(dur)
```
), handle incomplete durations using:
```
IIF(dur = -1, trace_end() - ts, dur)
```
.
Robust State Transitions: Avoid manual timestamp arithmetic (for example,
```
ts + dur = next.ts
```
) to join adjacent events. Rely on standard library modules (for example,
```
sched.runnable
```
,
```
linux.perf.counters
```
,
```
intervals.overlap
```
) which safely handle trace gaps and preemptions.
Unique Identifiers: When writing SQL queries in Perfetto, you must join tables using
```
utid
```
(unique thread ID) or
```
upid
```
(unique process ID) instead of the regular
```
tid
```
or
```
pid
```
. Why it's useful : The operating system recycles
```
TIDs
```
and
```
PIDs
```
, while
```
UTIDs
```
and
```
UPIDs
```
remain unique for the lifetime of the trace, which prevents incorrect joins.
Safe Argument Extraction: Use
```
EXTRACT_ARG(arg_set_id, 'key')
```
to extract dictionary or JSON-like properties from slices or tracks. Don't attempt string parsing.
String Matching (Always use GLOB): Use
```
GLOB
```
instead of
```
LIKE
```
.
```
LIKE
```
causes performance bottlenecks and treats underscores (
```
_
```
) as wildcards, leading to bugs.
- Exact matches: Use
```
=
```
  .
- Substring matches: Use
```
GLOB
```
  with
```
*
```
  (for example,
```
name GLOB '*RenderThread*'
```
  ).
- Case-insensitive matches: Use
```
LOWER(name) GLOB
```
  and make sure the search string is fully lowercase (for example,
```
LOWER(name) GLOB '*renderthread*'
```
  ). Use this when dealing with inconsistent trace capitalization (for example,
```
WakeLock
```
  versus
```
wakelock
```
  ).
Calculating Time Overlaps: To calculate the overlap duration between two time intervals
```
[start1, end1]
```
and
```
[start2, end2]
```
:
Precedence Rule: Always prefer using
```
SPAN_JOIN
```
or standard library functions (for example,
```
intervals.overlap
```
) to calculate overlaps between two different sets of intervals . Avoid manual arithmetic if a standard library feature or
```
SPAN_JOIN
```
can achieve the same result. Use the following logic if no built-in alternative exists.
1. Condition: The intervals overlap if
```
start1 < end2
```
  and
```
start2 < end1
```
  .
2. Duration: The overlap duration is calculated as
```
MIN(end1, end2) - MAX(start1, start2)
```
  Important: Incomplete Perfetto slices have a duration of -1 (
  dur = -1
  ). Always calculate the effective end time using
  ts + IIF(dur = -1, trace_end() - ts, dur)
  before applying this logic.
Query
```
android_thread_slices_for_all_startups
```
for app startup requests.
Join
```
counter_track
```
with
```
counter
```
to get values of counter with a specific name.
When querying for a CPU frequency counter, include the
```
linux.cpu.frequency
```
module and use the
```
cpu_frequency_counters
```
table.
When looking for events around a specific timestamp, start with 100ms as the window size.
Always prefix column names with table or view alias, that is:
```
{alias}.{column_name}
```
.
To calculate the total time spent in slices matching a specific name pattern (for example,
```
*{name_pattern}*
```
), you must sum their durations. Why it's useful : This helps quantify the total impact of a specific function or feature on performance across multiple calls. Here is an example query (note the safe handling of incomplete slices):
```
sql SELECT count(*) as total_count, sum(IIF(slice.dur = -1, trace_end() - slice.ts, slice.dur)) / 1000000.0 as total_dur_ms FROM slice WHERE slice.name GLOB '*{name_pattern}*';
```

幂等性：确保查询具备幂等性，避免多次执行时出现"已存在"错误。

对于Perfetto对象，始终使用

CREATE OR REPLACE

：

CREATE OR REPLACE PERFETTO TABLE

CREATE OR REPLACE PERFETTO VIEW

CREATE OR REPLACE PERFETTO FUNCTION

CREATE OR REPLACE PERFETTO MACRO

对于SQLite虚拟表（如

SPAN_JOIN

），不支持

CREATE OR REPLACE

。需先显式删除：

DROP TABLE IF EXISTS my_table; CREATE VIRTUAL TABLE my_table USING SPAN_JOIN(...);

对于标准SQLite索引，前置
```
DROP INDEX IF EXISTS index_name;
```
.

```
SPAN_JOIN
```
会在同一输入表内的时间区间重叠时崩溃。务必使用
```
PARTITIONED {column}
```
子句（例如
```
PARTITIONED upid
```
）来隔离区间。
传入
```
SPAN_JOIN
```
的中间表必须使用
```
CREATE PERFETTO TABLE
```
实现物化，而非
```
CREATE VIEW
```
。
跟踪边界（
dur = -1
）：在跟踪结束前未完成的切片或线程状态会被记录为
```
dur = -1
```
。计算边界框（例如
```
ts + dur
```
）或求和时长（
```
SUM(dur)
```
）时，需使用以下方式处理未完成的时长：
```
IIF(dur = -1, trace_end() - ts, dur)
```
.
可靠状态转换：避免手动进行时间戳运算（例如
```
ts + dur = next.ts
```
）来关联相邻事件。依赖标准库模块（如
```
sched.runnable
```
,
```
linux.perf.counters
```
,
```
intervals.overlap
```
），这些模块可安全处理跟踪间隙和抢占情况。
唯一标识符：在Perfetto中编写SQL查询时，必须使用
```
utid
```
（唯一线程ID）或
```
upid
```
（唯一进程ID）来关联表，而非常规的
```
tid
```
或
```
pid
```
。作用：操作系统会回收
```
TIDs
```
和
```
PIDs
```
，而
```
UTIDs
```
和
```
UPIDs
```
在整个跟踪周期内保持唯一，可避免错误关联。
安全参数提取：使用
```
EXTRACT_ARG(arg_set_id, 'key')
```
从切片或轨迹中提取字典或类JSON属性。不要尝试字符串解析。
字符串匹配（始终使用GLOB）：使用
```
GLOB
```
而非
```
LIKE
```
。
```
LIKE
```
会导致性能瓶颈，且会将下划线（
```
_
```
）视为通配符，引发bug。
- 精确匹配：使用
```
=
```
  .
- 子串匹配：结合
```
*
```
  使用
```
GLOB
```
  （例如
```
name GLOB '*RenderThread*'
```
  ）.
- 不区分大小写匹配：使用
```
LOWER(name) GLOB
```
  ，并确保搜索字符串全为小写（例如
```
LOWER(name) GLOB '*renderthread*'
```
  ）。当跟踪数据大小写不一致时（如
```
WakeLock
```
  与
```
wakelock
```
  ），可使用此方法。
计算时间重叠：计算两个时间区间
```
[start1, end1]
```
和
```
[start2, end2]
```
的重叠时长：
优先级规则：计算两组不同区间之间的重叠时，始终优先使用
```
SPAN_JOIN
```
或标准库函数（如
```
intervals.overlap
```
）。若标准库功能或
```
SPAN_JOIN
```
可实现相同结果，避免手动运算。若无内置替代方案，可使用以下逻辑：
1. 条件：当
```
start1 < end2
```
  且
```
start2 < end1
```
  时，区间存在重叠。
2. 时长：重叠时长计算公式为
```
MIN(end1, end2) - MAX(start1, start2)
```
  重要提示：未完成的Perfetto切片时长为-1（
  dur = -1
  ）。应用此逻辑前，务必使用
  ts + IIF(dur = -1, trace_end() - ts, dur)
  计算有效结束时间。
查询
```
android_thread_slices_for_all_startups
```
获取应用启动请求数据。
关联
```
counter_track
```
与
```
counter
```
表，获取特定名称计数器的值。
查询CPU频率计数器时，需包含
```
linux.cpu.frequency
```
模块，并使用
```
cpu_frequency_counters
```
表。
查找特定时间戳附近的事件时，初始窗口大小设为100ms。
始终为列名添加表或视图别名前缀，即：
```
{alias}.{column_name}
```
.
计算匹配特定名称模式（例如
```
*{name_pattern}*
```
）的切片总耗时，需对其时长求和。作用：这有助于量化特定函数或功能在多次调用中对性能的总体影响。以下是示例查询（注意对未完成切片的安全处理）：
```
sql SELECT count(*) as total_count, sum(IIF(slice.dur = -1, trace_end() - slice.ts, slice.dur)) / 1000000.0 as total_dur_ms FROM slice WHERE slice.name GLOB '*{name_pattern}*';
```

Resources

资源

Documentation: The Perfetto Standard Library documentation is in
```
perfetto-stdlib.md
```
. Use this file as a reference to discover available modules, find schemas (columns and types) for specific tables or views, or determine the
```
INCLUDE PERFETTO MODULE
```
statements required before drafting SQL query.
Execution Tool: Queries are executed using the official
```
trace_processor
```
wrapper script downloaded directly from Perfetto. Output is returned in pure CSV format.

文档：Perfetto标准库文档位于
```
perfetto-stdlib.md
```
。可参考此文件了解可用模块、查找特定表或视图的Schema（列和类型），或确定编写SQL查询前所需的
```
INCLUDE PERFETTO MODULE
```
语句。
执行工具：查询通过直接从Perfetto下载的官方
```
trace_processor
```
包装脚本执行。输出为纯CSV格式。

Execution Protocol

执行流程

You must follow these steps sequentially, mirroring a multi-agent pipeline:

你必须按顺序遵循以下步骤，模拟多Agent流水线：

Step 0: Tool Setup

步骤0：工具设置

Fetch the Wrapper: You must use the top level of the current project workspace (

./trace_processor

CRITICAL GUARDRAIL: NEVER use filesystem search tools (
find
,
find_by_name
,
grep
,
dir /s
,
Get-ChildItem
) across the home directory or workspace to locate
trace_processor
— unconstrained searches across entire workspaces will stop responding or time out.

Perform a direct file check at the top level of your workspace (e.g.,

ls trace_processor

). If missing, download

https://get.perfetto.dev/trace_processor

directly into the root workspace (

curl -LO

), make it executable on macOS/Linux (

chmod +x

), and ensure

trace_processor

is added to

.gitignore

. Execute queries directly via

./trace_processor

(on Windows, explicitly invoke

python trace_processor

Important: The file served at this URL is a
~10KB
Python wrapper script. Don't assume the download failed because it is human-readable text. This is the intended behavior. This script handles lazy-loading the precompiled binary automatically on its first run. Use it directly.

获取包装脚本：必须使用当前项目工作区的顶层目录（

./trace_processor

）。

关键约束：绝不要使用文件系统搜索工具（
find
,
find_by_name
,
grep
,
dir /s
,
Get-ChildItem
）在主目录或整个工作区中查找
trace_processor
——无限制的全工作区搜索会导致响应停止或超时。

直接检查工作区顶层目录的文件（例如

ls trace_processor

）。若缺失，直接将

https://get.perfetto.dev/trace_processor

下载到工作区根目录（使用

curl -LO

），在macOS/Linux上设置可执行权限（

chmod +x

），并确保

trace_processor

被添加到

.gitignore

中。通过

./trace_processor

直接执行查询（在Windows上，显式调用

python trace_processor

）。

重要提示：该URL提供的文件是一个约10KB的Python包装脚本。不要因它是人类可读的文本就认为下载失败，这是预期行为。该脚本会在首次运行时自动延迟加载预编译二进制文件。直接使用即可。

Step 1: Dissection and Schema Research

步骤1：拆解与Schema研究

Identify the core question, required data points, and filtering conditions.
Precedence Rule: If the user's request contains a SQL query, use it without modification and skip to Step 2 for validation.
Mandatory Schema and Module Search: For every table or view you plan to use, you MUST find its schema in
```
perfetto-stdlib.md
```
. Don't read the entire documentation file --- it consumes the context window. Follow this precise workflow:
- Discovery and Search: Use available search tools (
```
grep
```
  ,
```
read_file
```
  or file search) with line limits to discover relevant views, tables or modules based on your problem domain and high-level intents (for example, 'CPU time', 'running time', 'overlap', 'jank').
  - Why: Searching solely for exact table names misses comprehensive, pre-computed views built for these analyses.
  - Note: You must verify if a Standard Library module already provides the needed abstraction before drafting manual arithmetic or custom functions.
- Targeted Bounded Reads: Once you identify the relevant modules, efficiently read the tables and views within that module section.
- Extract: Extract only the schema, columns, and the exact
```
INCLUDE PERFETTO MODULE
```
  statements for the required object from the documentation.
- Verify: Review the columns, types, and descriptions to ensure the table matches your needs.
Print the research results before drafting the query:
Tables/Views:
```
Schema for {name}:
```
listing columns and types.

确定核心问题、所需数据点和过滤条件。
优先级规则：若用户请求中包含SQL查询，请勿修改直接使用，并跳至步骤2进行验证。
强制Schema与模块搜索：对于计划使用的每个表或视图，必须在
```
perfetto-stdlib.md
```
中找到其Schema。不要通读整个文档文件——这会占用上下文窗口。请遵循以下精确流程：
- 发现与搜索：使用可用的搜索工具（
```
grep
```
  ,
```
read_file
```
  或文件搜索）并设置行数限制，根据问题领域和高层意图（例如'CPU时间', '运行时间', '重叠', '卡顿'）查找相关视图、表或模块。
  - 原因：仅搜索确切表名会错过为这些分析构建的全面预计算视图。
  - 注意：编写手动运算或自定义函数前，必须验证标准库模块是否已提供所需的抽象。
- 定向有限读取：确定相关模块后，高效读取该模块部分内的表和视图。
- 提取：仅从文档中提取所需对象的Schema、列和确切的
```
INCLUDE PERFETTO MODULE
```
  语句。
- 验证：检查列、类型和描述，确保表符合需求。
编写查询前打印研究结果：
表/视图：
```
{name}的Schema：
```
列出列和类型。

Step 2: Draft and Validate Loop (Max 3 Iterations)

步骤2：编写与验证循环（最多3次迭代）

Draft the SQL query in SQLite syntax using only the schemas retrieved in Step 1. After drafting, you must validate against this checklist:

[ ] SQLite Syntax: Does the query parse successfully without syntax errors?
[ ] Idempotency: Are all object creations safe to re-run? (Did you use
```
CREATE OR REPLACE PERFETTO
```
and
```
DROP TABLE IF EXISTS
```
for virtual tables?)
[ ] Existence: Were all tables found in the documentation?
[ ] Intent Check: Is there a pre-existing standard library table or view that will fulfill this intent before instead of writing manual arithmetic?
[ ] Column Accuracy: Do columns match the retrieved schemas?
[ ] Alias Check: Are ALL column names prefixed with their table or view alias (for example,
```
alias.column_name
```
)?
[ ] Module Check: Are
```
INCLUDE PERFETTO MODULE
```
statements included for all non-prelude modules? You must use the exact module names provided in the documentation.
[ ] Span Join Check: If using
```
SPAN_JOIN
```
, are tables safely
```
PARTITIONED
```
to prevent overlapping interval crashes? Are intermediate tables materialized with
```
CREATE PERFETTO TABLE
```
?
[ ] No LIKE Constraint: Did you map string matches using
```
GLOB
```
or
```
=
```
instead of prohibited
```
LIKE
```
?
[ ] Execution Check: You MUST run queries using the standalone
```
./trace_processor
```
wrapper with the
```
--query-string
```
flag:
```
./trace_processor --query-string "QUERY" {trace_file}
```
.
Execution Rules:
- File Usage : If you must create a SQL file to execute queries (for example, due to query length or escaping issues), you must create them in the
```
/tmp/
```
  directory.
- State: The execution is purely ephemeral. Database state does not persist across turns. You cannot share state (like views or tables) across queries in different turns. Every query must be standalone and fully self-contained.
- Failure Resilience: Debug and fix SQL syntax and logic errors when query fails.Don't simplify the analytical intent to pass validation. For example, if requested to calculate an overlap or intersection, you must fix the intersection math. Don't substitute with disjoint queries (for example, returning independent total durations) as a workaround.

使用仅在步骤1中获取的Schema，以SQLite语法编写SQL查询。编写完成后，必须对照以下清单进行验证：

[ ] SQLite语法：查询能否成功解析，无语法错误？
[ ] 幂等性：所有对象创建操作是否可安全重复执行？（是否对Perfetto对象使用了
```
CREATE OR REPLACE PERFETTO
```
，对虚拟表使用了
```
DROP TABLE IF EXISTS
```
？）
[ ] 存在性：所有表是否都在文档中找到？
[ ] 意图检查：是否存在预先定义的标准库表或视图可满足该意图，无需编写手动运算？
[ ] 列准确性：列是否与获取的Schema匹配？
[ ] 别名检查：是否所有列名都添加了表或视图别名前缀（例如
```
alias.column_name
```
）？
[ ] 模块检查：是否为所有非预加载模块添加了
```
INCLUDE PERFETTO MODULE
```
语句？必须使用文档中提供的确切模块名称。
[ ] SPAN_JOIN检查：若使用
```
SPAN_JOIN
```
，是否已通过
```
PARTITIONED
```
安全处理表以避免重叠区间崩溃？中间表是否使用
```
CREATE PERFETTO TABLE
```
实现物化？
[ ] 无LIKE约束：是否使用
```
GLOB
```
或
```
=
```
进行字符串匹配，而非禁用的
```
LIKE
```
？
[ ] 执行检查：必须使用独立的
```
./trace_processor
```
包装脚本，通过
```
--query-string
```
标志执行查询：
```
./trace_processor --query-string "QUERY" {trace_file}
```
.
执行规则：
- 文件使用：若因查询长度或转义问题必须创建SQL文件执行，必须将其创建在
```
/tmp/
```
  目录下。
- 状态：执行是纯临时的。数据库状态不会在多轮会话中保留。无法在不同轮次的查询之间共享状态（如视图或表）。每个查询必须独立且完全自包含。
- 故障恢复：查询失败时，调试并修复SQL语法和逻辑错误。不要为通过验证而简化分析意图。例如，若要求计算重叠或交集，必须修复交集运算逻辑。不要用替代方案（如返回独立的总时长）作为变通方法。

Step 3: Final Output

步骤3：最终输出

Explicitly return and state the final validated SQL and explain the results to the user.
Before finishing your response, delete all temporary SQL files you created in
```
/tmp/
```
directory.

明确返回并说明最终验证后的SQL，并向用户解释结果。
结束响应前，删除所有在
```
/tmp/
```
目录下创建的临时SQL文件。