1-- Copyright (c) 2014, 2019, Oracle and/or its affiliates. All rights reserved. 2-- 3-- This program is free software; you can redistribute it and/or modify 4-- it under the terms of the GNU General Public License as published by 5-- the Free Software Foundation; version 2 of the License. 6-- 7-- This program is distributed in the hope that it will be useful, 8-- but WITHOUT ANY WARRANTY; without even the implied warranty of 9-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 10-- GNU General Public License for more details. 11-- 12-- You should have received a copy of the GNU General Public License 13-- along with this program; if not, write to the Free Software 14-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 15 16-- 17-- View: schema_table_statistics 18-- 19-- Statistics around tables. 20-- 21-- Ordered by the total wait time descending - top tables are most contended. 22-- 23-- mysql> SELECT * FROM schema_table_statistics\G 24-- *************************** 1. row *************************** 25-- table_schema: sys 26-- table_name: sys_config 27-- total_latency: 0 ps 28-- rows_fetched: 0 29-- fetch_latency: 0 ps 30-- rows_inserted: 0 31-- insert_latency: 0 ps 32-- rows_updated: 0 33-- update_latency: 0 ps 34-- rows_deleted: 0 35-- delete_latency: 0 ps 36-- io_read_requests: 8 37-- io_read: 2.28 KiB 38-- io_read_latency: 727.32 us 39-- io_write_requests: 0 40-- io_write: 0 bytes 41-- io_write_latency: 0 ps 42-- io_misc_requests: 10 43-- io_misc_latency: 126.88 us 44-- 45 46CREATE OR REPLACE 47 ALGORITHM = TEMPTABLE 48 DEFINER = 'mysql.sys'@'localhost' 49 SQL SECURITY INVOKER 50VIEW schema_table_statistics ( 51 table_schema, 52 table_name, 53 total_latency, 54 rows_fetched, 55 fetch_latency, 56 rows_inserted, 57 insert_latency, 58 rows_updated, 59 update_latency, 60 rows_deleted, 61 delete_latency, 62 io_read_requests, 63 io_read, 64 io_read_latency, 65 io_write_requests, 66 io_write, 67 io_write_latency, 68 io_misc_requests, 69 io_misc_latency 70) AS 71SELECT pst.object_schema AS table_schema, 72 pst.object_name AS table_name, 73 format_pico_time(pst.sum_timer_wait) AS total_latency, 74 pst.count_fetch AS rows_fetched, 75 format_pico_time(pst.sum_timer_fetch) AS fetch_latency, 76 pst.count_insert AS rows_inserted, 77 format_pico_time(pst.sum_timer_insert) AS insert_latency, 78 pst.count_update AS rows_updated, 79 format_pico_time(pst.sum_timer_update) AS update_latency, 80 pst.count_delete AS rows_deleted, 81 format_pico_time(pst.sum_timer_delete) AS delete_latency, 82 fsbi.count_read AS io_read_requests, 83 format_bytes(fsbi.sum_number_of_bytes_read) AS io_read, 84 format_pico_time(fsbi.sum_timer_read) AS io_read_latency, 85 fsbi.count_write AS io_write_requests, 86 format_bytes(fsbi.sum_number_of_bytes_write) AS io_write, 87 format_pico_time(fsbi.sum_timer_write) AS io_write_latency, 88 fsbi.count_misc AS io_misc_requests, 89 format_pico_time(fsbi.sum_timer_misc) AS io_misc_latency 90 FROM performance_schema.table_io_waits_summary_by_table AS pst 91 LEFT JOIN x$ps_schema_table_statistics_io AS fsbi 92 ON pst.object_schema = fsbi.table_schema 93 AND pst.object_name = fsbi.table_name 94 ORDER BY pst.sum_timer_wait DESC; 95