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

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.

4) it is not recommended to set it to SIMILAR after the Oracle12c version. The manual has discarded this parameter.

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:

11G: create a base table and enter data.

SYS@orcl > create table test (id number,name varchar2 (10))

SYS@orcl > insert into test values (1)

SYS@orcl > insert into test values (2)

SYS@orcl > insert into test values (3meme cc')

SYS@orcl > commit

Create three lab tables:

SYS@orcl > create table test_exact as select from test

SYS@orcl > create table test_similar as select from test

SYS@orcl > create table test_force as select * from test

Set tracking trace

SYS@orcl > oradebug setmypid

SYS@orcl > oradebug tracefile_name

Test exact:

SYS@orcl > alter session set cursor_sharing=exact

SYS@orcl > alter session set sql_trace=true

SYS@orcl > select * from test_exact where id=1

ID NAME

1 aa

SYS@orcl > select * from test_exact where id=2

ID NAME 2 bb

SYS@orcl > select * from test_exact where id=3

ID NAME 3 cc

SYS@orcl > alter session set sql_trace=false

Observe the trace file:

SYS@orcl > select sql_text from v$sql where sql_text like 'select * from test_ex%'

SQL_TEXT

Select from test_exact where id=2

Select from test_exact where id=3

Select * from test_exact where id=1

[oracle@orcl ~] $tkprof / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2849.trc out.txt aggregate=no

SQL ID: 22cdhbrvt2nmw

Plan Hash: 3210958934

Select *

From

Test_exact where id=1

Call count cpu elapsed disk query current rows

Parse 1 0.00 0.01 2 2 00

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 4 0 1

Total 4 0.00 0.01 2 6 01

Misses in library cache during parse: 1-hard parsing once

Optimizer mode: ALL_ROWS

Parsing user id: SYS

SQL ID: f9kq2n9utcww7

Plan Hash: 3210958934

Select *

From

Test_exact where id=2

Call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 0 1 00

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 4 0 1

Total 4 0.00 0.00 0 5 0 1

Misses in library cache during parse: 1-hard parsing once

Optimizer mode: ALL_ROWS

Parsing user id: SYS

SQL ID: 22cdhbrvt2nmw

Plan Hash: 3210958934

Select *

From

Test_exact where id=1

Call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 4 0 1

Total 4 0.00 0.00 0 4 0 1

Misses in library cache during parse: 0-soft parsing

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Summary: when cursor_sharing=exact, it can be reused only if the SQL statement is exactly the same.

Test SIMILAR:

SYS@orcl > oradebug setmypid

SYS@orcl > oradebug tracefile_name

SYS@orcl > alter session set cursor_sharing=similar

SYS@orcl > alter session set sql_trace=true

SYS@orcl > select * from test_similar where id=1

ID NAME

1 aa

SYS@orcl > select * from test_similar where id=2

ID NAME

2 bbSYS@orcl > select from test_similar where id=10

No rows selected

SYS@orcl > select sql_text from v$sql where sql_text like 'select from test_similar where%'

SQL_TEXT

Select from test_similar where id=: "SYS_B_0"

Select from test_similar where id=: "SYS_B_0"

Select * from test_similar where id=: "SYS_B_0"

SYS@orcl > alter session set sql_trace=false

Analyze the trace file:

SQL ID: 6wvc0ymwz50uq

Plan Hash: 3269012161

Select *

From

Test_similar where id=: "SYS_B_0"

Call count cpu elapsed disk query current rows

Parse 3 0.00 0.00 1 1 00

Execute 3 0.00 0.00 1 3 00

Fetch 5 0.00 0.00 0 11 0 2

Total 11 0.00 0.00 2 15 0 2

Misses in library cache during parse: 3-perform three hard parsing

Optimizer mode: ALL_ROWS

Parsing user id: SYS

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.

Further testing:

SYS@orcl > alter system flush shared_pool

SYS@orcl > alter system flush buffer_cache

SYS@orcl > insert into test_similar values (1)

SYS@orcl > commit

SYS@orcl > create index ind_test_similar on test_similar (id)

SYS@orcl > exec dbms_stats.gather_table_stats (user,'test_similar',cascade= > true)

SYS@orcl > alter session set cursor_sharing=similar

SYS@orcl > alter session set sql_trace=true

SYS@orcl > select * from test_similar where id=1 and name='aa'

ID NAME

1 aa

SYS@orcl > select * from test_similar where id=1 and name='abc'

ID NAME

1 abcSYS@orcl > alter session set sql_trace=false

SYS@orcl > select sql_text from v$sql where sql_text like 'select * from test_similar where%'

SQL_TEXT

Select * from test_similar where id=: "SYS_B_0" and name=: "SYS_B_1

View trace:

SQL ID: 23gux1agm4fnt

Plan Hash: 3269012161

Select *

From

Test_similar where id=: "SYS_B_0" and name=: "SYS_B_1"

Call count cpu elapsed disk query current rows

Parse 2 0.00 0.00 00 00

Execute 2 0.00 0.00 00 00

Fetch 4 0.00 0.00 0 9 0 2

Total 8 0.00 0.00 0 9 0 2

Misses in library cache during parse: 1-hard parsing once

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Test FORCE:

SYS@orcl > oradebug setmypid

SYS@orcl > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4104.trc

SYS@orcl > alter session set cursor_sharing=force

SYS@orcl > alter session set sql_trace=true

SYS@orcl > select * from test_force where id=1

ID NAME

1 aa

SYS@orcl > select * from test_force where id=2

ID NAME

2 bb

SYS@orcl > select * from test_force where id=1

ID NAME

1 aaSYS@orcl > alter session set sql_trace=false

SYS@orcl > select sql_text from v$sql where sql_text like 'select * from test_force where%'

SQL_TEXT

Select * from test_force where id=: "SYS_B_0"

View trace:

SQL ID: 5my70999m011j

Plan Hash: 1419416768

Select *

From

Test_force where id=: "SYS_B_0"

Call count cpu elapsed disk query current rows

Parse 3 0.00 0.00 1 1 00

Execute 3 0.00 0.00 1 1 00

Fetch 6 0.00 0.00 0 12 0 3

Total 12 0.00 0.00 2 14 0 3

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

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

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.