1# PerfettoSQL Common Queries 2 3This page acts as a reference guide for queries which often appear when 4performing ad-hoc analysis. 5 6## Computing CPU time for slices 7If collecting traces which including scheduling information (i.e. from ftrace) 8as well as userspace slices (i.e. from atrace), the actual time spent running 9on a CPU for each userspace slice can be computed: this is commonly known as 10the "CPU time" for a slice. 11 12Firstly, setup the views to simplify subsequent queries: 13``` 14DROP VIEW IF EXISTS slice_with_utid; 15CREATE VIEW slice_with_utid AS 16SELECT 17 ts, 18 dur, 19 slice.name as slice_name, 20 slice.id as slice_id, utid, 21 thread.name as thread_name 22FROM slice 23JOIN thread_track ON thread_track.id = slice.track_id 24JOIN thread USING (utid); 25 26DROP TABLE IF EXISTS slice_thread_state_breakdown; 27CREATE VIRTUAL TABLE slice_thread_state_breakdown 28USING SPAN_LEFT_JOIN( 29 slice_with_utid PARTITIONED utid, 30 thread_state PARTITIONED utid 31); 32``` 33 34Then, to compute the CPU time for all slices in the trace: 35``` 36SELECT slice_id, slice_name, SUM(dur) AS cpu_time 37FROM slice_thread_state_breakdown 38WHERE state = 'Running' 39GROUP BY slice_id; 40``` 41 42You can also compute CPU time for a specific slice: 43``` 44SELECT slice_name, SUM(dur) AS cpu_time 45FROM slice_thread_state_breakdown 46WHERE slice_id = <your slice id> AND state = 'Running'; 47``` 48 49These queries can be varied easily to compute other similar metrics. 50For example to get the time spent "runnable" and in "uninterruptible sleep": 51``` 52SELECT 53 slice_id, 54 slice_name, 55 SUM(IIF(state = 'R', dur, 0)) AS runnable_time, 56 SUM(IIF(state = 'D', dur, 0)) AS uninterruptible_time 57FROM slice_thread_state_breakdown 58GROUP BY slice_id; 59``` 60 61## Computing scheduling time by woken threads 62A given thread might cause other threads to wake up i.e. because work was 63scheduled on them. For a given thread, the amount of time threads it 64woke up ran for can be a good proxy to understand how much work is being 65spawned. 66 67To compute this, the following query can be used: 68``` 69SELECT 70 SUM(( 71 SELECT dur FROM sched 72 WHERE 73 sched.ts > wakee_runnable.ts AND 74 wakee_runnable.utid = wakee_runnable.utid 75 ORDER BY ts 76 LIMIT 1 77 )) AS scheduled_dur 78FROM thread AS waker 79JOIN thread_state AS wakee_runnable ON waker.utid = wakee_runnable.waker_utid 80WHERE waker.name = <your waker thread name here> 81``` 82 83To do this for all the threads in the trace simultaenously: 84``` 85SELECT 86 waker_process.name AS process_name, 87 waker.name AS thread_name, 88 SUM(( 89 SELECT dur FROM sched 90 WHERE 91 sched.ts > wakee_runnable.ts AND 92 sched.utid = wakee_runnable.utid 93 ORDER BY ts 94 LIMIT 1 95 )) AS scheduled_dur 96FROM thread AS waker 97JOIN process AS waker_process USING (upid) 98JOIN thread_state AS wakee_runnable ON waker.utid = wakee_runnable.waker_utid 99WHERE waker.utid != 0 100GROUP BY 1, 2 101ORDER BY 3 desc 102``` 103