Oracle 19c OCM讲义课程：应用SQL执行计划基线的案例

2023-10-20 16:04
文章标签 sql oracle 应用 案例 执行 计划 课程 19c database ocm 讲义 基线

本文主要是介绍Oracle 19c OCM讲义课程：应用SQL执行计划基线的案例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

使用SQL执行计划基线可以保证SQL的性能不下降，但实际生产中默认没有开启，这里是姚远老师在给OCM的学员授课中关于SQL执行计划基线的一个案例，大家可以借鉴一下。
在这里插入图片描述

关于号主，姚远：

  • Oracle ACE（Oracle和MySQL数据库方向）
  • 华为云最有价值专家
  • 《MySQL 8.0运维与优化》的作者
  • 拥有 Oracle 10g、12c和19c OCM等数十项数据库认证
  • 曾任IBM公司数据库部门经理
  • 20+年DBA经验，服务2万+客户
  • 精通C和Java，发明两项计算机专利

修改配置，采集SQL执行计划基线

Oracle 19c与SQL执行计划基线相关的默认参数值如下：

SQL> show parameter baseline
​
NAME             TYPE   VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean   FALSE
optimizer_use_sql_plan_baselines     boolean   TRUE
​

修改后的参数存放在视图DBA_SQL_MANAGEMENT_CONFIG中，检查一下：

COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32
​
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM   DBA_SQL_MANAGEMENT_CONFIG ;
​
PARAMETER_NAME       PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (TPCC)
AUTO_CAPTURE_SQL_TEXT
AUTO_SPM_EVOLVE_TASK     OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL   3600  -- 
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
PLAN_RETENTION_WEEKS     53  -- 53不用的计划会被清理
SPACE_BUDGET_PERCENT     10  -- 占用SYSAUX的空间不超过10%，超过在alert中报警
​
9 rows selected.
​

查看SQL执行计划基线的应用

先将一个索引改成不可见：

SQL> alter index tpcc.CUSTOMER_I1 invisible;
​
Index altered.

应用运行一段时间后，检查已经创建的SQL基线：

SQL> select count(distinct sql_handle),count(distinct plan_name),count(distinct SIGNATURE) from DBA_SQL_PLAN_BASELINES;
​
COUNT(DISTINCTSQL_HANDLE) COUNT(DISTINCTPLAN_NAME) COUNT(DISTINCTSIGNATURE)
------------------------- ------------------------ ------------------------30      30       30
​

可以看到为30个SQL建立了基线，都是ACCEPTED，因为每个SQL只有一个执行计划。

检查与这个索引相关的SQL的执行情况：

SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';
​
​
SQL_ID        EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
------------- ---------- -------------- ------------------------------
arykx3hpq9xsa      5102     1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea
​

可以看到它用到了一个SQL基线的执行计划，成本是1945，查看这个SQL基线的执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_2d858eea22dc0f79','SQL_PLAN_2v1cfx8jds3vt3a6ea7ea','basic') );
​
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SQL_2d858eea22dc0f79
SQL text: UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHERE C_W_ID = :B4AND C_D_ID = :B3 AND C_ID = :B2
--------------------------------------------------------------------------------
​
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_2v1cfx8jds3vt3a6ea7ea    Plan id: 980330474
Enabled: YES   Fixed: NO  Accepted: YES    Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
​
Plan hash value: 3529770744
​
----------------------------------------
| Id  | Operation   | Name        |
----------------------------------------
|   0 | UPDATE STATEMENT |         |
|   1 |  UPDATE    | CUSTOMER    |
|   2 |   INDEX SKIP SCAN| CUSTOMER_I2 |
----------------------------------------
​
22 rows selected.
​

可以看到这个SQL执行中使用了CUSTOMER_I2 索引，没有使用CUSTOMER_I1索引，因为CUSTOMER_I1这个索引被修改成了不可见。这种检查SQL执行计划的方法和在游标中查询SQL执行计划的方法得到同样的结果：

set pagesize 200
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('arykx3hpq9xsa'));

将这个索引改成可见：

alter index tpcc.CUSTOMER_I1 visible;

第二次执行应用程序，然后再检查这个SQL的执行情况：

SQL>  select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';
​
SQL_ID        EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
------------- ---------- -------------- ------------------------------
arykx3hpq9xsa      2376     1945 SQL_PLAN_2v1cfx8jds3vt3a6ea7ea
​

发现这个SQL的执行成本和使用执行计划基线仍然没有发生变化，检查这个SQL对应的执行计划基线：

col plan_name form a30
col signature forma 99999999999999999999999
select sql_handle,plan_name,signature,accepted,optimizer_cost  from DBA_SQL_PLAN_BASELINES 
where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');
​
SQL_HANDLE           PLAN_NAME             SIGNATURE ACC OPTIMIZER_COST
------------------------------ ------------------------------ ------------------------ --- --------------
SQL_2d858eea22dc0f79         SQL_PLAN_2v1cfx8jds3vt341d91fc     3280185039867613049 NO    3
SQL_2d858eea22dc0f79         SQL_PLAN_2v1cfx8jds3vt3a6ea7ea     3280185039867613049 YES       1945
​

发现这个SQL对应了两个基线，期中成本小到3的基线居然是没有被接受的！
在这里插入图片描述

手工进化基线

因为SQL基线的进化任务要到晚上维护窗口时才会执行，新的基线没有进化成可接受的，所以SQL执行时不会选择这个基线，我们可以手工对这个SQL基线进行进化：

VARIABLE cnt NUMBER
VARIABLE tk_name VARCHAR2(50)
VARIABLE exe_name VARCHAR2(50)
VARIABLE evol_out CLOB
​
begin:tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_2d858eea22dc0f79',  plan_name  => 'SQL_PLAN_2v1cfx8jds3vt341d91fc');
end;
/SELECT :tk_name FROM DUAL;
SQL> SELECT :tk_name FROM DUAL;
​
:TK_NAME
-----------------------
TASK_1551
​
EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); 
SELECT :exe_name FROM DUAL;
:EXE_NAME
--------------------------------
EXEC_3452
​

检查这个进化计划的执行报告：

EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;
SQL> SELECT :evol_out FROM DUAL;
​
:EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
​Task Information:---------------------------------------------Task Name        : TASK_1551
​Task Owner        : SYSExecution Name       : EXEC_3452Execution Type       : SPM EVOLVEScope          : COMPREHENSIVE
​Status         : COMPLETEDStarted        : 09/08/2023 15:21:50Finished        : 09/08/2023 15:21:50Last Updated        : 09/08/2023 15:21:50Global Time Limit    : 2147483646Per-Plan Time Limit  : UNUSEDNumber of Errors     : 0
-----------------------------------------
SUMMARY SECTION
--------------------------------------------------
7  Number of plans processed  : 1Number of findings       : 1Number of recommendations  : 1Number of errors       : 0
--------------------------------------------------------
-------------------------------------
​
DETAILS SECTION
---------------------------------------------
----------------------------------------
--------Object ID      : 2
​Test Plan Name     : SQL_PLAN_2v1cfx8jds3vt341d91fcBase Plan Name     : SQL_PLAN_2v1cfx8jds3vt3a6ea7eaSQL Handle      : SQL_2d858eea22dc0f79
​Parsing Schema     : TPCC
​Test Plan Creator  : SYS
​SQL Text      : UPDATE CUSTOMER SET C_BALANCE = C_BALANCE + :B1 WHEREC_W_ID = :B4 AND C_D_ID= :B3 AND C_ID = :B2
​
Bind Variables:
-----------------------------2  -  (NUMBER):  3
​3  -  (NUMBER):  1
​4  -  (NUMBER):  2285
​
​
Execution Statistics:
-----------------------------Base PlanTest Plan--------------------
--------  ----------------------------Elapsed Time (s):  .00064    .000002CPU Time (s):      .000638    .000002Buffer Gets:      117    0Optimizer Cost:    1945    3Disk Reads:      0    0Direct Writes:     0    0Rows Processed:    0    0Executions:      10    10
​
​
FINDINGS SECTION
-----------------------------------------
Findings (1):
-----------------------------1. The plan was verified in 0.12000 seconds. It passed the benefit criterionbecause its verified performance was 390.90001 times better than that ofthe baseline plan.
​
Recommendation:
-----------------------------Consider accepting the plan. Execute
​dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2,task_owner => 'SYS');
​
EXPLAIN PLANS SECTION
---------------------------------------------
​
Baseline Plan
-----------------------------Plan Id    : 9217Plan Hash Value  : 980330474
​
-------------------------------------------
| Id  | Operation     | Name   | Rows | Bytes | Cost | Time     |
---------------------------------------------------
------------------------
|   0 | UPDATE STATEMENT   |     |    1 |    15 | 1945 | 00:00:01 |
|   1 |   UPDATE     | CUSTOMER   |  |  |    |         |
| * 2 |    INDEX SKIP SCAN | CUSTOMER_I2 |   1 |    15 | 1944 | 00:00:01 |
-----------------------------------------------
​
Predicate Information (identified by operation id)
:
------------------------------------------
* 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)
* 2 - filter("C_ID"=:B2 AND "C_W_ID"=:B4 AND "C_D_ID"=:B3)
​
​
Test Plan
-----------------------------Plan Id    : 9218Plan Hash Value  : 874353148
​
----------------------------------------------------
| Id  | Operation       | Name     | Rows | Bytes | Cost | Time     |
--------------------------------------------------------
|   0 | UPDATE STATEMENT     |       |  1 |    15|    3 | 00:00:01 |
|   1 |   UPDATE       | CUSTOMER   |    |    |   |      |
| * 2 |    INDEX UNIQUE SCAN | CUSTOMER_I1 |    1 |    15 |    2 | 00:00:01 |
--------------------------------------------
​
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C_W_ID"=:B4 AND "C_D_ID"=:B3 AND "C_ID"=:B2)
​
--------------------------------------------------
​
​

根据报告中的建议，执行下面的SQL接受第二个基线：

SQL> exec dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_1551', object_id => 2, task_owner => 'SYS');
​
PL/SQL procedure successfully completed.
​

再次检查这个SQL对应的执行计划基线：

col plan_name form a30
col signature forma 99999999999999999999999
select sql_handle,plan_name,signature,accepted,optimizer_cost  from DBA_SQL_PLAN_BASELINES2  where SIGNATURE=(select EXACT_MATCHING_SIGNATURE from v$sql where sql_id='arykx3hpq9xsa');
​
SQL_HANDLE           PLAN_NAME             SIGNATURE ACC OPTIMIZER_COST
------------------------------ ------------------------------ ------------------------ --- --------------
SQL_2d858eea22dc0f79         SQL_PLAN_2v1cfx8jds3vt341d91fc     3280185039867613049 YES    3
SQL_2d858eea22dc0f79         SQL_PLAN_2v1cfx8jds3vt3a6ea7ea     3280185039867613049 YES       1945
​

发现两个基线都是接受的。

第三次执行应用后，然后检查SQL的执行情况：

SQL> select SQL_ID,EXECUTIONS,OPTIMIZER_COST,SQL_PLAN_BASELINE from v$sql where sql_id='arykx3hpq9xsa';
​
SQL_ID        EXECUTIONS OPTIMIZER_COST SQL_PLAN_BASELINE
------------- ---------- -------------- ------------------------------
arykx3hpq9xsa      6849        3 SQL_PLAN_2v1cfx8jds3vt341d91fc
​

发现应用的SQL采用了第二个基线，执行成本从1945降低到3。

删除SQL基线

使用dbms_spm.drop_sql_plan_baseline可以删除SQL基线，但不能一次删除所有的SQL基线，如果要删除所有的SQL基线，需要用游标进行循环删除 ，相关程序如下（参见Doc ID 790039.1）

declare
pgn number;
sqlhdl varchar2(30);
cursor hdl_cur is
select distinct sql_handle from dba_sql_plan_baselines;
begin
open hdl_cur;
loop
fetch hdl_cur into sqlhdl;
exit when hdl_cur%NOTFOUND;
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>sqlhdl);
end loop;
close hdl_cur;
commit;
end;/
​
PL/SQL procedure successfully completed.
​
SQL> select count(*) from dba_sql_plan_baselines;
​COUNT(*)
----------0
​

​# 欢迎加我的微信，拉你进数据库微信群👇

这篇关于Oracle 19c OCM讲义课程：应用SQL执行计划基线的案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/248173。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
中文分词jieba库的使用与实景应用（一）

中文分词jieba库的使用与实景应用（一）

知识星球：https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义： 精确模式（默认模式）： 全模式： 搜索引擎模式： paddle 模式（基于深度学习的分词模式）： 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取
阅读更多...
水位雨量在线监测系统概述及应用介绍

水位雨量在线监测系统概述及应用介绍

在当今社会，随着科技的飞速发展，各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中，水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术，其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成，形成了一个完整的闭环系统。 数据采集单元：这是系统的“眼睛”，
阅读更多...
Hadoop企业开发案例调优场景

Hadoop企业开发案例调优场景

需求 （1）需求：从1G数据中，统计每个单词出现次数。服务器3台，每台配置4G内存，4核CPU，4线程。 （2）需求分析： 1G / 128m = 8个MapTask；1个ReduceTask；1个mrAppMaster 平均每个节点运行10个 / 3台 ≈ 3个任务（4    3    3） HDFS参数调优 （1）修改：hadoop-env.sh export HDFS_NAMENOD
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
深入探索协同过滤：从原理到推荐模块案例

深入探索协同过滤：从原理到推荐模块案例

文章目录 前言一、协同过滤1. 基于用户的协同过滤（UserCF）2. 基于物品的协同过滤（ItemCF）3. 相似度计算方法 二、相似度计算方法1. 欧氏距离2. 皮尔逊相关系数3. 杰卡德相似系数4. 余弦相似度 三、推荐模块案例1.基于文章的协同过滤推荐功能2.基于用户的协同过滤推荐功能 前言     在信息过载的时代，推荐系统成为连接用户与内容的桥梁。本文聚焦于
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
csu 1446 Problem J Modified LCS （扩展欧几里得算法的简单应用）

csu 1446 Problem J Modified LCS （扩展欧几里得算法的简单应用）

这是一道扩展欧几里得算法的简单应用题，这题是在湖南多校训练赛中队友ac的一道题，在比赛之后请教了队友，然后自己把它a掉 这也是自己独自做扩展欧几里得算法的题目 题意：把题意转变下就变成了：求d1*x - d2*y = f2 - f1的解,很明显用exgcd来解 下面介绍一下exgcd的一些知识点：求ax + by = c的解 一、首先求ax + by = gcd(a,b)的解 这个
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2