Shulou(Shulou.com)06/01 Report--

According to the performance optimization of Oracle learning (2) cursor, we know that the following two statements are not shared.

Select * from emp where empno=7698;select * from emp where empno=7566

This results in hard parsing every time the user's query is executed, but we know that the execution plan of the other two statements should be the same. So is there any way to avoid unnecessary hard parsing? Here we offer two methods.

First, bind variables

SQL > variable empno number;SQL > exec: empno: = 7839 procedure successfully completed.SQL procedure successfully completed.SQL > select ename from emp where empno =: empno;ENAME--KINGSQL > exec: empno: = 7782

Let's take a look at the cursor

SQL > COL SQL_TEXT FOR A30SQL > COL SQL_ID FOR A20SQL > SET LINESIZE 200SQL > 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 =: empnoSQL > 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 =: empnoSQL >

It can be seen that both father and son cursors are shared.

In an OLTP environment, it is important to use binding variables to avoid too much hard parsing on the system.

Let's verify whether the performance has improved after not using bound variables and using bound variables.

1. Create a table

SQL > create table t (id int,text varchar2); Table created.

two。 Insert 10000 rows of records into the table without using binding variables

SQL > set timing onSQL > declarebegin for i in 1. Loop execute immediate 'insert into t values (' | I | |', 'test bind variable'')'; end loop; commit;end;/ PL/SQL procedure successfully completed.Elapsed: 10000 test bind variable'' 06.43

The system produces a lot of cursors.

SQL > set pause onSQL > 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 11 1t bind variable') insert into t values (8826 heroines 7yuz09vq9h0c4 11 1t bind variable') insert into t values (9905 recordings 97c7m0gxj80cv 11 1t bind variable') insert into t values (9396 'tes 9bvtw8y7080g5 1 11t bind variable') SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -insert into t values (9034) 'tes ck51y8bu1c0jr 11 1t bind variable') insert into t values (9153 bind variable' 7cxb26zpcn0q9 11 1t bind variable') insert into t values (9783 camera 7236x7yva40sq 11 1t bind variable') insert into t values (9491 'tes cn2n05f70810f 1 1 1

3. Use bind variables

SQL > set timing onSQL > declarebegin for i in 1. 10000 loop execute immediate 'insert into t values (: X-recording test bind variable'')' using i; end loop; commit;end;/ PL/SQL procedure successfully completed.Elapsed: 000.43

With binding variables, execution is many times faster.

SQL > SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t% EXECUTIONS x%' SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT---insert into t values (: X 'test 0nhbks92x50kk 10000 1 1bind variable')

There was only one implementation plan, which was carried out 10000 times.

Second, modify initialization parameters

The system provides an initialization parameter

SQL > show parameter cursor_sharingNAME 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. Clear shared_pool

SQL > set pagesize 10000SQL > set linesize 200SQL > col SQL_TEXT for a50SQL > col SQL_ID for 520SQL > col SQL_ID for a20SQL > 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% ratio; 2 3 4 5 6 7 no rows selectedSQL >

two。 Change cursor_sharing to FORCE and execute the query

SQL > alter session set cursor_sharing=force;Session altered.Elapsed: 00:00:00.02SQL > declarebegin 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 test bind variable'' 01.15

3. Check the cursor condition

SQL > SELECT sql_text, sql_id, EXECUTIONS, LOADS, VERSION_COUNT FROM v$sqlarea WHERE sql_text LIKE 'insert into t%' No rows selectedSQL > / SQL_TEXT SQL_ID EXECUTIONS LOADS VERSION_COUNT -insert into t values (9966) 'test bind variable') 07xgdm0uwn5gb 1 3 1insert into t values (10000 76rf2hx2w45m1 test bind variable') 76rf2hx2w45m1 1 3 1insert into t values (9969 Japanese test bind variable') bfayz4q1j4b37 1 3 1insert into t values (9984 'test bind variable') 48t1dy0tahgxh 1 3 1insert into t values (9998 bind variable') 04bhmfjq8hhtu 13 1insert into t values (9967 1insert into t values) 0njsupf834kn0 1 3 1insert into t values (9999 bind variable') 6uhdudx8k4rv3 1 3 1insert into t values (9975 'test bind variable') 0tqj0jcamsspu 1 3 1insert into t values (9957 Japanese test bind variable') a6as35h3wwu00 1 3 1insert into t values (9970 Japanese test bind variable') 58m581pqq8v4j 1 3 1insert into t values (9982 Japanese bind variable') 3gh2q9f2wnxr8 1 3 1insert into t values (9977 'test bind variable') 1t0t0jz9y0zg9 1 3 1insert into t values (998 111vappsrszy7 bind variable') 111vappsrszy7 1 3 1insert into t values (9994 camera test bind variable') 8kvjy7tns10vq 1 3 1insert into t values (9963 camera test bind variable') c1w951tadx4tb 1 3 1insert into t values (9993) 'test bind variable') 10h2mbxvtt9tm 1 3 1insert into t values (9981 gv77ng7kndhty test bind variable') gv77ng7kndhty 1 3 1insert into t values (9978 camera test bind variable') 0v7773365tj70 1 3 1insert into t values (9974 camera test bind variable') astu71gzn1uw5 1 3 1insert into t values (9960 'test bind variable') 09d0bxcsndxzm 1 3 1insert into t values (9959 bind variable') 7ht6qzyy0jz5w 1 3 1insert into t values (9965 bind variable') dgz4fbhzgpzh5 1 3 1insert into t values (9989 bind variable') 3srf852y19zx6 1 3 1insert into t values (9995 'test bind variable') 1r6tp423v613x 13 1insert into t values (9976 bind variable' 9vxdayk3yq1nn 13 1insert into t values) 9ptg2jd30k6d8 13 1insert into t values (9958 bind variable') akt2u5gn1y9kp 13 1insert into t values (× × 'test bind variable') ch5rx2b3ja9x8 1 3 1insert into t values (9962 a2p68fsk6abwz test bind variable') a2p68fsk6abwz 1 3 1insert into t values (9997 camera test bind variable') f0474tah8ubzq 1 3 1insert into t values (9972 'test bind variable') gzqpvbrsn6ggk 1 3 1insert into t values (9983 ah9r6ghzsugmp test bind variable') ah9r6ghzsugmp 1 3 1insert into t values (9979 camera test bind variable') 2cvqu9h5wagva 1 3 1insert into t values (9996 camera test bind variable') 3h90mc46sqmzr 1 3 1insert into t values (9961) 'test bind variable') 7t8njvfx8fn4y 1 3 1insert into t values (9987 1qxhj0g7cuw8u test bind variable') 1qxhj0g7cuw8u 1 3 1insert into t values (9991 camera test bind variable') 5n2jahrk5z258 1 3 1insert into t values (: "SYS_B_0" : "SYS_B_1") 950r47takm3c4 9953 1 1insert into t values (9971 fyb5pvjuqz4d0 test bind variable') fyb5pvjuqz4d0 1 3 1insert into t values (9955 camera test bind variable') 1adu3pctt76bp 1 3 1insert into t values (9990) 'test bind variable') 62pp4zqc9r767 1 3 1insert into t values (9973 adb60k3nxr9mk test bind variable') adb60k3nxr9mk 1 3 1insert into t values (9985 Japanese test bind variable') gz4hry47rzhvt 1 3 1insert into t values (9986 camera test bind variable') b54fdtcu47v0d 1 3 1insert into t values (9980 'test bind variable') fvwh53nh7zvhk 1 3 1insert into t values (9956 1vcjq6rm9gx72 bind variable') 1vcjq6rm9gx72 1 3 1insert into t values (9964 Japanese test bind variable') a06un7tf1rxgu 1 3 1insert into t values (9954 Japanese bind variable') 0nb4synx6bxqv 1 3148 rows selected.SQL >

4. Clear the shared_pool again

SQL > alter system flush shared_pool;System altered.

5. Change cursor_sharing to SIMILAR and execute the query

SQL > alter session set cursor_sharing=similar;Session altered.Elapsed: 00:00:00.03SQL > declarebegin 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 test bind variable'' 01.14

5. View shared cursors

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 1insert into t values (10000 76rf2hx2w45m1 test bind variable') 76rf2hx2w45m1 1 4 1insert into t values (9969 Japanese test bind variable') bfayz4q1j4b37 1 4 1insert into t values (9984) 'test bind variable') 48t1dy0tahgxh 1 4 1insert into t values (9998 bind variable') 04bhmfjq8hhtu 14 1insert into t values (9967 1insert into t values) 0njsupf834kn0 14 1insert into t values (9999 bind variable') 6uhdudx8k4rv3 14 1insert into t values (9975) 'test bind variable') 0tqj0jcamsspu 1 4 1insert into t values (9957 Japanese test bind variable') a6as35h3wwu00 14 1insert into t values (9970 Japanese test bind variable') 58m581pqq8v4j 1 4 1insert into t values (9982 Japanese bind variable') 3gh2q9f2wnxr8 1 4 1insert into t values (9977) 'test bind variable') 1t0t0jz9y0zg9 1 4 1insert into t values (998 bind variable') 111vappsrszy7 14 1insert into t values (9994 1insert into t values) 8kvjy7tns10vq 14 1insert into t values (9963 bind variable') c1w951tadx4tb 1 4 1insert into t values (9993) 'test bind variable') 10h2mbxvtt9tm 1 4 1insert into t values (9981 gv77ng7kndhty test bind variable') gv77ng7kndhty 1 4 1insert into t values (9978 Japanese test bind variable') 0v7773365tj70 1 4 1insert into t values (9974 Japanese test bind variable') astu71gzn1uw5 1 4 1insert into t values (9960 'test bind variable') 09d0bxcsndxzm 1 4 1insert into t values (9959 bind variable') 7ht6qzyy0jz5w 14 1insert into t values (9965 bind variable') dgz4fbhzgpzh5 1 4 1insert into t values (9989 bind variable') 3srf852y19zx6 1 4 1insert into t values (9995 'test bind variable') 1r6tp423v613x 1 4 1insert into t values (9976 bind variable') 9vxdayk3yq1nn 1 4 1insert into t values (9958 bind variable') 9ptg2jd30k6d8 4 1insert into t values (9968 bind variable') akt2u5gn1y9kp 1 4 1insert into t values (× × × 'test bind variable') ch5rx2b3ja9x8 1 4 1insert into t values (9962 a2p68fsk6abwz test bind variable') a2p68fsk6abwz 1 4 1insert into t values (9997 camera test bind variable') f0474tah8ubzq 1 4 1insert into t values (9972) 'test bind variable') gzqpvbrsn6ggk 1 4 1insert into t values (9983 ah9r6ghzsugmp test bind variable') ah9r6ghzsugmp 14 1insert into t values (9979 Japanese test bind variable') 2cvqu9h5wagva 1 4 1insert into t values (9996 Japanese test bind variable') 3h90mc46sqmzr 1 4 1insert into t values (9961) 'test bind variable') 7t8njvfx8fn4y 1 4 1insert into t values (9987 1qxhj0g7cuw8u test bind variable') 1qxhj0g7cuw8u 1 4 1insert into t values (9991 camera test bind variable') 5n2jahrk5z258 1 4 1insert into t values (: "SYS_B_0" : "SYS_B_1") 950r47takm3c4 9953 1 1insert into t values (9971 Japanese test bind variable') fyb5pvjuqz4d0 1 4 1insert into t values (9955 Japanese test bind variable') 1adu3pctt76bp 1 4 1insert into t values (9990) 'test bind variable') 62pp4zqc9r767 1 4 1insert into t values (9973 adb60k3nxr9mk test bind variable') adb60k3nxr9mk 14 1insert into t values (9985 Japanese test bind variable') gz4hry47rzhvt 1 4 1insert into t values (9986 Japanese test bind variable') b54fdtcu47v0d 1 4 1insert into t values (9980) 'test bind variable') fvwh53nh7zvhk 14 1insert into t values (9956 bind variable') 1vcjq6rm9gx72 14 1insert into t values (9964 bind variable') a06un7tf1rxgu 14 1insert into t values (9954 bind variable') 0nb4synx6bxqv 14148 rows selected.

As with cursor_sharing=FORCE, the situation is the same.

Neither of these methods is recommended, and there is bug. It is recommended to standardize front-end business queries and use binding variables as far as possible.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.