ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL语句为什么不会共享(上)

SQL语句为什么不会共享(上)

原创 Linux操作系统 作者:realkid4 时间:2011-07-31 19:41:28 0 删除 编辑

 

SQL语句是一种描述性的语言。Oracle接受一个SQL之后,要进行一系列的解析parse操作,最终生成真正操作的步骤——执行计划。Oracle对一个SQL进行第一次硬解析(hard parse)之后,就将执行计划缓存在SGA的library cache中。

 

进行执行计划缓存的目的就是进行执行计划共享,这样期望在下次输入相同SQL的时候能够共享该计划,减少由于硬解析带来的Library Cache Lock和Library Cache Pin。但是实际中,真正实现SQL语句执行计划共享是一件很复杂的事情,要满足很多现实条件。本系列将从SQL语句游标共享的角度,讨论几种SQL游标不能共享的场景。

 

 

1、 环境准备

 

首先,我们准备一个简单的实验环境。

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0    Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 Production

 

 

我们选择Oracle 10gR2的环境。同时,在sys的用户schema下,存在一个数据表t。我们本系列中的大部分实验都是围绕这个数据表进行的。

 

 

2、父子游标的cursor sharing

 

为了增加本系列的可理解性。在本部分中稍稍介绍Oracle父子游标机制和共享机制。

 

ü        Oracle接受到一个全新的SQL时,要进行硬解析hard parse,形成执行计划。执行计划对应的shared cursor本质上就是SGA上的一个地址区域。保存执行计划是以父游标parent cursor和子游标child cursor两部分进行保存;

ü        父游标parent cursor对应的就是SQL语句字面相同的SQL与之对应。对每一个父游标而言,Oracle都会分配一个系统唯一的sql_id与之对应。新SQL进行shared pool寻找可共享的shared cursor的时候,首先定位到的就是父游标parent cursor;

ü        子游标child cursor是绑定在父游标上形成多对一关系的执行计划。由于优化器模式等原因,一个父游标会对应多个子游标。每个子游标中携带的才是真正的执行计划;

ü        Oracle内部视图上,v$sqlarea视图表示的是父游标信息,v$sql视图中表示的是子游标信息;

 

 

3、 SQL字面值差异

 

两个SQL如果语义相同,是否可以实现游标共享吗?首先,我们执行两个SQL语句。

 

 

SQL> select /*+ demo_1 */count(*) from t;

 

  COUNT(*)

----------

     18015

 

SQL> select /*+ demo_1 */count(*) from T;

 

  COUNT(*)

----------

18015

 

 

两个SQL语句的差异只是在大小写拼写上。我们观察父子游标情况。

 

 

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address from v$sqlarea where sql_text like 'select /*+ demo_1 */%';

 

SQL_TEXT                            SQL_ID        OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS

----------------------------------- ------------- -------------- ---------- --------------- --------

select /*+ demo_1 */count(*) from t 1vp29y1g9zjs2 ALL_ROWS       1587529474      2966233522 6967139C

 

select /*+ demo_1 */count(*) from T 2kjp0bmmn7jsg ALL_ROWS       3879978767      2966233522 69670434

 

 

 

从结果看,Oracle为两个略微差异的SQL语句生成了不同的父游标。在v$sqlarea中,有两行记录与之对应。但是,我们注意到两个父游标对应的plan_hash_value值相同,说明虽然两个游标没有实现共享,但是游标生成的执行计划内容上是相同的。

 

结论:在Oracle中,要实现游标共享的前提条件,就是输入SQL的字面值100%相同,不允许有任何包括大小写的差异。

 

4、 SQL指定对象相同(1)

 

SQL字面相同,那么SQL语句一定可能共享吗?我们说,不同用户schema下对应相同的名称。这样就可能出现相同的SQL语句通过不同的用户发出,对应不同对象的情况。下面我们进行试验。

 

 

SQL> col owner for a10;

SQL> col object_name for a20;

SQL> select owner, object_name, object_id from dba_objects where object_name='T';

 

OWNER      OBJECT_NAME           OBJECT_ID

---------- -------------------- ----------

SYS        T                         54216

SCOTT      T                         54136

 

 

sys用户和scott用户下,存在两个相同名称T的对象。但是本质上肯定是两个对象。

 

//当前用户为sys

SQL> show user;

User is "SYS"

 

SQL> select /*+ demo_2 */ count(*) from t;

  COUNT(*)

----------

     18015

 

//切换到scott用户

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> select /*+ demo_2 */ count(*) from t;

  COUNT(*)

----------

         0

 

 

两个SQL字面完全一样,此时作为相同的SQL语句,在Oracle library cache中是如何呢?

 

 

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_2 */%';

 

SQL_TEXT                            SQL_ID        OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS  EXECUTIONS VERSION_COUNT

----------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------

select /*+ demo_2 */ count(*) from  6d100h31dw99u ALL_ROWS       3269338426      2966233522 69654C70          2             2

t                                                                                                              

 

 

我们在父游标中只看到了一个父游标信息,同时executions为2表示该SQL被执行过两次,同实际相同。而version_count为2说明两次执行,虽然可以共享父游标,但是在子游标层面上,变为两个子游标,进而是两个执行计划。

 

 

SQL> select sql_id, PARSING_SCHEMA_NAME, child_number from v$sql where sql_id='6d100h31dw99u';

 

SQL_ID        PARSING_SCHEMA_NAME            CHILD_NUMBER

------------- ------------------------------ ------------

6d100h31dw99u SYS                                       0

6d100h31dw99u SCOTT                                     1

 

 

结论:两个schema下发出的SQL,如果环境相同,SQL字面值相同,在shared pool中共享父游标,对应不同的子游标。执行计划不能共享。

 

 

此时,如果我们进一步想,如果两个SQL字面值相同,而且对应相同的对象,会怎么样呢?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-703624/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753032