src/trace_processor/metrics/sql/android/android_multiuser_populator.sql

--
-- Copyright 2021 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.
--

-- Collect the important timestamps for Multiuser events.
DROP VIEW IF EXISTS multiuser_events;
CREATE VIEW multiuser_events AS
SELECT
      {{start_event}}_time_ns AS event_start_time_ns,
      {{end_event}}_time_ns AS event_end_time_ns
FROM
  (
    SELECT slice.ts AS user_start_time_ns
    FROM slice
    WHERE (slice.name LIKE "onBeforeStartUser%")
    -- TODO: Use a signal based on SysUi so that it covers all switching, not just starting.
  ),
  (
    SELECT slice.ts + slice.dur AS launcher_end_time_ns
    FROM slice
    WHERE (slice.name = "launching: com.google.android.apps.nexuslauncher")
  );

-- Calculation of the duration of the Multiuser event of interest.
DROP VIEW IF EXISTS multiuser_timing;
CREATE VIEW multiuser_timing AS
SELECT
      CAST((event_end_time_ns - event_start_time_ns) / 1e6 + 0.5 AS INT) AS duration_ms
FROM
  multiuser_events;


-- Calculate CPU usage during the Multiuser event of interest.

-- Get all the scheduling slices.
DROP VIEW IF EXISTS sp_sched;
CREATE VIEW sp_sched AS
SELECT ts, dur, cpu, utid
FROM sched;
-- Get all the cpu frequency slices.
DROP VIEW IF EXISTS sp_frequency;
CREATE VIEW sp_frequency AS
SELECT
  ts,
  lead(ts) OVER (PARTITION BY track_id ORDER BY ts) - ts as dur,
  cpu,
  value as freq_khz
FROM counter
JOIN cpu_counter_track ON counter.track_id = cpu_counter_track.id;
-- Create the span joined table which combines cpu frequency with scheduling slices.
DROP TABLE IF EXISTS sched_with_frequency;
CREATE VIRTUAL TABLE sched_with_frequency
USING SPAN_JOIN(sp_sched PARTITIONED cpu, sp_frequency PARTITIONED cpu);

-- Calculate the CPU cycles spent per process during the duration.
DROP VIEW IF EXISTS cpu_usage_all;
CREATE VIEW cpu_usage_all AS
SELECT
    process.uid / 100000 AS user_id,
    process.name AS process_name,
    SUM(dur * freq_khz) / 1e9 AS cpu_kcycles
FROM
    sched_with_frequency
    JOIN thread USING (utid)
    JOIN process USING (upid)
WHERE
    ts >= (SELECT event_start_time_ns FROM multiuser_events)
    AND
    ts <= (SELECT event_end_time_ns FROM multiuser_events)
GROUP BY upid, process.name
ORDER BY cpu_kcycles DESC;

-- Get the data from cpu_usage_all, but also with the percentage.
DROP VIEW IF EXISTS cpu_usage;
CREATE VIEW cpu_usage AS
SELECT
    user_id,
    process_name,
    process_name || ":" || (CASE WHEN user_id = 0 THEN "system" ELSE "secondary" END) AS identifier,
    CAST(cpu_kcycles / 1e3 AS INT) AS cpu_mcycles,
    cpu_kcycles / (SELECT SUM(cpu_kcycles) FROM cpu_usage_all) * 100 AS cpu_percentage
FROM
    cpu_usage_all
ORDER BY cpu_mcycles DESC LIMIT 6;


-- Record the output for populating the proto.
DROP VIEW IF EXISTS {{output_table_name}};
CREATE VIEW {{output_table_name}} AS
SELECT AndroidMultiuserMetric_EventData(
  'duration_ms', (
    SELECT duration_ms
    FROM multiuser_timing
  ),
  'cpu_usage', (
    SELECT RepeatedField(
      AndroidMultiuserMetric_EventData_CpuUsage(
        'identifier', identifier,
        'user_id', user_id,
        'process_name', process_name,
        'cpu_mcycles', cpu_mcycles,
        'cpu_percentage', cpu_percentage
      )
    )
    FROM cpu_usage
  )
);