MySQLWorkbenchPerformanceQuries4DBeaver.sql

-- -MySQL Workbench Performance Reports
-- --From Performance / Performance Reports
-- --(Administration Tab → Performance Section → Performance Reports)

-- ================================================================================
-- MEMORY
-- ================================================================================
-- Buffer pool
SHOW STATUS LIKE 'Innodb_buffer_pool_bytes%';

-- Top memory by event
select * from sys.`x$memory_global_by_current_bytes`;

-- Top memory by user
SELECT * FROM sys.`x$memory_by_user_by_current_bytes`;

-- Top memory by host
select * from sys.`x$memory_by_host_by_current_bytes`;

-- Top memory by thread
select * from sys.`x$memory_by_thread_by_current_bytes`;

-- ================================================================================
-- HOT SPOTS FOR I/O
-- ================================================================================

-- Top file I/O activity report
select * from sys.`x$io_global_by_file_by_bytes`;

-- Top I/O file by time
select * from sys.`x$io_global_by_file_by_latency`;

-- Top I/O by event category
select * from sys.`x$io_global_by_wait_by_bytes`;

-- Top I/O in time by event category
select * from sys.`x$io_global_by_wait_by_latency`;

-- Top I/O time by user thread
select * from sys.`x$io_by_thread_by_latency`;

-- ================================================================================
-- HIGH-COST SQL STATEMENTS
-- ================================================================================

-- Statement Analysis
select * from sys.`x$statement_analysis`;

-- Statements in highest 5 percent by runtime
select * from sys.`x$statements_with_runtimes_in_95th_percentile`;

-- Using temp tables
select * from sys.`statements_with_temp_tables`;

-- With sorting
select * from sys.`statements_with_sorting`;

-- Full table scans
select * from sys.`statements_with_full_table_scans`;

-- Errors or Warnings
select * from sys.`statements_with_errors_or_warnings`;

-- ================================================================================
-- DATABASE SCHEMA STATISTICS
-- ================================================================================

-- Schema Object Overview (High Overhead)
select * from sys.`schema_object_overview`;

-- Schema index statistics
select * from sys.`x$schema_index_statistics`;

-- Schema table statistics
select * from sys.`x$schema_table_statistics`;

-- Schema table statistics (with InnoDB Buffer)
select * from sys.`x$schema_table_statistics_with_buffer`;

-- Tables with full table scans
select * from sys.`schema_tables_with_full_table_scans`;

-- Unused indexes
select * from sys.`schema_unused_indexes`;

-- ================================================================================
-- WAIT EVENT TIMES EXPERT
-- ================================================================================

-- Global waits by time
select * from sys.`x$waits_global_by_latency`;

--  Waits by user by time
select * from sys.`x$waits_by_user_by_latency`;

-- Wait Classes by time
select * from sys.`x$wait_classes_global_by_latency`;

-- Wait Classes by Average Time
select * from sys.`x$wait_classes_global_by_avg_latency`;

-- ================================================================================
-- INNODB STATISTICS
-- ================================================================================

-- InnoDB Buffer Stats by schema
select * from sys.`x$innodb_buffer_stats_by_schema`;

-- InnoDB Buffer Stats by table
select * from sys.`x$innodb_buffer_stats_by_table`;

-- =============================================================================
-- USER RESOURCE USE
-- ================================================================================

-- Overview
select * from sys.`x$user_summary`;

-- I/O Statistics
select * from sys.`x$user_summary_by_file_io_type`;

SELECT * FROM sys.`x$user_summary_by_statement_type`;

-- ============================ END ============================================