万能数据库查询分析器使用技...-SQL 自定义Split函数-视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异

当前位置: 数据库>其它

本页文章导读:

▪万能数据库查询分析器使用技巧之（九）万能数据库查询分析器使用技巧之（九）马根峰 (广东联合电子服务股份有限公司,广州 510300) 作者博客： CSDN博客：ht.........

▪SQL 自定义Split函数在给文章加自定义标签时，需要在存储过程中对输入的字符串按照“,”字符分割成一个字符数组。但是Sql中没有实现字符串分组的Split方法。因此就需要编写一个自定义的Split函数。我首先是.........

▪视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异视图v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是几个经常容易混淆的视图，主要是提供library cache中当前缓存的sql语句的信息。这几个视图都可以提供当前有关sql语句的具.........

[1]万能数据库查询分析器使用技巧之（九）

来源: 互联网发布时间: 2013-11-07

万能数据库查询分析器使用技巧之（九）
马根峰
(广东联合电子服务股份有限公司,广州 510300)

作者博客：

CSDN博客：http://blog.csdn.net/magenfeng

新浪博客： http://blog.sina.com.cn/magenfeng

QQ空间： http://user.qzone.qq.com/630414817

1 “万能数据库查询分析器”简介

中国本土程序员马根峰推出的个人作品----万能数据库查询分析器，中文版本《DB查询分析器》、英文版本《DB Query Analyzer》。

万能数据库查询分析器集哈希技术、链表等多种数据结构于一体，使用先进系统开发技术，经历4年的研究、开发、测试周期后在2006年面世。之后7年来一直在进行不断地完善、升级，到目前为止，最新版本为3.02。万能数据库查询分析器具有长达7万多行代码的工作量，使得其具有强大的功能、友好的操作界面、良好的操作性、跨越各种数据库平台乃至于EXCEL。

在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。本期只点评了5个工具，分别是“Adobe Acrobat 8 中文版”、“迅雷搜索 1.7新版上线”、“Google桌面搜索 5.0中文发布”、“BEA发布 WebLogic SIP Server 3.0”和特别推荐“万能数据库查询分析器”发布。前面4个都是国内外大型软件公司的产品，只有“万能数据库查询分析器”是个人创作的软件。截止到目前，在国内最著名的软件下载网站“中关村在线”中下载量达9万多次，位居整个数据库类排行谤中前20位。

截止到2011年11月9日，在Google搜索关键字“DB查询分析器”、“DB Query Analyzer”，搜索结果分别在80万、150万左右；在Baidu搜索关键字“DB查询分析器”、“DB Query Analyzer

[2]SQL 自定义Split函数

来源: 互联网发布时间: 2013-11-07

在给文章加自定义标签时，需要在存储过程中对输入的字符串按照“,”字符分割成一个字符数组。但是Sql中没有实现字符串分组的Split方法。因此就需要编写一个自定义的Split函数。我首先是使用表值函数的方法实现的字符串分组，但是在使用中感觉不是很方便。后来又在网上找到了一种使用两个标量函数，其中一个函数首先返回分割后字符数组的长度，另一个函数依次返回每个分割出的字符串。然后使用循环依次获取分割的字符。

表值函数实现Split方法

1 Create FUNCTION [dbo].[SplitToTable]
2 (
3 @SplitString nvarchar(max),
4 @Separator nvarchar(10)=' '
5 )
6 RETURNS @SplitStringsTable TABLE
7 (
8 [id] int identity(1,1),
9 [value] nvarchar(max)
10 )
11 AS
12 BEGIN
13 DECLARE @CurrentIndex int;
14 DECLARE @NextIndex int;
15 DECLARE @ReturnText nvarchar(max);
16 SELECT @CurrentIndex=1;
17 WHILE(@CurrentIndex<=len(@SplitString))
18 BEGIN
19 SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
20 IF(@NextIndex=0 OR @NextIndex IS NULL)
21 SELECT @NextIndex=len(@SplitString)+1;
22 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
23 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
24 SELECT @CurrentIndex=@NextIndex+1;
25 END
26 RETURN;
27 END
select * FROm dbo.SplitToTable('111,b2222,323232,32d,e,323232f,g3222', ',')

结果为

id value
----------- ---------------------------------------
1 111
2 b2222
3 323232
4 32d
5 e
6 323232f
7 g3222

(7 行受影响)

使用循环的方法

首先GetSplitLength函数返回分割后的字符数组的长度。

1 Create function [dbo].[GetSplitLength]
2 (
3 @String nvarchar(max), --要分割的字符串
4 @Split nvarchar(10) --分隔符号
5 )
6 returns int
7 as
8 begin
9 declare @location int
10 declare @start int
11 declare @length int
12
13 set @String=ltrim(rtrim(@String))
14 set @location=charindex(@split,@String)
15 set @length=1
16 while @location<>0
17 begin
18 set @start=@location+1
19 set @location=charindex(@split,@String,@start)
20 set @length=@length+1
21 end
22 return @length
23 end
select dbo.GetSplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

结果为7。

GetSplitOfIndex函数是按顺序分别获取分割后的字符串。

1 ALTER function [dbo].[GetSplitOfIndex]
2 (
3 @String nvarchar(max), --要分割的字符串
4 @split nvarchar(10), --分隔符号
5 @index int --取第几个元素
6 )
7 returns nvarchar(1024)
8 as
9 begin
10 declare @location int
11 declare @start int
12 declare @next int
13 declare @seed int
14
15 set @String=ltrim(rtrim(@String))
16 set @start=1
17 set @next=1
18 set @seed=len(@split)
19
20 set @location=charindex(@split,@String)
21 while @location<>0 and @index>@next
22 begin
23 set @start=@location+@seed
24 set @location=charindex(@split,@String,@start)
25 set @next=@next+1
26 end
27 if @location =0 select @location =len(@String)+1
29
30 return substring(@String,@start,@location-@start)
31 end
select dbo.GetSplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

结果323232。

1 DECLARE @Tags nvarchar(max);
2 SELECT @Tags='111,b2222,323232,32d,e,323232f,g3222';
3 DECLARE @Tag nvarchar(1000)
4 DECLARE @next int;
5 set @next=1
6
7 DECLARE @Length int;
8 SELECT @Length=dbo.GetSplitLength(@Tags,',')
9
10 while @next<=@Length
11 begin
12 SET @Tag = left(dbo.GetSplitOfIndex(@Tags,',',@next), 16);
13 print @Tag
14 SET @Next=@Next+1;
15 END
结果为：

111
b2222
323232
32d
e
323232f
g3222

作者：Mr_Data_Mining 发表于2013-3-11 10:39:53 原文链接

阅读：71 评论：0 查看评论

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

来源: 互联网发布时间: 2013-11-07

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