这里编写一系列模拟程序，近似模拟多个SESSSION进行硬解析、软解析、快解析运行所需要的挂钟时间
drop table i_binds purge;
create table t_binds
(
a int,
b varchar2(10)
);
drop table run_time_record purge;
create table run_time_record
(
run_type varchar2(30),
sid varchar2(10),
session_num int,
run_time number(10,2)
)
create or replace procedure do_insert_hp_(p1 in integer)
as
p integer;
sql_string varchar2(50);
v_sid int;
begin
delete from run_time_record where run_type='hard parse' and session_num=p1;
execute immediate 'truncate table t_binds';
p:=dbms_utility.get_time;
for i in 1..10000 loop
sql_string:= 'insert into t_binds values('||i||','''||i||'abc'')';
--dbms_output.put_line(sql_string);
execute immediate sql_string;
end loop;
select sid into v_sid from v$mystat where rownum<2;
insert into run_time_record values ('hard parse',v_sid,p1,(dbms_utility.get_time-p)/100);
commit;
end;
create or replace procedure do_insert_sp_(p1 in integer)
as
p integer;
sql_string varchar2(50);
v_sid int;
begin
delete from run_time_record where run_type='soft parse' and session_num=p1;
execute immediate 'truncate table t_binds';
p:=dbms_utility.get_time;
for i in 1..10000 loop
sql_string:= 'insert into t_binds values(:1,:2)';
execute immediate sql_string using i,i||'abc';
end loop;
select sid into v_sid from v$mystat where rownum<2;
insert into run_time_record values(' soft parse ',v_sid,p1,(dbms_utility.get_time-p)/100);
commit;
end;
create or replace procedure do_insert_fp_(p1 in integer)
as
p integer;
v_sid int;
begin
delete from run_time_record where run_type='fast parse' and session_num=p1;
execute immediate 'truncate table t_binds';
p:=dbms_utility.get_time;
for i in 1..10000 loop
insert into t_binds values(i,i||'abc');
end loop;
select sid into v_sid from v$mystat where rownum<2;
insert into run_time_record values(' fast parse ',v_sid,p1,(dbms_utility.get_time-p)/100);
commit;
end;
create or replace procedure run_mutil_task(p_task_name in varchar2, pt in integer )
as
l_job number;
task_name varchar2(100);
begin
for i in 1..pt loop
task_name:=p_task_name||'('||to_char(i)||');';
dbms_job.submit(l_job,task_name);
dbms_output.put_line(l_job);
end loop;
end;
目前本机数据库存在一些问题
可以通过以上测试得出以下图所显示的结论：
1、 使用直接变量引起的硬解析的应用系统中，随着SESSION数增加，响应时间会逐渐缓慢(如果我们作CPU的负荷—SESSION数的关系，会发现在多SESSION情况下，这这样系统CPU的工作负荷很高)；
2、 软解析也会表现出相同的特征，只是影响的程度要小得多
3、PL/SQL 封装的动态SQL 引起软解析， 而使用变量静态SQL 的变量是绑定变量，可以最大可能避免软分析。
JDBC的数据绑定写法
import java.sql.*;
import Oracle.jdbc.pool.*;
class prog2 {
public static void main (String args []) throws SQLException
{
String url = "jdbc:oracle:oci8:@//localhost/gx";
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setUser("hr");
ods.setPassword("hr");
Connection conn = ods.getConnection();
PreparedStatement cmd = conn.prepareStatement
("SELECT first_name, last_name FROM employees WHERE employee_id = ?");
cmd.setString(1, "101");
ResultSet rs = cmd.executeQuery();
rs.next();
System.out.println ("Last Name: " +
rs.getString(1) +
", First Name: " +
rs.getString(2));
conn.close();
}
}
CURSOR_SHAREING= FORCE可以解决部分只用直接变量的写法的应用，但是CURSOR_SHAREING= FORCE并不能解决soft parse的问题。
CURSOR_SHAREING= FORCE还能引起其它问题：
1、 将语句中所有的字符与数字都转化为绑定变量，这可能导致不同的执行计划；
2、 查询列的长度可能发生改变；
3、 查询优化更困难
因此，最好的方法，还是使用绑定变量。
使用绑定变量的例外规则
1、在WHERE的语句中，总有 FIELDS=CONST,这时候使用直接变量；
绑定变量窥视(bind peeking)
查询优化器在用户第一次运行SQL时候，会窥视用户定义的绑定变量。这个特征不仅决定了语句的过滤条件，也决定了用直接变量代替绑定变量，在后来再执行这条语句的时候，不再会去窥视变量，即使以后不同的直接变量，还是会共享以前的游标。
如何解决绑定变量窥视影响正确的执行计划的执行？
11G新特性：自适应游标共享Adaptive Cursor Sharing
当我们在对高变异数据(skewed data)列使用绑定变量的时候，由于在硬解析法发生了绑定变量窥视的缘故，可能导致使用差的执行计划。11g提供了一个新的特性Adaptive Cursor Sharingy用来解决这个问题，可以为不同的绑定变量真实的值，提供不同的执行计划。
要使用这个新的特性，不需要需要额外的构建或者参数设定。以下的代码给出一个adaptive cursor sharing的例子
Test Case
First we create and populate a test table with skewed data for record_type 2
CREATE TABLE adaptive_test (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT adaptive_test_pk PRIMARY KEY (id)
);
CREATE INDEX adaptive_test_record_type_i ON adaptive_test(record_type);
DECLARE
TYPE t_adaptive_test IS TABLE OF adaptive_test%ROWTYPE;
l_tab t_adaptive_test := t_adaptive_test() ;
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO adaptive_test VALUES l_tab(i);
COMMIT;
END;
/
Gather statistics for the table
EXEC DBMS_STATS.gather_table_stats(USER, 'adaptive_test', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);
The data in the RECORD_TYPE column is skewed we can check using :
select record_type,count(*) from adaptive_test
group by record_type having count(*) >1;
Here we can can see record_type of 2 is repeated 50000 so full table scan is better than index in this case
RECORD_TYPE COUNT(*)
----------- ----------
2 50000
显示adaptive cursor如何工作的
This will be shown by executing the query below and checking how the execution plan is changed to adapt to the new bind value.
SELECT MAX(id) FROM adaptive_test WHERE record_type = 1;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
This query has used the index as we would expect. Now we repeat the query, but this time use a bind variable.
VARIABLE bind NUMBER;
EXEC :bind := 1;
SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | &n sp; | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
So we ran what amounted to the same query, and got the same result and execution plan. The optimizer picked an execution plan
that it thinks is optimium for query by peeking at the value of the bind variable. The only problem is, this would be totally
the wrong thing to do for other bind values.
VARIABLE bind NUMBER;
EXEC :bind := 2;
SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ADAPTIVE_TEST | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ADAPTIVE_TEST_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y',
so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind';
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.
VARIABLE bind NUMBER;
EXEC :bind := 2;
SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| ADAPTIVE_TEST | 48031 | 422K| 138 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM adaptive_test WHERE record_type = :bind';
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
9bmm6cmwa8saf 1 Y Y
is_bind_sensitive (Y), means that Oracle is using multiple plans depending on bind variable.
is_bind_aware (Y), means that Oracle knows that the different data patterns may result depending on bind value.
Oracle switches to a bind-aware cursor and may hard parse the statement.
总结
In 11g, Oracle uses bind-aware cursor matching.
Share the plan when binds values are equivalent.
Plans are marked with selectivity range.
If current bind values fall within range they use the same plan .
Create a new plan if binds are not equivalent.
1498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
