oracle绑定变量测试及性能对比
1.创建测试数据
2.查看cursor_sharing的值
SQL> show parameter cursor_sharing; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT
3.打开SQL追踪
SQL> alter session set sql_trace=true; Session altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /oracle/diag/rdbms/monkey/monkey/trace/monkey_ora_26356.trc
4.执行语句块（使用绑定变量）
SQL> begin 2 for x in 1..10000 loop 3 execute immediate 'select * from monkey.testtable where id=:x' using x; 4 end loop; 5 end; 6 /
5.关闭SQL追踪
SQL> alter session set sql_trace=false; Session altered.
6.格式化trace文件
$ cd /oracle/diag/rdbms/monkey/monkey/trace/ $ tkprof monkey_ora_26356.trc out.txt $ more out.txt
7.结果
select * from monkey.testtable where id=:x call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 10000 0.03 0.03 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10001 0.03 0.03 0 1 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.01 0 0 0 0 Execute 4 0.16 0.16 0 0 0 1 Fetch 2 0.00 0.01 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.16 0.19 0 0 0 2 Misses in library cache during parse: 3 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 10001 0.03 0.03 0 1 0 0 Fetch 1 0.00 0.00 0 75 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10004 0.03 0.03 0 76 0 1
從最後的整體統計可以看到，包括執行產生的遞歸和非遞歸類SQL，總共解析了5次，花費了0.22秒
8.执行语句块（不适用绑定变量）
SQL> begin 2 for x in 1..10000 loop 3 execute immediate 'select * from monkey.testtable where id ='||x; 4 end loop; 5 end; 6 /
9.结果
select * from monkey.testtable where id =1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 SQL ID: 2q3s22f8bw5wx Plan Hash: 2994338341 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TESTTABLE") FULL("TESTTABLE") NO_PARALLEL_INDEX("TESTTABLE") */ 1 AS C1, CASE WHEN "TESTTABLE"."ID"=2 THEN 1 ELSE 0 END AS C2 FROM "monkey"."TESTTABLE" SAMPLE BLOCK (1.065089 , 1) SEED (1) "TESTTABLE") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 75 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 75 0 1 select * from monkey.testtable where id =2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 select * from monkey.testtable where id =3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 總這裡可以看到，每一個值都解析了一遍，並且每一個值都要對表進行採樣 .............................................. select * from monkey.testtable where id =10000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Number of plan statistics captured: 1 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 3 0.69 0.70 0 0 0 1 Fetch 2 0.00 0.02 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.69 0.72 0 0 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 20000 9.86 9.91 0 10000 0 0 Execute 20000 0.19 0.16 0 0 0 0 Fetch 10000 10.60 10.60 0 750000 0 10000 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 50000 20.65 20.68 0 760000 0 10000
從最後的整體統計可以看到，包括執行產生的遞歸和非遞歸類SQL，總共解析了20002次，花費了21.4秒
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 使用C#创建一个MCP客户端
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 按钮权限的设计及实现