關於sql語句的遊標共享問題
我們庫裡有個語句佔用了大量的記憶體，達到4.5G，該語句的整體架構是一樣的，主要差別在於裡面有四個in 例如： Id in ( 1,2 )，而in的值是動態變化的：
SQL> select sum(SHARABLE_MEM)/power(1024,3) from v$sql where substr(sql_text, 1, 40) = 'select id as ppppid, p2m.mllch';
SUM(SHARABLE_MEM)/POWER(1024,3)
-------------------------------
4.45558127
想法： 目前因為in是不定長，我們可以用重複的資料填充將其變為定長。即原來是 例如 id in (1,2,3),我們將其定長到20位， id in (1,2,3…………..3),後面補上17個3，這樣少於20個的，都變為20個(或者50、70、80.。)。
以下是我對上面想法的測試（下面只是框架，相關的表名已去掉）：
CREATE OR REPLACE PROCEDURE pro_test2 IS
v_str_sql varchar2(1000);
v_num1 number(18);
v_num2 number(18);
v_num3 number(18);
v_num4 number(18);
v_num5 number(18);
BEGIN
--兩個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2) ) or ( CId in (:1, :2)) or (pCId in (:1, :2) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,v_num1,v_num2,v_num1,v_num2,v_num1,v_num2 ;
end loop;
--三個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3) ) or ( CId in (:1, :2,:3)) or (pCId in (:1, :2,:3) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3;
end loop;
--四個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4) ) or ( CId in (:1, :2,:3,:4)) or (pCId in (:1, :2,:3,:4) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4;
end loop;
-------------定長
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1;
end loop;
END pro_test2;
執行過程中awr的情況：
Begin Snap: 7888 03-6月 -11 14:13:42 505 1.0
End Snap: 7889 03-6月 -11 14:14:33 499 1.1
Elapsed: 0.85 (mins)
DB Time: 2.34 (mins)
SQL ordered by CPU Time
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total % Total DB Time SQL Id SQL Module SQL Text
17 17 30,000 0.00 20.26 12.01 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 7.52 4.45 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.73 3.99 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.70 3.97 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,620,000 30,000 54.00 22.54 17.25 16.88 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 6.41 6.25 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.73 5.62 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.71 5.59 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
記憶體佔用情況：
SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
05xdg6pgfyfw0 54639 15320 13616
132hj2q7kx0uj 47943 15160 12848
3zm4ksgsdy4z1 45315 14360 13040
dgkg15t97fncr 50391 14680 13232
結果：
單獨從語句來看，這兩個沒有大的差別，分別模擬了變長2-4個引數
定長6個引數，變長累加和定長的消耗資源差不多，定長的sql變成了一個。
我擔心如果引數變多，sql的版本（sql的執行計劃）可能會增多，代價相應會增大。
所以我建議還是請QA同事壓力一下。
不知各位對sql語句的共享有什麼好的建議。
SQL> select sum(SHARABLE_MEM)/power(1024,3) from v$sql where substr(sql_text, 1, 40) = 'select id as ppppid, p2m.mllch';
SUM(SHARABLE_MEM)/POWER(1024,3)
-------------------------------
4.45558127
想法： 目前因為in是不定長，我們可以用重複的資料填充將其變為定長。即原來是 例如 id in (1,2,3),我們將其定長到20位， id in (1,2,3…………..3),後面補上17個3，這樣少於20個的，都變為20個(或者50、70、80.。)。
以下是我對上面想法的測試（下面只是框架，相關的表名已去掉）：
CREATE OR REPLACE PROCEDURE pro_test2 IS
v_str_sql varchar2(1000);
v_num1 number(18);
v_num2 number(18);
v_num3 number(18);
v_num4 number(18);
v_num5 number(18);
BEGIN
--兩個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2) ) or ( CId in (:1, :2)) or (pCId in (:1, :2) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,v_num1,v_num2,v_num1,v_num2,v_num1,v_num2 ;
end loop;
--三個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3) ) or ( CId in (:1, :2,:3)) or (pCId in (:1, :2,:3) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3;
end loop;
--四個引數
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4) ) or ( CId in (:1, :2,:3,:4)) or (pCId in (:1, :2,:3,:4) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4;
end loop;
-------------定長
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1;
end loop;
END pro_test2;
執行過程中awr的情況：
Begin Snap: 7888 03-6月 -11 14:13:42 505 1.0
End Snap: 7889 03-6月 -11 14:14:33 499 1.1
Elapsed: 0.85 (mins)
DB Time: 2.34 (mins)
SQL ordered by CPU Time
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total % Total DB Time SQL Id SQL Module SQL Text
17 17 30,000 0.00 20.26 12.01 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 7.52 4.45 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.73 3.99 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.70 3.97 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,620,000 30,000 54.00 22.54 17.25 16.88 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 6.41 6.25 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.73 5.62 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.71 5.59 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
記憶體佔用情況：
SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
05xdg6pgfyfw0 54639 15320 13616
132hj2q7kx0uj 47943 15160 12848
3zm4ksgsdy4z1 45315 14360 13040
dgkg15t97fncr 50391 14680 13232
結果：
單獨從語句來看，這兩個沒有大的差別，分別模擬了變長2-4個引數
定長6個引數，變長累加和定長的消耗資源差不多，定長的sql變成了一個。
我擔心如果引數變多，sql的版本（sql的執行計劃）可能會增多，代價相應會增大。
所以我建議還是請QA同事壓力一下。
不知各位對sql語句的共享有什麼好的建議。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-697169/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 共享SQL語句SQL
- SQL語句的解析過程 遊標週期SQL
- 使用遊標迴圈進行SQL更新插入的SQL語句SQL
- 關於sql語句的優化SQL優化
- jivejdon sql語句問題SQL
- 關於Mybatis中SQL語句的整理MyBatisSQL
- 關於資料共享的問題
- 對sql語句的優化問題SQL優化
- 關於在SAP中SQL語句的效能SQL
- 請教jdon關於連線池對應的遊標問題
- 4.3.2 關於使用SQL語句建立CDBSQL
- 對sql語句的最佳化問題SQL
- SQL語句巢狀查詢問題SQL巢狀
- oracle效能問題：sql語句優化OracleSQL優化
- Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）Oracle
- 一個JTextPane寫SQL語句的問題SQL
- MySQL 遊標（PREPARE預處理語句）MySql
- 關於評審開發人員的sql語句SQL
- 50個SQL語句(MySQL版) 問題十四MySql
- SQL 語句select top 變數問題SQL變數
- EditPlus不能著色顯示SQl語句的問題SQL
- 一個使用SQL語句解決的小問題SQL
- SQL Server技術問題之遊標優缺點SQLServer
- 關於Room資料庫，拼寫模糊查詢語句遇到的問題OOM資料庫
- SQL語句為什麼不會共享（上）SQL
- SQL語句為什麼不會共享（中）SQL
- SQL語句為什麼不會共享（下）SQL
- 生產SQL語句突然變慢問題定位SQL
- SQL查詢語句臃腫問題淺析SQL
- 關於C語言的面試問題C語言面試
- [20161230]檢視父遊標中sql語句.txtSQL
- 關於 TX 鎖的兩句sqlSQL
- MySQL關於根據日期查詢資料的sql語句MySql
- SQL 遊標SQL
- 關於共享SQL——窺視解析SQL
- 關於動態語句
- 關於觸發器在行級和語句級的執行順序問題觸發器
- 關於SQL的重複記錄問題SQL