溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
根據Oracle 學習之性能優化（二）游標中的描述，我們知道如下兩條語句是不共享的。
select * from emp where empno=7698; select * from emp where empno=7566;
這樣就造成每次執行用戶的查詢都要進行硬解析，但是我們知道，其他這兩個語句的執行計劃應該是相同。那么有什么方法能避免不必要的硬解析嗎？這里我們提供2種方法。
一、綁定變量
SQL> variable empno number; SQL> exec :empno := 7839; PL/SQL procedure successfully completed. SQL> select ename from emp where empno = :empno; ENAME ------------------------------ KING SQL> exec :empno := 7782; PL/SQL procedure successfully completed. SQL> select ename from emp where empno = :empno; ENAME ------------------------------ CLARK SQL>
我們查看一下游標
SQL> COL SQL_TEXT FOR A30 SQL> COL SQL_ID FOR A20 SQL> SET LINESIZE 200 SQL> SELECT sql_id,sql_text,executions,loads,version_count FROM v$sqlarea WHERE sql_text LIKE '%:empno'; SQL_ID SQL_TEXT EXECUTIONS LOADS VERSION_COUNT -------------------- ------------------------------ ---------- ---------- ------------- f6r0kqk0hsa7s select ename from emp where em 2 1 1 pno = :empno SQL> SELECT sql_id,sql_text,loads,child_number,parse_calls FROM v$sql WHERE sql_text LIKE '%:empno'; SQL_ID SQL_TEXT LOADS CHILD_NUMBER PARSE_CALLS -------------------- ------------------------------ ---------- ------------ ----------- f6r0kqk0hsa7s select ename from emp where em 1 0 2 pno = :empno SQL>
可見，父子游標都被共享啦。
在OLTP環境中，一定要使用綁定變量以避免系統有太多的硬解析。
我們驗證一下，不使用綁定變量和使用了綁定變量后，性能到底有沒有提升。
1. 建立一張表
SQL> create table t(id int,text varchar2(100)); Table created.
2. 不使用綁定變量向表中插入10000行記錄
SQL> set timing on
SQL> declare
begin
for i in 1 .. 10000 loop
execute immediate 'insert into t values('||i||',''test bind variable'')';
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.43系統產生了非常多的游標
SQL> set pause on SQL> SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%'; SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT ------------------------------ -------------------- ---------- ---------- ------------- insert into t values(9156,'tes ah7vdgtnj80b1 1 1 1 t bind variable') insert into t values(8826,'tes 7yuz09vq9h0c4 1 1 1 t bind variable') insert into t values(9905,'tes 97c7m0gxj80cv 1 1 1 t bind variable') insert into t values(9396,'tes 9bvtw8y7080g5 1 1 1 t bind variable') SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT ------------------------------ -------------------- ---------- ---------- ------------- insert into t values(9034,'tes ck51y8bu1c0jr 1 1 1 t bind variable') insert into t values(9153,'tes 7cxb26zpcn0q9 1 1 1 t bind variable') insert into t values(9783,'tes 7236x7yva40sq 1 1 1 t bind variable') insert into t values(9491,'tes cn2n05f70810f 1 1 1
3. 使用綁定變量
SQL> set timing on SQL> declare begin for i in 1 .. 10000 loop execute immediate 'insert into t values(:x,''test bind variable'')' using i; end loop; commit; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:00.43
使用綁定變量，執行速度快了很多倍。
SQL> SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%:x%'; SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT ------------------------------ -------------------- ---------- ---------- ------------- insert into t values(:x,'test 0nhbks92x50kk 10000 1 1 bind variable')
執行計劃只有一個，被執行了10000次。
二、修改初始化參數
系統提供了一個初始化參數
SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------ ------------------- ------------------ cursor_sharing string EXACT
CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
Values:
FORCE
Allows the creation of a new cursor if sharing an existing cursor, or if the cursor
plan is not optimal.
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.
EXACT
Only allows statements with identical text to share the same cursor
1. 清空shared_pool
SQL> set pagesize 10000 SQL> set linesize 200 SQL> col SQL_TEXT for a50 SQL> col SQL_ID for 520 SQL> col SQL_ID for a20 SQL> alter system flush shared_pool; System altered. SQL> SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%'; 2 3 4 5 6 7 no rows selected SQL>
2. 將cursor_sharing改為FORCE，執行查詢
SQL> alter session set cursor_sharing=force;
Session altered.
Elapsed: 00:00:00.02
SQL> declare
begin
for i in 1 .. 10000 loop
execute immediate 'insert into t values('||i||',''test bind variable'')';
end loop;
commit;
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.153. 查看游標情況
SQL> SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%'; no rows selected SQL> / SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -------------------------------------------------- -------------------- ---------- ---------- ------------- insert into t values(9966,'test bind variable') 07xgdm0uwn5gb 1 3 1 insert into t values(10000,'test bind variable') 76rf2hx2w45m1 1 3 1 insert into t values(9969,'test bind variable') bfayz4q1j4b37 1 3 1 insert into t values(9984,'test bind variable') 48t1dy0tahgxh 1 3 1 insert into t values(9998,'test bind variable') 04bhmfjq8hhtu 1 3 1 insert into t values(9967,'test bind variable') 0njsupf834kn0 1 3 1 insert into t values(9999,'test bind variable') 6uhdudx8k4rv3 1 3 1 insert into t values(9975,'test bind variable') 0tqj0jcamsspu 1 3 1 insert into t values(9957,'test bind variable') a6as35h3wwu00 1 3 1 insert into t values(9970,'test bind variable') 58m581pqq8v4j 1 3 1 insert into t values(9982,'test bind variable') 3gh2q9f2wnxr8 1 3 1 insert into t values(9977,'test bind variable') 1t0t0jz9y0zg9 1 3 1 insert into t values(9988,'test bind variable') 111vappsrszy7 1 3 1 insert into t values(9994,'test bind variable') 8kvjy7tns10vq 1 3 1 insert into t values(9963,'test bind variable') c1w951tadx4tb 1 3 1 insert into t values(9993,'test bind variable') 10h2mbxvtt9tm 1 3 1 insert into t values(9981,'test bind variable') gv77ng7kndhty 1 3 1 insert into t values(9978,'test bind variable') 0v7773365tj70 1 3 1 insert into t values(9974,'test bind variable') astu71gzn1uw5 1 3 1 insert into t values(9960,'test bind variable') 09d0bxcsndxzm 1 3 1 insert into t values(9959,'test bind variable') 7ht6qzyy0jz5w 1 3 1 insert into t values(9965,'test bind variable') dgz4fbhzgpzh5 1 3 1 insert into t values(9989,'test bind variable') 3srf852y19zx6 1 3 1 insert into t values(9995,'test bind variable') 1r6tp423v613x 1 3 1 insert into t values(9976,'test bind variable') 9vxdayk3yq1nn 1 3 1 insert into t values(9958,'test bind variable') 9ptg2jd30k6d8 1 3 1 insert into t values(9968,'test bind variable') akt2u5gn1y9kp 1 3 1 insert into t values(×××,'test bind variable') ch5rx2b3ja9x8 1 3 1 insert into t values(9962,'test bind variable') a2p68fsk6abwz 1 3 1 insert into t values(9997,'test bind variable') f0474tah8ubzq 1 3 1 insert into t values(9972,'test bind variable') gzqpvbrsn6ggk 1 3 1 insert into t values(9983,'test bind variable') ah9r6ghzsugmp 1 3 1 insert into t values(9979,'test bind variable') 2cvqu9h5wagva 1 3 1 insert into t values(9996,'test bind variable') 3h90mc46sqmzr 1 3 1 insert into t values(9961,'test bind variable') 7t8njvfx8fn4y 1 3 1 insert into t values(9987,'test bind variable') 1qxhj0g7cuw8u 1 3 1 insert into t values(9991,'test bind variable') 5n2jahrk5z258 1 3 1 insert into t values(:"SYS_B_0",:"SYS_B_1") 950r47takm3c4 9953 1 1 insert into t values(9971,'test bind variable') fyb5pvjuqz4d0 1 3 1 insert into t values(9955,'test bind variable') 1adu3pctt76bp 1 3 1 insert into t values(9990,'test bind variable') 62pp4zqc9r767 1 3 1 insert into t values(9973,'test bind variable') adb60k3nxr9mk 1 3 1 insert into t values(9985,'test bind variable') gz4hry47rzhvt 1 3 1 insert into t values(9986,'test bind variable') b54fdtcu47v0d 1 3 1 insert into t values(9980,'test bind variable') fvwh53nh7zvhk 1 3 1 insert into t values(9956,'test bind variable') 1vcjq6rm9gx72 1 3 1 insert into t values(9964,'test bind variable') a06un7tf1rxgu 1 3 1 insert into t values(9954,'test bind variable') 0nb4synx6bxqv 1 3 1 48 rows selected. SQL>
4. 再次清空shared_pool
SQL> alter system flush shared_pool; System altered.
5. 將cursor_sharing改為SIMILAR，執行查詢
SQL> alter session set cursor_sharing=similar;
Session altered.
Elapsed: 00:00:00.03
SQL> declare
begin
for i in 1 .. 10000 loop
execute immediate 'insert into t values('||i||',''test bind variable'')';
end loop;
commit;
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.145. 查看共享游標
SQL> SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%'; 2 3 4 5 6 7 SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -------------------------------------------------- -------------------- ---------- ---------- ------------- insert into t values(9966,'test bind variable') 07xgdm0uwn5gb 1 4 1 insert into t values(10000,'test bind variable') 76rf2hx2w45m1 1 4 1 insert into t values(9969,'test bind variable') bfayz4q1j4b37 1 4 1 insert into t values(9984,'test bind variable') 48t1dy0tahgxh 1 4 1 insert into t values(9998,'test bind variable') 04bhmfjq8hhtu 1 4 1 insert into t values(9967,'test bind variable') 0njsupf834kn0 1 4 1 insert into t values(9999,'test bind variable') 6uhdudx8k4rv3 1 4 1 insert into t values(9975,'test bind variable') 0tqj0jcamsspu 1 4 1 insert into t values(9957,'test bind variable') a6as35h3wwu00 1 4 1 insert into t values(9970,'test bind variable') 58m581pqq8v4j 1 4 1 insert into t values(9982,'test bind variable') 3gh2q9f2wnxr8 1 4 1 insert into t values(9977,'test bind variable') 1t0t0jz9y0zg9 1 4 1 insert into t values(9988,'test bind variable') 111vappsrszy7 1 4 1 insert into t values(9994,'test bind variable') 8kvjy7tns10vq 1 4 1 insert into t values(9963,'test bind variable') c1w951tadx4tb 1 4 1 insert into t values(9993,'test bind variable') 10h2mbxvtt9tm 1 4 1 insert into t values(9981,'test bind variable') gv77ng7kndhty 1 4 1 insert into t values(9978,'test bind variable') 0v7773365tj70 1 4 1 insert into t values(9974,'test bind variable') astu71gzn1uw5 1 4 1 insert into t values(9960,'test bind variable') 09d0bxcsndxzm 1 4 1 insert into t values(9959,'test bind variable') 7ht6qzyy0jz5w 1 4 1 insert into t values(9965,'test bind variable') dgz4fbhzgpzh5 1 4 1 insert into t values(9989,'test bind variable') 3srf852y19zx6 1 4 1 insert into t values(9995,'test bind variable') 1r6tp423v613x 1 4 1 insert into t values(9976,'test bind variable') 9vxdayk3yq1nn 1 4 1 insert into t values(9958,'test bind variable') 9ptg2jd30k6d8 1 4 1 insert into t values(9968,'test bind variable') akt2u5gn1y9kp 1 4 1 insert into t values(×××,'test bind variable') ch5rx2b3ja9x8 1 4 1 insert into t values(9962,'test bind variable') a2p68fsk6abwz 1 4 1 insert into t values(9997,'test bind variable') f0474tah8ubzq 1 4 1 insert into t values(9972,'test bind variable') gzqpvbrsn6ggk 1 4 1 insert into t values(9983,'test bind variable') ah9r6ghzsugmp 1 4 1 insert into t values(9979,'test bind variable') 2cvqu9h5wagva 1 4 1 insert into t values(9996,'test bind variable') 3h90mc46sqmzr 1 4 1 insert into t values(9961,'test bind variable') 7t8njvfx8fn4y 1 4 1 insert into t values(9987,'test bind variable') 1qxhj0g7cuw8u 1 4 1 insert into t values(9991,'test bind variable') 5n2jahrk5z258 1 4 1 insert into t values(:"SYS_B_0",:"SYS_B_1") 950r47takm3c4 9953 1 1 insert into t values(9971,'test bind variable') fyb5pvjuqz4d0 1 4 1 insert into t values(9955,'test bind variable') 1adu3pctt76bp 1 4 1 insert into t values(9990,'test bind variable') 62pp4zqc9r767 1 4 1 insert into t values(9973,'test bind variable') adb60k3nxr9mk 1 4 1 insert into t values(9985,'test bind variable') gz4hry47rzhvt 1 4 1 insert into t values(9986,'test bind variable') b54fdtcu47v0d 1 4 1 insert into t values(9980,'test bind variable') fvwh53nh7zvhk 1 4 1 insert into t values(9956,'test bind variable') 1vcjq6rm9gx72 1 4 1 insert into t values(9964,'test bind variable') a06un7tf1rxgu 1 4 1 insert into t values(9954,'test bind variable') 0nb4synx6bxqv 1 4 1 48 rows selected.
和cursor_sharing=FORCE時，情況一樣。
這兩種方法都不推薦使用，有bug 。建議規范前臺業務查詢，盡量使用綁定變量。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
