third_party/perfetto/src/trace_processor/metrics/sql/chrome/event_latency_scroll_jank_cause.sql - cobalt - Git at Google

 --
 -- Copyright 2022 The Android Open Source Project
 --
 -- Licensed under the Apache License, Version 2.0 (the "License");
 -- you may not use this file except in compliance with the License.
 -- You may obtain a copy of the License at
 --
 --     https://www.apache.org/licenses/LICENSE-2.0
 --
 -- Unless required by applicable law or agreed to in writing, software
 -- distributed under the License is distributed on an "AS IS" BASIS,
 -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 -- See the License for the specific language governing permissions and
 -- limitations under the License.
 --
 -- Calculating how often each breakdown causes a scroll jank.
 -- We define that a breakdown causes a scroll jank in the janky EventLatency event if it increased
 -- more than other breakdowns relatevly to its neigbour EventLatency events.
 --
 -- WARNING: This metric should not be used as a source of truth. It is under
 --          active development and the values & meaning might change without
 --          notice.


 SELECT RUN_METRIC('chrome/event_latency_scroll_jank.sql');

 -- Calculating the jank delta for EventLatency events which are janky relatevly to its next EventLatency event.
 -- For breakdowns that exist in the current EventLatency but not the next EventLatency
 -- we use a default of 0 so that the full duration is considered when looking for the maximum increase.
 -- Breakdowns that exist in the next EventLatency event, but not in the current EventLatency event,
 -- are ignored because they do not cause a jank anyway.
 DROP VIEW IF EXISTS event_latency_scroll_breakdowns_next_jank_deltas;
 CREATE VIEW event_latency_scroll_breakdowns_next_jank_deltas
 AS
 SELECT
     cur_breakdowns.*,
     next_breakdowns.event_type as next_event_type,
     next_breakdowns.slice_id as next_breakdown_id,
     next_breakdowns.dur as next_dur,
     cur_breakdowns.dur - COALESCE(next_breakdowns.dur, 0) as delta_dur_ns
 FROM event_latency_scroll_breakdowns_jank as cur_breakdowns LEFT JOIN event_latency_scroll_breakdowns_jank as next_breakdowns
 ON cur_breakdowns.next_event_latency_id = next_breakdowns.event_latency_id AND
     cur_breakdowns.name = next_breakdowns.name
 WHERE cur_breakdowns.next_jank = 1;

 -- Calculating the jank delta for EventLatency events which are janky relatevly to its prev EventLatency event.
 -- For breakdowns that exist in the current EventLatency but not the prev EventLatency
 -- we use a default of 0 so that the full duration is considered when looking for the maximum increase.
 -- Breakdowns that exist in the prev EventLatency event, but not in the current EventLatency event,
 -- are ignored because they do not cause a jank anyway.
 DROP VIEW IF EXISTS event_latency_scroll_breakdowns_prev_jank_deltas;
 CREATE VIEW event_latency_scroll_breakdowns_prev_jank_deltas
 AS
 SELECT
     cur_breakdowns.*,
     prev_breakdowns.event_type as prev_event_type,
     prev_breakdowns.slice_id as prev_breakdown_id,
     prev_breakdowns.dur as prev_dur,
     cur_breakdowns.dur - COALESCE(prev_breakdowns.dur, 0) as delta_dur_ns
 FROM event_latency_scroll_breakdowns_jank as cur_breakdowns LEFT JOIN event_latency_scroll_breakdowns_jank as prev_breakdowns
 ON cur_breakdowns.prev_event_latency_id = prev_breakdowns.event_latency_id AND
     cur_breakdowns.name = prev_breakdowns.name
 WHERE cur_breakdowns.prev_jank = 1;

 -- Add a jank indicator to each breakdown. Jank indicator is related to an entire EventLatency envent, not only to a breakdown.
 DROP VIEW IF EXISTS event_latency_scroll_breakdowns_jank;
 CREATE VIEW event_latency_scroll_breakdowns_jank
 AS
 SELECT
   event_latency_breakdowns.*,
   scroll_event_latency_jank.jank,
   scroll_event_latency_jank.next_jank,
   scroll_event_latency_jank.prev_jank,
   scroll_event_latency_jank.next_id as next_event_latency_id,
   scroll_event_latency_jank.prev_id as prev_event_latency_id
 FROM event_latency_breakdowns JOIN scroll_event_latency_jank
 ON event_latency_breakdowns.event_latency_id = scroll_event_latency_jank.id
 WHERE event_latency_breakdowns.event_type in ("GESTURE_SCROLL_UPDATE", "FIRST_GESTURE_SCROLL_UPDATE", "INERTIAL_GESTURE_SCROLL_UPDATE");

 -- Merge breakdowns from the |event_latency_scroll_breakdowns_next_jank_deltas|
 -- and |event_latency_scroll_breakdowns_prev_jank_deltas| tables and select the maximum |delta_dur_ns| of them.
 -- This is necessary in order to get a single reason for the jank for the event later.
 DROP VIEW IF EXISTS event_latency_scroll_breakdowns_max_jank_deltas;
 CREATE VIEW event_latency_scroll_breakdowns_max_jank_deltas
 AS
 SELECT
   COALESCE(next.slice_id, prev.slice_id) as slice_id,
   COALESCE(next.name, prev.name) as name,
   COALESCE(next.event_latency_id, prev.event_latency_id) as event_latency_id,
   COALESCE(next.event_latency_track_id, prev.event_latency_track_id) as track_id,
   COALESCE(next.event_latency_dur, prev.event_latency_dur) as event_latency_dur,
   COALESCE(next.event_latency_ts, prev.event_latency_ts) as event_latency_ts,
   COALESCE(next.event_type, prev.event_type) as event_type,
   COALESCE(next.event_latency_ts, prev.event_latency_ts) as ts,
   COALESCE(next.event_latency_dur, prev.event_latency_dur) as dur,
   COALESCE(next.jank, prev.jank) as jank,
   COALESCE(next.next_jank, 0) as next_jank,
   COALESCE(prev.prev_jank, 0) as prev_jank,
   next.next_event_latency_id as next_event_latency_id,
   prev.prev_event_latency_id as prev_event_latency_id,
   next.delta_dur_ns as next_delta_dur_ns,
   prev.delta_dur_ns as prev_delta_dur_ns,
   CASE
     WHEN prev.delta_dur_ns IS NULL OR next.delta_dur_ns >  prev.delta_dur_ns
       THEN next.delta_dur_ns
     ELSE prev.delta_dur_ns
   END as delta_dur_ns,
   CASE
     WHEN prev.delta_dur_ns IS NULL OR next.delta_dur_ns >  prev.delta_dur_ns
       THEN next.next_breakdown_id
     ELSE prev.prev_breakdown_id
   END as max_jank_neigbour_breakdown_id
 FROM event_latency_scroll_breakdowns_next_jank_deltas as next
 FULL JOIN event_latency_scroll_breakdowns_prev_jank_deltas as prev
 ON next.slice_id = prev.slice_id;

 -- Selecting breakdowns which have a maximum ns duration delta as a main causes of a jank for this EventLatency event.
 DROP VIEW IF EXISTS event_latency_scroll_jank_cause_top_level;
 CREATE VIEW event_latency_scroll_jank_cause_top_level
 AS
 SELECT
   event_latency_id as slice_id,
   track_id,
   event_latency_dur as dur,
   event_latency_ts as ts,
   event_type,
   next_jank,
   prev_jank,
   next_event_latency_id,
   prev_event_latency_id,
   next_delta_dur_ns,
   prev_delta_dur_ns,
   name as cause_of_jank,
   slice_id as max_jank_breakdown_id,
   max_jank_neigbour_breakdown_id,
   MAX(delta_dur_ns) as max_delta_dur_ns
 FROM event_latency_scroll_breakdowns_max_jank_deltas
 GROUP BY event_latency_id;

 -- Selecting sub-breakdowns of the main causes of a jank which have a maximum ns duration delta with a neighbour event's breakdowns.
 DROP VIEW IF EXISTS event_latency_scroll_sub_breakdowns_max_deltas;
 CREATE VIEW event_latency_scroll_sub_breakdowns_max_deltas
 AS
 SELECT
   cur_event_latency.slice_id as event_latency_id,
   cur_sub_breakdowns.name as sub_breakdown_name,
   MAX(cur_sub_breakdowns.dur - neighbour_sub_breakdowns.dur) as max_sub_breakdown_delta_dur_ns
 FROM event_latency_scroll_jank_cause_top_level as cur_event_latency
 LEFT JOIN slices as cur_sub_breakdowns
   ON cur_event_latency.max_jank_breakdown_id = cur_sub_breakdowns.parent_id
 LEFT JOIN slices as neighbour_sub_breakdowns
   ON cur_event_latency.max_jank_neigbour_breakdown_id = neighbour_sub_breakdowns.parent_id AND
    cur_sub_breakdowns.name = neighbour_sub_breakdowns.name
 GROUP BY cur_event_latency.slice_id, cur_event_latency.max_jank_breakdown_id;

 -- Selecting the main cause of jank and its sub-cause of jank.
 DROP VIEW IF EXISTS event_latency_scroll_jank_cause;
 CREATE VIEW event_latency_scroll_jank_cause
 AS
 SELECT
   event_latency_scroll_jank_cause_top_level.*,
   max_sub_breakdowns.sub_breakdown_name as sub_cause_of_jank
 FROM event_latency_scroll_jank_cause_top_level
 LEFT JOIN event_latency_scroll_sub_breakdowns_max_deltas as max_sub_breakdowns
 ON event_latency_scroll_jank_cause_top_level.slice_id = max_sub_breakdowns.event_latency_id;

 -- Calculate how often each breakdown is a main cause of a jank for EventLatency events.
 DROP VIEW IF EXISTS event_latency_scroll_jank_cause_cnt;
 CREATE VIEW event_latency_scroll_jank_cause_cnt
 AS
 SELECT
   cause_of_jank,
   sub_cause_of_jank,
   COUNT(*) as cnt
 FROM event_latency_scroll_jank_cause
 GROUP BY cause_of_jank, sub_cause_of_jank;
	--
	-- Copyright 2022 The Android Open Source Project
	--
	-- Licensed under the Apache License, Version 2.0 (the "License");
	-- you may not use this file except in compliance with the License.
	-- You may obtain a copy of the License at
	--
	-- https://www.apache.org/licenses/LICENSE-2.0
	--
	-- Unless required by applicable law or agreed to in writing, software
	-- distributed under the License is distributed on an "AS IS" BASIS,
	-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	-- See the License for the specific language governing permissions and
	-- limitations under the License.
	--
	-- Calculating how often each breakdown causes a scroll jank.
	-- We define that a breakdown causes a scroll jank in the janky EventLatency event if it increased
	-- more than other breakdowns relatevly to its neigbour EventLatency events.
	--
	-- WARNING: This metric should not be used as a source of truth. It is under
	-- active development and the values & meaning might change without
	-- notice.


	SELECT RUN_METRIC('chrome/event_latency_scroll_jank.sql');

	-- Calculating the jank delta for EventLatency events which are janky relatevly to its next EventLatency event.
	-- For breakdowns that exist in the current EventLatency but not the next EventLatency
	-- we use a default of 0 so that the full duration is considered when looking for the maximum increase.
	-- Breakdowns that exist in the next EventLatency event, but not in the current EventLatency event,
	-- are ignored because they do not cause a jank anyway.
	DROP VIEW IF EXISTS event_latency_scroll_breakdowns_next_jank_deltas;
	CREATE VIEW event_latency_scroll_breakdowns_next_jank_deltas
	AS
	SELECT
	cur_breakdowns.*,
	next_breakdowns.event_type as next_event_type,
	next_breakdowns.slice_id as next_breakdown_id,
	next_breakdowns.dur as next_dur,
	cur_breakdowns.dur - COALESCE(next_breakdowns.dur, 0) as delta_dur_ns
	FROM event_latency_scroll_breakdowns_jank as cur_breakdowns LEFT JOIN event_latency_scroll_breakdowns_jank as next_breakdowns
	ON cur_breakdowns.next_event_latency_id = next_breakdowns.event_latency_id AND
	cur_breakdowns.name = next_breakdowns.name
	WHERE cur_breakdowns.next_jank = 1;

	-- Calculating the jank delta for EventLatency events which are janky relatevly to its prev EventLatency event.
	-- For breakdowns that exist in the current EventLatency but not the prev EventLatency
	-- we use a default of 0 so that the full duration is considered when looking for the maximum increase.
	-- Breakdowns that exist in the prev EventLatency event, but not in the current EventLatency event,
	-- are ignored because they do not cause a jank anyway.
	DROP VIEW IF EXISTS event_latency_scroll_breakdowns_prev_jank_deltas;
	CREATE VIEW event_latency_scroll_breakdowns_prev_jank_deltas
	AS
	SELECT
	cur_breakdowns.*,
	prev_breakdowns.event_type as prev_event_type,
	prev_breakdowns.slice_id as prev_breakdown_id,
	prev_breakdowns.dur as prev_dur,
	cur_breakdowns.dur - COALESCE(prev_breakdowns.dur, 0) as delta_dur_ns
	FROM event_latency_scroll_breakdowns_jank as cur_breakdowns LEFT JOIN event_latency_scroll_breakdowns_jank as prev_breakdowns
	ON cur_breakdowns.prev_event_latency_id = prev_breakdowns.event_latency_id AND
	cur_breakdowns.name = prev_breakdowns.name
	WHERE cur_breakdowns.prev_jank = 1;

	-- Add a jank indicator to each breakdown. Jank indicator is related to an entire EventLatency envent, not only to a breakdown.
	DROP VIEW IF EXISTS event_latency_scroll_breakdowns_jank;
	CREATE VIEW event_latency_scroll_breakdowns_jank
	AS
	SELECT
	event_latency_breakdowns.*,
	scroll_event_latency_jank.jank,
	scroll_event_latency_jank.next_jank,
	scroll_event_latency_jank.prev_jank,
	scroll_event_latency_jank.next_id as next_event_latency_id,
	scroll_event_latency_jank.prev_id as prev_event_latency_id
	FROM event_latency_breakdowns JOIN scroll_event_latency_jank
	ON event_latency_breakdowns.event_latency_id = scroll_event_latency_jank.id
	WHERE event_latency_breakdowns.event_type in ("GESTURE_SCROLL_UPDATE", "FIRST_GESTURE_SCROLL_UPDATE", "INERTIAL_GESTURE_SCROLL_UPDATE");

	-- Merge breakdowns from the \|event_latency_scroll_breakdowns_next_jank_deltas\|
	-- and \|event_latency_scroll_breakdowns_prev_jank_deltas\| tables and select the maximum \|delta_dur_ns\| of them.
	-- This is necessary in order to get a single reason for the jank for the event later.
	DROP VIEW IF EXISTS event_latency_scroll_breakdowns_max_jank_deltas;
	CREATE VIEW event_latency_scroll_breakdowns_max_jank_deltas
	AS
	SELECT
	COALESCE(next.slice_id, prev.slice_id) as slice_id,
	COALESCE(next.name, prev.name) as name,
	COALESCE(next.event_latency_id, prev.event_latency_id) as event_latency_id,
	COALESCE(next.event_latency_track_id, prev.event_latency_track_id) as track_id,
	COALESCE(next.event_latency_dur, prev.event_latency_dur) as event_latency_dur,
	COALESCE(next.event_latency_ts, prev.event_latency_ts) as event_latency_ts,
	COALESCE(next.event_type, prev.event_type) as event_type,
	COALESCE(next.event_latency_ts, prev.event_latency_ts) as ts,
	COALESCE(next.event_latency_dur, prev.event_latency_dur) as dur,
	COALESCE(next.jank, prev.jank) as jank,
	COALESCE(next.next_jank, 0) as next_jank,
	COALESCE(prev.prev_jank, 0) as prev_jank,
	next.next_event_latency_id as next_event_latency_id,
	prev.prev_event_latency_id as prev_event_latency_id,
	next.delta_dur_ns as next_delta_dur_ns,
	prev.delta_dur_ns as prev_delta_dur_ns,
	CASE
	WHEN prev.delta_dur_ns IS NULL OR next.delta_dur_ns > prev.delta_dur_ns
	THEN next.delta_dur_ns
	ELSE prev.delta_dur_ns
	END as delta_dur_ns,
	CASE
	WHEN prev.delta_dur_ns IS NULL OR next.delta_dur_ns > prev.delta_dur_ns
	THEN next.next_breakdown_id
	ELSE prev.prev_breakdown_id
	END as max_jank_neigbour_breakdown_id
	FROM event_latency_scroll_breakdowns_next_jank_deltas as next
	FULL JOIN event_latency_scroll_breakdowns_prev_jank_deltas as prev
	ON next.slice_id = prev.slice_id;

	-- Selecting breakdowns which have a maximum ns duration delta as a main causes of a jank for this EventLatency event.
	DROP VIEW IF EXISTS event_latency_scroll_jank_cause_top_level;
	CREATE VIEW event_latency_scroll_jank_cause_top_level
	AS
	SELECT
	event_latency_id as slice_id,
	track_id,
	event_latency_dur as dur,
	event_latency_ts as ts,
	event_type,
	next_jank,
	prev_jank,
	next_event_latency_id,
	prev_event_latency_id,
	next_delta_dur_ns,
	prev_delta_dur_ns,
	name as cause_of_jank,
	slice_id as max_jank_breakdown_id,
	max_jank_neigbour_breakdown_id,
	MAX(delta_dur_ns) as max_delta_dur_ns
	FROM event_latency_scroll_breakdowns_max_jank_deltas
	GROUP BY event_latency_id;

	-- Selecting sub-breakdowns of the main causes of a jank which have a maximum ns duration delta with a neighbour event's breakdowns.
	DROP VIEW IF EXISTS event_latency_scroll_sub_breakdowns_max_deltas;
	CREATE VIEW event_latency_scroll_sub_breakdowns_max_deltas
	AS
	SELECT
	cur_event_latency.slice_id as event_latency_id,
	cur_sub_breakdowns.name as sub_breakdown_name,
	MAX(cur_sub_breakdowns.dur - neighbour_sub_breakdowns.dur) as max_sub_breakdown_delta_dur_ns
	FROM event_latency_scroll_jank_cause_top_level as cur_event_latency
	LEFT JOIN slices as cur_sub_breakdowns
	ON cur_event_latency.max_jank_breakdown_id = cur_sub_breakdowns.parent_id
	LEFT JOIN slices as neighbour_sub_breakdowns
	ON cur_event_latency.max_jank_neigbour_breakdown_id = neighbour_sub_breakdowns.parent_id AND
	cur_sub_breakdowns.name = neighbour_sub_breakdowns.name
	GROUP BY cur_event_latency.slice_id, cur_event_latency.max_jank_breakdown_id;

	-- Selecting the main cause of jank and its sub-cause of jank.
	DROP VIEW IF EXISTS event_latency_scroll_jank_cause;
	CREATE VIEW event_latency_scroll_jank_cause
	AS
	SELECT
	event_latency_scroll_jank_cause_top_level.*,
	max_sub_breakdowns.sub_breakdown_name as sub_cause_of_jank
	FROM event_latency_scroll_jank_cause_top_level
	LEFT JOIN event_latency_scroll_sub_breakdowns_max_deltas as max_sub_breakdowns
	ON event_latency_scroll_jank_cause_top_level.slice_id = max_sub_breakdowns.event_latency_id;

	-- Calculate how often each breakdown is a main cause of a jank for EventLatency events.
	DROP VIEW IF EXISTS event_latency_scroll_jank_cause_cnt;
	CREATE VIEW event_latency_scroll_jank_cause_cnt
	AS
	SELECT
	cause_of_jank,
	sub_cause_of_jank,
	COUNT(*) as cnt
	FROM event_latency_scroll_jank_cause
	GROUP BY cause_of_jank, sub_cause_of_jank;