并行执行入门实践

/* MySQL */
SET GLOBAL parallel_servers_target = MIN_CPU * 20;

/* Oracle */
ALTER SYSTEM SET parallel_servers_target = MIN_CPU * 20;

-- open_dt 表示算子从 Open 到 Close 的时间间隔
-- row_dt 表示算子从吐出第一行，到吐出 OB_ITER_END 的时间间隔
-- 如果值为 NULL，表示没有吐出过第一行或者没有吐出过 OB_ITER_END

-- 请给慢 SQL 增加一个 Hint： /*+ monitor */
-- 执行增加 Hint 后的慢 SQL，可以得到 Trace_id，替换下面的 Yxxxxxxxxx，执行下面的 SQL

-- MySQL 租户：
-- 汇总：
SELECT op_id, op, rows, rescan, threads, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt,  open_time, close_time,  first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad('', plan_depth, ' '), plan_operation) op, sum(output_rows) rows, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by  plan_line_id, plan_operation order by plan_line_id
) a;

-- 明细
SELECT op_id, thread, op, rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time  FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad('', plan_depth, ' '), plan_operation) op, output_rows rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time from oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, PROCESS_NAME
) a;

-- Oracle 租户：
--说明：open_dt 表示算子从 Open 到 Close 的时间间隔
--说明：row_dt 表示算子从吐出第一行，到吐出 OB_ITER_END 的时间间隔
--说明：如果值为 NULL，表示没有突出过第一行或者没有突出过 OB_ITER_END

-- 汇总
SELECT op_id, op, output_rows, rescan,threads ,(close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt,  open_time, close_time,  first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad(' ', max(plan_depth), ' '), plan_operation) op, sum(output_rows) output_rows, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by  plan_line_id, plan_operation,plan_depth order by plan_line_id
) a;

-- 明细
SELECT op_id, thread, op, output_rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad(' ', plan_depth, ' '), plan_operation) op, output_rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time  from sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, process_name
) a;