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

转载

mob6047570713c8 2014-01-08 17:19:00

文章标签 sql oracle sql语句执行计划绑定变量 文章分类 代码人生

视图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