1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
-- Copyright (c) 2014, 2025, Oracle and/or its affiliates.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
-- View: x$statement_analysis
--
-- Lists a normalized statement view with aggregated statistics,
-- mimics the MySQL Enterprise Monitor Query Analysis view,
-- ordered by the total execution time per normalized statement
--
-- mysql> select * from x$statement_analysis limit 1\G
-- *************************** 1. row ***************************
-- query: SELECT * FROM `schema_object_overview` SELECT `information_schema` . `routines` -- truncated
-- db: sys
-- full_scan: *
-- exec_count: 2
-- exec_secondary_count: 2
-- err_count: 0
-- warn_count: 0
-- total_latency: 16751388791000
-- max_latency: 16566171163000
-- avg_latency: 8375694395000
-- lock_latency: 16686483000000
-- rows_sent: 84
-- rows_sent_avg: 42
-- rows_examined: 20012
-- rows_examined_avg: 10006
-- rows_affected: 0
-- rows_affected_avg: 0
-- tmp_tables: 378
-- tmp_disk_tables: 66
-- rows_sorted: 168
-- sort_merge_passes: 0
-- digest: 54f9bd520f0bbf15db0c2ed93386bec9
-- first_seen: 2014-03-07 13:13:41
-- last_seen: 2014-03-07 13:13:48
--
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = 'mysql.sys'@'localhost'
SQL SECURITY INVOKER
VIEW x$statement_analysis (
query,
db,
full_scan,
exec_count,
exec_secondary_count,
err_count,
warn_count,
total_latency,
max_latency,
avg_latency,
lock_latency,
cpu_latency,
rows_sent,
rows_sent_avg,
rows_examined,
rows_examined_avg,
rows_affected,
rows_affected_avg,
tmp_tables,
tmp_disk_tables,
rows_sorted,
sort_merge_passes,
max_controlled_memory,
max_total_memory,
digest,
first_seen,
last_seen
) AS
SELECT DIGEST_TEXT AS query,
SCHEMA_NAME AS db,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
COUNT_SECONDARY AS exec_secondary_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
SUM_TIMER_WAIT AS total_latency,
MAX_TIMER_WAIT AS max_latency,
AVG_TIMER_WAIT AS avg_latency,
SUM_LOCK_TIME AS lock_latency,
SUM_CPU_TIME AS cpu_latency,
SUM_ROWS_SENT AS rows_sent,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
SUM_ROWS_EXAMINED AS rows_examined,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
SUM_ROWS_AFFECTED AS rows_affected,
ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
SUM_CREATED_TMP_TABLES AS tmp_tables,
SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
SUM_SORT_ROWS AS rows_sorted,
SUM_SORT_MERGE_PASSES AS sort_merge_passes,
MAX_CONTROLLED_MEMORY AS max_controlled_memory,
MAX_TOTAL_MEMORY AS max_total_memory,
DIGEST AS digest,
FIRST_SEEN AS first_seen,
LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC;
|
