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 ;
2 ;
表已更改。
SQL> select count(bind_id) from t_bind_diff where bind_id<=20;
COUNT(BIND_ID)
--------------
20
--------------
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 | | |
--------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------
|* 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
--------------
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
|
--------------------------------------------------------------------------------
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:
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
| 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)
--------------------------------------------------------------------------------
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BIND_ID"<=1000000)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择23行。
SQL> select count(bind_id) from t_bind_diff;
COUNT(BIND_ID)
--------------
100000
--------------
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 | | |
--------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------
| 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> 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> 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
---------- ----------
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 |
--------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
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>
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
-------- ---------- ---------- -------- ---------- --- -----------------
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 |
--------------------------------------------------------------------------------
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仅捕获首次的绑定变量;如何让智能捕获
这样可能导致产生差的执行计划
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
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 |
--------------------------------------------------------------------------------
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 / T_CAPTURE@SEL$1
Outline Data
-------------
/*+
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_CAPTURE@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" "T_CAPTURE"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------------------------------------------------
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" "T_CAPTURE"@"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):
--------------------------------------------------------------------------------
--------------------------------------
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)
--------------------------------------------------------------------------------
-----------------------------------------------------------
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 |
--------------------------------------------------------------------------------
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 / T_CAPTURE@SEL$1
Outline Data
-------------
/*+
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_CAPTURE@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" "T_CAPTURE"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------------------------------------------------
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" "T_CAPTURE"@"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):
--------------------------------------------------------------------------------
--------------------------------------
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)
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "A"[NUMBER,22], "B"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
已选择52行。
小结:
1,多次运行绑定变量sql,未多次捕获
1,多次运行绑定变量sql,未多次捕获
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-750033/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-750033/