The SHOW EXEC STATISTICS statement outputs statistics information of executing query.
For details, see Outputting Statistics Information of Server.
SHOW EXEC STATISTICS[ ALL]
The following example shows the result of exeucting query on demodb.
-- set session variable @collect_exec_stats as 1 to start collecting the statistical information.
SET @collect_exec_stats = 1;
SELECT * FROM db_class;
...
-- print the statistical information of the data pages.
SHOW EXEC STATISTICS;
variable value
============================================
'data_page_fetches' 332
'data_page_dirties' 85
'data_page_ioreads' 18
'data_page_iowrites' 28
SELECT * FROM db_index;
...
-- print all of the statistical information.
SHOW EXEC STATISTICS ALL;
variable value
============================================
'file_creates' 0
'file_removes' 0
'file_ioreads' 6
'file_iowrites' 0
'file_iosynches' 0
'data_page_fetches' 548
'data_page_dirties' 34
'data_page_ioreads' 6
'data_page_iowrites' 0
'data_page_victims' 0
'data_page_iowrites_for_replacement' 0
'log_page_ioreads' 0
'log_page_iowrites' 0
'log_append_records' 0
'log_checkpoints' 0
'log_wals' 0
'page_locks_acquired' 13
'object_locks_acquired' 9
'page_locks_converted' 0
'object_locks_converted' 0
'page_locks_re-requested' 0
'object_locks_re-requested' 8
'page_locks_waits' 0
'object_locks_waits' 0
'tran_commits' 3
'tran_rollbacks' 0
'tran_savepoints' 0
'tran_start_topops' 6
'tran_end_topops' 6
'tran_interrupts' 0
'btree_inserts' 0
'btree_deletes' 0
'btree_updates' 0
'btree_covered' 0
'btree_noncovered' 2
'btree_resumes' 0
'query_selects' 4
'query_inserts' 0
'query_deletes' 0
'query_updates' 0
'query_sscans' 2
'query_iscans' 4
'query_lscans' 0
'query_setscans' 2
'query_methscans' 0
'query_nljoins' 2
'query_mjoins' 0
'query_objfetches' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0