程序博客网 > 淘宝知己知彼软件

Oracle绑定变量

来源：互联网 发布：淘宝知己知彼软件 编辑：程序博客网 时间：2024/06/04 00:33
http://blog.csdn.net/wh62592855/article/details/4778343
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94588

目录：
1 绑定变量和非绑定变量在资源消耗上的比较
2 OLAP不适合使用绑定变量
3 绑定变量窥探 
4 绑定变量分级
5 相关参数
6 获取绑定变量的值
7 PLSQL与绑定变量

   绑定变量适应于OLTP，因为一般OLTP的数据倾斜的可能性比较小，而且同一SQL执行的次数比较多。如果不用绑定变量，硬解析将消耗很高的资源，严重影响效率。
   对于OLAP系统，使用绑定变量反而会带来负面的影响，比如导致SQL选择了错误的执行计划，OLATP中,sql硬解析的代价相对于查询的I/O消耗，可以忽略不计，同时由于SQL比较少，硬解析的次数不多的情况下，对系统的压力也不会造成太大的影响。
   下面我们用例子来说明在OLTP中绑定变量的好处和用法，以及为何不要再OLAP系统中使用绑定变量，最后讨论绑定变量窥探的问题。

 
ORACLE系统本身是能够对变量做绑定的，例如下面的代码：
Declare
  i number;
Begin
For i in 1..1000 loop
insert into t(i,i+1,i*1,i*2,i-1)
End loop;
End;
这段代码是不需要使用绑定变量的方法来提高效率的，ORACLE会自动将其中的变量绑定。
我们可以这样理解：这段代码执行了1000次的 insert into 测试表 (i,i+1,i*1,i*2,i-1) 语句，每次发出去的语句都是一样的。
如果把这段代码改成如下：
Declare
i number;
Sqlstr varchar2 (200);
Begin
For i in 1..1000 loop
sqlstr:='insert into t ('||to_char(i)||','||to_char(i)||'+1,'||to_char(i)||'*1,'||to_char(i)||'*2,'||to_char(i)||'-1) ';
Execute immediate sqlstr;
End loop;
End;
这段代码同样是执行了1000条insert语句，但是每一条语句都是不同的，因此ORACLE会把每条语句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用绑定变量将循环中的语句改为
sqlstr:='insert into t(:i,:i+1,:i*1,:i*2,:i-1) ';
Execute immediate sqlstr using i,i,i,i,i;
这样执行的效率就高得多了。

  • 1 绑定变量和非绑定变量在资源消耗上的比较
oracle@localhost ~]$ vi get_trace.sql

col trace_file_name for a60;
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
       P.SPID || '.trc' TRACE_FILE_NAME
  FROM (SELECT P.SPID
          FROM SYS.V$MYSTAT M, SYS.V$SESSION S, SYS.V$PROCESS P
         WHERE M.STATISTIC# = 1
           AND S.SID = M.SID
           AND P.ADDR = S.PADDR) P,
       (SELECT T.INSTANCE
          FROM SYS.V$THREAD T, SYS.V$PARAMETER V
         WHERE V.NAME = 'thread'
           AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
       (SELECT VALUE FROM SYS.V$PARAMETER WHERE NAME = 'user_dump_dest') D;

SQL> desc t;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
OBJECT_NAME                                        VARCHAR2(128)

SQL> select count(1) from t;

  COUNT(1)
----------
     69189

SQL> alter session set sql_trace=true;

Session altered.

SQL> ed
Wrote file afiedt.buf

begin
for x in 1 .. 10000 loop
execute immediate 'select * from t where ID=:x' using x;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.
SQL> @get_trace.sql

TRACE_FILE_NAME
------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9042.trc

[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9290.trc bind.txt sys=yes;

[oracle@localhost ~]$ less bind.txt
 
SQL ID: 3yxwagyspybax
Plan Hash: 3131770069
select *
from
t where id=:x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.24       0.22          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.24       0.23          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX RANGE SCAN IND_T (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)(object id 70355)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.06       0.08          0          0          0           0
Execute      3      2.07       2.10          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      2.14       2.19          0          0          0           1

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute  10010      0.40       0.48          0          0          0           0
Fetch       15      0.03       0.11          3         35          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10030      0.44       0.61          3         35          0          12

Misses in library cache during parse: 4
Misses in library cache during execute: 3

    3  user  SQL statements in session.
10010  internal SQL statements in session.
10013  SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9290.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
   10010  internal SQL statements in trace file.
   10013  SQL statements in trace file.
       8  unique SQL statements in trace file.
   60167  lines in trace file.
      47  elapsed seconds in trace file.

执行时间：2.19+0.61=2.80
CPU时间：2.14+0.44=2.58
分析次数：2+5=7
执行次数：3+10010=10013


下面看下没有使用绑定变量的情况：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9380.trc
SQL> alter session set sql_trace=true;

Session altered.

SQL> begin
  2  for x in 1 .. 10000 loop
  3   execute immediate 'select * from t where id='||x;  
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;

Session altered.

[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9380.trc unbind.txt sys=yes;

TKPROF: Release 11.2.0.1.0 - Development on Thu May 16 22:21:37 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

[oracle@localhost ~]$ less unbind.txt
 
********************************************************************************

SQL ID: 07hpk6hpb7pp8
Plan Hash: 3131770069
select *
from
t where id=1


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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX RANGE SCAN IND_T (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)(object id 70355)

********************************************************************************

SQL ID: 1nbw0urx7srxy
Plan Hash: 3131770069
select *
from
t where id=2


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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX RANGE SCAN IND_T (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)(object id 70355)

********************************************************************************

SQL ID: 5n7tzb8hh3t8h
Plan Hash: 3131770069
select *
from
t where id=3


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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX RANGE SCAN IND_T (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)(object id 70355)

********************************************************************************

...........省略

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.06       0.06          0          0          0           0
Execute      3      3.07       3.88          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      3.13       3.95          0          0          0           1

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    10000      9.64      13.58          0          0          0           0
Execute  10000      0.29       0.34          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20000      9.94      13.93          0          0          0           0

Misses in library cache during parse: 10000

    3  user  SQL statements in session.
10000  internal SQL statements in session.
10003  SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9380.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
   10000  internal SQL statements in trace file.
   10003  SQL statements in trace file.
   10003  unique SQL statements in trace file.
   80098  lines in trace file.
      74  elapsed seconds in trace file.

执行时间：3.95+13.93=17.88
CPU时间：3.13+9.94=13.07
分析次数：2+10000=10002
执行次数：3+10000=10003

结论：
两种情况显示，绑定变量SQL的资源消耗源少于未绑定变量SQL的资源消耗，SQL执行的次数越多，这种差距越明显。未绑定变量SQL的资源主要消耗在硬解析上。


  • 2OLAP不适合使用绑定变量
OLAP系统在SQL的操作中比较复杂，大多数时候运行的是一些报表SQL，这些SQL经常会用到聚合查询(比如GROUP BY)，而且结果集也非常庞大，在这种情况下，索引并不是必然的选择，甚至有的时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词条件不同，执行计划都可能不同。
下面用例子说明OLAP系统不适合使用绑定变量：
SQL> create table t(object_id int,object_name varchar2(1000)) partition by range(object_id)
  2  (partition p1 values less than(5000),
  3  partition p2 values less than(10000),
  4  partition p3 values less than(15000),
  5  partition p4 values less than(20000),
  6  partition pm values less than(maxvalue)
  7  )
  8  /

表已创建。

SQL> begin
  2    for i in 1..300 loop
  3      insert into t select object_id,object_name from dba_objects;
  4    end loop;
  5  end;
  6  /

SQL> select count(1) from t partition(p1);

  COUNT(1)
----------
   1498800

SQL> select count(1) from t partition(p2);

  COUNT(1)
----------
   1487400

SQL> select count(1) from t partition(p3);

  COUNT(1)
----------
   1500000

SQL> select count(1) from t partition(p4);

  COUNT(1)
----------
   1389600

SQL> select count(1) from t partition(pm);

  COUNT(1)
----------
  21401100

创建本地索引:

SQL> create index ind_t_id on t(object_Id) local;

索引已创建。

收集统计信息:
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL 过程已成功完成。

下面执行3次只有谓词不同的SQL:
SQL> set autotrace traceonly explain
(1):
SQL> select object_id,count(*) from t
  2  where object_id>10000 and object_id<40000 group by object_id;

执行计划
----------------------------------------------------------
Plan hash value: 4172664537

-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 29808 |   145K| 16593   (3)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|          | 29808 |   145K| 16593   (3)| 00:00:01 |     3 |     5 |
|   2 |   HASH GROUP BY          |          | 29808 |   145K| 16593   (3)| 00:00:01 |       |       |
|*  3 |    INDEX FAST FULL SCAN  | IND_T_ID |  8838K|    42M| 16358   (1)| 00:00:01 |     3 |     5 |
-----------------------------------------------------------------------------------------------------

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

   3 - filter("OBJECT_ID"<40000 AND "OBJECT_ID">10000)

(2):
SQL> select object_id,count(*) from t
  2  where object_id>10000  group by object_id;

执行计划
----------------------------------------------------------
Plan hash value: 4172664537

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 82057 |   400K|       | 43024   (2)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ITERATOR|          | 82057 |   400K|       | 43024   (2)| 00:00:02 |     3 |     5 |
|   2 |   HASH GROUP BY          |          | 82057 |   400K|   279M| 43024   (2)| 00:00:02 |       |       |
|*  3 |    INDEX FAST FULL SCAN  | IND_T_ID |    24M|   116M|       | 16343   (1)| 00:00:01 |     3 |     5 |
-------------------------------------------------------------------------------------------------------------

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

   3 - filter("OBJECT_ID">10000)

(3):
SQL> select object_id,count(*) from t
  2  where object_id>1 and object_id<5999  group by object_id;

执行计划
----------------------------------------------------------
Plan hash value: 2931106398

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |  5959 | 29795 |  3108   (2)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|      |  5959 | 29795 |  3108   (2)| 00:00:01 |     1 |     2 |
|   2 |   HASH GROUP BY          |      |  5959 | 29795 |  3108   (2)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL     | T    |  1766K|  8626K|  3066   (1)| 00:00:01 |     1 |     2 |
-------------------------------------------------------------------------------------------------

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

   3 - filter("OBJECT_ID"<5999 AND "OBJECT_ID">1)

   从结果中我们可以清楚地看到，3条SQL语句只有谓词不同，但Oracle分别选择INDEX FAST FULL SCAN和TABLE ACCESS FULL两种不同的执行计划。
根据扫描的实例和分析，可以大致得出下面的结论：
（1）OLAP系统完全没有必要绑定变量，那样只会带来负面的影响，比如导致SQL选择了错误的执行计划。让ORACLE对每条SQL做硬解析，确切地指定谓词条件的值，这对执行计划的选择至关重要，这样做的原因在于OLAP系统中，SQL硬解析的代价是可以忽略的，系统的资源基本上是用于做大的SQL查询，和查询比较起来，SQL解析消耗的资源显然微不足道。
（2）在OLAP系统上，让Oracle确切地指定谓词的数值至关重要。它直接决定了SQL执行计划的选择，这样做的方式就是不要绑定变量，不然可能存在绑定变量窥探的问题。
（3）在OLAP系统中，表和索引的分析显得至关重要，因为它是Oracle为SQL做出正确的执行计划的信息来源和依据，所以需要建立一套能够满足系统要求的对对象分析的定时执行任务。

  •  3 绑定变量窥探

官方文档：
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94588


为何发生绑定变量窥探
答案是：在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。
即会以第一个语句中绑定的值为基准，建立执行计划。如果列值分布不平均。绑定变量窥探只发生在硬解析阶段，即SQL被第一次执行的时候，之后的变量将不会再做peeking。

下面的例子 参考oracle性能诊断艺术 第二章

VARIABLE id NUMBER

COLUMN is_bind_sensitive FORMAT A17
COLUMN is_bind_aware FORMAT A13
COLUMN is_shareable FORMAT A12
COLUMN peeked FORMAT A6
COLUMN predicate FORMAT A9 TRUNC

COLUMN sql_id NEW_VALUE sql_id

SET ECHO ON


ALTER SYSTEM FLUSH SHARED_POOL;

DROP TABLE t;

CREATE TABLE t
AS
SELECT rownum AS id, rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 1000;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user,
    tabname          => 't',
    estimate_percent => 100,
    method_opt       => 'for all columns size 1'
  );
END;
/

SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;

COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)
---------- ----------------- ---------- ----------
      1000              1000          1       1000


SELECT count(pad) FROM t WHERE id < 990;
COUNT(PAD)
----------
       989


SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < 990

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------


已选择14行。


SELECT count(pad) FROM t WHERE id < 10;
COUNT(PAD)
----------
         9


SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < 10

Plan hash value: 4270555908

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------

以上硬解析，结果显示正确，第一个执行计划为全表扫描，第二个为索引范围扫描。
下面使用绑定变量，首先还是取绝大多数数据。

EXECUTE :id := 990;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
-------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------


已选择14行。

全表扫描，正确。

绑定变量的值换成10，即只占少部分。

EXECUTE :id := 10;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

还是全表扫描，可见和非绑定变量相比，是不正确的。
下面清空共享池，第一个使用10 作为绑定值。

ALTER SYSTEM FLUSH SHARED_POOL;

EXECUTE :id := 10;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------
正确，选择了索引范围扫描。

绑定值改成990。

EXECUTE :id := 990;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------
错误，还是和之前一样，使用了索引范围扫描。


//11g中采取了扩展的游标共享（即适应性游标共享），可以基于统计信息，对绑定变量重新做分析，以确定是否生成新的执行计划。
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_text = 'SELECT count(pad) FROM t WHERE id < :id'
ORDER BY child_number;


--is_bind_sensitive：不仅指出是否使用绑定变量窥探来生成执行计划，而且指出这个执行计划是否依赖于窥探到的值。
--is_bind_aware：表明游标是否使用了扩展的游标共享。如果为 N,则这个游标不可用。
--is_shareable：表明游标是否被共享，为N,则不可用


 
EXECUTE :id := 10;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));


SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY child_number;

CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------ ----------------- ------------- ------------
0 Y N N
1 Y Y Y
2 Y Y Y


SELECT * FROM table(dbms_xplan.display_cursor('&sql_id',NULL));



SELECT child_number, peeked, executions, rows_processed, buffer_gets
FROM v$sql_cs_statistics
WHERE sql_id = '&sql_id'
ORDER BY child_number;

CHILD_NUMBER PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------ ---------- -------------- -----------
0 Y 1 19 3
1 Y 1 990 19
2 Y 1 19 3


SELECT child_number, trim(predicate) AS predicate, low, high
FROM v$sql_cs_selectivity
WHERE sql_id = '&sql_id'
ORDER BY child_number;

CHILD_NUMBER PREDICATE      LOW      HIGH
------------ ---------     -------------------  
1            <ID           0.890991 1.088989
2            <ID           0.008108 0.009910


SQL> SELECT child_number, bucket_id, count
  2  FROM v$sql_cs_histogram
  3  WHERE sql_id = '&sql_id'
  4  ORDER BY child_number, bucket_id;
原值    3: WHERE sql_id = '&sql_id'
新值    3: WHERE sql_id = 'asth1mx10aygn'

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          1
           0          2          0
           1          0          1
           1          1          0
           1          2          0

已选择6行。

可以参考ORACLE高级知识->metalink 文章-> Query using Bind Variables is suddenly slow 
  • 4绑定变量分级 
  32字节以内为第一级(number类型以22字节为准)，33-128为第二级，129-2000为第三级，大于2000的为第四级。
SET ECHO OFF
REM ***************************************************************************
REM ******************* Troubleshooting Oracle Performance ********************
REM ************************ http://top.antognini.ch **************************
REM ***************************************************************************
REM
REM File name...: bind_variables.sql
REM Author......: Christian Antognini
REM Date........: August 2008
REM Description.: This script shows how and when bind variables lead to the
REM               sharing of cursors.
REM Notes.......: This script works as of 10g only. The 9i version is named
REM               bind_variables_9i.sql.
REM Parameters..: -
REM
REM You can send feedbacks or questions about this script to top@antognini.ch.
REM
REM Changes:
REM DD.MM.YYYY Description
REM ---------------------------------------------------------------------------
REM 08.03.2009 Fixed typo in comment
REM 24.06.2010 Because of 11g modified/added queries against V$SQL_SHARED_CURSOR
REM ***************************************************************************

SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET SCAN ON

@../connect.sql

SET ECHO ON

REM
REM Setup test environment
REM

DROP TABLE t;

CREATE TABLE t (n NUMBER, v VARCHAR2(4000));

ALTER SYSTEM FLUSH SHARED_POOL;

COLUMN sql_id NEW_VALUE sql_id

PAUSE

REM
REM This script only works if:
REM - the database character set is a single-byte encoding (e.g. WE8MSWIN1252)
REM - the database national character is a two-byte encoding (e.g. AL16UTF16)
REM

SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PAUSE

REM
REM Execute three times the same SQL statement. Every time the value of the
REM bind variable is changed. Note that the SQL statement uses two bind
REM variables: a NUMBER and a VARCHAR2(32).
REM

VARIABLE n NUMBER
VARIABLE v VARCHAR2(32)

EXECUTE :n := 1; :v := 'Helicon';

INSERT INTO t (n, v) VALUES (:n, :v);

EXECUTE :n := 2; :v := 'Trantor';

INSERT INTO t (n, v) VALUES (:n, :v);

EXECUTE :n := 3; :v := 'Kalgan';

INSERT INTO t (n, v) VALUES (:n, :v);

PAUSE

REM
REM Display information about the associated child cursors
REM

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

PAUSE

REM
REM Re-execute the SQL statement two times. Compared to the previous
REM executions, the size of the VARCHAR2 bind variable is increased.
REM

VARIABLE v VARCHAR2(33)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

VARIABLE v VARCHAR2(128)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

PAUSE

REM
REM Display information about the associated child cursors
REM

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

PAUSE

REM
REM Re-execute the SQL statement two times. Compared to the previous
REM executions, the size of the VARCHAR2 bind variable is increased.
REM

VARIABLE v VARCHAR2(129)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

VARIABLE v VARCHAR2(2000)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

PAUSE

REM
REM Display information about the associated child cursors
REM

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

PAUSE

REM
REM Re-execute the SQL statement two times. Compared to the previous
REM executions, the size of the VARCHAR2 bind variable is increased.
REM

VARIABLE v VARCHAR2(2001)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

VARIABLE v VARCHAR2(4000)

EXECUTE :n := 4; :v := 'Terminus';

INSERT INTO t (n, v) VALUES (:n, :v);

PAUSE

REM
REM Display information about the associated child cursors
REM

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

PAUSE

REM
REM Several child cursors were generated because of the increasing size of the
REM VARCHAR2 bind variable.
REM

COLUMN bind_mismatch FORMAT a13
COLUMN incomplete_cursor FORMAT a17
COLUMN bind_length_upgradeable FORMAT a23

SELECT child_number, bind_mismatch, incomplete_cursor
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id';

PAUSE

REM The following query works as of 11.2 only

SELECT child_number, bind_length_upgradeable
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id';

PAUSE

REM
REM The metadata associated to the bind variables confirms that the database
REM engine uses bind variable graduation to minimize the number of child
REM cursors.
REM

SELECT s.child_number, m.position, m.max_length,
       decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2;

PAUSE

REM
REM Show that the boundaries for bind variable graduation (32, 128 and 2000)
REM are bytes, not characters. For that purpose, the national character set
REM is used.
REM

ALTER SYSTEM FLUSH SHARED_POOL;

VARIABLE n NUMBER
VARIABLE v NVARCHAR2(16)

EXECUTE :n := 1; :v := 'Helicon';

INSERT INTO t (n, v) VALUES (:n, :v);

VARIABLE v NVARCHAR2(17)

EXECUTE :n := 2; :v := 'Trantor';

INSERT INTO t (n, v) VALUES (:n, :v);

PAUSE

SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';

PAUSE

SELECT child_number, bind_mismatch, incomplete_cursor
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id';

PAUSE

REM The following query works as of 11.2 only

SELECT child_number, bind_length_upgradeable
FROM v$sql_shared_cursor
WHERE sql_id = '&sql_id';

PAUSE

SELECT s.child_number, m.position, m.max_length,
       decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) aS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2;

 
  • 5 相关参数

CURSOR_SHARING

PropertyDescriptionParameter typeStringSyntaxCURSOR_SHARING = { EXACT | FORCE }  11gr2版本推荐使用自适应游标，废弃了 similar（再使用绑定变量处理后，会判断执行计划是否合理）
exact完全相同的语句才会生成相同的执行计划，适应于已经使用了绑定变量的场景；
force强制将字面值解析为绑定变量。Default valueEXACTModifiableALTER SESSION, ALTER SYSTEMBasicNo

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

  • FORCE

    Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

  • EXACT

    Only allows statements with identical text to share the same cursor.

    Notes:

    • If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment.

When to Set CURSOR_SHARING to a Nondefault Value

The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.

If stored outlines were generated with CURSOR_SHARING set to EXACT, then the database does not use stored outlines generated with literals. To avoid this problem, generate outlines with CURSOR_SHARING set to FORCE and use the CREATE_STORED_OUTLINES parameter.

Setting CURSOR_SHARING to FORCE has the following drawbacks:

  • The database must perform extra work during the soft parse to find a similar statement in the shared pool.
  • There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT     statement. However, the actual length of the data returned does not change.
  • Star transformation is not supported.

When deciding whether to set CURSOR_SHARING to FORCE, consider the performance implications of each setting. When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement. For example, consider the following statement:

SELECT * FROM hr.employees WHERE employee_id = 101

If FORCE is used, then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.

Note:

Starting with Oracle Database 11g Release 2, setting the value of the CURSOR_SHARING to SIMILAR is obsolete. Consider using adaptive cursor sharing instead.


  • 6 获取绑定变量的值
例如如下语句：

 SELECT distinct p.* FROM TB_BM_QOS_PROCESS p ,TB_BM_QOS_INSTR i   where p.PR
OCESS_ID =
i.PROCESS_ID           AND      p.PROCESS_ID != :1                  AND
p.PROC_STATE = :2             AND     i.INSTR_POLICY_KEY=:3



 
SQL> select dbms_sqltune.extract_bind(bind_data, 3) .value_string from v$sql where sql_id = 'd9h7aaq9pcs7f';

 
DBMS_SQLTUNE.EXTRACT_BIND(BIND
--------------------------------------------------------------------------------
CMD_TYPE=001
CONTROL_DIR=0
DEST_IP=0.230.0.24
DEV_IP=10.1.0.12
SRC_IP=0.0.0.0
USED_ID=JUNIPER--BAS-12prot5558@163.gd
USED_IP=0.230.0.24

 有时候，我们从AWR报告中抽取出一个TOP SQL 来分析，上面都是绑定了变量，而数据无法得到,进而无法得到真正的执行计划。



猛一看，数据来源无从下手，但是 在ORACLE 10G 的时候就已经给我们留了一个隐藏参数的后门。

ORACLE为我们提供了一个隐含参数_cursor_bind_capture_interval，这个就是控制绑定变量抓取频率的参数，默认值是900，单位是秒，也就是15分钟。下面通过一个例子来看看调整这个参数对抓取绑定变量的影响：
ORA10G 使用隐含参数 来查看变量的值
参数: 
SQL>  alter system set "_cursor_bind_capture_interval"=5 scope=both;
这个参数理论上是900秒更新一次 V$SQL_BIND_CAPTURE 视图，使里面的变量值得带填充。
     
我们通过缩小这个时间值来查看更新的变量。
SQL> conn scott/tiger;
Connected.
SQL> set serveroutput on;
设置变量
SQL> var a number;
SQL>begin
       :a :=123;
   end;
    /
     
PL/SQL procedure successfully completed.
     
SQL> SELECT *  FROM T WHERE ID = :a;
        ID
----------
       123
     
SYS用户查询得到SQL_ID：
SELECT * FROM v$session T
 WHERE   T.TYPE='USER' AND T. USERNAME='SCOTT' ;
     
SQL> SELECT name,T.VALUE_STRING 
      FROM V$SQL_BIND_CAPTURE  T
     WHERE  T.SQL_ID='2hufysyv5sfmq';
      
NAME VALUE_STRING
----- ----------
:A    123
如果我们将隐含参数_cursor_bind_capture_interval 的值设为100秒,我们再进行测试
SQL>begin
      :a :=1123;
  end;
  /
SQL> SELECT *  FROM T WHERE ID = :a;
     
        ID
----------
      1123
SQL> SELECT NAME,T.VALUE_STRING FROM V$SQL_BIND_CAPTURE  T WHERE  T.SQL_ID='2hufysyv5sfmq';
     
NAME VALUE_STRING
----- ----------
:A    123

可见更新速度没有测试一那么快了。这也证明_cursor_bind_capture_interval 参数确实控制着变量值的更新频率；
10G 之后的方法
 SELECT snap_id, NAME, position , value_string, last_captured,WAS_CAPTURED
   FROM dba_hist_sqlbind 
  WHERE sql_id = '018x8jv0c9900' AND snap_id ='406';  
----------SNAP_ID就是AWR报告的快照ID。
----------name,绑定变量的名称
----------position，绑定值在SQL语句中的位置，以1，2，3进行标注
---------value_string，就是绑定变量值
---------,last_captured，最后捕获到的时间
---------WAS_CAPTURED，是否绑定被捕获，where子句前面的绑定不进行捕获。

dba_hist_sqlbind视图强大的地方在于，它记录了每个AWR报告里的SQL的绑定变量值，当然这个绑定变量值也是AWR生成的时候从v$sql_bind_capture采样获得的。

通过这个视图，我们能够获得比较多的绑定变量值，对于我们排查问题，这些值一般足够了。
还有一个需要注意的地方是，这两个视图中记录的绑定变量只对where条件后面的绑定进行捕获，这点需要使用的时候注意。

查询dba_hist_sqlbind.VALUE_STRING列,DBA_HIST_SQLBIND是视图 V$SQL_BIND_CAPTURE 历史快照
查询wrh$_sqlstat
select dbms_sqltune.extract_bind(bind_data, 1).value_string
 from wrh$_sqlstat
where sql_id = '018x8jv0c9900'----根据绑定变量的多少增加dbms_sqltune.extract_bind(bind_data, 2).value_string 等
  •  7 PLSQL与绑定变量

由于几个方面的原因，开发人员通常会选择不在 PL/SQL 中使用动态 SQL。一个方面的原因是这样做有风险，因为 SQL 语句到运行时期才解析，
所以捕获简单语法错误的难度会更大。此外，很多动态 SQL 的尝试都会带来性能问题，简单编写动态 SQL 的复杂性对动态 SQL 也有负面的影响。

这里给出一个简单的测试，我们使用两个方法将某段范围内的数字插入到一个表中，然后对两种方法进行了对比：

drop table mytest;
create table mytest(i number);

prompt 1)  using pure insert
set timing on;
begin
     for i in 1..10000 loop
          insert into mytest values(i);
     end loop;
end;
/
show errors;
set timing off;

truncate table mytest;

prompt 2) using execute immediate
set timing on;
begin
     for i in 1..10000 loop
          execute immediate 'insert into mytest values('||i||')';
     end loop;
end;
/
show errors;
set timing off;


在我的测试中，第二个 PL/SQL 块的运行时间相当于第一个 PL/SQL 块运行时间的六倍。然而，这不是一个公平的比较，因为它违反了动态 SQL 的一个黄金规则：在能够绑定时永远不要串联（concatenation）。固定串联意味着需要解析、分解和执行10000个独立的 SQL 语句。
第一个例子中的原生 PL/SQL 插入语句实际上生成一个递归 SQL 语句——'insert into mytest values(:1)'——然后在每次迭代时绑定具体值。

下面是动态 SQL 的一个稍好的对比：

truncate table mytest;

prompt 3) using execute immediate with bind
set timing on;
begin
for i in 1..10000 loop
execute immediate 'insert into mytest values(:i)' using in i;
end loop;
end;
/
show errors;
set timing off;

这段代码的运行时间是第一个块的运行时间的两倍。其速度是第二个 PL/SQL 块运行速度的三倍。然而，动态 SQL 依然占据劣势。
execute immediate依然会进行解析，但是它使用 SQL 共享来分解语句。

理想的情况是 PL/SQL 允许我们在 OPEN 过程中创建游标，然后再有一些新的命令允许我们 EXECUTE <cursor> USING……
即使在 Oracle 10g 中也没有这样的命令。我已经观察原生动态 SQL 很长时间了，我依然发现在这些情况下它没有DBMS_SQL 强大。

PL/SQL 用户指南警告说DBMS_SQL 的性能没有EXECUTE IMMEDIATE 的性能好，但是让我们自己试一下看看：

truncate table mytest;

prompt 4) using dbms_sql
set timing on;
declare
     c integer;
     r integer;
begin
     c := dbms_sql.open_cursor;
     for i in 1..10000 loop
          dbms_sql.parse(c,'insert into mytest values('||i||')',dbms_sql.native);
          r := dbms_sql.execute(c);
     end loop;
     dbms_sql.close_cursor(c);
end;
/
show errors;
set timing off;

truncate table mytest;

prompt 5) using dbms_sql with bind
set timing on;
declare
     c integer;
     r integer;
begin
     c := dbms_sql.open_cursor;
     dbms_sql.parse(c,'insert into mytest values(:i)',dbms_sql.native);
     for i in 1..10000 loop
          dbms_sql.bind_variable(c,':i',i);
          r := dbms_sql.execute(c);
     end loop;
     dbms_sql.close_cursor(c);
end;
/
show errors;
set timing off;


第四个例子的问题与第二个例子一样——它使用了串联。这也说明 PL/SQL 手册在这种情况下是对的。第四个例子的运行时间是第一个例子运行时间的七倍。DBMS_SQL 确实比EXECUTE IMMEDIATE 慢，但是EXECUTE IMMEDIATE 依然没有DBMS_SQL 灵活。

第五个例子是一个惊喜——它的完成时间与第一个例子几乎完全相同。它的执行过程几乎与第一个例子完全相同，它的编码难度明显比较大并且更容易出现错误，但是对于很多任务来说，动态 SQL 可能很必要。

更进一步地对比，等效的 Java 存储过程（总是动态的），它的运行时间与原始的 PL/SQL 以及带绑定的DBMS_SQL 几乎相同：

import java.sql.*;

public class dynsql{
public static void ins() throws SQLException{
Connection conn= DriverManager.getConnection("jdbc:default:connection:");
PreparedStatement pstmt = conn.prepareStatement("insert into mytest values(?)");
for (int i=0;i<10000;i++){
pstmt.setInt(1,i);
pstmt.executeUpdate();
}
pstmt.close();
}
}

set timing off;
create or replace procedure dynsql_ins as language java name 'dynsql.ins()';
/
show errors;

truncate table mytest;
set timing on;
exec dynsql_ins;
set timing off;



 
0 0
淘宝知己知彼软件 淘宝知己知彼软件
原创粉丝点击
热门IT博客
淘宝退款邮费淘宝退款邮费
行政管理工商管理知乎行政管理工商管理知乎
linux安装amd显卡驱动
管螺纹怎么编程
移动广告网络平台
家装平面模板软件
淘宝买岛国片怎么搜索
hive元数据查看大小
宁泽涛傅园慧的知乎
知乎 黄盈盈
淘宝实拍保护怎么授权
淘宝出名的店铺
淘宝会员等级怎么看
淘宝旗下的是9块9包邮
js 输出json字符串
属下知罪by枯目腐书
电脑点歌软件下载
如何找回网络密码
java equals方法重写
如果还有明天 知乎
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 樱桃状血管瘤 肝血管瘤会自动消失吗 高压180会爆血管吗 喝醋能软化血管吗 风湿性血管炎 肝血管瘤怎么办 通血管的食物 血管硬化吃什么能软化 血管闭塞性脉管炎 草莓状血管瘤 血管堵塞吃什么好 血管堵塞是什么引起的 血管为什么会堵塞 腿部动脉血管堵塞 血管硬化吃什么食物 下肢血管堵塞的症状 腿血管堵塞的症状 血管性头痛的症状 婴儿毛细血管扩张 眼睛动脉血管堵塞 下肢动脉血管闭塞 小腿静脉血管堵塞 局部毛细血管扩张 血管痉挛是怎么回事 静脉血管堵塞的症状 血管性头疼的症状 下肢动脉血管堵塞 老人血管硬化怎么办 大腿毛细血管扩张 血管性偏头痛怎么办 毛细血管破裂的症状 颈部动脉血管狭窄 肝血管内皮细胞瘤 小腿血管静脉曲张 血管性帕金森综合征 血管畸形是什么病 血管性头痛怎么办 血管动脉硬化怎么办 小腿静脉血管曲张 血管神经性头痛怎么办 心脏血管破裂

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里