Common queries

This page acts as a reference guide for queries which often appear when performing ad-hoc analysis.

Computing CPU time for slices

If collecting traces which including scheduling information (i.e. from ftrace) as well as userspace slices (i.e. from atrace), the actual time spent running on a CPU for each userspace slice can be computed: this is commonly known as the “CPU time” for a slice.

Firstly, setup the views to simplify subsequent queries:

DROP VIEW IF EXISTS slice_with_utid;
CREATE VIEW slice_with_utid AS
SELECT
  ts,
  dur,
  slice.name as slice_name,
  slice.id as slice_id, utid,
  thread.name as thread_name
FROM slice
JOIN thread_track ON thread_track.id = slice.track_id
JOIN thread USING (utid);

DROP TABLE IF EXISTS slice_thread_state_breakdown;
CREATE VIRTUAL TABLE slice_thread_state_breakdown
USING SPAN_LEFT_JOIN(
  slice_with_utid PARTITIONED utid,
  thread_state PARTITIONED utid
);

Then, to compute the CPU time for all slices in the trace:

SELECT slice_id, slice_name, SUM(dur) AS cpu_time
FROM slice_thread_state_breakdown
WHERE state = 'Running'
GROUP BY slice_id;

You can also compute CPU time for a specific slice:

SELECT slice_name, SUM(dur) AS cpu_time
FROM slice_thread_state_breakdown
WHERE slice_id = <your slice id> AND state = 'Running';

These queries can be varied easily to compute other similar metrics. For example to get the time spent “runnable” and in “uninterruptible sleep”:

SELECT
  slice_id,
  slice_name,
  SUM(CASE state = 'R' THEN dur ELSE 0 END) AS runnable_time,
  SUM(CASE state = 'D' THEN dur ELSE 0 END) AS uninterruptible_time
FROM slice_thread_state_breakdown
GROUP BY slice_id;