Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to understand the initialization parameters of the database cursor_sharing". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Brief introduction to Cursor_sharing:

This parameter is used to tell Oracle when cursors can be shared, that is, SQL reuse.

The Cursor_sharing parameter has three values that can be set:

1), EXACT: generally speaking, the exact value is recommended by Oracle and is also the default. It requires that the SQL statement be reused when it is exactly the same, otherwise the hard parsing operation will be re-performed.

2) SIMILAR:similar is reanalyzed only when Oracle believes that the predicate condition of a SQL statement may affect its execution plan, otherwise SQL will be reused.

3) FORCE:force is the unconditional reuse of SQL in any case.

Note: the SQL reuse mentioned above only refers to SQL statements with different predicate conditions. In fact, such SQL basically performs the same business operation.

Second, the influence on SQL when the values of Cursor_sharing parameters are different:

2.1 create a lab environment:

-first create a woo table

WOO@woo > create table woo (id int,name varchar2 (10)); Table created.Elapsed: 00lv 00lv 00.06

-generate some data-

WOO@woo > insert into woo values (1); row created.Elapsed: 00:00:00.00WOO@woo > insert into woo values (2); 1 row created.Elapsed: 00:00:00.00WOO@woo > insert into woo values (3); 1 row created.Elapsed: 00:00:00.00WOO@woo > insert into woo values (4); 1 row created.Elapsed: 00:00:00.00WOO@woo > commit;Commit complete.Elapsed: 00:00:00.00WOO@woo > select * from woo ID NAME--1 aa 2 bb 3 cc 4 ddElapsed: 00 bb 00.01

-create three tables that will be used in the following experiment-

WOO@woo > create table woo_exact as select * from woo;Table created.Elapsed: 00:00:00.01WOO@woo > create table woo_similar as select * from woo;Table created.Elapsed: 00:00:00.01WOO@woo > create table woo_force as select * from woo;Table created.Elapsed: 00.00

-View the path of the current session trace file

WOO@woo > SELECT d.Value | |'/'| Lower (Rtrim (i.Instance, Chr (0) | |'_ ora_' | | 2 p.Spid | | '.trc' AS "trace_file_name" 3 FROM (SELECT p.Spid 4 FROM V$mystat m, V$session s, V$process p 5 WHERE m.Statistic# = 1 6 AND s.Sid = m.Sid 7 AND p.Addr = s.Paddr) p 8 (SELECT t.Instance 9 FROM V$thread t, V$parameter v 10 WHERE v.Name = 'thread' 11 AND (v.Value = 0 OR t.Thread# = To_Number (v.Value) I, 12 (SELECT VALUE 13 FROM V$parameter 14 WHERE NAME =' user_dump_dest') d Trace_file_name---/DBSoft/diag/rdbms/woo/woo/trace/woo_ora_37746.trcElapsed: 00:00:00.01

2.2 cursor_sharing=exact:

WOO@woo > alter session set cursor_sharing=exact;Session altered.Elapsed: 00:00:00.00WOO@woo > alter session set sql_trace=true;Session altered.Elapsed: 00:00:00.00WOO@woo > select * from woo_exact where id=1; ID NAME--1 aaElapsed: 00:00:00.00WOO@woo > select * from woo_exact where id=2 ID NAME--2 bbElapsed: 00:00:00.01WOO@woo > select * from woo_exact where id=3; ID NAME--3 ccElapsed: 00:00:00.00WOO@woo > select * from woo_exact where id=1 ID NAME--1 aaElapsed: 00VRO 0000VOUR 00.00

-you can see from the query below that hard parsing was performed twice-

WOO@woo > select sql_text from v$sql where sql_text like 'select * from woo_exact where%' SQL_TEXT-select * from woo_exact where id=1select * from woo_exact where id=3select * from woo_exact where id=2Elapsed: 00:00:00.05NAME VALUE-----ADG parselock X get attempts 0ADG parselock X get successes 0parse time cpu 326parse time elapsed 307parse count (total) 56211parse count (hard) 1681parse count (failures) 10parse count (describe) 08 rows selected.

For cursor_sharing=similar:

WOO@woo > alter session set cursor_sharing=similar;Session altered.Elapsed: 00:00:00.00WOO@woo > alter system flush shared_pool;System altered.Elapsed: 00:00:00.13WOO@woo > select * from woo_similar where id=1; ID NAME--1 aaElapsed: 00:00:00.01WOO@woo > select * from woo_similar where id=4 ID NAME--4 ddElapsed: 00:00:00.00WOO@woo > select * from woo_similar where id=8;no rows selectedElapsed: 0015 0015 00.00

-you can see here that two SQL queries were executed, with only one hard parse

WOO@woo > select sql_text from v$sql where sql_text like 'select * from woo_similar where%' SQL_TEXT-select * from woo_similar where id=: "SYS_B _ 0 "Elapsed: 00:00:00.02WOO@woo > select name Value from v$sysstat where name like'% parse%' NAME VALUE-----ADG parselock X get attempts 0ADG parselock X get successes 0parse time cpu 374parse time elapsed 352parse count (total) 57024parse count (hard) 2006parse count (failures) 10parse count (describe) 08 rows selected.Elapsed: 00:00:00.00WOO@woo >

In the case of SIMILAR, if CBO finds that the predicate of the bound variable has other execution plans to choose from, if the value of the predicate condition changes, it will generate a new subcursor instead of reusing the previous SQL;. If the predicate has no other execution plan to choose from, ignore the value of the predicate and reuse the previous SQL.

The above example is not enough to illustrate the situation, and then the following simulation:

The situation of cursor_sharing=force

WOO@woo > alter session set cursor_sharing=force;Session altered.Elapsed: 00:00:00.00WOO@woo > alter system flush shared_pool;System altered.Elapsed: 0000vig 00.07 WOO@woo > alter session set sql_trace=true;Session altered.Elapsed: 00:00:00.02WOO@woo > select * from woo_force where id=1; ID NAME--1 aaElapsed: 00:00:00.00WOO@woo > select * from woo_force where id=4 ID NAME--4 ddElapsed: 00:00:00.00WOO@woo > select * from woo_force where id=1; ID NAME--1 aaElapsed: 00.00

-as you can see from the query below, only one hard parse was performed, and the binding variable was used-

WOO@woo > select sql_text from v$sql where sql_text like 'select * from woo_force where%' SQL_TEXT-select * from woo_force where id=: "SYS_B_0" Elapsed: 00:00:00.02WOO@woo > select name Value from v$sysstat where name like'% parse%' NAME VALUE-----ADG parselock X get attempts 0ADG parselock X get successes 0parse time cpu 379parse time elapsed 355parse count (total) 57385parse count (hard) 2145parse count (failures) 10parse count (describe) 08 rows selected.Elapsed

Summary: force is the unconditional reuse of SQL in any case.

"how to understand the initialization parameters of the database cursor_sharing" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.