1-- 2-- Copyright 2023 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 slices.flat_slices; 17 18-- Create a table which joins the thread state across the flattened slices. 19CREATE VIRTUAL TABLE __span_joined_thread USING SPAN_JOIN (_slice_flattened PARTITIONED utid, thread_state PARTITIONED utid); 20 21-- Get the thread state breakdown of a flattened slice from its slice id. 22-- This table pivoted and summed for better visualization and aggregation. 23-- The concept of a "flat slice" is to take the data in the slice table and 24-- remove all notion of nesting. For more information, read the description 25-- of _slice_flattened. 26CREATE PERFETTO FUNCTION _get_flattened_thread_state( 27 -- Id of the slice of interest. 28 slice_id JOINID(slice.id), 29 -- Utid. 30 utid JOINID(thread.id) 31) 32RETURNS TABLE ( 33 -- Timestamp. 34 ts TIMESTAMP, 35 -- Duration. 36 dur DURATION, 37 -- Utid. 38 utid JOINID(thread.id), 39 -- Depth. 40 depth LONG, 41 -- Name. 42 name STRING, 43 -- Slice id. 44 slice_id JOINID(slice.id), 45 -- Track id. 46 track_id JOINID(track.id), 47 -- CPU. 48 cpu LONG, 49 -- State. 50 state STRING, 51 -- IO wait. 52 io_wait LONG, 53 -- Thread state's blocked_function. 54 blocked_function STRING, 55 -- Thread state's waker utid. 56 waker_utid JOINID(thread.id), 57 -- Thread state's IRQ context. 58 irq_context LONG 59) AS 60WITH 61 interesting_slice AS ( 62 SELECT 63 ts, 64 dur, 65 slice.track_id AS track_id 66 FROM slice 67 JOIN thread_track 68 ON slice.track_id = thread_track.id 69 JOIN thread 70 USING (utid) 71 WHERE 72 ( 73 ( 74 NOT $slice_id IS NULL AND slice.id = $slice_id 75 ) OR ( 76 $slice_id IS NULL 77 ) 78 ) 79 AND ( 80 ( 81 NOT $utid IS NULL AND utid = $utid 82 ) OR ( 83 $utid IS NULL 84 ) 85 ) 86 ) 87SELECT 88 ts, 89 dur, 90 utid, 91 depth, 92 name, 93 slice_id, 94 track_id, 95 cpu, 96 state, 97 io_wait, 98 blocked_function, 99 waker_utid, 100 irq_context 101FROM __span_joined_thread 102WHERE 103 track_id = ( 104 SELECT 105 track_id 106 FROM interesting_slice 107 ) 108 AND ts >= ( 109 SELECT 110 ts 111 FROM interesting_slice 112 ) 113 AND ts < ( 114 SELECT 115 ts + dur 116 FROM interesting_slice 117 ); 118 119-- Get the thread state breakdown of a flattened slice from slice id. 120-- This table pivoted and summed for better visualization and aggragation. 121-- The concept of a "flat slice" is to take the data in the slice table and 122-- remove all notion of nesting. For more information, read the description 123-- of _slice_flattened. 124CREATE PERFETTO FUNCTION _get_flattened_thread_state_aggregated( 125 -- Slice id. 126 slice_id JOINID(slice.id), 127 -- Utid. 128 utid JOINID(thread.id) 129) 130RETURNS TABLE ( 131 -- Id of a slice. 132 slice_id JOINID(slice.id), 133 -- Name of the slice. 134 slice_name STRING, 135 -- Time (ns) spent in Uninterruptible Sleep (non-IO) 136 uninterruptible_sleep_nonio LONG, 137 -- Time (ns) spent in Uninterruptible Sleep (IO) 138 uninterruptible_sleep_io LONG, 139 -- Time (ns) spent in Runnable 140 runnable LONG, 141 -- Time (ns) spent in Sleeping 142 sleeping LONG, 143 -- Time (ns) spent in Stopped 144 stopped LONG, 145 -- Time (ns) spent in Traced 146 traced LONG, 147 -- Time (ns) spent in Exit (Dead) 148 exit_dead LONG, 149 -- Time (ns) spent in Exit (Zombie) 150 exit_zombie LONG, 151 -- Time (ns) spent in Task Dead 152 task_dead LONG, 153 -- Time (ns) spent in Wake Kill 154 wake_kill LONG, 155 -- Time (ns) spent in Waking 156 waking LONG, 157 -- Time (ns) spent in Parked 158 parked LONG, 159 -- Time (ns) spent in No Load 160 no_load LONG, 161 -- Time (ns) spent in Runnable (Preempted) 162 runnable_preempted LONG, 163 -- Time (ns) spent in Running 164 running LONG, 165 -- Time (ns) spent in Idle 166 idle LONG, 167 -- Total duration of the slice 168 dur DURATION, 169 -- Depth of the slice in Perfetto 170 depth LONG 171) AS 172WITH 173 final_table AS ( 174 SELECT 175 * 176 FROM _get_flattened_thread_state($slice_id, $utid) 177 ) 178SELECT 179 fs.slice_id, 180 fs.name AS slice_name, 181 sum(CASE WHEN fs.state = 'D' AND io_wait = 0 THEN fs.dur END) AS uninterruptible_sleep_nonio, 182 sum(CASE WHEN fs.state = 'D' AND io_wait = 1 THEN fs.dur END) AS uninterruptible_sleep_io, 183 sum(CASE WHEN fs.state = 'R' THEN fs.dur END) AS runnable, 184 sum(CASE WHEN fs.state = 'S' THEN fs.dur END) AS sleeping, 185 sum(CASE WHEN fs.state = 'T' THEN fs.dur END) AS stopped, 186 sum(CASE WHEN fs.state = 't' THEN fs.dur END) AS traced, 187 sum(CASE WHEN fs.state = 'X' THEN fs.dur END) AS exit_dead, 188 sum(CASE WHEN fs.state = 'Z' THEN fs.dur END) AS exit_zombie, 189 sum(CASE WHEN fs.state = 'x' THEN fs.dur END) AS task_dead, 190 sum(CASE WHEN fs.state = 'K' THEN fs.dur END) AS wake_kill, 191 sum(CASE WHEN fs.state = 'W' THEN fs.dur END) AS waking, 192 sum(CASE WHEN fs.state = 'P' THEN fs.dur END) AS parked, 193 sum(CASE WHEN fs.state = 'N' THEN fs.dur END) AS no_load, 194 sum(CASE WHEN fs.state = 'R+' THEN fs.dur END) AS runnable_preempted, 195 sum(CASE WHEN fs.state = 'Running' THEN fs.dur END) AS running, 196 sum(CASE WHEN fs.state = 'I' THEN fs.dur END) AS idle, 197 sum(fs.dur) AS dur, 198 fs.depth 199FROM final_table AS fs 200GROUP BY 201 fs.slice_id; 202