1,測試sql繫結變在捕獲且是不同的繫結變時值時，執行計劃的變化情況
小結：短速不會在檢視中記錄繫結變數的資料
　　　既然繫結變數對於效能影響很大，如何處理此問題

SQL> create table t_bind_diff(bind_id int,bind_value varchar2(100));

表已建立。

SQL> insert into t_bind_diff select level,to_char(level)||'bind_value' from dual connect by level<=100000;

已建立100000行。

SQL> commit;

提交完成。

SQL> alter table t_bind_diff add constraint pk_bind_id primary key(bind_id)
2 ;

表已更改。

SQL> select count(bind_id) from t_bind_diff where bind_id<=20;

COUNT(BIND_ID)
--------------
            20

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bdn9bh7krg288, child number 0
-------------------------------------
select count(bind_id) from t_bind_diff where bind_id<=20

Plan hash value: 2874089213

--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     2 (100)|          |
|   1 | SORT AGGREGATE   |            |     1 |    13 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 |   INDEX RANGE SCAN| PK_BIND_ID |    20 |   260 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("BIND_ID"<=20)

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

已選擇23行。

SQL> select count(bind_id) from t_bind_diff where bind_id<=1000000;

COUNT(BIND_ID)
--------------
        100000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 64ac36k5gaurs, child number 0
-------------------------------------
select count(bind_id) from t_bind_diff where bind_id<=1000000

Plan hash value: 2806203814

--------------------------------------------------------------------------------
----

| Id | Operation             | Name       | Rows | Bytes | Cost (%CPU)| Time
   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT      |            |       |       |    63 (100)|
   |

|   1 | SORT AGGREGATE       |            |     1 |    13 |            |
   |

|* 2 |   INDEX FAST FULL SCAN| PK_BIND_ID |   107K| 1359K|    63   (2)| 00:00:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
01 |

--------------------------------------------------------------------------------
----


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

   2 - filter("BIND_ID"<=1000000)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇23行。

SQL> select count(bind_id) from t_bind_diff;

COUNT(BIND_ID)
--------------
        100000

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 03hr7ruq506h2, child number 0
-------------------------------------
select count(bind_id) from t_bind_diff

Plan hash value: 2806203814

----------------------------------------------------------------------------
| Id | Operation             | Name       | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |    62 (100)|          |
|   1 | SORT AGGREGATE       |            |     1 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   INDEX FAST FULL SCAN| PK_BIND_ID |   107K|    62   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇18行。

--繼續測試繫結變數捕獲與執行計劃的問題

SQL> var a number;
SQL> var b number;

SQL> create table t_capture(a number,b number);

表已建立。

SQL> insert into t_capture values(1,2);

已建立 1 行。

SQL> insert into t_capture values(2,4);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from t_capture where a=:a and b=:b;

未選定行

SQL> var a number;
SQL> var b number;

SQL> exec :a:=1;

PL/SQL 過程已成功完成。

SQL> exec :b:=2;

PL/SQL 過程已成功完成。

SQL> select * from t_capture where a=:a and b=:b;

         A          B
---------- ----------
         1          2

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5u4m059xkhbn, child number 0
-------------------------------------
select * from t_capture where a=:a and b=:b

Plan hash value: 1287275987

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T_CAPTURE |     1 |    26 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------

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

   1 - filter(("A"=:A AND "B"=:B))

Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇22行。

SQL>
SQL>
SQL>

SQL> select address,hash_value,name,position,datatype,was_captured,to_char(last_captured,'yyyymmdd hh24:mi:ss'),value_string from v$sql_bind_capture where sql_id='b5u4m059xkhbn';

SQL> r
1* select address,hash_value,name,position,datatype,was_captured,to_char(last_captured,'yyyymmdd hh24:mi:ss'),value_string from v$sql_bind_capture where sql_id='b5u4m059xkhbn'

ADDRESS HASH_VALUE NAME       POSITION   DATATYPE WAS TO_CHAR(LAST_CAPT
-------- ---------- ---------- -------- ---------- --- -----------------
VALUE_STRI
----------
EBDDEC50 1406746996 :A                1          2 YES 20121126 18:45:39
NULL

EBDDEC50 1406746996 :B                2          2 YES 20121126 18:45:39
NULL

SQL> exec :a:=3;

PL/SQL 過程已成功完成。

SQL> exec :b:=20;

PL/SQL 過程已成功完成。

SQL> select * from t_capture where a=:a and b=:b;

未選定行

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5u4m059xkhbn, child number 0
-------------------------------------
select * from t_capture where a=:a and b=:b

Plan hash value: 1287275987

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T_CAPTURE |     1 |    26 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------

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

   1 - filter(("A"=:A AND "B"=:B))

Note
-----
   - dynamic sampling used for this statement (level=2)

小結：繫結變數執行多次sql,v$sql_bind_capture僅捕獲首次的繫結變數；如何讓智慧捕獲
　　　這樣可能導致產生差的執行計劃

已選擇22行。

SQL> alter system flush buffer_cache;

系統已更改。

SQL> alter system flush shared_pool;

系統已更改。

SQL> exec :a:=3;

PL/SQL 過程已成功完成。

SQL> exec :b:=20;

PL/SQL 過程已成功完成。

SQL> select * from t_capture where a=:a and b=:b;

未選定行

SQL> select * from table(dbms_xplan.display_cursor('b5u4m059xkhbn',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5u4m059xkhbn, child number 0
-------------------------------------
select * from t_capture where a=:a and b=:b

Plan hash value: 1287275987

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T_CAPTURE |     1 |    26 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 /

Outline Data
-------------

/*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" ")
      END_OUTLINE_DATA
*/

Peeked Binds (identified by position):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------

   1 - :A (NUMBER): 3
   2 - :B (NUMBER): 20

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

   1 - filter(("A"=:A AND "B"=:B))

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------

   1 - "A"[NUMBER,22], "B"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇52行。

SQL> exec :a:=111

PL/SQL 過程已成功完成。

SQL> exec :b:=112

PL/SQL 過程已成功完成。

SQL> select * from t_capture where a=:a and b=:b;

未選定行

SQL> select * from table(dbms_xplan.display_cursor('b5u4m059xkhbn',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5u4m059xkhbn, child number 0
-------------------------------------
select * from t_capture where a=:a and b=:b

Plan hash value: 1287275987

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T_CAPTURE |     1 |    26 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 /

Outline Data
-------------

/*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" ")
      END_OUTLINE_DATA
*/

Peeked Binds (identified by position):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------

   1 - :A (NUMBER): 3
   2 - :B (NUMBER): 20

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

   1 - filter(("A"=:A AND "B"=:B))

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------

   1 - "A"[NUMBER,22], "B"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)

已選擇52行。

小結：
　　　1,多次執行繫結變數sql,未多次捕獲

oracle10g_11g_繫結變數bind_與最優執行計劃explain plan

相關文章