cursor_sharing test&record
测试 cursor_sharing 参数
可以看出exact similar force对sql解析的区别。
SQL> show parameter cursor_sh
NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
cursor_sharing
string
EXACT
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
SQL> select * from hr.employees where last_name = 'Baer';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
------------------------- -------------------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
204 Hermann Baer
HBAER 515.123.8888 07-6? -94 PR_REP
10000 101 70
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
SQL> select * from hr.employees where last_name = 'Gietz';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
------------------------- -------------------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
206 William Gietz
WGIETZ 515.123.8181 07-6? -94 AC_ACCOUNT
8300 205 110
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
SQL>
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> select * from hr.employees where last_name = 'Higgins';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
------------------------- -------------------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
205 Shelley Higgins
SHIGGINS 515.123.8080 07-6? -94 AC_MGR
12000 101 110
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL> select /*+ rule*/* from hr.employees where last_name||'' = 'Grant';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
------------------------- -------------------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
199 Douglas Grant
DGRANT 650.507.9844 13-1? -00 SH_CLERK
2600 124 50
178 Kimberely Grant
KGRANT 011.44.1644.429263 24-5? -99 SA_REP
7000 .15 149
SQL> select * from v$sqltext a where a.SQL_TEXT like '%select * from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
SQL> select * from v$sqltext a where a.SQL_TEXT like '%from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A35FFD0 3093344661 fntwda2w61ccp 3 0
select /*+ rule*/* from hr.employees where last_name = 'Grant'
7A356290 2010448615 gzbdmxpvxa0r7 3 0
select /*+ rule*/* from hr.employees where last_name||:"SYS_B_0"
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A356974 628478345 4sagb3skrbnc9 3 0
select /*+ rule*/* from hr.employees where last_name||'' = 'Gran
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
6 rows selected.
SQL> alter session set cursor_sharing=force;
Session altered.
SQL> select /*+ rule*/* from hr.employees where last_name||'' = 'Grant';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
------------------------- -------------------- ---------- ----------
SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- -------------- ---------- -------------
199 Douglas Grant
DGRANT 650.507.9844 13-1? -00 SH_CLERK
2600 124 50
178 Kimberely Grant
KGRANT 011.44.1644.429263 24-5? -99 SA_REP
7000 .15 149
SQL> select * from v$sqltext a where a.SQL_TEXT like '%from hr.employees where%';
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
-------- ---------- ------------- ------------ ----------
SQL_TEXT
----------------------------------------------------------------
7A361EFC 2826078190 a542tjun751zf 3 0
select * from hr.employees where last_name = 'Mavris'
7A35FFD0 3093344661 fntwda2w61ccp 3 0
select /*+ rule*/* from hr.employees where last_name = 'Grant'
7A356290 2010448615 gzbdmxpvxa0r7 3 0
select /*+ rule*/* from hr.employees where last_name||:"SYS_B_0"
7A362A74 2348231834 7vrsv9u5zfb4u 3 0
select * from hr.employees where last_name = 'Grant'
7A356974 628478345 4sagb3skrbnc9 3 0
select /*+ rule*/* from hr.employees where last_name||'' = 'Gran
7A360748 2405427127 71m954u7pztxr 3 0
select * from hr.employees where last_name = :"SYS_B_0"
6 rows selected.
SQL>
以下来自：
http://wiki.oracle.com/page/CURSOR_SHARING
CURSOR_SHARING
1 BINDING, CURSOR_SHARING parameter
1.1 What it’s for?
1.1.1 Shared Cursors
One of the first stages of parsing is to compare the text of the statement with existing statements in the shared pool to see if the statement can be shared. If the statement differs textually in any way, then Oracle does not share the statement, and too parse again the statement, this uses several resources, and is negative to performance.
Reuse of shared SQL for multiple users running the same application, avoids hard parsing. Soft parses provide a significant reduction in the use of resources such as the shared pool and library cache latches. To share cursors, do the following:
· Use bind variables rather than literals in SQL statements whenever possible. For example, the following two statements cannot use the same shared area because they do not match character for character:
SELECT employee_id FROM employees WHERE department_id = 10;
SELECT employee_id FROM employees WHERE department_id = 20;
By replacing the literals with a bind variable, only one SQL statement would result, which could be executed twice:
SELECT employee_id FROM employees WHERE department_id = :dept_id;
Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements. Typically, the majority of data required by most users can be satisfied using preset queries. Use dynamic SQL where such functionality is required.
Be sure that users of the application do not change the optimization approach and goal for their individual sessions.
· Establish the following policies for application developers:
Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks. Consider using stored procedures whenever possible. Multiple users issuing the same stored procedure use the same shared PL/SQL area automatically. Because stored procedures are stored in a parsed form, their use reduces runtime parsing.
1.1.2 SQL Sharing Criteria
Oracle automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
Oracle performs the following steps for the comparison:
1. The text of the statement issued is compared to existing statements in the shared pool.
SELECT * FROM employees WHERE id = cVariableID;
uses the same statement in the shared pool, for different values in the variable cVariableID, this statement is parsed only one time, 1 hard parse.
But if instead of using a variable you use a value
SELECT * FROM employees WHERE id = 1
SELECT * FROM employees WHERE id = 2
SELECT * FROM employees WHERE id = 3
Every one uses a distinct statement in the shared pool, parsing every one of them, 3 hard parses.
2. The text of the statement is hashed. If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
If there is a matching hash value for an existing SQL statement in the shared pool, then Oracle compares the text of the matched statement to the text of the statement hashed to see if they are identical. The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.
For example, the following statements cannot use the same shared SQL area:
SELECT * FROM employees; SELECT * FROM Employees;
SELECT * FROM employees;
Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following SQL statements do not resolve to the same SQL area:
SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;
The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE. Similar statements can share SQL areas when the CURSOR_SHARING parameter is set to SIMILAR or FORCE. The costs and benefits involved in using CURSOR_SHARING are explained later in this section.
4. The objects referenced in the issued statement are compared to the referenced objects of all existing statements in the shared pool to ensure that they are identical.
References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema.
For example, if two users each issue the following SQL statement:
SELECT * FROM employees;
and they each have their own employees table, then this statement is not considered identical, because the statement references different tables for each user.
5. Bind variables in the SQL statements must match in name, datatype, and length.
For example, the following statements cannot use the same shared SQL area, because the bind variable names differ:
SELECT * FROM employees WHERE department_id = :department_id;
SELECT * FROM employees WHERE department_id = :dept_id;
Many Oracle products (such as Oracle Forms and the precompilers) convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
6. The session's environment must be identical. Items compared include the following:
Optimization approach and goal. SQL statements must be optimized using the same optimization approach and, in the case of the cost-based approach, the same optimization goal.
Session-configurable parameters such as SORT_AREA_SIZE.
1.1.3 When to set CURSOR_SHARING to SIMILAR or FORCE
The optimal solution is to write sharable SQL (CURSOR_SHARING=EXACT), rather than rely on the CURSOR_SHARING parameter. This is because although CURSOR_SHARING does significantly reduce the amount of resources used by eliminating hard parses, it requires some extra work as a part of the soft parse to find a similar statement in the shared pool.
Consider setting CURSOR_SHARING to SIMILAR or FORCE if you can answer 'yes' to both of the following questions:
1. Are there statements in the shared pool that differ only in the values of literals?
2. Is the response time low due to a very high number of library cache misses?
Binding not is always the best, to use histograms you need to use literal parameters, for example to get the best execution plan in a table that sex field 10%man (index access) and 90% women(full table scan access)
1.1.3.1 EXACT
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
1.1.3.2 SIMILAR and FORCE
When CURSOR_SHARING is used set to SIMILAR or FORCE , Oracle first checks the shared pool to see if there is an identical statement in the shared pool. If an identical statement is not found, then Oracle searches for a similar statement in the shared pool. If the similar statement is there, then the parse checks continue to verify the executable form. of the cursor can be used. If the statement is not there, then a hard parse is necessary to generate the executable form. of the statement.
Using CURSOR_SHARING = SIMILAR (or FORCE) can significantly improve cursor sharing on some applications that have many similar statements, resulting in reduced memory usage, faster parses, and reduced latch contention.
Statements that are identical, except for the values of some literals, are called similar statements. Similar statements pass the textual check in the parse phase when the CURSOR_SHARING parameter is set to SIMILAR or FORCE. Textual similarity does not guarantee sharing. The new form. of the SQL statement still needs to go through the remaining steps of the parse phase to ensure that the execution plan of the preexisting statement is equally applicable to the new statement.
Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to share SQL.
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.
FORCE, Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.
1.2 Syntax
CURSOR_SHARING SIMILAR | EXACT | FORCE}, default EXACT
1.3 Evaluating this parameter
We stated that the correct solution is to fix the code and use EXACT, but you can test this value and see the effect in performance
In Try
OLTP FORCE
Mixed workload SIMILAR
(where you need a different plan for some of the queries)
DSS/DW EXACT
1.4 Hints
1.4.1 /*+ CURSOR_SHARING_EXACT */
Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior. to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables.
1.5 Notes
Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.
Oracle can force similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINE statement. Also, the outline must have been created with the CURSOR_SHARING parameter set to SIMILAR or FORCE, and the parameter must also set to SIMILAR or FORCE when attempting to use the outline.
Orace does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE.
Setting CURSOR_SHARING to SIMILAR or FORCE causes an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals (in a SELECT statement). However, the actual length of the data returned does not change.
Setting CURSOR_SHARING to FORCE or SIMILAR prevents any outlines generated with literals from being used if they were generated with CURSOR_SHARING set to EXACT.
To use stored outlines with CURSOR_SHARING=FORCE or SIMILAR, the outlines must be generated with CURSOR_SHARING set to FORCE or SIMILAR and with the CREATE_STORED_OUTLINES parameter.
Shared SQL may be less appropriate for data warehousing applications. Also, setting CURSOR_SHARING to FORCE or SIMILAR may affect the execution plans of the statements.
1.6 Examples and techniques
1.6.1 Similar
The best bet -- have the application use bind variables WHERE appropriate and
constants where NOT appropriate.
Nothing else will come even marginally close!!!
In the above example, the optimizer, under similar, should detect that binding
"deptno = constant" would be bad due to the skewed data and would leave the
statement "as is"
For example:
ops$tkyte@ORA920.US.ORACLE.COM> /*
DOC>
DOC>drop table emp;
DOC>create table emp as select * from scott.emp;
DOC>exec gen_data( 'EMP', 50000 )
DOC>update emp set deptno = 99;
DOC>update emp set deptno = 1 where rownum = 1;
DOC>create index dept_idx on emp(deptno);
DOC>*/
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze table emp compute statistics
2 for table
3 for all indexes
4 for all indexed columns
5 /
Table analyzed.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter system flush shared_pool;
System altered.
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA920.US.ORACLE.COM> select * from emp where deptno = 99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=49999 Bytes=2749945)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=41 Card=49999 Bytes=2749945)
ops$tkyte@ORA920.US.ORACLE.COM> select * from emp where deptno = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=55)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=55)
2 1 INDEX (RANGE SCAN) OF 'DEPT_IDX' (NON-UNIQUE) (Cost=1 Card=1)
that shows, given constants -- the queries would do different things... So, we
test:
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set cursor_sharing=similar;
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM> select * from emp where deptno = 1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
143 wFZVhMqDxB QjPZKgTZw 3520 15-SEP-03 17353.19 80462.15
1
ops$tkyte@ORA920.US.ORACLE.COM> alter session set cursor_sharing=exact;
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select sql_text from v$sql
2 where sql_text like 'select * from emp where deptno =%';
SQL_TEXT
------------------------------------------------------------
select * from emp where deptno = 1
select * from emp where deptno = 99
and we can see that it left it be. No binds. Now, we try again but take away
some information:
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter system flush shared_pool;
System altered.
ops$tkyte@ORA920.US.ORACLE.COM> analyze table emp delete statistics;
Table analyzed.
ops$tkyte@ORA920.US.ORACLE.COM> analyze table emp compute statistics
2 for table
3 for all indexes
4 /
Table analyzed.
ops$tkyte@ORA920.US.ORACLE.COM> alter session set cursor_sharing=similar;
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM> select * from emp where deptno = 1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
143 wFZVhMqDxB QjPZKgTZw 3520 15-SEP-03 17353.19 80462.15
1
ops$tkyte@ORA920.US.ORACLE.COM> alter session set cursor_sharing=exact;
Session altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select sql_text from v$sql
2 where sql_text like 'select * from emp where deptno =%';
SQL_TEXT
------------------------------------------------------------
select * from emp where deptno = :"SYS_B_0"
ops$tkyte@ORA920.US.ORACLE.COM>
and here we did bind -- because the plans would NOT change in this case. The
optimizer didn't have enough data to tell if the plans would change
But remember -- it is all just software. The right answer: you use binds when
you want to, you don't use them when you don't want to.
1.7 Views
1.7.1 V$SQL_BIND_DATA
For each distinct bind variable in each cursor owned by the session querying this view, this view describes:
· Actual bind data, if the bind variable is user defined
· The underlying literal, if the CURSOR_SHARING parameter is set to FORCE and the bind variable is system generated. (System-generated binds have a value of 256 in the SHARED_FLAG2 column.)
1.7.2 V$SQL
V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.
1.7.3 V$SQL_BIND_METADATA
For each distinct bind variable in each cursor owned by the session querying this view, this view describes:
· Bind metadata provided by the client, if the bind variable is user defined
· Metadata based on the underlying literal, if the CURSOR_SHARING parameter is set to FORCE and the bind variable is system-generated.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-627268/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-627268/