xref: /external/perfetto/src/trace_processor/metrics/sql/android/android_startup.sql

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

-- Create the base tables and views containing the launch spans.
SELECT RUN_METRIC('android/startup/launches.sql');
SELECT RUN_METRIC('android/startup/hsc.sql');
SELECT RUN_METRIC('android/process_metadata.sql');

-- Create the base CPU span join table.
SELECT RUN_METRIC('android/android_cpu_agg.sql');

-- Create a span join safe launches view; since both views
-- being span joined have an "id" column, we need to rename
-- the id column for launches to disambiguate the two.
DROP VIEW IF EXISTS launches_span_join_safe;
CREATE VIEW launches_span_join_safe AS
SELECT ts, dur, id AS launch_id
FROM launches;

-- Span join the CPU table with the launches table to get the
-- breakdown per-cpu.
DROP TABLE IF EXISTS cpu_freq_sched_per_thread_per_launch;
CREATE VIRTUAL TABLE cpu_freq_sched_per_thread_per_launch
USING SPAN_JOIN(
  launches_span_join_safe,
  cpu_freq_sched_per_thread PARTITIONED cpu
);

SELECT RUN_METRIC('android/cpu_info.sql');

DROP VIEW IF EXISTS mcycles_per_core_type_per_launch;
CREATE VIEW mcycles_per_core_type_per_launch AS
SELECT
  launch_id,
  IFNULL(core_type_per_cpu.core_type, 'unknown') AS core_type,
  CAST(SUM(dur * freq_khz / 1000) / 1e9 AS INT) AS mcycles
FROM cpu_freq_sched_per_thread_per_launch
LEFT JOIN core_type_per_cpu USING (cpu)
WHERE utid != 0
GROUP BY 1, 2;

-- Slices for forked processes. Never present in hot starts.
-- Prefer this over process start_ts, since the process might have
-- been preforked.
DROP VIEW IF EXISTS zygote_fork_slice;
CREATE VIEW zygote_fork_slice AS
SELECT slice.ts, slice.dur, STR_SPLIT(slice.name, ": ", 1) AS process_name
FROM slice WHERE name GLOB 'Start proc: *';

DROP TABLE IF EXISTS zygote_forks_by_id;
CREATE TABLE zygote_forks_by_id AS
SELECT
  launches.id,
  zygote_fork_slice.ts,
  zygote_fork_slice.dur
FROM zygote_fork_slice
JOIN launches
ON (launches.ts < zygote_fork_slice.ts
    AND zygote_fork_slice.ts + zygote_fork_slice.dur < launches.ts_end
    AND zygote_fork_slice.process_name = launches.package
);

DROP VIEW IF EXISTS launch_main_threads;
CREATE VIEW launch_main_threads AS
SELECT
  launches.ts AS ts,
  launches.dur AS dur,
  launches.id AS launch_id,
  thread.utid AS utid
FROM launches
JOIN launch_processes ON launches.id = launch_processes.launch_id
JOIN process USING(upid)
JOIN thread ON (process.upid = thread.upid AND process.pid = thread.tid)
ORDER BY ts;

DROP VIEW IF EXISTS thread_state_extended;
CREATE VIEW thread_state_extended AS
SELECT
  ts,
  IIF(dur = -1, (SELECT end_ts FROM trace_bounds), dur) AS dur,
  utid,
  state
FROM thread_state;

DROP TABLE IF EXISTS main_thread_state;
CREATE VIRTUAL TABLE main_thread_state
USING SPAN_JOIN(
  launch_main_threads PARTITIONED utid,
  thread_state_extended PARTITIONED utid);

DROP VIEW IF EXISTS launch_by_thread_state;
CREATE VIEW launch_by_thread_state AS
SELECT launch_id, state, SUM(dur) AS dur
FROM main_thread_state
GROUP BY 1, 2;

-- Tracks all main thread process threads.
DROP VIEW IF EXISTS launch_threads;
CREATE VIEW launch_threads AS
SELECT
  launches.id AS launch_id,
  launches.ts AS ts,
  launches.dur AS dur,
  thread.utid AS utid,
  thread.name AS thread_name
FROM launches
JOIN launch_processes ON (launches.id = launch_processes.launch_id)
JOIN thread ON (launch_processes.upid = thread.upid);

-- Tracks all slices for the main process threads
DROP VIEW IF EXISTS main_process_slice_unaggregated;
CREATE VIEW main_process_slice_unaggregated AS
SELECT
  launch_threads.launch_id AS launch_id,
  launch_threads.utid AS utid,
  launch_threads.thread_name AS thread_name,
  slice.id AS slice_id,
  slice.arg_set_id AS arg_set_id,
  slice.name AS slice_name,
  slice.ts AS slice_ts,
  slice.dur AS slice_dur
FROM launch_threads
JOIN thread_track USING (utid)
JOIN slice ON (
  slice.track_id = thread_track.id
  AND slice.ts BETWEEN launch_threads.ts AND launch_threads.ts + launch_threads.dur)
WHERE slice.name IN (
  'PostFork',
  'ActivityThreadMain',
  'bindApplication',
  'activityStart',
  'activityRestart',
  'activityResume',
  'inflate',
  'ResourcesManager#getResources',
  'binder transaction')
  OR slice.name GLOB 'performResume:*'
  OR slice.name GLOB 'performCreate:*'
  OR slice.name GLOB 'location=* status=* filter=* reason=*'
  OR slice.name GLOB 'OpenDexFilesFromOat*'
  OR slice.name GLOB 'VerifyClass*'
  OR slice.name GLOB 'Choreographer#doFrame*'
  OR slice.name GLOB 'JIT compiling*'
  OR slice.name GLOB '*mark sweep GC'
  OR slice.name GLOB '*concurrent copying GC'
  OR slice.name GLOB '*semispace GC';

DROP TABLE IF EXISTS main_process_slice;
CREATE TABLE main_process_slice AS
SELECT
  launch_id,
  CASE
    WHEN slice_name GLOB 'OpenDexFilesFromOat*' THEN 'OpenDexFilesFromOat'
    WHEN slice_name GLOB 'VerifyClass*' THEN 'VerifyClass'
    WHEN slice_name GLOB 'JIT compiling*' THEN 'JIT compiling'
    WHEN slice_name GLOB '*mark sweep GC' THEN 'GC'
    WHEN slice_name GLOB '*concurrent copying GC' THEN 'GC'
    WHEN slice_name GLOB '*semispace GC' THEN 'GC'
    ELSE slice_name
  END AS name,
  AndroidStartupMetric_Slice(
    'dur_ns', SUM(slice_dur),
    'dur_ms', SUM(slice_dur) / 1e6
  ) AS slice_proto
FROM main_process_slice_unaggregated
GROUP BY 1, 2;

DROP TABLE IF EXISTS report_fully_drawn_per_launch;
CREATE TABLE report_fully_drawn_per_launch AS
WITH report_fully_drawn_launch_slices AS (
  SELECT
    launches.id AS launch_id,
    launches.ts AS launch_ts,
    min(slice.ts) as report_fully_drawn_ts
  FROM launches
  JOIN launch_processes ON (launches.id = launch_processes.launch_id)
  JOIN thread ON (launch_processes.upid = thread.upid)
  JOIN thread_track USING (utid)
  JOIN slice ON (
    slice.track_id = thread_track.id
    AND slice.ts >= launches.ts)
  WHERE slice.name GLOB 'reportFullyDrawn*'
  GROUP BY launches.id
)
SELECT
  launch_id,
  report_fully_drawn_ts - launch_ts as report_fully_drawn_dur
FROM report_fully_drawn_launch_slices;

DROP VIEW IF EXISTS to_event_protos;
CREATE VIEW to_event_protos AS
SELECT
  slice.name as slice_name,
  launch_id,
  AndroidStartupMetric_Slice(
    'dur_ns', slice.ts - l.ts,
    'dur_ms', (slice.ts - l.ts) / 1e6
  ) as slice_proto
FROM launch_main_threads l
JOIN thread_track USING (utid)
JOIN slice ON (
  slice.track_id = thread_track.id
  AND slice.ts BETWEEN l.ts AND l.ts + l.dur);

DROP VIEW IF EXISTS gc_slices;
CREATE VIEW gc_slices AS
  SELECT
    slice_ts AS ts,
    slice_dur AS dur,
    utid,
    launch_id
  FROM main_process_slice_unaggregated
  WHERE (
    slice_name GLOB '*mark sweep GC'
    OR slice_name GLOB '*concurrent copying GC'
    OR slice_name GLOB '*semispace GC');

DROP TABLE IF EXISTS gc_slices_by_state;
CREATE VIRTUAL TABLE gc_slices_by_state
USING SPAN_JOIN(gc_slices PARTITIONED utid, thread_state_extended PARTITIONED utid);

DROP TABLE IF EXISTS gc_slices_by_state_materialized;
CREATE TABLE gc_slices_by_state_materialized AS
SELECT launch_id, SUM(dur) as sum_dur
FROM gc_slices_by_state
WHERE state = 'Running'
GROUP BY launch_id;

DROP TABLE IF EXISTS launch_threads_cpu;
CREATE VIRTUAL TABLE launch_threads_cpu
USING SPAN_JOIN(launch_threads PARTITIONED utid, thread_state_extended PARTITIONED utid);

DROP TABLE IF EXISTS launch_threads_cpu_materialized;
CREATE TABLE launch_threads_cpu_materialized AS
SELECT launch_id, SUM(dur) as sum_dur
FROM launch_threads_cpu
WHERE thread_name = 'Jit thread pool' AND state = 'Running'
GROUP BY launch_id;

DROP TABLE IF EXISTS activity_names_materialized;
CREATE TABLE activity_names_materialized AS
SELECT launch_id, slice_name, slice_ts
FROM main_process_slice_unaggregated
WHERE (slice_name GLOB 'performResume:*' OR slice_name GLOB 'performCreate:*');

DROP TABLE IF EXISTS jit_compiled_methods_materialized;
CREATE TABLE jit_compiled_methods_materialized AS
SELECT
  launch_id,
  COUNT(1) as count
FROM main_process_slice_unaggregated
WHERE
  slice_name GLOB 'JIT compiling*'
  AND thread_name = 'Jit thread pool'
GROUP BY launch_id;

DROP TABLE IF EXISTS long_binder_transactions;
CREATE TABLE long_binder_transactions AS
SELECT
  s.slice_id,
  s.launch_id,
  s.slice_dur,
  s.thread_name,
  EXTRACT_ARG(s.arg_set_id, 'destination name') AS destination_thread,
  process.name AS destination_process,
  EXTRACT_ARG(s.arg_set_id, 'flags') AS flags,
  EXTRACT_ARG(s.arg_set_id, 'code') AS code,
  EXTRACT_ARG(s.arg_set_id, 'data_size') AS data_size
FROM
  main_process_slice_unaggregated s
JOIN process ON (EXTRACT_ARG(s.arg_set_id, 'destination process') = process.pid)
WHERE
  s.slice_name = 'binder transaction' AND
  s.slice_dur >= 5e7;

SELECT CREATE_FUNCTION(
  'MAIN_PROCESS_SLICE_PROTO(launch_id LONG, name STRING)',
  'PROTO', '
    SELECT slice_proto
    FROM main_process_slice s
    WHERE s.launch_id = $launch_id AND name GLOB $name
    LIMIT 1
  ');

DROP VIEW IF EXISTS startup_view;
CREATE VIEW startup_view AS
SELECT
  AndroidStartupMetric_Startup(
    'startup_id', launches.id,
    'package_name', launches.package,
    'process_name', (
      SELECT p.name
      FROM launch_processes lp
      JOIN process p USING (upid)
      WHERE lp.launch_id = launches.id
      LIMIT 1
    ),
    'process', (
      SELECT m.metadata
      FROM process_metadata m
      JOIN launch_processes p USING (upid)
      WHERE p.launch_id = launches.id
      LIMIT 1
    ),
    'activities', (
      SELECT RepeatedField(AndroidStartupMetric_Activity(
        'name', (SELECT STR_SPLIT(s.slice_name, ':', 1)),
        'method', (SELECT STR_SPLIT(s.slice_name, ':', 0)),
        'ts_method_start', s.slice_ts
      ))
      FROM activity_names_materialized s
      WHERE s.launch_id = launches.id
    ),
    'long_binder_transactions', (
      SELECT RepeatedField(AndroidStartupMetric_BinderTransaction(
        'duration', AndroidStartupMetric_Slice(
          'dur_ns', lbt.slice_dur,
          'dur_ms', lbt.slice_dur / 1e6
        ),
        'thread', lbt.thread_name,
        'destination_thread', lbt.destination_thread,
        'destination_process', lbt.destination_process,
        'flags', lbt.flags,
        'code', lbt.code,
        'data_size', lbt.data_size
      ))
      FROM long_binder_transactions lbt
      WHERE lbt.launch_id = launches.id
    ),
    'zygote_new_process', EXISTS(SELECT TRUE FROM zygote_forks_by_id WHERE id = launches.id),
    'activity_hosting_process_count', (
      SELECT COUNT(1) FROM launch_processes p
      WHERE p.launch_id = launches.id
    ),
    'event_timestamps', AndroidStartupMetric_EventTimestamps(
      'intent_received', launches.ts,
      'first_frame', launches.ts_end
    ),
    'to_first_frame', AndroidStartupMetric_ToFirstFrame(
      'dur_ns', launches.dur,
      'dur_ms', launches.dur / 1e6,
      'main_thread_by_task_state', AndroidStartupMetric_TaskStateBreakdown(
        'running_dur_ns', IFNULL(
            (
            SELECT dur FROM launch_by_thread_state l
            WHERE l.launch_id = launches.id AND state = 'Running'
            ), 0),
        'runnable_dur_ns', IFNULL(
            (
            SELECT dur FROM launch_by_thread_state l
            WHERE l.launch_id = launches.id AND state = 'R'
            ), 0),
        'uninterruptible_sleep_dur_ns', IFNULL(
            (
            SELECT dur FROM launch_by_thread_state l
            WHERE l.launch_id = launches.id AND (state = 'D' or state = 'DK')
            ), 0),
        'interruptible_sleep_dur_ns', IFNULL(
            (
            SELECT dur FROM launch_by_thread_state l
            WHERE l.launch_id = launches.id AND state = 'S'
            ), 0)
      ),
      'mcycles_by_core_type', AndroidStartupMetric_McyclesByCoreType(
        'little', (
          SELECT mcycles
          FROM mcycles_per_core_type_per_launch m
          WHERE m.launch_id = launches.id AND m.core_type = 'little'
        ),
        'big', (
          SELECT mcycles
          FROM mcycles_per_core_type_per_launch m
          WHERE m.launch_id = launches.id AND m.core_type = 'big'
        ),
        'bigger', (
          SELECT mcycles
          FROM mcycles_per_core_type_per_launch m
          WHERE m.launch_id = launches.id AND m.core_type = 'bigger'
        ),
        'unknown', (
          SELECT mcycles
          FROM mcycles_per_core_type_per_launch m
          WHERE m.launch_id = launches.id AND m.core_type = 'unknown'
        )
      ),
      'to_post_fork', (
        SELECT slice_proto
        FROM to_event_protos p
        WHERE p.launch_id = launches.id AND slice_name = 'PostFork'
      ),
      'to_activity_thread_main', (
        SELECT slice_proto
        FROM to_event_protos p
        WHERE p.launch_id = launches.id AND slice_name = 'ActivityThreadMain'
      ),
      'to_bind_application', (
        SELECT slice_proto
        FROM to_event_protos p
        WHERE p.launch_id = launches.id AND slice_name = 'bindApplication'
      ),
      'other_processes_spawned_count', (
        SELECT COUNT(1) FROM process
        WHERE (process.name IS NULL OR process.name != launches.package)
        AND process.start_ts BETWEEN launches.ts AND launches.ts + launches.dur
      ),
      'time_activity_manager', (
        SELECT AndroidStartupMetric_Slice(
          'dur_ns', l.ts - launches.ts,
          'dur_ms', (l.ts - launches.ts) / 1e6
        )
        FROM launching_events l
        WHERE l.ts BETWEEN launches.ts AND launches.ts + launches.dur
      ),
      'time_post_fork', MAIN_PROCESS_SLICE_PROTO(launches.id, 'PostFork'),
      'time_activity_thread_main', MAIN_PROCESS_SLICE_PROTO(launches.id, 'ActivityThreadMain'),
      'time_bind_application', MAIN_PROCESS_SLICE_PROTO(launches.id, 'bindApplication'),
      'time_activity_start', MAIN_PROCESS_SLICE_PROTO(launches.id, 'activityStart'),
      'time_activity_resume', MAIN_PROCESS_SLICE_PROTO(launches.id, 'activityResume'),
      'time_activity_restart', MAIN_PROCESS_SLICE_PROTO(launches.id, 'activityRestart'),
      'time_choreographer', MAIN_PROCESS_SLICE_PROTO(launches.id, 'Choreographer#doFrame*'),
      'time_inflate', MAIN_PROCESS_SLICE_PROTO(launches.id, 'inflate'),
      'time_get_resources', MAIN_PROCESS_SLICE_PROTO(launches.id, 'ResourcesManager#getResources'),
      'time_dex_open', MAIN_PROCESS_SLICE_PROTO(launches.id, 'OpenDexFilesFromOat'),
      'time_verify_class', MAIN_PROCESS_SLICE_PROTO(launches.id, 'VerifyClass'),
      'time_gc_total', MAIN_PROCESS_SLICE_PROTO(launches.id, 'GC'),
      'time_before_start_process', (
        SELECT AndroidStartupMetric_Slice(
          'dur_ns', ts - launches.ts,
          'dur_ms', (ts - launches.ts) / 1e6
        )
        FROM zygote_forks_by_id z
        WHERE z.id = launches.id
      ),
      'time_during_start_process', (
        SELECT AndroidStartupMetric_Slice(
          'dur_ns', dur,
          'dur_ms', dur / 1e6
        )
        FROM zygote_forks_by_id z
        WHERE z.id = launches.id
      ),
      'jit_compiled_methods', (
        SELECT count
        FROM jit_compiled_methods_materialized s
        WHERE s.launch_id = launches.id
      ),
      'time_jit_thread_pool_on_cpu', (
        SELECT
          NULL_IF_EMPTY(AndroidStartupMetric_Slice(
            'dur_ns', sum_dur,
            'dur_ms', sum_dur / 1e6
          ))
        FROM launch_threads_cpu_materialized
        WHERE launch_id = launches.id
      ),
      'time_gc_on_cpu', (
        SELECT
          NULL_IF_EMPTY(AndroidStartupMetric_Slice(
            'dur_ns', sum_dur,
            'dur_ms', sum_dur / 1e6
          ))
        FROM gc_slices_by_state_materialized
        WHERE launch_id = launches.id
      )
    ),
    'hsc', (
      SELECT NULL_IF_EMPTY(AndroidStartupMetric_HscMetrics(
        'full_startup', (
          SELECT AndroidStartupMetric_Slice(
            'dur_ns', h.ts_total,
            'dur_ms', h.ts_total / 1e6
          )
          FROM hsc_based_startup_times h
          WHERE h.id = launches.id
        )
      ))
    ),
    'report_fully_drawn', (
      SELECT NULL_IF_EMPTY(AndroidStartupMetric_Slice(
        'dur_ns', report_fully_drawn_dur,
        'dur_ms', report_fully_drawn_dur / 1e6
      ))
      FROM report_fully_drawn_per_launch r
      WHERE r.launch_id = launches.id
    ),
    'optimization_status',(
      SELECT RepeatedField(AndroidStartupMetric_OptimizationStatus(
        'location', SUBSTR(STR_SPLIT(name, ' status=', 0), LENGTH('location=') + 1),
        'odex_status', STR_SPLIT(STR_SPLIT(name, ' status=', 1), ' filter=', 0),
        'compilation_filter', STR_SPLIT(STR_SPLIT(name, ' filter=', 1), ' reason=', 0),
        'compilation_reason', STR_SPLIT(name, ' reason=', 1)
      ))
      FROM main_process_slice s
      WHERE name GLOB 'location=* status=* filter=* reason=*'
    )
  ) as startup
FROM launches;

DROP VIEW IF EXISTS android_startup_event;
CREATE VIEW android_startup_event AS
SELECT
  'slice' as track_type,
  'Android App Startups' as track_name,
  l.ts as ts,
  l.dur as dur,
  l.package as slice_name
FROM launches l;

DROP VIEW IF EXISTS android_startup_output;
CREATE VIEW android_startup_output AS
SELECT
  AndroidStartupMetric(
    'startup', (
      SELECT RepeatedField(startup) FROM startup_view
    )
  );