1-- 2-- Copyright 2022 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 16SELECT RUN_METRIC('android/process_metadata.sql'); 17 18-- Stores information about the CUJs (important UI transitions) in the trace. 19DROP TABLE IF EXISTS android_jank_cuj; 20CREATE TABLE android_jank_cuj AS 21-- Finds slices like J<SHADE_EXPAND_COLLAPSE> which mark which frames were 22-- rendered during a specific CUJ. 23WITH cujs AS ( 24 SELECT 25 ROW_NUMBER() OVER (ORDER BY ts) AS cuj_id, 26 process.upid AS upid, 27 process.name AS process_name, 28 process_metadata.metadata AS process_metadata, 29 slice.name AS cuj_slice_name, 30 -- Extracts "CUJ_NAME" from "J<CUJ_NAME>" 31 SUBSTR(slice.name, 3, LENGTH(slice.name) - 3) AS cuj_name, 32 ts, 33 dur, 34 ts + dur AS ts_end 35 FROM slice 36 JOIN process_track 37 ON slice.track_id = process_track.id 38 JOIN process USING (upid) 39 JOIN process_metadata USING (upid) 40 WHERE 41 slice.name GLOB 'J<*>' 42 AND ( 43 process.name GLOB 'com.google.android*' 44 OR process.name GLOB 'com.android.*') 45 AND dur > 0 46), 47-- Slices logged from FrameTracker#markEvent that describe when 48-- the instrumentation was started and the reason the CUJ ended. 49cuj_state_markers AS ( 50 SELECT 51 cujs.cuj_id, 52 CASE 53 WHEN cuj_state_marker.name GLOB '*FT#begin*' THEN 'begin' 54 WHEN cuj_state_marker.name GLOB '*FT#deferMonitoring*' THEN 'deferMonitoring' 55 WHEN cuj_state_marker.name GLOB '*FT#end*' THEN 'end' 56 WHEN cuj_state_marker.name GLOB '*FT#cancel*' THEN 'cancel' 57 WHEN cuj_state_marker.name GLOB '*FT#layerId*' THEN 'layerId' 58 WHEN cuj_state_marker.name GLOB '*#UIThread' THEN 'UIThread' 59 ELSE 'other' 60 END AS marker_type, 61 cuj_state_marker.name as marker_name, 62 thread_track.utid AS utid 63 FROM cujs 64 LEFT JOIN slice cuj_state_marker 65 ON cuj_state_marker.ts >= cujs.ts 66 AND cuj_state_marker.ts < cujs.ts_end 67 LEFT JOIN track marker_track on marker_track.id = cuj_state_marker.track_id 68 LEFT JOIN thread_track on cuj_state_marker.track_id = thread_track.id 69 WHERE 70 -- e.g. J<CUJ_NAME>#FT#end#0 this for backward compatibility 71 cuj_state_marker.name GLOB (cujs.cuj_slice_name || "#FT#*") 72 OR (marker_track.name = cuj_slice_name and cuj_state_marker.name GLOB 'FT#*') 73 OR cuj_state_marker.name = (cujs.cuj_slice_name || "#UIThread") 74) 75SELECT 76 cujs.*, 77 CASE 78 WHEN EXISTS ( 79 SELECT 1 80 FROM cuj_state_markers csm 81 WHERE csm.cuj_id = cujs.cuj_id 82 AND csm.marker_type = 'cancel') 83 THEN 'canceled' 84 WHEN EXISTS ( 85 SELECT 1 86 FROM cuj_state_markers csm 87 WHERE csm.cuj_id = cujs.cuj_id 88 AND csm.marker_type = 'end') 89 THEN 'completed' 90 ELSE NULL 91 END AS state, 92 ( 93 SELECT CAST(STR_SPLIT(csm.marker_name, 'layerId#', 1) AS INTEGER) 94 FROM cuj_state_markers csm 95 WHERE csm.cuj_id = cujs.cuj_id AND csm.marker_name GLOB '*layerId#*' 96 LIMIT 1 97 ) AS layer_id, 98 ( 99 SELECT CAST(STR_SPLIT(csm.marker_name, 'beginVsync#', 1) AS INTEGER) 100 FROM cuj_state_markers csm 101 WHERE csm.cuj_id = cujs.cuj_id AND csm.marker_name GLOB '*beginVsync#*' 102 LIMIT 1 103 ) AS begin_vsync, 104 ( 105 SELECT CAST(STR_SPLIT(csm.marker_name, 'endVsync#', 1) AS INTEGER) 106 FROM cuj_state_markers csm 107 WHERE csm.cuj_id = cujs.cuj_id AND csm.marker_name GLOB '*endVsync#*' 108 LIMIT 1 109 ) AS end_vsync, 110 ( 111 SELECT utid from cuj_state_markers csm 112 WHERE csm.cuj_id = cujs.cuj_id AND csm.marker_name GLOB "*#UIThread" 113 LIMIT 1 114 ) AS ui_thread 115FROM cujs 116WHERE 117 state != 'canceled' 118 -- Older builds don't have the state markers so we allow NULL but filter out 119 -- CUJs that are <4ms long - assuming CUJ was canceled in that case. 120 OR (state IS NULL AND cujs.dur > 4e6) 121ORDER BY ts ASC;