關於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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於Mybatis中SQL語句的整理MyBatisSQL
- [20211221]分析sql語句遇到的問題.txtSQL
- 4.3.2 關於使用SQL語句建立CDBSQL
- python關於pymysql 執行sql語句in的用法PythonMySql
- 50個SQL語句(MySQL版) 問題十四MySql
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- MySQL關於根據日期查詢資料的sql語句MySql
- 關於Room資料庫，拼寫模糊查詢語句遇到的問題OOM資料庫
- 12C多租戶關於CDB、PDB的常用SQL語句SQL
- MyBatis標籤實現的動態SQL語句MyBatisSQL
- 一些sql語句的關鍵詞SQL
- 關於 a 標籤跳轉問題
- 關於a標籤的字型顏色問題
- [20211229]sql語句包含中文儲存clob的編碼問題.txtSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- [20210407]分析sql語句的共享記憶體段3.txtSQL記憶體
- SQL語句IN的用法SQL
- (xml中sql語句為紅)解決No data sources are configured to run this SQL and provide advanced的問題XMLSQLIDE
- [20191220]關於共享記憶體段相關問題.txt記憶體
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- 關於二維陣列指標的問題陣列指標
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- SQL SELECT 語句SQL
- sql常用語句SQL
- 20180417PLSQL中sql語句格式化與註解問題SQL
- **【求助】關於抽樣和標準化的問題**
- # 關於select關鍵字語句定義順序# 關於select關鍵字語句執行順序
- [20211220]關於標量子查詢問題.txt
- 關於IDEA使用xml實現動態sql的問題IdeaXMLSQL
- SQL Server技術問題之遊標優缺點SQLServer
- 關於QT的標頭檔案相互包含的問題QT
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- sql語句如何執行的SQL
- MySQL中常用的SQL語句MySql
- sqlserver dba常用的sql語句SQLServer