当前位置： 首页 > article >正文

绑定变量对于SQL性能的影响

article 2024/11/15 19:41:20

绑定变量

SQL> set timing on
SQL> alter session set  statistics_level=all;
VAR v4 VARCHAR2(32)
VAR v5 VARCHAR2(128)
VAR v6 VARCHAR2(128)

EXEC :v4:='007680'
EXEC :v5:='P1803585080457506817'
EXEC :v6:='202406200826069960000000000'

SELECT AD.* FROM TEST1.TESTTABLE AD WHERE AD.InstitutionID = :v4 AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo = :v6 AND Status = 30;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Elapsed: 00:00:00.87
SQL> SQL_ID     06nzu53vnm5gf, child number 0
-------------------------------------
SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE
AD.InstitutionID = :v4 AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo =
:v6 AND Status = 30

Plan hash value: 4134968517

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |      1 |        |      0 |00:00:00.86 |     542K|
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTTABLE                    |      1 |      1 |      0 |00:00:00.86 |     542K|
|*  2 |   INDEX RANGE SCAN                  | U_TESTTABLE_INSTID_TXSN      |      1 |      1 |    572K|00:00:00.08 |    6340 |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("AD"."PAYMENTTXSN"=:V5 AND "AD"."SOURCESYSTEMNO"=:V6 AND TO_NUMBER("STATUS")=30))
   2 - access("AD"."INSTITUTIONID"=:V4)

Elapsed: 00:00:00.03
SQL> alter session set  statistics_level=all;
VAR v4 VARCHAR2(32)
VAR v5 VARCHAR2(128)
VAR v6 VARCHAR2(128)

EXEC :v4:='007759'
EXEC :v5:='P1803585080457506817'
EXEC :v6:='202406200826069960000000000'

SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE AD.InstitutionID = :v4 AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo = :v6 AND Status = 30;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Elapsed: 00:00:12.48
SQL> SQL_ID     06nzu53vnm5gf, child number 0
-------------------------------------
SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE
AD.InstitutionID = :v4 AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo =
:v6 AND Status = 30

Plan hash value: 4134968517

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |      1 |        |      0 |00:00:12.48 |      11M|      4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTTABLE                    |      1 |      1 |      0 |00:00:12.48 |      11M|      4 |
|*  2 |   INDEX RANGE SCAN                  | U_TESTTABLE_INSTID_TXSN      |      1 |      1 |     12M|00:00:01.54 |     128K|      0 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("AD"."PAYMENTTXSN"=:V5 AND "AD"."SOURCESYSTEMNO"=:V6 AND TO_NUMBER("STATUS")=30))
   2 - access("AD"."INSTITUTIONID"=:V4)

Elapsed: 00:00:00.03

全使用绑定变量第一次执行sql之后，紧接着第二次执行不同绑定变量的值情况下第一次执行0.87秒第二次执行12.48秒，是因为oracle数据库为了防止大量硬解析做的优化工作。第二次会沿用第一次的执行计划。

不绑定变量

SQL> alter session set  statistics_level=all;
VAR v5 VARCHAR2(128)
VAR v6 VARCHAR2(128)

EXEC :v5:='P1803585080457506817'
EXEC :v6:='202406200826069960000000000'

SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE AD.InstitutionID = '007759' AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo = :v6 AND Status = 30;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Elapsed: 00:00:00.00

SQL_ID  a7x2cx23c1034, child number 0
-------------------------------------
SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE
AD.InstitutionID = '007759' AND AD.PaymentTxSn = :v5 AND
AD.SourceSystemNo = :v6 AND Status = 30

Plan hash value: 1281936248

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |      1 |        |      0 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTTABLE                    |      1 |      1 |      0 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | I_TESTTABLE_PAYMENTTXSN      |      1 |      1 |      0 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("AD"."SOURCESYSTEMNO"=:V6 AND "AD"."INSTITUTIONID"='007759' AND TO_NUMBER("STATUS")=30))
   2 - access("AD"."PAYMENTTXSN"=:V5)

Elapsed: 00:00:00.10

SQL> alter session set  statistics_level=all;
VAR v5 VARCHAR2(128)
VAR v6 VARCHAR2(128)

EXEC :v5:='P1803585080457506817'
EXEC :v6:='202406200826069960000000000'

SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE AD.InstitutionID = '007898' AND AD.PaymentTxSn = :v5 AND AD.SourceSystemNo = :v6 AND Status = 30;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); 

Elapsed: 00:00:00.01

SQL_ID  gn57p95cqx1ga, child number 0
-------------------------------------
SELECT AD.* FROM AGGREGATE1.TESTTABLE AD WHERE
AD.InstitutionID = '007898' AND AD.PaymentTxSn = :v5 AND
AD.SourceSystemNo = :v6 AND Status = 30

Plan hash value: 4134968517

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |      1 |        |      0 |00:00:00.01 |       6 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTTABLE                    |      1 |      1 |      0 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN                  | U_TESTTABLE_INSTID_TXSN      |      1 |      1 |      1 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("AD"."PAYMENTTXSN"=:V5 AND "AD"."SOURCESYSTEMNO"=:V6 AND TO_NUMBER("STATUS")=30))
   2 - access("AD"."INSTITUTIONID"='007898')

Elapsed: 00:00:00.04

把机构id绑定变量取消了，带入不同机构id情况下执行计划改变了，并且每次执行时间都很短，符合开发规范中对于绑定变量不可以使用在分布不均匀的列上的说法

总结：

绑定变量：

SQL在数据库认为就是一条SQL，该SQL在硬解析后会统一使用一个执行计划，在重新硬解析前都会沿用该执行计划，即便有效率高的执行计划，此时数据库也不再考虑使用，因此出现慢SQL。

不绑定变量：

SQL参数不同，在数据库认为是不同的SQL，每条SQL会分别解析出自己合适的执行计划，会减少慢SQL的发生，考虑当前环境因素，建议在报表类或分析类场景不绑定变量。

  1. 对于字段值比较少，个数小于100，且分布很不均匀的情况不要使用绑定变量，比如说状态、性别、类型等，还有绑定变量最好用于等值连接
  2. 对于那种大于小于列上有索引，并且会出现跨度很大、跨度很小两级分化很严重的情况也不要使用绑定变量

http://www.kler.cn/a/315322.html

相关文章：

  • 基于node一键发布到服务器的js脚本
  • 排序算法 -快速排序
  • 微澜：用 OceanBase 搭建基于知识图谱的实时资讯流的应用实践
  • 火车车厢重排问题，C++详解
  • 在JPA和EJB中用乐观锁解决并发问题
  • 微信小程序中使用离线版阿里云矢量图标
  • Python练习宝典：Day 1 - 选择题 - 基础知识
  • Go搭建TcpSocket服务器
  • 华润电力最新校招社招润择认知能力测评：逻辑推理数字计算语言理解高分攻略
  • K8s容器运行时，移除Dockershim后存在哪些疑惑？
  • 神经网络面试题目
  • 【AI视频】复刻抖音爆款AI数字人作品初体验
  • 什么是机器学习力场
  • 多维时序 | Matlab基于BO-LSSVM贝叶斯优化最小二乘支持向量机数据多变量时间序列预测
  • cesium.js 入门到精通(5-2)
  • CentOS7.9环境上NFS搭建及使用
  • linux-系统备份与恢复-系统恢复
  • 云硬盘EVS详细解析和配置使用方法
  • 【在Linux世界中追寻伟大的One Piece】IP分片和组装的具体过程
  • Qt5详细安装教程（包含导入pycharm）
  • LangChain4j支持的API类型
  • Linux中使用cp命令的 -f 选项，但还是提醒覆盖的问题
  • 828华为云征文｜云服务器Flexus X实例｜MacOS系统-宝塔部署Nuxt项目
  • 【C#生态园】从基础到深度学习：探索C#机器学习库
  • EclipseRCP开发（三）-如何去除顽固原生菜单项
  • 递归手撕，JSON 字符串化和解析，加权树结构的字符串解析对象，解析并返回DOM 树结构（DOMParser），解析带有层级的文本