Oracle最佳实践-优化硬解析

前段时间参加oracle CAB,oracle高级服务部门做了一个数据库最佳实践的报告,其中就有一项就是解决未使用绑定变量但执行次数很多的SQL; 对于一个数据库来说如果不知道该如何优化,那么最简单最有效的优化就是减少硬解析,当然这部分优化对于DBA来说推动起来会有些难度,毕竟修改代码需要开发和业务部分配合,但是相对来说优化的难度低(绑定变量),带来的收益高,这里来介绍一下如何优化硬解析。

1. sql的执行路径

在优化之前首先要了解sql的执行路径,只有了解了sql的执行路径才能更好的了解硬解析和软解析。

1.1 SQL 解析(Parsing)

语法检查:验证 SQL 语句的语法是否正确。

语义检查:检查语义的合法性(如表、列是否存在,权限是否满足)。

共享池检查:在共享池中查找相同的 SQL 语句,避免重复解析(软解析)。

生成执行计划:如果是首次执行(硬解析),优化器会生成最佳的执行计划。

1.2 SQL 绑定(Binding)

如果 SQL 包含绑定变量(如 :1 或 :2),将实际的变量值替换到绑定变量的位置。

确保 SQL 在执行时能够正确应用输入参数。

1.3 SQL 优化(Optimization)

使用优化器(基于成本CBO或规则)评估多种执行计划。

选择具有最低成本的计划,用于后续执行。

1.4 执行计划生成(Execution Plan Generation)

确定具体的访问路径(如全表扫描、索引扫描)。

确定连接方法(如嵌套循环、哈希连接)。

1.5 SQL 执行(Execution)

根据执行计划,读取所需的数据块。

完成逻辑操作(如过滤、排序、连接)。

1.6 数据返回(Fetching)

如果是查询操作,按需从缓冲区或磁盘中提取数据。

数据按行或批量形式返回给客户端。

oracle sql执行流程图

2.硬解析VS软解析VS软软解析

通用整体性sql优化,我认为最容易最有效的方式就是减少系统的硬解析比例,使用绑定变量减少硬解析的比例可以有效提升系统的整体性能。

硬解析,软解析和软软解析对比

类型硬解析(Hard Parsing)软解析(Soft Parsing)软软解析(Fast  Parsing)
定义SQL 无匹配计划,需重新生成执行计划。SQL 匹配到已有计划,但需部分验证。SQL 完全命中缓存,直接使用执行计划。
触发条件不使用绑定变量或缓存中无匹配。使用绑定变量,计划部分验证通过。使用绑定变量,计划完全匹配,无需验证。
资源消耗最高(CPU、内存开销大)。较低(部分解析验证)。最低(几乎无消耗)。
性能最差(增加解析时间、锁竞争)。中等(解析步骤减少,性能提升)。最优(完全缓存命中,解析效率最高)。
优化方法统一 SQL 语句结构、使用绑定变量、增加库缓存大小。减少库缓存竞争、优化游标共享设置。高效利用绑定变量和共享游标机制。

3.了解系统整体的解析情况

最常用的办法是通过AWR报表来了解系统的整体的解析情况,其中关键的指标如下几个指标

Parses(SQL):每秒or事务解析次数,反应了系统的繁忙层度,数据来源v$sysstat statistics parse count (total)

Hard parses(SQL):每秒or事务硬解析次数,反应了系统硬解析的整体状况,数据来源v$sysstat statistics parse count (hard)

Soft Parse%:最重要的一个指标, 软解析比例,无需多说的经典指标,数据来源v$sysstat statistics的parse count(total)和parse count(hard)。 合理值>95% Soft Parse %是AWR中另一个重要的解析指标,该指标反应了快照时间内 软解析次数 和 总解析次数 (soft+hard 软解析次数+硬解析次数)的比值,若该指标很低,那么说明了可能 存在剧烈的hard parse硬解析,大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharing=FORCE); 理论上我们总是希望 Soft Parse % 接近于 100%, 但并不是说100%的软解析就是最理想的解析状态,通过设置 session_cached_cursors参数和反复重用游标我们可以让解析来的更轻量级,即通俗所说的利用会话缓存游 标实现的软软解析(fast parse)

Execute to Parse%:Execute to Parse% 指标反映了执行解析比 计算方法 1-(parse/execute) , 目标为100% 即接近于只执行而不解析。 数据来源v$sysstat statistics parse count (total) 和execute count 

在oracle中解析往往是执行的先提工作,但是通过游标共享 可以解析一次 执行多次, 执行解析可能分成多种场景:

解析与执行的场景

  1. 硬解析 SQL(Hard Coding)

    • 特点:硬解析一次,执行一次。
    • 表现
      • 执行解析比接近 1:1。
      • Execute to Parse% 接近 0(极差)。
      • 软解析率(Soft Parse%) 也接近 0%。
    • 问题:高频硬解析会显著增加系统资源消耗。
  2. 绑定变量但仍需软解析

    • 特点:每次执行前仍需软解析。
    • 表现
      • 执行解析比接近 1:1。
      • Execute to Parse% 仍接近 0(差)。
      • 软解析率(Soft Parse%) 可能很高。
    • 问题:虽然比硬解析略好,但解析开销仍然较高。
  3. 高效的解析与执行(理想场景)

    • 特点:通过静态SQL、绑定变量、session_cached_cursoropen_cursors等技术,实现“解析一次,执行多次”。
    • 表现
      • 执行解析比为 N:1(N 越大越好)。
      • Execute to Parse% 趋近于 100%。
      • 软解析比例降低,解析开销显著减少。
    • 优化效果:非常适合 OLTP 环境。

 通俗地说 soft parse% 反映了软解析率, 而软解析在oracle中仍是较昂贵的操作, 我们希望的是解析1次执行N次,如果每次执行均需要软解析,那么虽然soft parse%=100% 但是parse time仍可能是消耗DB TIME的大头。 Execute to Parse反映了 执行解析比,Execute to Parse和soft parse% 都很低 那么说明确实没有绑定变量 , 而如果 soft parse% 接近99% 而Execute to Parse 不足90% 则说明执行解析比低, 可以通过静态SQL、动态绑定、session_cached_cursor、open cursors等技术减少软解析。

Parse CPU To Parse Elapsd:该指标反映了 快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time); 若该指标水平很低,那么说明在整个解析过程中 实际在CPU上运算的时间是很短的,而主要的解析时间都耗费在各种其他非空闲的等待事件上了(如latch:shared pool,row cache lock之类等) 数据来源 V$sysstat 的 parse time cpu和parse time elapsed

4.查询未绑定变量的高频 SQL

V$SQL 视图包含每个 SQL 语句的执行统计信息,可以用以下 SQL 查询未绑定变量的高频 SQL

SELECT     sql_id,    executions,    parse_calls,    sql_text,    module,    parsing_schema_name,    ROUND(executions / (parse_calls + 1), 2) AS execution_to_parse_ratioFROM     v$sqlWHERE     executions > 1000   -- 执行次数大于 1000(可调整)    AND parse_calls > 0 -- 存在解析调用    AND executions / (parse_calls + 1) < 10 -- 执行与解析比值较低(表示未使用绑定变量)ORDER BY     executions DESC;

-- 说明

-- executions:SQL 语句的执行次数。

-- parse_calls:SQL 被解析的次数(高解析调用可能是未绑定变量的症状)。

-- execution_to_parse_ratio:执行次数与解析次数的比值,越低越可能是未绑定变量。

-- sql_text:SQL 文本,可以查看具体内容。

重点关注用户schema下的高频sql

检查具体 SQL 的绑定变量使用情况

SELECT *   FROM    v$sql_bind_captureWHERE sql_id = '&SQL_ID';

 VALUE_STRING 最近一次捕获的绑定变量值(以字符串形式存储)。

5.检查高负载 SQL

使用 Oracle 提供的 Active Session History(ASH)或 AWR 报告分析高负载 SQL:

  • ASH:分析活跃会话中的高频 SQL
  • AWR 报告:查看执行次数最多的 SQL 列表
  • 根据查出的结果在针对sql做具体的分析

在AWR中可以按各种维度来定位top sql 然后再做针对性优化

查询 AWR 中执行次数最多的 SQL

-- 查询 AWR 中执行次数最多的 SQLSELECT * FROM dba_hist_sqlstatWHERE executions_delta > 1000 -- 根据执行次数筛选ORDER BY executions_delta DESC;

查询平均执行时间超过10秒的sql

 SELECT     SQL_ID,    ELAPSED_TIME / 1000000 AS ELAPSED_SECONDS,    EXECUTIONS,    SQL_TEXTFROM     V$SQLWHERE     EXECUTIONS > 0 -- 排除未执行的SQL    AND (ELAPSED_TIME / EXECUTIONS) / 1000000 > 10 -- 平均执行时间超过10秒ORDER BY     ELAPSED_SECONDS DESC;

利用sql_monitor优化

----sql monitor---------SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sql_id', type => 'TEXT') AS report FROM dual;

被sql monitor监控的触发条件

执行时间>5秒

SQL 的累计执行时间(CPU 时间 + I/O 时间)超过 5秒

这个时间阈值可以通过参数 SQLMON_THRESHOLD 调整:

ALTER SESSION SET "_sqlmon_threshold" = 2; -- session级别设置阈值为 2 秒ALTER SESSION SET "_sqlmon_threshold" = 2; --系统级别设置阈值为2秒

并行执行(Parallel Execution)

SQL 使用了并行执行计划(Parallel Execution)。

即使执行时间较短,但因为使用了并行,SQL 会自动被监控。

被强制要求监控

通过 SQL Hints 强制启用 SQL Monitor

SELECT /*+ MONITOR */ ... FROM table_name;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/937117.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

源码编译jdk11 超详细教程 openjdk11

关于源代码 当前的openJDK的源代码已经被发布到了github上了&#xff0c;所以我们可以直接从github上下载到。 OpenJDK11u源码托管地址&#xff1a;https://github.com/openjdk/jdk11u 带后缀U的地址&#xff0c;或者发行的jdk包&#xff0c;表示当前版本下的持续跟新版。而…

STL-vector类

目录 vector介绍及其使用 介绍 使用 vector定义 vector iterator vector内存管理 vector内容管理 vector的模拟实现 vector的迭代器失效 会引起迭代器失效的操作 vector介绍及其使用 介绍 向量是序列容器&#xff0c;表示大小可以变化的数组 见 chttps://cpluspl…

http1.1 vs http2.0 速度对比实测

首先对比一下http1.1 vs http2.0 区别&#xff1a; 1. 连接管理&#xff1a; HTTP/1.1: 每个请求/响应都需要一个独立的 TCP 连接&#xff0c;虽然可以使用持久连接&#xff08;keep-alive&#xff09;来复用连接&#xff0c;但仍然存在请求队头阻塞&#xff08;Head-of-Line…

JAVA学习日记(二十六)网络编程

一、网络编程的概念 常见的软件架构&#xff1a; 二、网络编程三要素 IP&#xff1a;设备在网络中的地址&#xff0c;是唯一的标识 端口号&#xff1a;应用程序在设备中的唯一标识 协议&#xff1a;数据在网络中传输的规则&#xff0c;常见的协议有UDP、TCP、http、https、f…

域名信息(小迪网络安全笔记~

附&#xff1a;完整笔记目录~ ps&#xff1a;本人小白&#xff0c;笔记均在个人理解基础上整理&#xff0c;若有错误欢迎指正&#xff01; 2.1 域名信息 引子&#xff1a;上一章介绍了服务器的信息收集。本篇则介绍在面对存在Web资产企业时&#xff0c;其域名信息该如何收集。…

ubuntu18.04配置实时内核

ubuntu系统&#xff1a;18.04 当前内核&#xff1a;5.4.0-84-generic 待安装实时内核&#xff1a; 5.6.19-rt11 1、查看当前版本 uname -r 2、下载内核与补丁 一种方式从官网自己下载 官方内核下载地址官方补丁下载地址阿里镜像内核下载地址&#xff08;速度快&#xff0…

Lumos学习王佩丰Excel第二十一讲:经典Excel动态图表实现原理

一、动态图表实现原理 1、理解图表中的数据系列 在Excel图表中&#xff0c;系列指的是图表中的数据集合&#xff0c;它通常代表着一个数据源。每个系列都可以包含多个数据点&#xff0c;这些数据点在图表中以特定的形式展现&#xff0c;如柱状图中的柱子&#xff0c;折线图中…

医学分割数据集B超图像肾脏分割数据集labelme格式715张1类别

数据集格式&#xff1a;labelme格式(不包含mask文件&#xff0c;仅仅包含jpg图片和对应的json文件) 图片数量(jpg文件个数)&#xff1a;715 标注数量(json文件个数)&#xff1a;715 标注类别数&#xff1a;1 标注类别名称:["kidney"] 每个类别标注的框数&#x…

福湘板材:树立行业一线品牌典范

在当今的建筑装修市场中&#xff0c;板材作为一种重要的建筑材料&#xff0c;其品质和性能直接关系到工程质量和使用寿命。福湘板材&#xff0c;作为一个在行业内具有广泛影响力的品牌&#xff0c;一直以来都以高品质、环保性能和卓越的服务赢得了广大消费者的认可&#xff0c;…

redis数据类型(一)

博主主页: 码农派大星. 数据结构专栏:Java数据结构 数据库专栏:数据库 JavaEE专栏:JavaEE 软件测试专栏:软件测试 关注博主带你了解更多知识 目录 1. String 字符串类型 常见命令: 1. set 2. get 3. MGET和MSET 4.SETNX 5. setex和psetex 计数命令: 1. INCR 2. IN…

在 macOS 下安装和使用 Clang Static Analyzer

在 macOS 下安装和使用 Clang Static Analyzer 相对简单&#xff0c;因为 macOS 自带 Clang 编译器&#xff0c;并且工具链已经包含了静态分析器的功能。以下是详细步骤&#xff1a; 1. 检查系统自带的 Clang macOS 自带 Clang 编译器&#xff0c;安装在 Xcode 或 Xcode Comm…

docker简单私有仓库的创建

1&#xff1a;下载Registry镜像 导入镜像到本地中 [rootlocalhost ~]# docker load -i registry.tag.gz 进行检查 2&#xff1a;开启Registry registry开启的端口号为5000 [rootlocalhost ~]# docker run -d -p 5000:5000 --restartalways registry [rootlocalhost ~]# dock…

搭建mqtt服务端并在Spring Boot项目中集成mqtt

文章目录 一、MQTT1.MQTT 的特点2.MQTT 的工作原理3.MQTT 的应用场景 二、EMQX(服务端)1.EMQX 的特点2.安装 三、客户端1.MQTTX2.EMQX后台建客户端3.H5页面客户端4.Spring Boot集成mqtt做客户端 一、MQTT MQTT&#xff08;Message Queuing Telemetry Transport&#xff0c;消息…

Easysearch Java SDK 2.0.x 使用指南(一)

各位 Easysearch 的小伙伴们&#xff0c;我们前一阵刚把 easysearch-client 更新到了 2.0.2 版本&#xff01;借此详细介绍下新版客户端的使用。 新版客户端和 1.0 版本相比&#xff0c;完全重构&#xff0c;抛弃了旧版客户端的一些历史包袱&#xff0c;从里到外都焕然一新&am…

《计算机视觉证书:开启职业发展新航道》

一、引言 在当今科技飞速发展的时代&#xff0c;计算机视觉技术正以惊人的速度改变着我们的生活和工作方式。从智能手机的人脸识别解锁到自动驾驶汽车的环境感知&#xff0c;计算机视觉技术的应用无处不在。而计算机视觉证书作为这一领域的专业认证&#xff0c;其作用愈发凸显…

ubuntu 用 ss-tproxy的最终网络结构

1、包含了AD广告域名筛选 2、Ss-tproxy 国内国外地址分类 3、chinadns-ng解析 4、透明网关 更多细节看之前博客 ubuntu 用ss-TPROXY实现透明代理&#xff0c;基于TPROXY的透明TCP/UDP代理,在 Linux 2.6.28 后进入官方内核。ubuntu 用 ss-tproxy的内置 DNS 前挂上 AdGuardHome…

OpenCV相关函数

一、二值化函数&#xff08;threshold&#xff09; 功能&#xff1a;将灰度图像转换为二值图像&#xff0c;通常用于图像分割。通过设置阈值&#xff0c;把图像中低于阈值的像素设为0&#xff0c;高于阈值的像素设为1。 参数&#xff1a; src&#xff1a;输入图像。 thresh&a…

前端html,vue使用第三方地图详细教程,以百度地图为例,实现地图标注,导航,定位,路线规划,坐标转换

目录 示例&#xff1a; 准备&#xff1a; ?编辑 开始&#xff1a; 1、新建页面&#xff0c;在script标签中引入百度地图的api数据&#xff0c;把自己在控制台创建的应用的ak替换上去 2、创建一个dom对象&#xff0c;设置宽高 3、在js中初始化地图 进阶&#xff1a; 1…

【经验分享】容器云搭建的知识点

最近忙于备考没关注&#xff0c;有次点进某小黄鱼发现首页出现了我的笔记还被人收费了 虽然我也卖了一些资源&#xff0c;但我以交流、交换为主&#xff0c;笔记都是免费给别人看的 由于当时刚刚接触写的并不成熟&#xff0c;为了避免更多人花没必要的钱&#xff0c;所以决定公…

【Liunx篇】基础开发工具 - vim

文章目录 一.vim的基本概念1.正常/命令模式2.插入模式3.底行模式/末行模式4.视图模式5.替换模式 二.vim的基本操作1.进入vim&#xff1a;2.退出vim: 三.vim正常模式命令集1.光标定位&#xff1a;2.复制/粘贴3.撤销4.剪切/删除5. 更改 四.vim底行模式命令集1.保存/退出2.调出行号…