Added sys.optimizer_switch_on() and sys.optimizer_switch_off()

These are helpful tools to quickly see what optimizer switch options
are on or off.  The different options are displayed alphabetically

2 files changed, 70 insertions, 0 deletions

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 ;
+