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