程序员经验分享-hwhale

稳定ORACLE的执行计划

2023-11-10

 很多时候可能我们都希望CBO能够帮我们生成正确、高效的执行计划，但是很多时候事实并非如此，可能因为各种各样的原因(如，统计信息不正确或者CBO天生的缺陷等)都会导致生成的执行计划特别的低效。之前的一家公司有一台专门用于批量做数据校验清洗的数据库，每次校验清洗完成数据就会清理掉，统计信息经常会发生较大的变更，之前跑得好好的SQL，可能有时候跑5-6个小时都跑不完了，这时候查看执行计划，发现不正确的统计信息导致了执行计划的变更。

 这时候我们就希望数据库中运行的SQL都能有正确、稳定的执行计划，在10g开始的版本中可以通过SQL Profile来稳定执行计划或者在不改变SQL的情况下修改执行计划。11g开始可以使用偏主动的稳定执行计划的手段——SPM(SQL PLAN MANAGEMENT)，保证只有被验证过的执行计划才会被启用。

SQL Profile

 SQL Profile是包含特定于SQL语句的辅助统计信息的数据库对象，可以改进优化器基数估计，从而选择更好的执行计划。
 当选择执行计划时优化器会考虑以下信息：

  1. SQL Profile提供的辅助统计信息
  2. 当时SQL的运行环境，如数据库配置，变量绑定，与优化器相关的统计信息等。

 所以，上面两个条件的任意一个发生变化，都有可能导致执行计划的改变。下面看下SQL Profile的一些基本操作以及如何在线进行SQL的调整。

accepting sql profile

 通过DBMS_SQLTUNE.ACCEPT_SQL_PROFILE存储过程可以接受一个SQL Profile，只有在我们接受了一个SQL Profile之后，优化器才能使用他作为产生执行计划的输入。这个存储过程有两个比较重要的参数：

  • profile_type
    这个参数用于控制是否改变并行执行行为，REGULAR_PROFILE不更改为并行执行，PX_PROFLE用于更改并行执行的SQL Profile。
  • force_match
    该参数用于控制SQL语句匹配，有两个值——TRUE和FALSE。对于SQL语句中where条件的字面值，当force_match=TRUE时，会将其替换为变量绑定，所以当字面值不同时也可以重用该SQL Profile。值为FALSE时，where条件的字面值则不会替换。

下面是ACCEPT_SQL_PROFILE的例子：

DECLARE
  my_sqlprofile_name VARCHAR2(30);
BEGIN
  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'STA_SPECIFIC_EMP_TASK'
,   name         => 'my_sql_profile'
,   profile_type => DBMS_SQLTUNE.PX_PROFILE
,   force_match  => true 
);
END;
/

Listing SQL Profile

 可以通过DBA_SQL_PROFILES数据字典视图来查看存储在数据库中的SQL Profile。

SQL> SELECT NAME,CATEGORY,SQL_TEXT,FORCE_MATCHING,STATUS FROM DBA_SQL_PROFILES;

NAME                           CATEGORY   SQL_TEXT                  FOR STATUS
------------------------------ ---------- ------------------------- --- --------
SYS_SQLPROF_016986bccd640000   DEFAULT    select /*+ use_nl(a b) in NO  ENABLED
                                          dex(b) */a.brwyid,a.yljgd
                                          m,a.jzlsh,b.mzzddm from t
                                          est_e

Altering SQL Profile

 通过ALTER_SQL_PROFILE中的attribute_name参数可以修改SQL Profile相应的参数值。

BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
   name            =>  'my_sql_profile'
,  attribute_name  =>  'FORCE_MATCH'
,  value           =>  'TRUE'      
);
END;
/

Droping SQL Profile

 通过DROP_SQL_PROFILE存储过程可以删除特定的SQL Profile

BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
    name => 'my_sql_profile' 
);
END;
/

通过SQL Profile调整线上SQL执行计划

 通过手工创建SQL Profile的方式，可以在不更改目标SQL的SQL文本的情况下修改SQL的执行计划，而且可以很好的稳定SQL的执行计划。
 下面是手工创建SQL Profile的例子,在TEST_ENV.TB_TABLE_LIST的列TABLE_NAME上有一个名为IDX_TB_TABLE_LIST_TBNAME的B树索引：
1、首先加一个全表扫描的HINTS来执行下面的SQL，模拟线上的一个执行低效的SQL，并查看其执行计划。

SQL> SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';

TABLE_NAME
------------------------------
ACCESS$

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1a319c1c2b3rz, child number 0
-------------------------------------
SELECT /*+FULL(T)*/ TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE
TABLE_NAME='ACCESS$'

Plan hash value: 1475094007

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |    31 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TB_TABLE_LIST |     1 |    18 |    31   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1

Outline Data
-------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"='ACCESS$')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (rowset=256) "TABLE_NAME"[VARCHAR2,128]


已选择 43 行。

2、然后加入走索引的HINTS来更正这个SQL的执行计划，得到下面的执行计划相关信息，此时我们就需要用这个执行计划来替换掉上面走全表扫描的SQL的执行计划。

SQL> SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$';


TABLE_NAME
------------------------------
ACCESS$

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  44j1ysb93cwdq, child number 0
-------------------------------------
SELECT /*+ INDEX(T IDX_TB_TABLE_LIST_TBNAME) */TABLE_NAME FROM
TEST_ENV.TB_TABLE_LIST T WHERE TABLE_NAME='ACCESS$'

Plan hash value: 3318876060

---------------------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |       |       |     1 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| IDX_TB_TABLE_LIST_TBNAME |     1 |    18 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1

Outline Data
-------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      DB_VERSION('18.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))
      END_OUTLINE_DATA
  */


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TABLE_NAME"='ACCESS$')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "TABLE_NAME"[VARCHAR2,128]


已选择 43 行。

3、下面查看对应的SQL_ID。

SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQLAREA WHERE SQL_TEXT LIKE '%TABLE_NAME FROM TEST_ENV.TB_TABLE%';

SQL_TEXT                  SQL_ID
------------------------- -------------
SELECT /*+ INDEX(T IDX_TB 44j1ysb93cwdq
_TABLE_LIST_TBNAME) */TAB
LE_NAME FROM TEST_ENV.TB_
TABLE_LIST T WHERE TABLE_
NAME='ACCESS$'

SELECT SQL_TEXT,SQL_ID FR g4v1sg4ycf96y
OM V$SQLAREA WHERE SQL_TE
XT LIKE '%TABLE_NAME FROM
 TEST_ENV.TB_TABLE%'


SQL_TEXT                  SQL_ID
------------------------- -------------
SELECT /*+FULL(T)*/ TABLE 1a319c1c2b3rz
_NAME FROM TEST_ENV.TB_TA
BLE_LIST T WHERE TABLE_NA
ME='ACCESS$'

4、创建SQL PROFILE，用正确的执行计划的OUT LINE DATA来创建SQL Profile

SQL> declare
  2     v_hints sys.sqlprof_attr;
  3     clsql_text clob;
  4  begin
  5     v_hints := sys.sqlprof_attr('BEGIN_OUTLINE_DATA',
  6        'IGNORE_OPTIM_EMBEDDED_HINTS',
  7        'OPTIMIZER_FEATURES_ENABLE(''18.1.0'')',
  8        'DB_VERSION(''18.1.0'')',
  9        'ALL_ROWS',
 10        'OUTLINE_LEAF(@"SEL$1")',
 11        'INDEX(@"SEL$1" "T"@"SEL$1" ("TB_TABLE_LIST"."TABLE_NAME"))',
 12        'END_OUTLINE_DATA');
 13
 14      select sql_fulltext into clsql_text from v$sqlarea where sql_id='1a319c1c2b3rz';
 15
 16      dbms_sqltune.import_sql_profile(clsql_text,v_hints,'my_sql_profile',force_match=>true,r
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

Oracle

sql

sql优化

性能优化

稳定ORACLE的执行计划 的相关文章

  • PostgreSQL 在递归查询中找到所有可能的组合（排列）

    输入是一个长度为 n 的数组 我需要生成数组元素的所有可能组合 包括输入数组中元素较少的所有组合 IN j A B C OUT k A AB AC ABC ACB B BA BC BAC BCA 随着重复 所以AB BA 我尝试过这样的事情

  • 我应该使用平面表还是标准化数据库？

    我目前正在开发一个使用 MySQL 数据库作为后端的 Web 应用程序 在继续下一步之前 我需要知道什么更适合我的情况 简而言之 在这个应用程序中 用户将能够使用任何数字字段 他们决定 构建自己的表单 现在我将其全部存储在通过外键链接的几个

  • 使用条件 SQL 统计每月汇总记录

    我有一张桌子 我们就叫他们桌子吧SUMMARYDATA NIP NAME DEPARTMENT STATUSIN STATUSOUT TOTALLOSTTIME A1 ARIA BB 2020 01 21 08 06 23 2020 01

  • 如何计算 Postgres 上图表中所有连接的节点（行）？

    我的桌子有account id and device id One account id可以有多个device ids 反之亦然 我正在尝试计算每个连接的多对多关系的深度 Ex account id device id 1 10 1 11

  • 删除表的重复项

    In my activity logs 它包含列 material name user id mod result 这标志着测试是否通过 失败 cert links 不知何故 用户生成了两倍的条目material name与cert lin

  • 如何引用下一行的数据？

    我正在 PostgreSQL 9 2 中编写一个函数 对于股票价格和日期的表 我想计算每个条目较前一天的百分比变化 对于最早一天的数据 不会有前一天 因此该条目可以简单地为 Nil 我知道WITH声明可能不应该高于IF陈述 到目前为止 这就

  • 在 Mysql 上使用 EntityManager JPA 运行脚本

    我正在尝试运行脚本 sql 文件 但由于我尝试了多种方法 因此出现多个错误 这是我的主要 sql 脚本 INSERT INTO Unity VALUES 11 paq 0 2013 04 15 11 41 37 Admin Paquete

  • 在 PostgreSql 中计算百分比

    例如我有一个这样的表 string adm A 2 A 1 B 2 A 1 C 1 A 2 通过 SQL 查询 我想要这样的结果 string perc adm A 50 B 100 C 0 我想要每个字符串中数字 2 出现的百分比 我可以

  • 如何更新 pl/sql 中嵌套表的列？ [复制]

    这个问题在这里已经有答案了 我正在尝试在表中创建一个可以存储多个值的列 如下所示 我有一个学生id std和一个名为marks可以采用几个值 例如2 3 4 我想更新此列表以添加另一个标记2 3 4 5但我不知道怎么做 我如何更新专栏mar

  • C# using 语句、SQL 和 SqlConnection

    使用 using 语句 C SQL 可以吗 private static void CreateCommand string queryString string connectionString using SqlConnection c

  • SQL Server 2008 错误 233

    我正在使用以下 sql 脚本在 SQL Server 2008 中创建新登录名 CREATE LOGIN xyz WITH PASSWORD xyz DEFAULT DATABASE master DEFAULT LANGUAGE us e

  • 何时在 mysql 中使用 Union [关闭]

    Closed 这个问题需要多问focused help closed questions 目前不接受答案 对于 Union 您会在什么现实情况下使用它 因为对我来说 对具有不同列用途 含义的两个表中的两个选择查询使用联合是没有意义的 例如

  • NVARCHAR 变量在Where 子句中不起作用

    在 SQL Server 我想是 2018 我不知道如何判断 中 我的变量不起作用WHERE的条款NVARCHAR 比较应该返回值 但它什么也没返回 如果我只是手动输入声明的文本 它会突然起作用并返回值 没有任何逻辑原因应该有任何不同 类型

  • postgresql：插入...（选择*...）

    我不确定它是否是标准 SQL INSERT INTO tblA SELECT id time FROM tblB WHERE time gt 1000 我正在寻找的是 如果 tblA 和 tblB 位于不同的数据库服务器中怎么办 Postg

  • 将两个表合并为一个输出

    假设我有两张表 已知营业时间 ChargeNum CategoryID Month Hours 111111 1 2 1 09 10 111111 1 3 1 09 30 111111 1 4 1 09 50 222222 1 3 1 09

  • SQL - 需要查找重复记录但排除反向事务

    我有一张交易表 偶尔会有 重复条目 如果 当管理员发现这些重复条目时 他们将撤销交易 从而创建负值 但由于监管要求 原始重复条目仍然保留 我想创建一个 SQL 查询 并使用 Crystal Reports 来制作报告 以便管理员轻松查找重复

  • WHERE NOT EXIST 附近的语法错误

    我在堆栈中搜索 但没有一个达到最终答案 我的查询是这样的 INSERT INTO user username frequence autoSend VALUES feri2 3 1 WHERE NOT EXISTS SELECT FROM

  • 获取mysql中逗号分隔行中不同值的计数

    一个表 Jobs 有 2 列 JobId 城市 当我们保存工作时 工作位置可能是多个城市 如下所示 JobId City 1 New York 2 New York Ohio Virginia 3 New York Virginia 我如何

  • 在同一查询中选择 Count of ip 和 Count of DISTINCT ip

    我有一个这样的表结构 TABLE NAME counter id datetime url ip 1 2013 04 12 13 27 09 url1 ip01 2 2013 04 13 10 55 43 url2 ip02 3 2013

  • 在Oracle中使用IW和MM

    我使用 IW 表示每周结果 使用 MM 表示每月结果 但我总是收到错误 ORA 00979 not a GROUP BY expression 00979 00000 not a GROUP BY expression 我的疑问是这些 We

随机推荐

  • 这个效果是怎么做的？

    http higkoo i sohu com blog view 81445953 htm 想要那个红狐狸效果

  • 【论文阅读】MPViT : Multi-Path Vision Transformer for Dense Prediction

    发表年份 2021 12 发表单位 Electronics and Telecommunications Research Institute ETRI South Korea 期刊 会议 CVPR 论文链接 https arxiv org

  • Servlet_01_介绍

    Servlet的作用是处理请求 服务器会把接收到的请求交给Servlet来处理 在Servlet中通常需要 1 接收请求数据 2 处理请求 3 完成响应 例如客户端发出登录请求 或者输出注册请求 这些请求都应该由Servlet来完成处理 S

  • ubuntu下学习c++(输出定义变量）

    2022 6 11 学习cout cin的使用方法以及变量的定义 include

  • 接口和类有啥区别：

    接口和类有啥区别 接口是一系列抽象方法的集合 接口中只有抽象方法 只有的意思就是 没有成员变量 除了静态常量 没有构造方法 因此不能被实例化 类只是一种抽象的数据类型 接口没有构造方法 一个类只能继承一个类 但是可以实现多个接口 接口中不能

  • 远处是风景，近处是人生

    常听到有人说 最近好累 想出去走走 换个地方透透气 如果去到大理 心情一定如花儿一般灿烂 如果去到内蒙 心情一定如草原一般辽阔 如果去到三亚 心情一定如大海一般坦荡 但实际上 改变你心情的从不是新的城市或者美丽的景色 是你愿意与生活和解的态

  • 网络常考题

    45 当数据接收者不能处理更多数据时 哪一层发出停止信息给发送者 A 网络层 B 传输层 C 会话层 D 表示层 B 49 在传输层采用了以下哪些方法来保证接收缓冲区不溢出 多选 A 数据分段 B 确认机制 C 流量控制 D 滑动窗口 E

  • Spring中@JsonFormat与@DateTimeFormat注解使用简介说明

    转自 Spring中 JsonFormat与 DateTimeFormat注解使用简介说明 下文笔者讲述Spring中JsonFormat和DateTimeFormat注解的简介说明 如下所示 JsonFormat和DateTimeForm

  • C语言编译执行的全过程

    编译 编译程序读取源程序 字符流 对之进行词法和语法的分析 将高级语言指令转换为功能等效的汇编代码 再由汇编程序转换为机器语言 并且按照操作系统对可执行文件格式的要求链接生成可执行程序 C源程序头文件 gt 预编译处理 cpp gt 编译程

  • 高光谱遥感数值建模技术及在植被、水体、土壤信息提取领域应用

    在高光谱影像中 结合纹理 表面粒度 风化程度 作物密度等辅助信息 能估计出多种地物及其上覆作物的状态参量 提高遥感高定量分析的精度和可靠性 如何通过构建遥感光谱反射信号与地表参数之间的关系模型来实现数值计算 是举办本次培训班的主要目的 针对

  • 校园网络系统服务器配置摘要,校园网网络应用服务器配置

    浅谈校园网网络应用服务器配置 摘要 以某校园网为例 在网络应用服务器设计这个环节中 我们分别用到了web服务器 ftp服务器 dns服务器 dhcp服务器 mail服务器 并且在一台已经安装了windows 2003 server的计算机上

  • 前端框架——React 学习总结，这篇7000字全解决

    React组件复用 React组件复用 把多个组件中部分功能相似或者相同的状态或者逻辑进行复用 复用 state和操作state的方法 复用的方式 render props模式 高阶组件 HOC render props模式 用childr

  • Mysql 时间转换 && 时间函数

    1 时间转换 涉及的函数 DATE FORMAT date format MySQL日期格式化函数 STR TO DATE str format MySQL字符串格式化为日期 UNIX TIMESTAMP MySQL其他数据转换为时间戳 F

  • Vue的antd多选下拉框增加全选操作

    因为antd的多选下拉框没有提供全选操作 我做了一个简易的全选操作 data return categoryList 存放获取到的分选数据 category 已选分类数据

  • QT信号槽的5种连接方式

    在面试中 这是一个经常被问到的问题点 也是刚刚上qt的工程师不会去注意的一个点 qt源代码定义的连接方式如下 1 Qt AutoConnection 一般信号槽不会写第五个参数 其实使用的默认值 使用这个值则连接类型会在信号发送时决定 如果

  • markdown编辑数学公式

    在输入数学公式的时候 需要在数学公式的前后加入 符号 将需要输入的公式加入到 中间 上下标 上标 下标 名称 数学表达式 markdown公式 上标 ab a b a b 下标 ab a b a b 分数 frac 第一个 写分子 第二个

  • React Native(RN)－组件生命周期

    生命周期简介 像 Android 开发一样 React Native RN 中的组件也有生命周期 Lifecycle 借用大神流程图 这张图很简洁直观地告诉我们 生命周期的整个流程以及阶段划分 第一阶段 getDefaultProps gt

  • 目标检测入门

    目录 R CNN 1 1提取候选区域 1 1 1合并规则 1 1 2多样化与后处理 1 2特征提取 1 2 1预处理 2 Fast RCNN 2 1RoI Pooling Layer Faster RCNN 结构 RPN anchor 目标

  • Junit中使用线程池不执行任务代码

    1 在test中使用线程池发送MQ 没有报错 没有执行线程池中的代码 2 查资料 junit框架只要主线程结束完成 单元测试就会关闭 导致线程池中的线程没有执行代码就被销毁关闭了 可以在主线程中sleep一段时间 或者用main方法

  • 稳定ORACLE的执行计划

    很多时候可能我们都希望CBO能够帮我们生成正确 高效的执行计划 但是很多时候事实并非如此 可能因为各种各样的原因 如 统计信息不正确或者CBO天生的缺陷等 都会导致生成的执行计划特别的低效 之前的一家公司有一台专门用于批量做数据校验清洗的数

热门标签