1-- 2-- Copyright 2024 The Android Open Source Project 3-- 4-- Licensed under the Apache License, Version 2.0 (the "License"); 5-- you may not use this file except in compliance with the License. 6-- You may obtain a copy of the License at 7-- 8-- https://www.apache.org/licenses/LICENSE-2.0 9-- 10-- Unless required by applicable law or agreed to in writing, software 11-- distributed under the License is distributed on an "AS IS" BASIS, 12-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13-- See the License for the specific language governing permissions and 14-- limitations under the License. 15 16INCLUDE PERFETTO MODULE intervals.intersect; 17 18-- The time a thread spent in each scheduling state during it's lifetime. 19CREATE PERFETTO TABLE sched_time_in_state_for_thread( 20 -- Utid of the thread. 21 utid INT, 22 -- Total runtime of thread. 23 total_runtime INT, 24 -- One of the scheduling states of kernel thread. 25 state STRING, 26 -- Total time spent in the scheduling state. 27 time_in_state INT, 28 -- Percentage of time thread spent in scheduling state in [0-100] range. 29 percentage_in_state INT 30) AS 31WITH total_dur AS ( 32 SELECT 33 utid, 34 sum(dur) AS sum_dur 35 FROM thread_state 36 GROUP BY 1 37), 38summed AS ( 39 SELECT 40 utid, 41 state, 42 sum(dur) AS time_in_state 43 FROM thread_state group by 1, 2 44) 45SELECT 46 utid, 47 sum_dur AS total_runtime, 48 state, 49 time_in_state, 50 (time_in_state*100)/(sum_dur) AS percentage_in_state 51FROM summed JOIN total_dur USING (utid); 52 53CREATE PERFETTO MACRO _case_for_state(state Expr) 54RETURNS Expr AS 55MAX(CASE WHEN state = $state THEN percentage_in_state END); 56 57-- Summary of time spent by thread in each scheduling state, in percentage ([0, 100] 58-- ranges). Sum of all states might be smaller than 100, as those values 59-- are rounded down. 60CREATE PERFETTO TABLE sched_percentage_of_time_in_state( 61 -- Utid of the thread. 62 utid INT, 63 -- Percentage of time thread spent in running ('Running') state in [0, 100] 64 -- range. 65 running INT, 66 -- Percentage of time thread spent in runnable ('R') state in [0, 100] 67 -- range. 68 runnable INT, 69 -- Percentage of time thread spent in preempted runnable ('R+') state in 70 -- [0, 100] range. 71 runnable_preempted INT, 72 -- Percentage of time thread spent in sleeping ('S') state in [0, 100] range. 73 sleeping INT, 74 -- Percentage of time thread spent in uninterruptible sleep ('D') state in 75 -- [0, 100] range. 76 uninterruptible_sleep INT, 77 -- Percentage of time thread spent in other ('T', 't', 'X', 'Z', 'x', 'I', 78 -- 'K', 'W', 'P', 'N') states in [0, 100] range. 79 other INT 80) AS 81SELECT 82 utid, 83 _case_for_state!('Running') AS running, 84 _case_for_state!('R') AS runnable, 85 _case_for_state!('R+') AS runnable_preempted, 86 _case_for_state!('S') AS sleeping, 87 _case_for_state!('D') AS uninterruptible_sleep, 88 SUM( 89 CASE WHEN state IN ('T', 't', 'X', 'Z', 'x', 'I', 'K', 'W', 'P', 'N') 90 THEN time_in_state END 91 ) * 100/total_runtime AS other 92FROM sched_time_in_state_for_thread 93GROUP BY utid; 94 95-- Time the thread spent each state in a given interval. 96CREATE PERFETTO FUNCTION sched_time_in_state_for_thread_in_interval( 97 -- The start of the interval. 98 ts INT, 99 -- The duration of the interval. 100 dur INT, 101 -- The utid of the thread. 102 utid INT) 103RETURNS TABLE( 104 -- Thread state (from the `thread_state` table). 105 -- Use `sched_state_to_human_readable_string` function to get full name. 106 state INT, 107 -- A (posssibly NULL) boolean indicating, if the device was in uninterruptible 108 -- sleep, if it was an IO sleep. 109 io_wait BOOL, 110 -- Some states can specify the blocked function. Usually NULL. 111 blocked_function INT, 112 -- Total time spent with this state, cpu and blocked function. 113 dur INT) AS 114SELECT 115 state, 116 io_wait, 117 blocked_function, 118 sum(ii.dur) as dur 119FROM thread_state 120JOIN 121 (SELECT * FROM _interval_intersect_single!( 122 $ts, $dur, 123 (SELECT id, ts, dur 124 FROM thread_state 125 WHERE utid = $utid))) ii USING (id) 126GROUP BY 1, 2, 3 127ORDER BY 4 DESC; 128 129-- Time the thread spent each state and cpu in a given interval. 130CREATE PERFETTO FUNCTION sched_time_in_state_and_cpu_for_thread_in_interval( 131 -- The start of the interval. 132 ts INT, 133 -- The duration of the interval. 134 dur INT, 135 -- The utid of the thread. 136 utid INT) 137RETURNS TABLE( 138 -- Thread state (from the `thread_state` table). 139 -- Use `sched_state_to_human_readable_string` function to get full name. 140 state INT, 141 -- A (posssibly NULL) boolean indicating, if the device was in uninterruptible 142 -- sleep, if it was an IO sleep. 143 io_wait BOOL, 144 -- Id of the CPU. 145 -- Use `cpu_guess_core_type` to get the CPU size (little/mid/big). 146 cpu INT, 147 -- Some states can specify the blocked function. Usually NULL. 148 blocked_function INT, 149 -- Total time spent with this state, cpu and blocked function. 150 dur INT) AS 151SELECT 152 state, 153 io_wait, 154 cpu, 155 blocked_function, 156 sum(ii.dur) as dur 157FROM thread_state 158JOIN 159 (SELECT * FROM _interval_intersect_single!( 160 $ts, $dur, 161 (SELECT id, ts, dur 162 FROM thread_state 163 WHERE utid = $utid))) ii USING (id) 164GROUP BY 1, 2, 3, 4 165ORDER BY 5 DESC; 166 167-- Time spent by CPU in each scheduling state in a provided interval. 168CREATE PERFETTO FUNCTION sched_time_in_state_for_cpu_in_interval( 169 -- CPU id. 170 cpu INT, 171 -- Interval start. 172 ts INT, 173 -- Interval duration. 174 dur INT 175) RETURNS TABLE ( 176 -- End state. From `sched.end_state`. 177 end_state STRING, 178 -- Duration in state. 179 dur INT 180) AS 181WITH sched_for_cpu AS ( 182 SELECT id, ts, dur 183 FROM sched 184 WHERE cpu = $cpu AND dur != -1 185) 186SELECT 187 end_state, 188 sum(ii.dur) AS dur 189FROM sched 190JOIN _interval_intersect_single!($ts, $dur, sched_for_cpu) ii 191USING (id) 192GROUP BY end_state; 193 194 195 196