ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 游标脚本性能问题解决与分析 (1) - Cursor Performance Analysis

游标脚本性能问题解决与分析 (1) - Cursor Performance Analysis

原创 Linux操作系统 作者:edwardking888 时间:2011-08-25 13:37:20 0 删除 编辑

第一部分:游标类型对性能影响的实例引出

 

下面的两个游标脚本分别创建并执行了dynamicfast forward only两种类型的游标: 

不理想的游标类型:(dynamic游标)

理想的游标类型(fast forward only游标)

declare @p1 int  set @p1=NULL 

declare @p2 int  set @p2=0 

declare @p5 int  set @p5=4098

declare @p6 int  set @p6=8193 

declare @p7 int  set @p7=0 

 

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',

N'

SELECT       T1.CONFLICT_ID

FROM         dbo.S_AUDIT_ITEM T1           

LEFT OUTER JOIN dbo.S_USER T2

ON T1.USER_ID = T2.PAR_ROW_ID   

WHERE  ((T1.BC_BASE_TBL = @P1) 

AND  (T1.RECORD_ID = @P2))   

ORDER BY  T1.OPERATION_DT DESC 

OPTION (FAST 40)

',

@p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY' 

 

print 'fetch'

exec sp_cursorfetch @p2,2,4,1 

 

exec sp_cursorclose @p2

 

declare @p1 int  set @p1=NULL 

declare @p2 int  set @p2=0 

declare @p5 int  set @p5=4112

declare @p6 int  set @p6=8193 

declare @p7 int  set @p7=0 

 

exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',

N'

SELECT       T1.CONFLICT_ID

FROM         dbo.S_AUDIT_ITEM T1           

LEFT OUTER JOIN dbo.S_USER T2

ON T1.USER_ID = T2.PAR_ROW_ID   

WHERE  ((T1.BC_BASE_TBL = @P1) 

AND  (T1.RECORD_ID = @P2))   

ORDER BY  T1.OPERATION_DT DESC 

OPTION (FAST 40)

',

@p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6' 

 

select @p1, @p2, @p5, @p6, @p7

 

print '2'

exec sp_cursorfetch @p2,2,1,1 

print '3'

exec sp_cursorclose @p2

考:http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec

 

一,如何解读游标的类型

sp_cursorprepexec [@handle =] statement_handle OUTPUT,
     [@cursor =] cursor_handle OUTPUT,
     [@paramdef =] N'parameter_name data_type, [,...n]' 
     [@stmt =] N'stmt',
    [, [@scrollopt =] scroll_options OUTPUT]
    [, [@ccopt =] concurrency_options OUTPUT]
    [, [@rowcount =] rowcount OUTPUT]

 @scrollopt

Value

Description

0x0001

Keyset-driven cursor.

0x0002

Dynamic cursor.

0x0004

Forward-only cursor.

0x0008

Static cursor.

0x0010

Fast forward-only cursor.

0x1000

Parameterized query.

0x2000

Auto fetch.

0x4000

Auto close.

0x8000

Check acceptable types.

0x10000

Keyset-driven acceptable.

0x20000

Dynamic acceptable.

0x40000

Forward-only acceptable.

0x80000

Static acceptable.

0x100000

Fast forward-only acceptable.


@ccopt

Value

Description

0x0001

Read-only.

0x0002

Scroll locks.

0x0004

Optimistic. Checks timestamps and, when not available, values.

0x0008

Optimistic. Checks values (non-text, non-image).

0x2000

Open on any SQL.

0x4000

Update keyset in place.

0x10000

Read-only acceptable.

0x20000

Locks acceptable.

0x40000

Optimistic acceptable.

 

 

@p5=4098 转成16进制就是1002,对应的游标类型为Parameterized query + Dynamic cursor

@p5=4112 转成16进制就是1010,对应的游标类型为Parameterized query + Fast forward-only cursor

 

问题的现象是,左边的游标类型下,该脚本执行时间远大于右边的游标类型。

 

二,如何比较两个不同执行计划的优劣

 

在继续以下内容之前,这里要介绍一些查看和比较语句执行计划的知识。通常情况下,我们从management studio中输出图形界面的执行计划进行直观的比较,查看每个表用的访问方式,使用index还是table scan,使用了哪个index,表和表之间使用的join 方式有什么不一样。但是如果是一个复杂的语句,在不同的数据库上使用了不同的执行计划,对于同样表的访问,使用了不同的index,如何比较哪种执行计划更加优化呢?比较整个语句的执行时间是一种方法,但是这个比较的结果并不准确。语句的执行时间很容易受到其他外在因素的影响:

1.       不同机器上CPUmemorydisk的性能会影响执行时间。

2.       测试的时候有没有其他人在使用同样的数据造成阻塞

3.       其他人堆数据库的使用占用了系统资源

 

以上这些原因都有可能影响的语句的执行时间,从而影响到我们对语句性能结果的比较。因此我们不能把语句的执行时间作为衡量语句性能的标准。

 

这里介绍一种比较语句cost的方法。我们对于语句cost的衡量,主要是通过比对语句总的logical reads.

我们可以通过在management studio里的query window 执行”set statistics io on” ,在当前窗口中对所有执行的语句输出信息:

 

set statistics io on

select * from dbo.test_TicketFact

set statistics io on

 

执行语句两次,以消除physical readsread-ahead reads的影响。

输出的结果如下:

 (320 row(s) affected)

Table 'test_TicketFact'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

这里打印出来了语句中访问过的table的访问次数,总共的logical readsphysical reads等信息

 

这里我们需要关注的是logic reads的值,这个值实际上决定了对于IODISK以及内存的消耗。当语句是第一次执行,我们会看到physical reads的数字,以,而当语句第二次执行的时候,这些数据已经被读到memory里面了,因此我们会看到physical readread-ahead reads都变为0,而logical reads的值就变成了语句所有使用的data的量。

 

为什么logic reads是我们需要关注的值呢?因为logic reads决定了语句要访问数据的量。如果我们的系统瓶颈在IO上,一旦语句需要访问的数据从内存里面清除,这个语句原本所有的logic reads会全部转为physical reads.因此那些大量使用logic reads就是可能导致大量physical reads的元凶。如果我们的bottleneckCPU,这些做大量logical reads的语句同样有可能导致大量的memory 读,而读memory是需要消耗CPU资源的。因此,无论是CPUmemory还是DISK的瓶颈,那些做大量logical reads的语句都非常可能是造成问题的原因。

 

由以上内容,我们可以得出结论,语句的性能好坏,取决与这个语句做了多少logical reads.因此,如果同样的语句,使用了不同的执行计划,那么总的logical reads低的那个执行计划就是相对优化的。

 

三,分析本案例中两种游标的执行计划

 

现在我们回到需要研究的脚本,在这里,语句是一样的,不同的只是游标的类型。不同的执行时间说明很可能这个语句使用了不同的执行计划。现在问题变成了,同样语句使用了不同的执行计划,得到了不同的执行时间。我们首先从”set statistics io on” 的结果入手:

 

1.左边使用dynamic游标有大量的逻辑读,情况如下:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 9770695, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


2.而右边使用fast forward only游标只有三次逻辑读,情况为:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


从这里输出的结果的区别,说明了在table S_AUDIT_ITEMSQL Server使用了不同的访问方式

 

接下来我们分析两个脚本的执行计划:

 

1 dynamic游标对应的不理想的执行计划中,SQL Server选择了索引扫描(index scan)及索引S_AUDIT_ITEM_M4来查阅S_AUDIT_ITEM表。因此我们会在这里看到大量的IO

这个索引扫描实际上访问了整张表的数据。

  

2.而fast forward only游标对应的理想的执行计划中,SQL Server选择的是索引查找(index seek)及索引S_AUDIT_ITEM_M3来查阅S_AUDIT_ITEM表。所以我们只看到3个逻辑读。索引S_AUDIT_ITEM_M3包含4个列,第一个列是RECORD_ID。另外,在语句中,有WHERE条件T1.RECORD_ID=@P2

 

四,尝试解决问题

首先我们尝试更新统计信息:UPDATE STATISTICS ON S_AUDIT_ITEM  WITH FULLSCAN,但是这个操作在此问题案例中没有作用。

从以上的分析中,我们已经发现,如果使用index S_AUDIT_ITEM_M3访问S_AUDIT_ITEM表,得到的执行计划非常好,我们可以直接用index hint来解决这个问题:

 

declare @p1 int  set @p1=NULL 

declare @p2 int  set @p2=0 

declare @p5 int  set @p5=4098

declare @p6 int  set @p6=8193 

declare @p7 int  set @p7=0 

 exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',

N'

SELECT       T1.CONFLICT_ID

FROM         dbo.S_AUDIT_ITEM T1  with (INDEX=S_AUDIT_ITEM_M3) /* 解决方案2 */        

LEFT OUTER JOIN dbo.S_USER T2

ON T1.USER_ID = T2.PAR_ROW_ID   

WHERE  ((T1.BC_BASE_TBL = @P1) 

AND  (T1.RECORD_ID = @P2))   

ORDER BY  T1.OPERATION_DT DESC 

OPTION (FAST 40)

',

@p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY' 

 print 'fetch'

exec sp_cursorfetch @p2,2,4,1 

 exec sp_cursorclose @p2

  

To be continued…

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

请登录后发表评论 登录
全部评论

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1436995