`
baobaojinjin
  • 浏览: 142886 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

Cursor_sharing(转)

 
阅读更多

一、Cursor_sharing简介:

    这个参数是用来告诉Oracle在什么情况下可以共享游标,即SQL重用。

    Cursor_sharing参数有3个值可以设置:

     1)、EXACT:通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作

     2)、SIMILAR:similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL

     3)、FORCE:force是在任何情况下,无条件重用SQL

   备注:上面所说的SQL重用,仅仅是指谓词条件不同的SQL语句,实际上这样的SQL基本上都在执行同样的业务操作。

二、在Cursor_sharing参数值不同的时对SQL的影响:

   2.1 创建实验环境:

复制代码
 ----首先创建一张jack表----
1
SQL> create table jack (id int,name varchar2(10)); 2 3 Table created. 4 ----产生一些数据----
5
SQL> insert into jack values(1,'aa'); 6 7 1 row created. 8 9 SQL> insert into jack values(2,'bb'); 10 11 1 row created. 12 13 SQL> insert into jack values(3,'cc'); 14 15 1 row created. 16 17 SQL> insert into jack values(4,'dd'); 18 19 1 row created. 20 21 SQL> commit; 22 23 Commit complete. 24 25 SQL> select * from jack; 26 27 ID NAME 28 ---------- ---------- 29 1 aa 30 2 bb 31 3 cc 32 4 dd 33 ----创建下面实验将要用到的三张表----
34
SQL> create table jack_exact as select * from jack; 35 36 Table created. 37 38 SQL> create table jack_similar as select * from jack; 39 40 Table created. 41 42 SQL> create table jack_force as select * from jack; 43 44 Table created. 45 ----查看该session的trace文件的路径----
46
SQL> @/u01/scripts/showtrace 47 48 trace_file_name 49 -------------------------------------------------------------------------------- 50 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc
复制代码

  2.2 cursor_sharing=exact的情况:

复制代码
  ----将cursor_sharing设置为exact----
1
SQL> alter session set cursor_sharing=exact; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_exact where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_exact where id=3; 16 17 ID NAME 18 ---------- ---------- 19 3 cc 20
21
SQL> select * from jack_exact where id=1; 22 23 ID NAME 24 ---------- ---------- 25 1 aa 26 27 SQL> alter session set sql_trace=false; 28 29 Session altered. 30 ----从下面的查询可以看出执行了两次硬解析----
31
SQL> select sql_text from v$sql where sql_text like 'select * from jack_exact where%'; 32 33 SQL_TEXT 34 -------------------------------------------------------------------------------- 35 select * from jack_exact where id=1 36 select * from jack_exact where id=3 37 ----查看trace文件,通过tkprof工具
[oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc out.txt aggregate=no sys=no----
38
SQL ID: fnggytkynxz04 39 Plan Hash: 4127630146 40 select * 41 from 42 jack_exact where id=1 43 44 45 call count cpu elapsed disk query current rows 46 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 47 Parse 1 0.00 0.00 0 1 0 0 48 Execute 1 0.00 0.00 0 0 0 0 49 Fetch 2 0.00 0.00 0 4 0 1 50 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 51 total 4 0.00 0.00 0 5 0 1 52 53 Misses in library cache during parse: 1 ---id=1,执行一次硬解析 54 Optimizer mode: ALL_ROWS 55 Parsing user id: 105 56 57 Rows Row Source Operation 58 ------- --------------------------------------------------- 59 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 60 61 ******************************************************************************** 62 63 SQL ID: 1n0paamkf7sup 64 Plan Hash: 4127630146 65 select * 66 from 67 jack_exact where id=3 68 69 70 call count cpu elapsed disk query current rows 71 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 72 Parse 1 0.00 0.00 0 1 0 0 73 Execute 1 0.00 0.00 0 0 0 0 74 Fetch 2 0.00 0.00 0 4 0 1 75 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 76 total 4 0.00 0.00 0 5 0 1 77 78 Misses in library cache during parse: 1 ----id=3,执行一次硬解析 79 Optimizer mode: ALL_ROWS 80 Parsing user id: 105 81 82 Rows Row Source Operation 83 ------- --------------------------------------------------- 84 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 85 86 ******************************************************************************** 87 88 SQL ID: fnggytkynxz04 89 Plan Hash: 4127630146 90 select * 91 from 92 jack_exact where id=1 93 94 95 call count cpu elapsed disk query current rows 96 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 97 Parse 1 0.00 0.00 0 0 0 0 98 Execute 1 0.00 0.00 0 0 0 0 99 Fetch 2 0.00 0.00 0 4 0 1 100 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 101 total 4 0.00 0.00 0 4 0 1 102 103 Misses in library cache during parse: 0 ----执行一次软解析 104 Optimizer mode: ALL_ROWS 105 Parsing user id: 105 106 107 Rows Row Source Operation 108 ------- --------------------------------------------------- 109 1 TABLE ACCESS FULL JACK_EXACT (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 110 111 ********************************************************************************
  总结:当cursor_sharing=exact时,只有当SQL语句是完全一样的情况下才能被重用。
复制代码

   2.3 cursor_sharing=similar的情况:

复制代码
 ----将curor_sharing设置为similar----
1
SQL> alter session set cursor_sharing=similar; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_similar where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_similar where id=4; 16 17 ID NAME 18 ---------- ---------- 19 4 dd 20 21 SQL> select * from jack_similar where id=8; 22 23 no rows selected 24 ----下面查询中可以看到Oracle将SQL中的谓词条件用同一个名词的一个变量替代,尽管看起来是一样的,但是Oracle依然把它们作为两条SQL来处理----
25
SQL> select sql_text from v$sql where sql_text like 'select * from jack_similar where%'; 26 27 SQL_TEXT 28 -------------------------------------------------------------------------------- 29 select * from jack_similar where id=:"SYS_B_0" 30 select * from jack_similar where id=:"SYS_B_0" 31 select * from jack_similar where id=:"SYS_B_0" 32 33 SQL> alter session set sql_trace=false; 34 35 Session altered. 36 37 [oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5329.trc out.txt sys=no 38 39 TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 22 10:18:16 2013 40 41 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 42 ----从trace文件中可以清楚看到上面的结论----
43
SQL ID: 80chtmbbwpx49 44 Plan Hash: 1559066762 45 select * 46 from 47 jack_similar where id=:"SYS_B_0" 48 49 50 call count cpu elapsed disk query current rows 51 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 52 Parse 3 0.00 0.00 0 0 0 0 53 Execute 3 0.00 0.00 0 3 0 0 54 Fetch 5 0.00 0.00 0 10 0 2 55 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 56 total 11 0.01 0.01 0 13 0 2 57 58 Misses in library cache during parse: 3 ----进行三次查询,都各执行了一次硬解析。 59 Optimizer mode: ALL_ROWS 60 Parsing user id: 105 61 62 Rows Row Source Operation 63 ------- --------------------------------------------------- 64 1 TABLE ACCESS FULL JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 65 66 ********************************************************************************
复制代码

     对于SIMILAR的情况,如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时,如果谓词条件的值有变化,就将会产生一个新的子游标,而不是重用之前的SQL;如果谓词没有其他的执行计划可选择,则忽略谓词的值,重用之前的SQL。
     上面的例子还不能足以说明该情况,接着下面的模拟:

复制代码
  ----清楚一下shared_pool中的内容,否则会影响后面的输出----
1
SQL> alter system flush shared_pool; 2 3 System altered. 4 5 SQL> select * from jack_similar; 6 7 ID NAME 8 ---------- ---------- 9 1 aa 10 2 bb 11 3 cc 12 4 dd 13 14 SQL> insert into jack_similar values(1,'gg'); 15 16 1 row created. 17 18 SQL> commit; 19 20 Commit complete. 21 ----创建索引,并进行分析----
22
SQL> create index jack_similar_ind on jack_similar(id); 23 24 Index created. 25 SQL> exec dbms_stats.gather_table_stats(user,'jack_similar',cascade=>true); 26 27 PL/SQL procedure successfully completed. 28 29 SQL> select * from jack_similar; 30 31 ID NAME 32 ---------- ---------- 33 1 aa 34 2 bb 35 3 cc 36 4 dd 37 1 gg 38 39 SQL> alter session set cursor_sharing=similar; 40 41 Session altered. 42 43 SQL> alter session set sql_trace=true; 44 45 Session altered. 46 47 SQL> select * from jack_similar where id=1 and name='aa'; 48 49 ID NAME 50 ---------- ---------- 51 1 aa 52 53 SQL> select * from jack_similar where id=1 and name='gg'; 54 55 ID NAME 56 ---------- ---------- 57 1 gg 58 59 SQL> alter session set sql_trace=false; 60 61 Session altered. 62 ----在这里可以看到执行两次SQL查询,只进行了一个硬解析----
63
SQL> select sql_text from v$sql where sql_text like 'select * from jack_similar where%'; 64 65 SQL_TEXT 66 -------------------------------------------------------------------------------- 67 select * from jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 68 69 ----在trace文件也验证了这一点。----
----虽然name的值发生了改变,但是id的值没有发生变,而id上有索引的,name上没有索引,CBO认为这样的情况不会改变SQL的执行计划。
70
SQL ID: 10ku2kuy1sqaj 71 Plan Hash: 2730352089 72 select * 73 from 74 jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 75 76 77 call count cpu elapsed disk query current rows 78 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 79 Parse 1 0.00 0.00 0 0 0 0 80 Execute 1 0.00 0.00 0 0 0 0 81 Fetch 2 0.00 0.00 0 4 0 1 82 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 83 total 4 0.00 0.00 0 4 0 1 84 85 Misses in library cache during parse: 1 86 Optimizer mode: ALL_ROWS 87 Parsing user id: 105 88 89 Rows Row Source Operation 90 ------- --------------------------------------------------- 91 1 TABLE ACCESS BY INDEX ROWID JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1) 92 2 INDEX RANGE SCAN JACK_SIMILAR_IND (cr=2 pr=0 pw=0 time=17 us cost=1 size=0 card=2)(object id 75044) 93 94 ******************************************************************************** 95 96 SQL ID: 10ku2kuy1sqaj 97 Plan Hash: 2730352089 98 select * 99 from 100 jack_similar where id=:"SYS_B_0" and name=:"SYS_B_1" 101 102 103 call count cpu elapsed disk query current rows 104 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 105 Parse 1 0.00 0.00 0 0 0 0 106 Execute 1 0.00 0.00 0 0 0 0 107 Fetch 2 0.00 0.00 0 4 0 1 108 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 109 total 4 0.00 0.00 0 4 0 1 110 111 Misses in library cache during parse: 0 112 Optimizer mode: ALL_ROWS 113 Parsing user id: 105 114 115 Rows Row Source Operation 116 ------- --------------------------------------------------- 117 1 TABLE ACCESS BY INDEX ROWID JACK_SIMILAR (cr=4 pr=0 pw=0 time=0 us cost=3 size=6 card=1) 118 2 INDEX RANGE SCAN JACK_SIMILAR_IND (cr=2 pr=0 pw=0 time=3 us cost=1 size=0 card=2)(object id 75044) 119 120 ********************************************************************************
复制代码

  2.4 cursor_sharing=force的情况

复制代码
  ----设置cursor_sharing=force----
1
SQL> alter session set cursor_sharing=force; 2 3 Session altered. 4 5 SQL> alter session set sql_trace=true; 6 7 Session altered. 8 9 SQL> select * from jack_force where id=1; 10 11 ID NAME 12 ---------- ---------- 13 1 aa 14 15 SQL> select * from jack_force where id=4; 16 17 ID NAME 18 ---------- ---------- 19 4 dd 20 21 SQL> select * from jack_force where id=1; 22 23 ID NAME 24 ---------- ---------- 25 1 aa 26 27 SQL> alter session set sql_trace=false; 28 29 Session altered. 30 ----从下面的查询中可以看出只进行了一次硬解析,而且使用了绑定变量----
31
SQL> select sql_text from v$sql where sql_text like 'select * from jack_force where%'; 32 33 SQL_TEXT 34 -------------------------------------------------------------------------------- 35 select * from jack_force where id=:"SYS_B_0" 36 37 ----查看trace文件内容----
38
[oracle@yft ~]$ tkprof /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5551.trc aggregate=no sys=no 39 40 SQL ID: 38vy9d4quwdwk 41 Plan Hash: 1272021682 42 select * 43 from 44 jack_force where id=:"SYS_B_0" 45 46 47 call count cpu elapsed disk query current rows 48 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 49 Parse 1 0.01 0.01 0 1 0 0 50 Execute 1 0.00 0.00 0 1 0 0 51 Fetch 2 0.00 0.00 0 4 0 1 52 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 53 total 4 0.01 0.01 0 6 0 1 54 55 Misses in library cache during parse: 1 ----id=1的时候进行一次硬解析 56 Optimizer mode: ALL_ROWS 57 Parsing user id: 105 58 59 Rows Row Source Operation 60 ------- --------------------------------------------------- 61 1 TABLE ACCESS FULL JACK_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 62 63 ******************************************************************************** 64 65 SQL ID: 38vy9d4quwdwk 66 Plan Hash: 1272021682 67 select * 68 from 69 jack_force where id=:"SYS_B_0" 70 71 72 call count cpu elapsed disk query current rows 73 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 74 Parse 1 0.00 0.00 0 0 0 0 75 Execute 1 0.00 0.00 0 0 0 0 76 Fetch 2 0.00 0.00 0 3 0 1 77 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 78 total 4 0.00 0.00 0 3 0 1 79 80 Misses in library cache during parse: 0 ----id=4的时候进行0次硬解析,一次软解析 81 Optimizer mode: ALL_ROWS 82 Parsing user id: 105 83 84 Rows Row Source Operation 85 ------- --------------------------------------------------- 86 1 TABLE ACCESS FULL JACK_FORCE (cr=3 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 87 88 ******************************************************************************** 89 90 SQL ID: 38vy9d4quwdwk 91 Plan Hash: 1272021682 92 select * 93 from 94 jack_force where id=:"SYS_B_0" 95 96 97 call count cpu elapsed disk query current rows 98 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 99 Parse 1 0.00 0.00 0 0 0 0 100 Execute 1 0.00 0.00 0 0 0 0 101 Fetch 2 0.00 0.00 0 4 0 1 102 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 103 total 4 0.00 0.00 0 4 0 1 104 105 Misses in library cache during parse: 0 ----id=1时进行0次硬解析,一次软解析 106 Optimizer mode: ALL_ROWS 107 Parsing user id: 105 108 109 Rows Row Source Operation 110 ------- --------------------------------------------------- 111 1 TABLE ACCESS FULL JACK_FORCE (cr=4 pr=0 pw=0 time=0 us cost=3 size=20 card=1) 112 113 ********************************************************************************
总结:force是在任何情况下,无条件重用SQL。
复制代码

 三、总结:

   FORCE和SIMIALR最大的区别在于,FORCE会把所有的谓词用变量代替,并且不管变量的值如何,一律重用第一条SQL语句,而SIMILAR会根据谓词的不同,来重新选择SQL的执行计划。

   如果一个系统,它存在变量绑定的问题,并且这种问题已经影响到了系统的性能,这时候可以考虑将参数cursor_sharing的值设置为SIMILAR或FORCE来改善这种局面,不过在改成SIMILAR或FORCE都可能带来一些Bug以及很多未知的东西,所以需要慎用。

   最后需要说明一点,对于OLTP系统,如果绑定变量情况不好的话,也许可以考虑通过设置这个参数来缓解一下问题;对于是在OLAP系统上,这个参数应该设置成EXACT,并且不应该使用绑定变量,因为在OLAP系统中,SQL的解析对于SQL的执行来看,话费的代价几乎可以忽略,而正确的SQL执行计划才是OLAP数据库最需要关注的。

分享到:
评论

相关推荐

    Oracle 性能调整(真正由ORACLE甲骨文出品)

    Oracle 性能调整 (真正由ORACLE甲骨文出品) <br>如何开始调整?... <br>ERROR: ORA-00096: invalid value SIMILAR for parameter cursor_sharing, must be from among EXACT, FORCE

    oracle 参数详解

    Oracle cursor_sharing参数详解.docx

    Oracle_SQL调优课程教材

    Objectives After completing this lesson, you should be able to do the following: • Describe the basic steps...• Understand how to use the CURSOR_SHARING parameter • Use automatic PGA memory management

    解析Oracle 8i/9i的计划稳定性

    最后,它可以让你选择使用新的cursor_sharing参数而无需要担心因此而不采用优化的执行路径。 要知道如何使用存储概要才是最优的,我们首先运行一些极度没有效率的SQL的存储过程开始,要注意的是,我们不能修改源代

    让Oracle跑得更快 Oracle 10g性能分析与优化思路.part2.rar

    12.2.1 cursor_sharing 313 12.2.2 db_file_multiblock_read_count 328 12.2.3 pga_aggregate_target和sga_target 334 12.2.4 optimizer_dynamic_sampling 334 第13章 性能报告 335 13.1 awr性能报告 335 13.1.1 ...

    让Oracle跑得更快 Oracle 10g性能分析与优化思路.part1.rar

    12.2.1 cursor_sharing 313 12.2.2 db_file_multiblock_read_count 328 12.2.3 pga_aggregate_target和sga_target 334 12.2.4 optimizer_dynamic_sampling 334 第13章 性能报告 335 13.1 awr性能报告 335 13.1.1 ...

    ORACLE数据库 安装配置规范 (V2.0.1)

    6.2.2.2 CURSOR_SHARING 40 6.2.2.3 SGA_TARGET 40 6.2.3 建议不修改的初始化参数 41 6.2.3.1 UNDO_RETENTION 41 6.2.3.2 SESSIONS 41 6.2.3.3 TRANSACTIONS 42 6.2.3.4 DB_KEEP_CACHE_SIZE 42 6.2.3.5 LOCK_SGA 43 ...

    ora分析脚本

    - sharing <sql_id>: print why cursors are not shared - events [px]: events that someone is waiting for - events [read_by_other_session] events that someone is read by other session - ash <minutes...

    Oracle9i的init.ora参数中文说明

    Oracle9i初始化参数中文说明 Blank_trimming: 说明: 如果值为TRUE, 即使源长度比目标长度 (SQL92 兼容) 更长, 也允许分配数据。 值范围: TRUE | FALSE 默认值: FALSE serializable: 说明: 确定查询是否获取表级...

    未找到「NOT FOUND」-crx插件

    By putting your mouse cursor over the cursor of the person sharing their browsing, you can share in the scenery (website screen shots) and places (URL) the other person is looking at. ・ランダムなタイ...

    数位板压力测试

    Devices may have multiple cursor types that have different physical configurations, or that have differ¬ent numbers of buttons, or return auxiliary information, such as pressure information. Cursor ...

    Bochs - The cross platform IA-32 (x86) emulator

    Changes in 2.4.6 (February 22, 2011): Brief summary : - Support more host OS to run on: - Include win64 native binary in the release. - Fixed failures on big endian hosts. - BIOS: Support for up to...

    微软内部资料-SQL性能优化3

    Contents Overview 1 Lesson 1: Concepts – Locks and Lock Manager 3 Lesson 2: Concepts – Batch and Transaction 31 Lesson 3: Concepts – Locks and Applications 51 Lesson 4: Information Collection and ...

    python3.6.5参考手册 chm

    PEP 412: Key-Sharing Dictionary PEP 362: Function Signature Object PEP 421: Adding sys.implementation SimpleNamespace Using importlib as the Implementation of Import New APIs Visible Changes ...

    Turbo C++ 3.0[DISK]

    again, you will get a sharing violation. In this specific case, you can call the real mode version, MAKER, within the given makefile, and a sharing violation won't occur. 4. IMPORTANT INFORMATION -...

    Turbo C++ 3.00[DISK]

    again, you will get a sharing violation. In this specific case, you can call the real mode version, MAKER, within the given makefile, and a sharing violation won't occur. 4. IMPORTANT INFORMATION -...

    highlight-pointer:用点突出显示鼠标指针光标-对演示,屏幕共享等有用。

    高亮指针 用点突出显示鼠标指针/光标-适用于演示,屏幕共享,... 演示版 特征 非常轻巧,可以在运行X服务器的任何Linux / Unix系统上运行 无论是否显示光标(例如“缩放”)(或不显示)(例如“ Skype”),都可以...

    JavaEE 5.0 Tutorial.pdf

    Sun 官方 J2ee 5.0 教程 The Java EE 5Tutorial For Sun Java System Application Server 9.1 Contents Preface ..................................................................................................

    阿呆系列PhotoshopCS6

    work that relies on precise cursor movement (like painting, dodging, burning, path creation and editing, cloning, healing, patching, or lassoing, just to name a few), yes, I do recommend a Wacom ...

    spring-framework-reference4.1.4

    Not Using Commons Logging ................................................................... 12 Using SLF4J ..............................................................................................

Global site tag (gtag.js) - Google Analytics