oracle 绑定变量

最新推荐文章于 2023-08-15 09:58:26 发布

踏雪|无痕

最新推荐文章于 2023-08-15 09:58:26 发布

阅读量728

点赞数

分类专栏： oracle性能优化文章标签： oracle 绑定变量

本文链接：https://blog.youkuaiyun.com/u014531707/article/details/48374487

版权

oracle性能优化专栏收录该内容

6 篇文章

订阅专栏

本文通过实例展示了Oracle绑定变量在SQL查询中的应用，解释了绑定变量如何将硬解析转化为软解析，从而显著减少资源消耗和latch争用。通过对未使用和使用绑定变量的查询进行性能对比，揭示了绑定变量在降低硬解析次数和提高执行效率方面的优势。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

绑定变量的本质是将硬解析变成软解析，减少sql解析时的资源消耗及latch争用。

绑定变量将一个变量代替常量，使oracle在对sql做hash运算时得到同一个值。

e.g select * from user_tables where table_name='wuhen';

使用绑定变量：

select * from user_tables where table_name=:i;

测试绑定变量的效果

SQL> create table t1(c1 number,c2 number);--新建测试表

SQL> declare

2 begin

3 for i in 1..10000 loop

4 insert into t1 values(i,i+ 1);

5 end loop ;

6 commit;

7 end;

8 /

未使用绑定变量：

SQL> set timing on --开启查看执行时间

SQL> alter system flush shared_pool; --清空share_pool,避免影响实验结果

SQL> alter session set tracefile_identifier='wuhen01';

SQL> alter session set sql_trace=TRUE;--开启trace跟踪

SQL> declare

2 begin

3 for i in 1..1000 loop

4 execute immediate 'select * from t1 where c1='||i;

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed

Executed in 4.29 seconds

SQL> alter session set sql_trace=FALSE;

C:\Users\Administrator>tkprof E:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_7504_wuhen01.trc wuhen01.txt record=wuhen01sql.txt sys=no --用tkprof查看trace 文件

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse     2012      1.31       1.40          0       1001          0           0
Execute   2013      0.06       0.07          0          0          0           0
Fetch     1014      2.35       2.38          0      23029          0        1006
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     5039      3.72       3.87          0      24030          0        1006

Misses in library cache during parse: 1012 --硬解析1012
Misses in library cache during execute: 12

2010 user SQL statements in session.
   12 internal SQL statements in session.

2022 SQL statements in session.

使用绑定变量：

SQL> alter system flush shared_pool; --清空share_pool,避免影响实验结果

SQL> alter session set tracefile_identifier='wuhen02';

SQL> alter session set sql_trace=TRUE;

SQL> declare

2 begin

3 for i in 1..1000 loop

4 execute immediate 'select * from t1 where c1=:i' using i;

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed

Executed in 0.25 seconds

SQL> alter session set sql_trace=FALSE;

C:\Users\Administrator>tkprof E:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_7504_wuhen02.trc wuhen02.txt record=wuhen02sql.txt sys=no

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       14      0.06       0.03          0          1          0           0
Execute   1014      0.01       0.10          0          1          0           0
Fetch       15      0.00       0.00          0         52          0           7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     1043      0.07       0.15          0         54          0           7

Misses in library cache during parse: 13 --硬解析13

Misses in library cache during execute: 12

12 user SQL statements in session.
12 internal SQL statements in session.

24 SQL statements in session.