调优实践04–利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句

6月 15th, 2014
利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL语句
1.10g之后的v$sql视图增加了两列:
1.EXACT_MATCHING_SIGNATURE:Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
2.FORCE_MATCHING_SIGNATURE:Signature used when the CURSOR_SHARING parameter is set to FORCE,也就是SQL语句在CURSOR_SHARING=FORCE模式下运行的签名值;
2.控制共享游标的参数:CURSOR_SHARING
1.FORCE:在不改变语义的情况下都使用游标共享,可能使用的并不是最优的执行计划,强制绑定变量,并在左边的列进行to_char转换;
2.EXACT:默认值,只有在SQL语句完全一致的情况下才共享游标;
3.SIMILAR:在不改变语义或者不降低性能的情况下,尽量采用游标共享,一般用于第三方的系统,无法修改源码的情况;
3.执行计划相关内容:
1.v$sql_plan:在Shared Pool中的Library Cache中保存的实际使用的执行计划;
2.查看实际的执行计划:SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));
4.在CURSOR_SHARING=EXACT模式下查看使用字面值的sql语句;
4.1创建测试表;
CREATE TABLE tb_sqltuning AS SELECT * FROM dba_objects;
ALTER TABLE TB_SQLTUNING ADD CONSTRAINT PK_sqltuning_sqlid PRIMARY KEY (OBJECT_ID);
4.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
4.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
4.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
4.5结论;
每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;(plan_hash_value相同更说明了,不同的sql产生的执行计划是相同的,需要优化)
5.在CURSOR_SHARING=EXACT模式下查看使用绑定变量的sql语句;
5.1清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
5.2执行测试sql;
VARIABLE id NUMBER;
EXEC :id := 1;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 2;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 3;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 4;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 5;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
EXEC :id := 6;
SELECT /* SQL_TUNING_BIND */ * FROM tb_sqltuning WHERE object_id = :id;
5.3查看sql信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_BIND%’ AND
sql_text NOT LIKE ‘%LIKE%’;
5.4结论;
只进行一次硬结析,之后的语句使用相同的执行计划,它们的exact_matching_signature和force_matching_signature都相同;
硬结析了三次是应为11g里面SPM(Sql Plan Management)的特性,它可以自动学习并选择最优的执行计划,最大的优点是通过避免执行计划的退化,从而保证系统性能的稳定;10g的话第一次产生执行计划就固定了,很可能不是最优的执行计划;
1.第一次执行SQL的时候跟原来一样,什么也不做,只是把执行计划保存在Library Cache中;
2.第二次执行SQL的时候会产生Plan History,而且Plan Baseline为空,把这次的执行计划放到Plan Baseline中,并且固定执行计划;
3.第三次执行SQL的时候,会把产生的执行计划放入到Plan History中,但是不会进入Plan Baseline;
6.修改参数,在CURSOR_SHARING=SIMILAR模式下查看使用字面值的sql语句;
6.1修改参数;
ALTER SYSTEM SET cursor_sharing = SIMILAR
6.2清除shared pool内容;
ALTER SYSTEM FLUSH SHARED_POOL;
6.3执行测试sql;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 1;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 2;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 3;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 4;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 5;
SELECT /* SQL_TUNING_LITERAL */ * FROM tb_sqltuning WHERE object_id = 6;
6.4查看sql的信息;
SELECT sql_text,
sql_id,
address,
hash_value,
plan_hash_value,
is_shareable,
executions,
parse_calls,
child_address,
child_number,
force_matching_signature,
exact_matching_signature
FROM v$sql
WHERE sql_text LIKE ‘%SQL_TUNING_LITERAL%’ AND
sql_text NOT LIKE ‘%LIKE%’;
6.5结论;
跟在EXACT模式下使用绑定变量的结果一致:每一条sql都会进行硬结析并产生不同的执行计划,它们的exact_matching_signature不同,但是force_matching_signature相同;
7.How to Find Literal SQL in Shared Pool;
DECLARE
b_myadr   VARCHAR2(20);
b_myadr1  VARCHAR2(20);
qstring   VARCHAR2(100);
b_anybind NUMBER;

CURSOR my_statement IS
SELECT address FROM v$sql GROUP BY address;
CURSOR getsqlcode IS
SELECT substr(sql_text, 1, 60) FROM v$sql WHERE address = b_myadr;
CURSOR kglcur IS
SELECT kglhdadr
FROM x$kglcursor
WHERE kglhdpar = b_myadr AND
kglhdpar != kglhdadr AND
kglobt09 = 0;
CURSOR isthisliteral IS
SELECT kkscbndt FROM x$kksbv WHERE kglhdadr = b_myadr1;
BEGIN
dbms_output.enable(10000000);
OPEN my_statement;
LOOP
FETCH my_statement
INTO b_myadr;
OPEN kglcur;
FETCH kglcur
INTO b_myadr1;
IF kglcur%FOUND THEN
OPEN isthisliteral;
FETCH isthisliteral
INTO b_anybind;
IF isthisliteral%NOTFOUND THEN
OPEN getsqlcode;
FETCH getsqlcode
INTO qstring;
dbms_output.put_line(‘Literal:’ || qstring || ‘ address: ‘ || b_myadr);
CLOSE getsqlcode;
END IF;
CLOSE isthisliteral;
END IF;
CLOSE kglcur;
EXIT WHEN my_statement%NOTFOUND;
END LOOP;
CLOSE my_statement;
END;

标签:
本文的评论功能被关闭了.