1-- 2-- Copyright 2021 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 17-- Create the base tables and views containing the launch spans. 18SELECT RUN_METRIC('android/startup/launches.sql'); 19 20-- Collect the important timestamps for Multiuser events. 21DROP VIEW IF EXISTS multiuser_events; 22CREATE VIEW multiuser_events AS 23SELECT 24 {{start_event}}_time_ns AS event_start_time_ns, 25 {{end_event}}_time_ns AS event_end_time_ns 26FROM 27 ( 28 SELECT MIN(slice.ts) AS user_start_time_ns 29 FROM slice 30 WHERE ( 31 slice.name = "UserDetailView.Adapter#onClick" OR -- QuickSettings 32 slice.name = "UserDetailSettings.switchUser" OR -- Settings 33 slice.name = "shell_runSwitchUser" -- adb shell 34 ) 35 ), 36 ( 37 SELECT ts_end AS launcher_end_time_ns 38 FROM launches 39 WHERE (package = 'com.android.launcher3' OR package = 'com.google.android.apps.nexuslauncher') 40 ), 41 ( 42 SELECT MIN(slice.ts) AS user_create_time_ns 43 FROM slice 44 WHERE ( 45 slice.name = "UserDetailView.Adapter#onClick" OR -- QuickSettings 46 slice.name = "UserSettings.addUserNow" OR -- Settings 47 slice.name = "UserSettings.addGuest" OR -- Settings 48 slice.name = "shell_runCreateUser" -- adb shell 49 ) 50 ); 51 52-- Calculation of the duration of the Multiuser event of interest. 53DROP VIEW IF EXISTS multiuser_timing; 54CREATE VIEW multiuser_timing AS 55SELECT 56 CAST((event_end_time_ns - event_start_time_ns) / 1e6 + 0.5 AS INT) AS duration_ms 57FROM 58 multiuser_events; 59 60 61-- Calculate CPU usage during the Multiuser event of interest. 62 63-- Get all the scheduling slices. 64DROP VIEW IF EXISTS sp_sched; 65CREATE VIEW sp_sched AS 66SELECT ts, dur, cpu, utid 67FROM sched; 68-- Get all the cpu frequency slices. 69DROP VIEW IF EXISTS sp_frequency; 70CREATE VIEW sp_frequency AS 71SELECT 72 ts, 73 lead(ts) OVER (PARTITION BY track_id ORDER BY ts) - ts as dur, 74 cpu, 75 value as freq_khz 76FROM counter 77JOIN cpu_counter_track ON counter.track_id = cpu_counter_track.id; 78-- Create the span joined table which combines cpu frequency with scheduling slices. 79DROP TABLE IF EXISTS sched_with_frequency; 80CREATE VIRTUAL TABLE sched_with_frequency 81USING SPAN_JOIN(sp_sched PARTITIONED cpu, sp_frequency PARTITIONED cpu); 82 83-- Calculate the CPU cycles spent per process during the duration. 84DROP VIEW IF EXISTS cpu_usage_all; 85CREATE VIEW cpu_usage_all AS 86SELECT 87 process.uid / 100000 AS user_id, 88 process.name AS process_name, 89 SUM(dur * freq_khz) / 1e9 AS cpu_kcycles 90FROM 91 sched_with_frequency 92 JOIN thread USING (utid) 93 JOIN process USING (upid) 94WHERE 95 ts >= (SELECT event_start_time_ns FROM multiuser_events) 96 AND 97 ts <= (SELECT event_end_time_ns FROM multiuser_events) 98GROUP BY upid, process.name 99ORDER BY cpu_kcycles DESC; 100 101-- Get the data from cpu_usage_all, but also with the percentage. 102DROP VIEW IF EXISTS cpu_usage; 103CREATE VIEW cpu_usage AS 104SELECT 105 user_id, 106 process_name, 107 process_name || ":" || (CASE WHEN user_id = 0 THEN "system" ELSE "secondary" END) AS identifier, 108 CAST(cpu_kcycles / 1e3 AS INT) AS cpu_mcycles, 109 cpu_kcycles / (SELECT SUM(cpu_kcycles) FROM cpu_usage_all) * 100 AS cpu_percentage 110FROM 111 cpu_usage_all 112ORDER BY cpu_mcycles DESC LIMIT 25; 113 114 115-- Record the output for populating the proto. 116DROP VIEW IF EXISTS {{output_table_name}}; 117CREATE VIEW {{output_table_name}} AS 118SELECT AndroidMultiuserMetric_EventData( 119 'duration_ms', ( 120 SELECT duration_ms 121 FROM multiuser_timing 122 ), 123 'cpu_usage', ( 124 SELECT RepeatedField( 125 AndroidMultiuserMetric_EventData_CpuUsage( 126 'identifier', identifier, 127 'user_id', user_id, 128 'process_name', process_name, 129 'cpu_mcycles', cpu_mcycles, 130 'cpu_percentage', cpu_percentage 131 ) 132 ) 133 FROM cpu_usage 134 ) 135); 136