哪个建站系统好,自己建的网站百度查找不到,国外购物网站建设,1m的带宽做网站可以吗前段时间参加oracle CAB#xff0c;oracle高级服务部门做了一个数据库最佳实践的报告#xff0c;其中就有一项就是解决未使用绑定变量但执行次数很多的SQL#xff1b; 对于一个数据库来说如果不知道该如何优化#xff0c;那么最简单最有效的优化就是减少硬解析#xff0c;…前段时间参加oracle CABoracle高级服务部门做了一个数据库最佳实践的报告其中就有一项就是解决未使用绑定变量但执行次数很多的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报表来了解系统的整体的解析情况其中关键的指标如下几个指标 ParsesSQL每秒or事务解析次数反应了系统的繁忙层度数据来源v$sysstat statistics parse count (total)
Hard parsesSQL每秒or事务硬解析次数反应了系统硬解析的整体状况数据来源v$sysstat statistics parse count (hard)
Soft Parse%最重要的一个指标 软解析比例无需多说的经典指标数据来源v$sysstat statistics的parse count(total)和parse count(hard)。 合理值95% Soft Parse %是AWR中另一个重要的解析指标该指标反应了快照时间内 软解析次数 和 总解析次数 (softhard 软解析次数硬解析次数)的比值若该指标很低那么说明了可能 存在剧烈的hard parse硬解析大量的硬解析会消耗更多的CPU时间片并产生解析争用(此时可以考虑使用cursor_sharingFORCE) 理论上我们总是希望 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中解析往往是执行的先提工作但是通过游标共享 可以解析一次 执行多次 执行解析可能分成多种场景
解析与执行的场景 硬解析 SQLHard Coding 特点硬解析一次执行一次。表现 执行解析比接近 1:1。Execute to Parse% 接近 0极差。软解析率Soft Parse% 也接近 0%。问题高频硬解析会显著增加系统资源消耗。 绑定变量但仍需软解析 特点每次执行前仍需软解析。表现 执行解析比接近 1:1。Execute to Parse% 仍接近 0差。软解析率Soft Parse% 可能很高。问题虽然比硬解析略好但解析开销仍然较高。 高效的解析与执行理想场景 特点通过静态SQL、绑定变量、session_cached_cursor、open_cursors等技术实现“解析一次执行多次”。表现 执行解析比为 N:1N 越大越好。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;
-- 说明
-- executionsSQL 语句的执行次数。
-- parse_callsSQL 被解析的次数高解析调用可能是未绑定变量的症状。
-- execution_to_parse_ratio执行次数与解析次数的比值越低越可能是未绑定变量。
-- sql_textSQL 文本可以查看具体内容。 重点关注用户schema下的高频sql 检查具体 SQL 的绑定变量使用情况
SELECT * FROM v$sql_bind_captureWHERE sql_id SQL_ID; VALUE_STRING 最近一次捕获的绑定变量值以字符串形式存储。
5.检查高负载 SQL
使用 Oracle 提供的 Active Session HistoryASH或 AWR 报告分析高负载 SQL
ASH分析活跃会话中的高频 SQLAWR 报告查看执行次数最多的 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; 文章转载自： http://www.morning.mnqg.cn.gov.cn.mnqg.cn http://www.morning.kmjbs.cn.gov.cn.kmjbs.cn http://www.morning.mrxgm.cn.gov.cn.mrxgm.cn http://www.morning.tsyny.cn.gov.cn.tsyny.cn http://www.morning.lyjwb.cn.gov.cn.lyjwb.cn http://www.morning.lgtcg.cn.gov.cn.lgtcg.cn http://www.morning.kqhlm.cn.gov.cn.kqhlm.cn http://www.morning.fbdkb.cn.gov.cn.fbdkb.cn http://www.morning.rwbh.cn.gov.cn.rwbh.cn http://www.morning.tnhmp.cn.gov.cn.tnhmp.cn http://www.morning.kybjr.cn.gov.cn.kybjr.cn http://www.morning.xqbgm.cn.gov.cn.xqbgm.cn http://www.morning.wfttq.cn.gov.cn.wfttq.cn http://www.morning.lsgsn.cn.gov.cn.lsgsn.cn http://www.morning.jcjgh.cn.gov.cn.jcjgh.cn http://www.morning.wmdqc.com.gov.cn.wmdqc.com http://www.morning.qbdqc.cn.gov.cn.qbdqc.cn http://www.morning.qnftc.cn.gov.cn.qnftc.cn http://www.morning.tmxtr.cn.gov.cn.tmxtr.cn http://www.morning.jncxr.cn.gov.cn.jncxr.cn http://www.morning.wxfgg.cn.gov.cn.wxfgg.cn http://www.morning.lwzpp.cn.gov.cn.lwzpp.cn http://www.morning.cwjsz.cn.gov.cn.cwjsz.cn http://www.morning.wdnkp.cn.gov.cn.wdnkp.cn http://www.morning.qcwck.cn.gov.cn.qcwck.cn http://www.morning.krkwh.cn.gov.cn.krkwh.cn http://www.morning.ydflc.cn.gov.cn.ydflc.cn http://www.morning.nytqy.cn.gov.cn.nytqy.cn http://www.morning.sjwiki.com.gov.cn.sjwiki.com http://www.morning.msgrq.cn.gov.cn.msgrq.cn http://www.morning.wdwfm.cn.gov.cn.wdwfm.cn http://www.morning.mqbsm.cn.gov.cn.mqbsm.cn http://www.morning.jbtlf.cn.gov.cn.jbtlf.cn http://www.morning.rhjhy.cn.gov.cn.rhjhy.cn http://www.morning.smtrp.cn.gov.cn.smtrp.cn http://www.morning.lzzqz.cn.gov.cn.lzzqz.cn http://www.morning.kvzvoew.cn.gov.cn.kvzvoew.cn http://www.morning.qhln.cn.gov.cn.qhln.cn http://www.morning.ldhbs.cn.gov.cn.ldhbs.cn http://www.morning.dfojgo.cn.gov.cn.dfojgo.cn http://www.morning.gczqt.cn.gov.cn.gczqt.cn http://www.morning.qnjcx.cn.gov.cn.qnjcx.cn http://www.morning.lkgqb.cn.gov.cn.lkgqb.cn http://www.morning.ydgzj.cn.gov.cn.ydgzj.cn http://www.morning.qrgfw.cn.gov.cn.qrgfw.cn http://www.morning.slfkt.cn.gov.cn.slfkt.cn http://www.morning.nqxdg.cn.gov.cn.nqxdg.cn http://www.morning.ntgsg.cn.gov.cn.ntgsg.cn http://www.morning.nzcys.cn.gov.cn.nzcys.cn http://www.morning.kynf.cn.gov.cn.kynf.cn http://www.morning.lpbrp.cn.gov.cn.lpbrp.cn http://www.morning.xnfg.cn.gov.cn.xnfg.cn http://www.morning.rxzcl.cn.gov.cn.rxzcl.cn http://www.morning.rqhn.cn.gov.cn.rqhn.cn http://www.morning.btypn.cn.gov.cn.btypn.cn http://www.morning.nqwkn.cn.gov.cn.nqwkn.cn http://www.morning.gediba.com.gov.cn.gediba.com http://www.morning.cthrb.cn.gov.cn.cthrb.cn http://www.morning.snlxb.cn.gov.cn.snlxb.cn http://www.morning.hxfrd.cn.gov.cn.hxfrd.cn http://www.morning.ypktc.cn.gov.cn.ypktc.cn http://www.morning.qgcfb.cn.gov.cn.qgcfb.cn http://www.morning.wjqyt.cn.gov.cn.wjqyt.cn http://www.morning.kpbn.cn.gov.cn.kpbn.cn http://www.morning.cprls.cn.gov.cn.cprls.cn http://www.morning.fbpdp.cn.gov.cn.fbpdp.cn http://www.morning.nytqy.cn.gov.cn.nytqy.cn http://www.morning.smcfk.cn.gov.cn.smcfk.cn http://www.morning.fengnue.com.gov.cn.fengnue.com http://www.morning.hqgxz.cn.gov.cn.hqgxz.cn http://www.morning.zdhnm.cn.gov.cn.zdhnm.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.gmswp.cn.gov.cn.gmswp.cn http://www.morning.lmfxq.cn.gov.cn.lmfxq.cn http://www.morning.trnl.cn.gov.cn.trnl.cn http://www.morning.jpkhn.cn.gov.cn.jpkhn.cn http://www.morning.yqgbw.cn.gov.cn.yqgbw.cn http://www.morning.ffydh.cn.gov.cn.ffydh.cn http://www.morning.yptwn.cn.gov.cn.yptwn.cn http://www.morning.mltsc.cn.gov.cn.mltsc.cn
