統計未用繫結變數的sql語句
在解析SQL語句的時候,如果在共享池中發現匹配的SQL語句,就可以避免掉解析的大部分開銷。在共享池中找到匹配的SQL語句所對應的解析被稱為軟解析(soft parse)。如果沒有找到匹配的SQL語句,則必須進行硬解析(hard parse)。
硬解析不僅耗費CPU時間,在有大量會話想要同時快取SQL語句到共享池時還會造成爭用。透過使用繫結變數,可以最小化解析的代價。
1.CURSOR_SHARING引數
該引數轉換SQL語句中的字面值到繫結變數。轉換值提高了遊標共享,且可能會影響SQL語句的執行計劃。最佳化器是基於繫結變數的存在生成執行計劃,而不是實際字面量值。
CURSOR_SHARING決定什麼型別的SQL語句可以共享相同的遊標。CURSOR_SHARING引數有三個值:
FORCE:只要有可能,字面量就會被替換為繫結變數。SIMILAR:只有當替換不會影響到執行計劃時,才會將字面量替換為繫結變數EXACT:這是預設值。不將字面量替換為繫結變數。注意:不推薦修改CURSOR_SHARING引數的預設值。如果實在無法修改現有應用的程式碼,可以透過設定CURSOR_SHARING引數來指示Oracle透明地將字面量替換為繫結變數。
2.識別沒有使用繫結變數的SQL語句
利用v$sql檢視的FORCE_MATCHING_SIGNATURE欄位,可以識別可能從繫結變數或CURSOR_SHARING獲益的SQL語句。如果SQL已使用繫結變數或者CURSOR_SHARING,則FORCE_MATCHING_SIGNATURE在對其進行標識時將給出同樣的簽名。換句話說,如果兩個SQL語句除了字面量的值之外都是相同的,它們將擁有相同的FORCE_MATCHING_SIGNATURE,這意味著如果為他們提供了繫結變數或者CURSOR_SHARING,它們就成了完全相同的語句。
使用FORCE_MATCHING_SIGNATURE識別沒有使用繫結變來的SQL語句。
with force_mathces as
(select l.force_matching_signature mathces,
max(l.sql_id || l.child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking
from v$sql l
where l.force_matching_signature <> 0
and l.parsing_schema_name <> 'SYS'
group by l.force_matching_signature
having count(*) > 10)
select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 5
order by fm.ranking;
3.透過執行動態SQL語句獲取繫結變數的好處
透過執行動態SQL語句,比較字面量和繫結引數對SQL解析的影響。
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_sql clob;
begin
dbms_output.put_line('*********使用字面量************');
for vrt_emp in (select * from emp) loop
v_sql := 'select e.ename,e.sal from emp e where e.empno =' ||
vrt_emp.empno;
execute immediate v_sql
into v_ename, v_sql;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
dbms_output.put_line('');
dbms_output.put_line('*********使用繫結變數************');
for vrt_emp in (select * from emp) loop
v_sql := 'select e.ename,e.sal from emp e where e.empno =:empno';
execute immediate v_sql
into v_ename, v_sql
using vrt_emp.empno;
dbms_output.put_line(v_ename || ':' || v_sql);
end loop;
end;
查詢v$sql檢視,比較執行結果:
SQL> select v.sql_text, v.sql_id, v.force_matching_signature
2 from v$sql v
3 where v.sql_text like 'select e.ename,e.sal from emp e where e.empno %';
SQL_TEXT SQL_ID FORCE_MATCHING_SIGNATURE
--------------------------------------------------------- -------------- ------------------------
select e.ename,e.sal from emp e where e.empno =7782 766syjydcn5fh 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7499 6ymy4hcb386vt 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7934 3t96y707p8by7 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7902 f9pyzxf7tnuzw 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7654 fvk1fzmrvjc4j 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7788 gsmatg9f4jd2z 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7566 4q9pzzpvvdpuu 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7369 3xhqmvm5vdqy0 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7698 bjjjw0gzaprzv 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7876 8nd8v8mrzxw4w 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7521 5tnyy066zfk1b 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7844 4kd7jb013g2zz 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7900 grx9sh4fwrcwx 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =:empno 20wmyr4cvrr6k 3.49355109645567E18
select e.ename,e.sal from emp e where e.empno =7839 6u2ajyu05gw9s 1.27397653964533E19
在v$sql檢視中,發現使用字面量的SQL語句有14條,而使用繫結變數的SQL語句只有一條。其中使用字面量的SQL語句除以了字面量值不同之外,其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假設該SQL語句使用繫結變數或者CURSOR_SHARING得到的,因此透過FORCE_MATCHING_SIGNATURE欄位識別沒有繫結變數的SQL語句。
---實踐案例
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2 desc;
create table tb_force(id integer,name varchar2(10));
insert into tb_force(id,name) values ('1','111');
insert into tb_force(id,name) values ('2','222');
insert into tb_force(id,name) values ('3','333');
commit;
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> select /*mystatement*/ * from tb_force where id=1;
ID NAME
--------------------------------------- ----------
1 111
SQL> select /*mystatement*/ * from tb_force where id=2;
ID NAME
--------------------------------------- ----------
2 222
SQL> select /*mystatement*/ * from tb_force where id=3;
ID NAME
--------------------------------------- ----------
3 333
SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%mystatement%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*mystatement*/ * from tb_force where id=2 1.30958144015941E19 6.87046715690462E18
select /*mystatement*/ * from tb_force where id=1 1.30958144015941E19 5.16419277214989E17
select /*mystatement*/ * from tb_force where id=3 1.30958144015941E19 1.36147732398326E19
--id取不同值時,FORCE_MATCHING_SIGNATURE的值相同,而EXACT_MATCHING_SIGNATURE值不同,說明在cursor_sharing=force時,執行計劃可共用;cursor_sharing=exact時,執行計劃不可共用;實際
上我們使用繫結變數後,同樣也可以達到執行計劃共用的效果。
> alter system set cursor_sharing=force;
System altered.
> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
> alter system flush shared_pool;
System altered.
SQL> select /*mystatement*/ * from tb_force where id=1;
ID NAME
--------------------------------------- ----------
1 111
SQL> select /*mystatement*/ * from tb_force where id=2;
ID NAME
--------------------------------------- ----------
2 222
SQL> select /*mystatement*/ * from tb_force where id=3;
ID NAME
--------------------------------------- ----------
3 333
SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%mystatement%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
-------------------------------------------------------------------------------- ------------------------ ------------------------
select /*mystatement*/ * from tb_force where id=:"SYS_B_0" 1.30958144015941E19 1.30958144015941E19
--當我們把cursor_sharing設定為force後,發現語句執行計劃共用了,並且自動使用繫結變數(此時v$sql檢視中只查詢到一條與之相關的語句)。
--透過上面的實驗,我們發現查詢系統中有哪些語句未使用繫結變數,可根據FORCE_MATCHING_SIGNATURE實現。
--下面我們就透過FORCE_MATCHING_SIGNATURE來查詢系統中未使用繫結變數的語句...
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2 desc;
--透過上面查詢出來的FORCE_MATCHING_SIGNATURE值,來查詢sql語句
select t.* from v$sql t where FORCE_MATCHING_SIGNATURE=16456394970215993993;
--延伸:
cursor_sharing有三個可選引數:EXACT、SIMILAR、FORCE。
--語句完全一樣,則利用share pool中的語句,反之則重新生成(預設)。
EXACT:Only allows statements with identical text to share the same cursor.
--語句相似,但系統自行判斷是否利用share pool中的語句(不推薦,bug很多)。
SIMILAR:Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or
the degree to which the plan is optimized.
--語句相似,系統強制利用share pool中的語句(不推薦,bug較多)。
FORCE:Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1444316/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- V$sql查詢未使用繫結變數的語句SQL變數
- oracle找出沒有使用繫結變數的sql語句Oracle變數SQL
- 【效能優化】查詢繫結變數的sql語句優化變數SQL
- 尋找沒有使用繫結變數的sql語句變數SQL
- 統計沒有繫結變數SQL變數SQL
- Oracle中如何查詢未使用繫結變數的SQL語句?Oracle變數SQL
- 關於高效捕獲資料庫非繫結變數的SQL語句資料庫變數SQL
- oracle對非使用繫結變數的語句去重Oracle變數
- 【實驗】sql語句在shared_pool中的查詢(程式 繫結變數)SQL變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- SQL Server動態SQL,繫結變數SQLServer變數
- 使用字面量或者繫結變數在HANA Studio裡執行SQL語句變數SQL
- sql宣告變數,及if -else語句、while語句的用法SQL變數While
- 檢視未繫結變數的sql變數SQL
- 獲取sql繫結變數的值SQL變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- 關於pl/sql中的繫結變數SQL變數
- PLSQL中使用繫結變數的語法SQL變數
- 【SQL 調優】繫結變數窺測SQL變數
- 【sql調優】繫結變數與CBOSQL變數
- 【sql調優】使用繫結變數(二)SQL變數
- 【sql調優】使用繫結變數(一)SQL變數
- 統計介面sql語句SQL
- 關於sql_profile中的繫結變數SQL變數
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- 查詢出系統中沒有使用繫結變數的SQL變數SQL
- 繫結變數變數
- 統計報表 -- sql統計語句SQL
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- SQL使用繫結變數,測試例項。SQL變數
- SQL 語句select top 變數問題SQL變數
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- 查詢沒有使用繫結變數的sql zt變數SQL
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 繫結變數在靜態sql和動態sql中變數SQL
- Oracle 繫結變數Oracle變數
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- 繫結變數的測試變數