From b3f04a6488dc493eca8908d40e4956beba49c378 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Wed, 4 Jan 2023 18:52:18 +0100 Subject: optimizer_switch stored procedures in sys schema. Split optimizer_switch into individual option names and values, and return it as result set. --- .../suite/sysschema/r/all_sys_objects_exist.result | 3 + .../suite/sysschema/r/optimizer_switch.result | 40 +++++++++++++ mysql-test/suite/sysschema/t/optimizer_switch.test | 2 + scripts/sys_schema/CMakeLists.txt | 1 + scripts/sys_schema/procedures/optimizer_switch.sql | 69 ++++++++++++++++++++++ 5 files changed, 115 insertions(+) create mode 100644 mysql-test/suite/sysschema/r/optimizer_switch.result create mode 100644 mysql-test/suite/sysschema/t/optimizer_switch.test create mode 100644 scripts/sys_schema/procedures/optimizer_switch.sql diff --git a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result index 6dddd8a186c..4c1bf311450 100644 --- a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result +++ b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result @@ -129,6 +129,9 @@ version_patch FUNCTION create_synonym_db PROCEDURE diagnostics PROCEDURE execute_prepared_stmt PROCEDURE +optimizer_switch_choice PROCEDURE +optimizer_switch_off PROCEDURE +optimizer_switch_on PROCEDURE ps_setup_disable_background_threads PROCEDURE ps_setup_disable_consumer PROCEDURE ps_setup_disable_instrument PROCEDURE diff --git a/mysql-test/suite/sysschema/r/optimizer_switch.result b/mysql-test/suite/sysschema/r/optimizer_switch.result new file mode 100644 index 00000000000..017276fc4b8 --- /dev/null +++ b/mysql-test/suite/sysschema/r/optimizer_switch.result @@ -0,0 +1,40 @@ +call sys.optimizer_switch_on(); +option opt +condition_pushdown_for_derived on +condition_pushdown_for_subquery on +condition_pushdown_from_having on +derived_merge on +derived_with_keys on +exists_to_in on +extended_keys on +firstmatch on +index_condition_pushdown on +index_merge on +index_merge_intersection on +index_merge_sort_union on +index_merge_union on +in_to_exists on +join_cache_bka on +join_cache_hashed on +join_cache_incremental on +loosescan on +materialization on +optimize_join_buffer_size on +orderby_uses_equalities on +outer_join_with_cache on +partial_match_rowid_merge on +partial_match_table_scan on +rowid_filter on +semijoin on +semijoin_with_cache on +split_materialized on +subquery_cache on +table_elimination on +call sys.optimizer_switch_off(); +option opt +engine_condition_pushdown off +index_merge_sort_intersection off +mrr off +mrr_cost_based off +mrr_sort_keys off +not_null_range_scan off diff --git a/mysql-test/suite/sysschema/t/optimizer_switch.test b/mysql-test/suite/sysschema/t/optimizer_switch.test new file mode 100644 index 00000000000..b4d527e5519 --- /dev/null +++ b/mysql-test/suite/sysschema/t/optimizer_switch.test @@ -0,0 +1,2 @@ +call sys.optimizer_switch_on(); +call sys.optimizer_switch_off(); diff --git a/scripts/sys_schema/CMakeLists.txt b/scripts/sys_schema/CMakeLists.txt index ccb268cc4fd..dc023174fc7 100644 --- a/scripts/sys_schema/CMakeLists.txt +++ b/scripts/sys_schema/CMakeLists.txt @@ -130,6 +130,7 @@ ${CMAKE_CURRENT_SOURCE_DIR}/views/p_s/session_ssl_status.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/create_synonym_db.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/execute_prepared_stmt.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/diagnostics.sql +${CMAKE_CURRENT_SOURCE_DIR}/procedures/optimizer_switch.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_statement_avg_latency_histogram.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_statement_digest.sql ${CMAKE_CURRENT_SOURCE_DIR}/procedures/ps_trace_thread.sql diff --git a/scripts/sys_schema/procedures/optimizer_switch.sql b/scripts/sys_schema/procedures/optimizer_switch.sql new file mode 100644 index 00000000000..febeabc1208 --- /dev/null +++ b/scripts/sys_schema/procedures/optimizer_switch.sql @@ -0,0 +1,69 @@ +-- Copyright (C) 2023, MariaDB +-- +-- 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 + +DROP PROCEDURE IF EXISTS optimizer_switch_choice; +DROP PROCEDURE IF EXISTS optimizer_switch_on; +DROP PROCEDURE IF EXISTS optimizer_switch_off; +DELIMITER $$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_choice(IN on_off VARCHAR(3)) +COMMENT 'return @@optimizer_switch options as a result set for easier readability' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + DECLARE tmp VARCHAR(1024); + DECLARE opt VARCHAR(1024); + DECLARE start INT; + DECLARE end INT; + DECLARE pos INT; + set tmp=concat(@@optimizer_switch,","); + CREATE OR REPLACE TEMPORARY TABLE tmp_opt_switch (a varchar(64), opt CHAR(3)) character set latin1 engine=heap; + set start=1; + FIND_OPTIONS: + LOOP + set pos= INSTR(SUBSTR(tmp, start), ","); + if (pos = 0) THEN + LEAVE FIND_OPTIONS; + END IF; + set opt= MID(tmp, start, pos-1); + set end= INSTR(opt, "="); + insert into tmp_opt_switch values(LEFT(opt,end-1),SUBSTR(opt,end+1)); + set start=start + pos; + END LOOP; + SELECT t.a as "option",t.opt from tmp_opt_switch as t where t.opt = on_off order by a; + DROP TEMPORARY TABLE tmp_opt_switch; +END$$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_on() +COMMENT 'return @@optimizer_switch options that are on' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + call optimizer_switch_choice("on"); +END$$ + +CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE optimizer_switch_off() +COMMENT 'return @@optimizer_switch options that are off' +SQL SECURITY INVOKER +NOT DETERMINISTIC +CONTAINS SQL +BEGIN + call optimizer_switch_choice("off"); +END$$ + +DELIMITER ; + -- cgit v1.2.1