PerfettoSQL standard library

This page documents the PerfettoSQL standard library.

Introduction

The PerfettoSQL standard library is a repository of tables, views, functions and macros, contributed by domain experts, which make querying traces easier Its design is heavily inspired by standard libraries in languages like Python, C++ and Java.

Some of the purposes of the standard library include:

1. Acting as a way of sharing and commonly written queries without needing to copy/paste large amounts of SQL.
2. Raising the abstraction level when exposing data in the trace. Many modules in the standard library convert low-level trace concepts e.g. slices, tracks and into concepts developers may be more familar with e.g. for Android developers: app startups, binder transactions etc.

Standard library modules can be included as follows:

More information on importing modules is available in the syntax documentation for the INCLUDE PERFETTO MODULE statement.

Summary

Views/tables

Functions

Table Functions

Module: common

common.args

Functions

formatted_arg Returns the formatted value of a given argument. Similar to EXTRACT_ARG, but instead of returning the raw value, it returns the value formatted according to the 'value_type' column (e.g. for booleans, EXTRACT_ARG will return 0 or 1, while FORMATTED_ARG will return 'true' or 'false').

Returns: STRING, Formatted value of the argument.

common.counters

Functions

earliest_timestamp_for_counter_track Timestamp of first counter value in a counter.

Returns: LONG, Timestamp of first counter value. Null if doesn't exist.

common.cpus

Functions

guess_cpu_size Guess size of CPU. On some multicore devices the cores are heterogeneous and divided into two or more 'sizes'. In a typical case a device might have 8 cores of which 4 are 'little' (low power & low performance) and 4 are 'big' (high power & high performance). This functions attempts to map a given CPU index onto the relevant descriptor. For homogeneous systems this returns NULL.

Returns: STRING, A descriptive size ('little', 'mid', 'big', etc) or NULL if we have insufficient information.

common.metadata

Functions

extract_int_metadata Extracts an int value with the given name from the metadata table.

Returns: LONG, int_value for the given name. NULL if there's no such entry.

common.percentiles

Table Functions

counter_percentiles_for_time_range All percentiles (range 1-100) for counter track ID in a given time range. Percentiles are calculated by: 1. Dividing the sum of duration in time range for each value in the counter by duration of the counter in range. This gives us percentile_for)value (DOUBLE). 2. Fetching each percentile by taking floor of each percentile_for_value, grouping by resulting percentile and MIN from value for each grouping. As we are rounding down, taking MIN assures most reliable data. 3. Filling the possible gaps in percentiles by getting the minimal value from higher percentiles for each gap.

counter_percentiles_for_track All percentiles (range 1-100) for counter track ID.

common.slices

Views/Tables

thread_slice, VIEW

All thread slices with data about thread, thread track and process. Where possible, use available view functions which filter this view.

process_slice, VIEW

All process slices with data about process track and process. Where possible, use available view functions which filter this view.

Functions

has_parent_slice_with_name Checks if slice has an ancestor with provided name.

Returns: BOOL, Whether parent_name is a name of an ancestor slice.

slice_count Count slices with specified name.

Returns: INT, Number of slices with the name.

slice_name_from_id Given a slice id, returns the name of the slice.

Returns: STRING, the name of slice with the given id.

common.timestamps

Functions

is_spans_overlapping Checks whether two spans are overlapping.

Returns: BOOL, Whether two spans are overlapping.

spans_overlapping_dur Return the overlapping duration between two spans. If either duration is less than 0 or there's no intersection, 0 is returned

Returns: INT, Overlapping duration

ns Converts a duration in seconds to nanoseconds, which is the default representation of time durations in trace processor. Provided for consisensy with other functions.

Returns: INT, Time duration in nanoseconds.

us Converts a duration in microseconds to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

ms Converts a duration in millseconds to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

seconds Converts a duration in seconds to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

minutes Converts a duration in minutes to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

hours Converts a duration in hours to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

days Converts a duration in days to nanoseconds, which is the default representation of time durations in trace processor.

Returns: INT, Time duration in nanoseconds.

Module: android

android.anrs

Views/Tables

android_anrs, VIEW

List of all ANRs that occurred in the trace (one row per ANR).

android.battery

Views/Tables

android_battery_charge, VIEW

Battery charge at timestamp.

android.battery_stats

Views/Tables

android_battery_stats_state, VIEW

View of human readable battery stats counter-based states. These are recorded by BatteryStats as a bitmap where each 'category' has a unique value at any given time.

android_battery_stats_event_slices, VIEW

View of slices derived from battery_stats events. Battery stats records all events as instants, however some may indicate whether something started or stopped with a '+' or '-' prefix. Events such as jobs, top apps, foreground apps or long wakes include these details and allow drawing slices between instant events found in a trace. For example, we may see an event like the following on 'battery_stats.top': -top=10215:"com.google.android.apps.nexuslauncher" This view will find the associated start ('+top') with the matching suffix (everything after the '=') to construct a slice. It computes the timestamp and duration from the events and extract the details as follows: track_name='battery_stats.top' str_value='com.google.android.apps.nexuslauncher' int_value=10215

Functions

android_battery_stats_counter_to_string Converts a battery_stats counter value to human readable string.

Returns: STRING, The human-readable name for the counter value.

android.binder

Views/Tables

android_binder_metrics_by_process, VIEW

Count Binder transactions per process.

android_sync_binder_metrics_by_txn, VIEW

Breakdown synchronous binder transactions per txn. It returns data about the client and server ends of every binder transaction.

android_sync_binder_thread_state_by_txn, VIEW

Aggregated thread_states on the client and server side per binder txn This builds on the data from |android_sync_binder_metrics_by_txn| and for each end (client and server) of the transaction, it returns the aggregated sum of all the thread state durations. The |thread_state_type| column represents whether a given 'aggregated thread_state' row is on the client or server side. 'binder_txn' is client side and 'binder_reply' is server side.

android_sync_binder_blocked_functions_by_txn, VIEW

Aggregated blocked_functions on the client and server side per binder txn This builds on the data from |android_sync_binder_metrics_by_txn| and for each end (client and server) of the transaction, it returns the aggregated sum of all the kernel blocked function durations. The |thread_state_type| column represents whether a given 'aggregated blocked_function' row is on the client or server side. 'binder_txn' is client side and 'binder_reply' is server side.

android_async_binder_metrics_by_txn, VIEW

Breakdown asynchronous binder transactions per txn. It returns data about the client and server ends of every binder transaction async.

android_binder_txns, VIEW

Breakdown asynchronous binder transactions per txn. It returns data about the client and server ends of every binder transaction async.

Table Functions

android_binder_outgoing_graph Returns a DAG of all outgoing binder txns from a process. The roots of the graph are the threads making the txns and the graph flows from: thread -> server_process -> AIDL interface -> AIDL method. The weights of each node represent the wall execution time in the server_process.

android_binder_incoming_graph Returns a DAG of all incoming binder txns from a process. The roots of the graph are the clients making the txns and the graph flows from: client_process -> AIDL interface -> AIDL method. The weights of each node represent the wall execution time in the server_process.

android_binder_graph Returns a graph of all binder txns in a trace. The nodes are client_process and server_process. The weights of each node represent the wall execution time in the server_process.

android.io

Views/Tables

android_io_f2fs_counter_stats, VIEW

Aggregates f2fs IO and latency stats by counter name.

android_io_f2fs_write_stats, VIEW

Aggregates f2fs_write stats by inode and thread.

android_io_f2fs_aggregate_write_stats, VIEW

Aggregates f2fs write stats. Counts distinct datapoints, total write operations, and bytes written

android.monitor_contention

Views/Tables

android_monitor_contention, TABLE

Contains parsed monitor contention slices.

android_monitor_contention_chain, TABLE

Contains parsed monitor contention slices with the parent-child relationships.

android_monitor_contention_chain_thread_state, TABLE

Note that we only span join the duration where the lock was actually held and contended. This can be less than the duration the lock was 'waited on' when a different waiter acquired the lock earlier than the first waiter.

android_monitor_contention_chain_thread_state_by_txn, VIEW

Aggregated thread_states on the 'blocking thread', the thread holding the lock. This builds on the data from |android_monitor_contention_chain| and for each contention slice, it returns the aggregated sum of all the thread states on the blocking thread. Note that this data is only available for the first waiter on a lock.

android_monitor_contention_chain_blocked_functions_by_txn, VIEW

Aggregated blocked_functions on the 'blocking thread', the thread holding the lock. This builds on the data from |android_monitor_contention_chain| and for each contention, it returns the aggregated sum of all the kernel blocked function durations on the blocking thread. Note that this data is only available for the first waiter on a lock.

Functions

android_extract_android_monitor_contention_blocking_thread Extracts the blocking thread from a slice name

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_blocking_tid Extracts the blocking thread tid from a slice name

Returns: INT, Blocking thread tid

android_extract_android_monitor_contention_blocking_method Extracts the blocking method from a slice name

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_short_blocking_method Extracts a shortened form of the blocking method name from a slice name. The shortened form discards the parameter and return types.

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_blocked_method Extracts the monitor contention blocked method from a slice name

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_short_blocked_method Extracts a shortened form of the monitor contention blocked method name from a slice name. The shortened form discards the parameter and return types.

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_waiter_count Extracts the number of waiters on the monitor from a slice name

Returns: INT, Count of waiters on the lock

android_extract_android_monitor_contention_blocking_src Extracts the monitor contention blocking source location from a slice name

Returns: STRING, Blocking thread

android_extract_android_monitor_contention_blocked_src Extracts the monitor contention blocked source location from a slice name

Returns: STRING, Blocking thread

Table Functions

android_monitor_contention_graph Returns a DAG of all Java lock contentions in a process. Each node in the graph is a <thread:Java method> pair. Each edge connects from a node waiting on a lock to a node holding a lock. The weights of each node represent the cumulative wall time the node blocked other nodes connected to it.

android.network_packets

Views/Tables

android_network_packets, VIEW

Android network packet events (from android.network_packets data source).

android.process_metadata

Views/Tables

android_process_metadata, TABLE

Data about packages running on the process.

android.slices

Functions

android_standardize_slice_name Some slice names have params in them. This functions removes them to make it possible to aggregate by name. Some examples are: - Lock/monitor contention slices. The name includes where the lock contention is in the code. That part is removed. - DrawFrames/ooFrame. The name also includes the frame number. - Apk/oat/dex loading: The name of the apk is removed

Returns: STRING, Simplified name.

android.startup.startups

Views/Tables

android_startups, TABLE

All activity startups in the trace by startup id. Populated by different scripts depending on the platform version/contents.

android_startup_processes, TABLE

Maps a startup to the set of processes that handled the activity start. The vast majority of cases should be a single process. However it is possible that the process dies during the activity startup and is respawned.

android_startup_threads, VIEW

Maps a startup to the set of threads on processes that handled the activity start.

android_thread_slices_for_all_startups, VIEW

All the slices for all startups in trace. Generally, this view should not be used. Instead, use one of the view functions related to the startup slices which are created from this table.

Functions

android_sum_dur_for_startup_and_slice Returns duration of startup for slice name. Sums duration of all slices of startup with provided name.

Returns: INT, Sum of duration.

android_sum_dur_on_main_thread_for_startup_and_slice Returns duration of startup for slice name on main thread. Sums duration of all slices of startup with provided name only on main thread.

Returns: INT, Sum of duration.

android.statsd

Views/Tables

android_statsd_atoms, VIEW

Statsd atoms. A subset of the slice table containing statsd atom instant events.

Module: chrome

chrome.chrome_scrolls

Views/Tables

chrome_scrolls, TABLE

Defines slices for all of the individual scrolls in a trace based on the LatencyInfo-based scroll definition.

chrome_scrolling_intervals, VIEW

Defines slices for all of scrolls intervals in a trace based on the scroll definition in chrome_scrolls. Note that scrolls may overlap (particularly in cases of jank/broken traces, etc); so scrolling intervals are not exactly the same as individual scrolls.

chrome.cpu_powerups

Views/Tables

chrome_cpu_power_slice, VIEW

The CPU power transitions in the trace.

chrome_cpu_power_first_sched_slice_after_powerup, TABLE

The Linux scheduler slices that executed immediately after a CPU power up.

chrome_cpu_power_post_powerup_slice, TABLE

A table holding the slices that executed within the scheduler slice that ran on a CPU immediately after power-up.

chrome_cpu_power_first_toplevel_slice_after_powerup, VIEW

The first top-level slice that ran after a CPU power-up.

chrome.histograms

Views/Tables

chrome_histograms, VIEW

A helper view on top of the histogram events emitted by Chrome. Requires "disabled-by-default-histogram_samples" Chrome category.

chrome.scroll_jank.scroll_jank_intervals

Views/Tables

chrome_janky_event_latencies_v3, TABLE

Selects EventLatency slices that correspond with janks in a scroll. This is based on the V3 version of scroll jank metrics.

chrome_janky_frame_presentation_intervals, VIEW

Frame presentation interval is the delta between when the frame was supposed to be presented and when it was actually presented.

chrome_scroll_stats, VIEW

Scroll jank frame presentation stats for individual scrolls.

chrome_scroll_jank_intervals_v3, TABLE

Defines slices for all of janky scrolling intervals in a trace.

chrome.scroll_jank.scroll_jank_v3

Views/Tables

chrome_gesture_scroll_updates, TABLE

Grabs all gesture updates with respective scroll ids and start/end timestamps, regardless of being coalesced.

chrome_presented_gesture_scrolls, TABLE

Scroll updates, corresponding to all input events that were converted to a presented scroll update.

chrome_scroll_updates_with_deltas, TABLE

Associate every trace_id with it's perceived delta_y on the screen after prediction.

chrome_gesture_scroll_event_latencies, TABLE

Extract event latency timestamps, to later use it for joining with gesture scroll updates, as event latencies don't have trace ids associated with it.

chrome_full_frame_view, TABLE

Join presented gesture scrolls with their respective event latencies based on |LatchToSwapEnd| timestamp, as it's the end timestamp for both the gesture scroll update slice and the LatchToSwapEnd slice.

chrome_full_frame_delta_view, TABLE

Join deltas with EventLatency data.

chrome_merged_frame_view, VIEW

Group all gestures presented at the same timestamp together in a single row.

chrome_frame_info_with_delay, VIEW

View contains all chrome presented frames during gesture updates while calculating delay since last presented which usually should equal to |VSYNC_INTERVAL| if no jank is present.

chrome_vsyncs, VIEW

Calculate |VSYNC_INTERVAL| as the lowest delay between frames larger than zero. TODO(b/286222128): Emit this data from Chrome instead of calculating it.

chrome_janky_frames_no_cause, VIEW

Filter the frame view only to frames that had missed vsyncs.

chrome_janky_frames_no_subcause, VIEW

Janky frame information including the jank cause.

chrome_janky_frames, VIEW

Finds all causes of jank for all janky frames, and a cause of sub jank if the cause of jank was GPU related.

chrome_unique_frame_presentation_ts, VIEW

Counting all unique frame presentation timestamps.

chrome_janky_frames_percentage, VIEW

Dividing missed frames over total frames to get janky frame percentage. This represents the v3 scroll jank metrics. Reflects Event.Jank.DelayedFramesPercentage UMA metric.

chrome_frames_per_scroll, VIEW

Number of frames and janky frames per scroll.

chrome_causes_per_scroll, VIEW

Scroll jank causes per scroll.

chrome.speedometer

Views/Tables

chrome_speedometer_measure, VIEW

Augmented slices for Speedometer measurements. These are the intervals of time Speedometer uses to compute the final score. There are two intervals that are measured for every test: sync and async sync is the time between the start and sync-end marks, async is the time between the sync-end and async-end marks.

chrome_speedometer_iteration, VIEW

Slice that covers one Speedometer iteration. This slice is actually estimated as a default Speedometer run will not emit marks to cover this interval. The metrics associated are the same ones Speedometer would output, but note we use ns precision (Speedometer uses ~100us) so the actual values might differ a bit. Also note Speedometer returns the values in ms these here and in ns.

chrome.tasks

Views/Tables

chrome_java_views, VIEW

A list of slices corresponding to operations on interesting (non-generic) Chrome Java views. The view is considered interested if it's not a system (ContentFrameLayout) or generic library (CompositorViewHolder) views.

chrome_scheduler_tasks, VIEW

A list of tasks executed by Chrome scheduler.

chrome_tasks, VIEW

A list of "Chrome tasks": top-level execution units (e.g. scheduler tasks / IPCs / system callbacks) run by Chrome. For a given thread, the slices corresponding to these tasks will not intersect.

chrome.vsync_intervals

Views/Tables

chrome_vsync_intervals, TABLE

A simple table that checks the time between VSync (this can be used to determine if we're refreshing at 90 FPS or 60 FPS). Note: In traces without the "Java" category there will be no VSync TraceEvents and this table will be empty.

Module: experimental

experimental.android_broadcast

Views/Tables

experimental_android_broadcasts_minsdk_u, VIEW

Provides a list of broadcast names and processes they were sent to by the system_server process on U+ devices.

experimental.flat_slices

Views/Tables

experimental_slice_flattened, TABLE

The concept of a "flat slice" is to take the data in the slice table and remove all notion of nesting; we do this by projecting every slice in a stack to their ancestor slice, i.e at any point in time, taking the most specific active slice (i.e. the slice at the bottom of the stack) and representing that as the only slice that was running during that period. This concept becomes very useful when you try and linearise a trace and compare it with other traces spanning the same user action; "self time" (i.e. time spent in a slice but not any children) is easily computed and span joins with thread state become possible without limiting to only depth zero slices. Note that, no slices will be generated for intervals without without any slices. As an example, consider the following slice stack: A-------------B. ----C----D----. The flattened slice will be: A----C----D----B.

experimental.proto_path

Functions

experimental_proto_path_to_stack Creates a Stack following the parent relations in EXPERIMENTAL_PROTO_PATH table starting at the given path_id.

Returns: BYTES, Stack

experimental.slices

Views/Tables

experimental_slice_with_thread_and_process_info, VIEW

All slices with related process and thread info if available. Unlike thread_slice and process_slice, this view contains all slices, with thread- and process-related columns set to NULL if the slice is not associated with a thread or a process.

experimental.thread_executing_span

Views/Tables

experimental_thread_executing_span_graph, TABLE

thread_executing_span graph of all wakeups across all processes.

Functions

experimental_thread_executing_span_id_from_thread_state_id Gets the thread_executing_span id a thread_state belongs to. Returns NULL if thread state is sleeping and not blocked on an interrupt.

Returns: INT, thread_executing_span id

experimental_thread_executing_span_following_thread_state_id Gets the next thread_executing_span id after a sleeping state. Returns NULL if there is no thread_executing_span after the |thread_state_id|.

Returns: INT, thread_executing_span id

Module: pkvm

pkvm.hypervisor

Views/Tables

pkvm_hypervisor_events, VIEW

Events when CPU entered hypervisor.