Oracle 视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异 -

tooby

浏览: 110273 次
性别:
来自: 广州

最近访客更多访客>>

Java_love_88

bigstar119

Deo

sahala79

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Oracle 视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异

博客分类：

视图 v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是几个经常容易混淆的视图，主要是提供library cache中当前缓存的sql语句的信息。这几个视图都可以提供当前有关sql语句的具体信息，但稍有差异。本文主要描述其差异并给出实例。

一、sql语句与游标
sql语句，这个没什么好说的，就是按照sql标准书写的sql语句
游标，包含shared cursor，session cursor，简单点来理解，一条sql语句对应一个或多个游标，且一条sql语句至少解析为一个游标。

      当任一sql语句被解析到shared_pool中之后，必定会产生相应的游标，有下列三种情形，
          a、存在可完全共享的父游标
          b、父游标存在，但是由于执行环境的变化，不得不生存新的子游标
          c、没有父游标存在，需要生成全新的游标
      对于情形a，由于存在可共享的父游标，也就是说v$sql中必定已经存在一个对应的sql游标，我们可以查询到，执行之后对应executions及相关列会发生变化。
    对于情形b或c，sql语句产生的游标会被添加到v$sql视图，也即是新增游标(b为新增子游标，c为新增父游标)。

注:在shared_pool由于aged out原则后的sql可能无法在该视图查询到，这个是另外一个话题。

二、视图差异
1、v$sql视图
      假定用户A与用户B都基于自身schema创建了表t
      用户A发布查询select * from t，此时共享池中产生一条与该语句的相关的sql游标，在v$sql视图体现(假定为首次执行)
      不久用户B也发出select * from t的查询，同上，v$sql中也对应有一条该语句的游标
      为了便于理解，我们将v$sql视图中的sql文本称之为游标，将v$sqlarea中的sql文本称为sql语句

2、v$sqlarea
      对于上述情形
      此时v$sqlarea则是对视图v$sql的一个聚合，也即是相当于对视图v$sql使用了distinct关键字。
      尽管v$sql中出现了两条select * from t，而v$sqlarea仅为一条
      v$sqlarea提供的是每条sql语句执行的汇总信息

3、v$sqltext
该视图包括Shared pool中SQL语句的完整文本，但一条SQL语句是被分成多个块来进行保存的。
对于比较短的sql语句，一个piece就搞定，对于比较长的sql语句则需要多个piece的结合来完整展现一条真正的sql语句。

4、v$sqltext_with_newlines
该视图用于完整保存SQL语句所有字符，保留SQL语句的回车和制表符。

三、示例说明

1、创建演示环境
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table t ( x varchar2(30) primary key, y int );

SQL> exec dbms_stats.set_table_stats('SCOTT','T', numrows => 1000000, numblks=>100000);

SQL> alter system flush shared_pool;

SQL> select sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME%'
  2  and upper(sql_text) not like '%FROM V$SQL%';

no rows selected

2、产生sql游标
SQL> declare
  2     l_x_number      number;
  3     l_x_string  varchar2(30);
  4  begin
  5     execute immediate 'alter session set optimizer_mode=all_rows';
  6     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
  7     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
  8     execute immediate 'alter session set optimizer_mode=first_rows';
  9     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
 10     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

3、分析 
SQL> col sql_text format a55
SQL> set linesize 180
SQL> col plan_table_output format a80 truncate
SQL> col sql_id new_val sql_id
SQL> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME %B1_';

SQL_ID        SQL_TEXT
------------- -------------------------------------------------------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

--从上面的查询可知，对于上面的SQL语句我们得到了相同的SQL_ID。这是因为SQL_ID是由SQL文本hash得到的一个值
--只要SQL文本相同(完全相同)，则SQL_ID一定是相同的。

--我们从v$sql视图里边查询得到了四条相同sql_id的sql语句，也即是四个不同的游标
--为什么同样的sql文本产生了四个不同的游标呢?这是因为:
-- cursor 1) 使用ALL_ROWS 优化器模式, 绑定变量为number类型
-- cursor 2) 使用ALL_ROWS 优化器模式, 绑定变量为varchar2类型
-- cursor 3) 使用FIRST_ROWS 优化器模式, 绑定变量为number类型  
-- cursor 4) 使用FIRST_ROWS 优化器模式,绑定变量为varchar2类型

--查询v$sql视图
SQL> select sql_id,loaded_versions,executions,optimizer_mode, plan_hash_value,child_number,child_address
  2  from v$sql where sql_id = '&sql_id';
old   2: from v$sql where sql_id = '&sql_id'
new   2: from v$sql where sql_id = '1qqtru155tyz8'

SQL_ID        LOADED_VERSIONS EXECUTIONS OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
------------- --------------- ---------- ---------- --------------- ------------ ----------------
1qqtru155tyz8               1          1 ALL_ROWS        1601196873            0 0000000081111008
1qqtru155tyz8               1          1 ALL_ROWS        2572036781            1 00000000841B1DD8
1qqtru155tyz8               1          1 FIRST_ROWS      1601196873            2 00000000813D1A70
1qqtru155tyz8               1          1 FIRST_ROWS      2572036781            3 000000007FFE3370

--从上面的查询结果知，optimizer_mode不同，plan_hash_value的值不同，child_address的值也不同
--尤其是child_address表明是pin到shared_pool中不同的位置

--查看v$sqlarea视图
SQL> select sql_id,sql_text,version_count vs_cnt,loaded_versions ld_vs,executions ex_cnt
  2  from v$sqlarea where sql_id = '&sql_id';
old   2: from v$sqlarea where sql_id = '&sql_id'
new   2: from v$sqlarea where sql_id = '1qqtru155tyz8'

SQL_ID        SQL_TEXT                                        VS_CNT      LD_VS     EX_CNT
------------- ------------------------------------------- ---------- ---------- ----------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1            4          4          4

--从上面的视图可知，是sql_id的一个聚合，列出了version_count以及executions的总次数等

--下面来看看每个sql对应的执行计划
--child_number为0的游标，此时为父游标
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',0))
new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',0))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1qqtru155tyz8, child number 0
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 28616 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 10000 |   292K| 28616   (6)| 00:05:44 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=:B1)  -->存在谓词转换

--下面是child_number为1的子游标
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',1));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',1))
new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',1))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1qqtru155tyz8, child number 1
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 2572036781

--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     2   (0)
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011143 |     1 |       |     1   (0)
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=:B1)   

--从上面的两个执行计划中可以看出，因为绑定变量的类型不同，导致了sql语句产生了不同的执行计划
--且第一个执行计划中使用了隐式转换

--下面是child_number为2的子游标的执行计划
--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

SQL> select * from table(dbms_xplan.display_cursor('&sql_id',2));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',2))
new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',2))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1qqtru155tyz8, child number 2
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 28616 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 10000 |   292K| 28616   (6)| 00:05:44 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=:B1)

--下面是child_number为3的子游标的执行计划
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',3));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',3))
new   1: select * from table(dbms_xplan.display_cursor('1qqtru155tyz8',3))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1qqtru155tyz8, child number 3
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 2572036781

--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     2   (0)
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011143 |     1 |       |     1   (0)
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=:B1)

--子游标2与子游标3也是使用了不同的执行计划，这个原因与父游标0，子游标1的原因相同
--子游标2与父游标0有相同的执行计划，从Plan hash value的值可知
--同样，子游标3与父游标1也有相同的执行计划，从Plan hash value的值可知

--产生不同执行计划的原因
--v$sql_shared_cursor视图记录了那些不能共享子游标的记录并给给出原因，如下查询
SQL> SELECT child_number,bind_mismatch, optimizer_mode_mismatch 
  2  from v$sql_shared_cursor
  3  where sql_id = '&SQL_ID';
old   3: where sql_id = '&SQL_ID'
new   3: where sql_id = '1qqtru155tyz8'

CHILD_NUMBER B O
------------ - -
           0 N N
           1 Y N
           2 N Y
           3 Y Y

--从上面的查询结果可知，游标1与父游标0是由于绑定变量不匹配而导致了不可共享子游标
--游标2则是由于不同的执行环境，游标3则是不同的执行环境与不匹配的绑定变量导致不可共享子游标           

--从上面的测试可以，父游标相同，子游标不同，执行计划可能相同，也可能不相同 
--下面的这个查询也说明了这个问题，得到的是不同的PLAN_HASH_VALUE  
SQL> select a.snap_id, a.sql_id, a.plan_hash_value,to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss')
  2  from dba_hist_sqlstat a, dba_hist_snapshot b 
  3  where a.snap_id = b.snap_id
  4  and sql_id ='&sql_id';
old   4: and sql_id ='&sql_id'
new   4: and sql_id ='1qqtru155tyz8'

   SNAP_ID SQL_ID        PLAN_HASH_VALUE TO_CHAR(B.BEGIN_INT
---------- ------------- --------------- -------------------
       275 1qqtru155tyz8      1601196873 2013-03-08 12:00:25
       275 1qqtru155tyz8      2572036781 2013-03-08 12:00:25

四、小结
    a、本文讨论了v$sql,v$sqlarea,v$sqltext以及v$sqltext_with_newlines几个视图的差异
    b、需要记住的是v$sql存储所有游标，v$sqlarea等同于使用了distinct关键字，仅保留sql语句。v$sqltext提供完整的sql语句
    c、硬解析通常是由于不可共享的父游标造成的，如经常变动的SQL语句，或动态SQL或未使用绑定变量等
    d、与父游标SQL文本完全一致的情形下，多个相同的SQL语句可以共享一个父游标
    e、SQL文本、执行环境完全一致的情形下，子游标能够被共享，否则如果执行环境不一致则生成新的子游标
    f、父游标相同，子游标不同，执行计划可能相同，也可能不相同