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 16-- Matches slices with frames within CUJs and aggregates slices durations within each frame. 17-- This allows comparing the cumulative durations of a set of slices vs what was the expected 18-- duration of each frame. It can be a useful heuristic to figure out what contributed to 19-- frames missing their expected deadlines. 20-- 21-- EXAMPLE: 22-- 23-- 24-- CUJ Frames: 25-- 26-- |==== FRAME 1 ====| 27-- |====== FRAME 2 ======| 28-- |== FRAME 3 ==| 29-- |== FRAME 4 ==| 30-- 31-- Main thread frame boundaries: 32-- 33-- |= FRAME 1 =|==== FRAME 2 ====| |= FRAME 3 =||= FRAME 4 =| 34-- 35-- `relevant_slice_table_name` slices: 36-- 37-- |== binder ==| |== binder ==| |=b=| |=b=||=b=| 38-- 39-- 40-- OUTPUT: 41-- 42-- Data in `*_slice_in_frame` - slices from `relevant_slice_table_name` matched 43-- to frames and trimmed to their boundaries: 44-- 45-- * FRAME 1: 46-- | binder | 47-- * FRAME 2: 48-- |== binder ==| 49-- * FRAME 3: 50-- |=b=||=b=| 51-- * FRAME 4 - not present in the output 52-- 53-- 54-- Data in `*_slice_in_frame_agg` is just an aggregation of durations in *_slice_in_frame. 55 56 57-- For simplicity we allow `relevant_slice_table_name` to be based on any 58-- of the slice tables. This table filters it down to only include slices within 59-- the (broadly bounded) CUJ and on the specific process / thread. 60-- Using TABLE and not VIEW as this gives better, localized error messages in cases 61-- `relevant_slice_table_name` is not correct (e.g. missing cuj_id). 62DROP TABLE IF EXISTS {{table_name_prefix}}_query_slice; 63CREATE TABLE {{table_name_prefix}}_query_slice AS 64SELECT 65 slice.cuj_id, 66 slice.utid, 67 slice.id, 68 slice.name, 69 slice.ts, 70 slice.dur, 71 slice.ts_end 72FROM {{relevant_slice_table_name}} slice 73JOIN {{slice_table_name}} android_jank_cuj_slice_table 74 USING (cuj_id, id); 75 76-- Flat view of frames and slices matched and "trimmed" to each frame boundaries. 77DROP VIEW IF EXISTS {{table_name_prefix}}_slice_in_frame; 78CREATE VIEW {{table_name_prefix}}_slice_in_frame AS 79SELECT 80 frame.*, 81 query_slice.id AS slice_id, 82 query_slice.utid AS slice_utid, 83 query_slice.name AS slice_name, 84 MAX(query_slice.ts, frame_boundary.ts) AS slice_ts, 85 MIN(query_slice.ts_end, frame_boundary.ts_end) AS slice_ts_end, 86 MIN(query_slice.ts_end, frame_boundary.ts_end) - MAX(query_slice.ts, frame_boundary.ts) AS slice_dur, 87 query_slice.ts_end AS ts_end_original 88FROM {{frame_table_name}} frame 89-- We want to use different boundaries depending on which thread's slices the query is targetting. 90JOIN {{frame_boundary_table_name}} frame_boundary USING (cuj_id, vsync) 91JOIN {{table_name_prefix}}_query_slice query_slice 92 ON frame_boundary.cuj_id = query_slice.cuj_id 93 AND ANDROID_JANK_CUJ_SLICE_OVERLAPS(frame_boundary.ts, frame_boundary.dur, query_slice.ts, query_slice.dur); 94 95-- Aggregated view of frames and slices overall durations within each frame boundaries. 96DROP VIEW IF EXISTS {{table_name_prefix}}_slice_in_frame_agg; 97CREATE VIEW {{table_name_prefix}}_slice_in_frame_agg AS 98SELECT 99 cuj_id, 100 frame_number, 101 vsync, 102 dur_expected, 103 app_missed, 104 sf_missed, 105 1.0 * SUM(slice_dur) / dur_expected AS slice_dur_div_frame_dur_expected, 106 SUM(slice_dur) AS slice_dur_sum, 107 MAX(slice_dur) AS slice_dur_max 108FROM {{table_name_prefix}}_slice_in_frame 109GROUP BY cuj_id, frame_number, vsync, dur_expected, app_missed, sf_missed; 110