视图 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、父游标相同,子游标不同,执行计划可能相同,也可能不相同

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏吴伟祥

MySQL 存储引擎 原

使用MEMORY存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎非常快。

791
来自专栏程序猿

SQL 注入语句特征

语句特征 1.判断有无注入点 ; and 1=1 and 1=2 2.猜表一般的表的名称无非是admin adminuser user pass passwor...

49811
来自专栏Jackson0714

聚集索引VS非聚集索引

1253
来自专栏数据和云

【动手实践】Oracle 12.2新特性:多列列表分区和外部表分区

在Oracle 12.2版本中,增加了大量的分区新特性,这其中包括: 自动的列表分区创建 在线的普通表转换分区表 支持只读分区和读写分区混合 以下介绍的三个特...

3265
来自专栏资深Tester

SQL系列之DDL/DCL语言

2536
来自专栏蓝天

MYSQL不能从远程连接的一个解决方法

ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server

912
来自专栏MySQL内核

MySQL InnoDB创建索引

InnoDB的索引基于B+树实现,每张InnoDB的表都有一个特殊的索引,叫做聚簇索引(Clustered Index),聚簇索引存储了表中的真实数据。索引项的...

3822
来自专栏拂晓风起

[C#]log4net写SQLServer数据库日志的配置方法

1544
来自专栏java一日一条

理解MySQL——架构与概念

写在前面:最早接触的MySQL是在三年前,那时候MySQL还是4.x版本,很多功能都不支持,比如,存储过程,视图,触发器,更别说分布式事务等复杂特性了。但从5....

732
来自专栏Linyb极客之路

SQL优化指南

slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

1212

扫码关注云+社区

领取腾讯云代金券