DatabaseProcApplicationCreatedLinks
sybsystemprocssp_find_qplan  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_find_qplan"
6     */
7     
8     /*
9     ** sp_find_qplan does a pattern matching SYSQUERYPLANS lookup. 
10    ** @pattern is used as such, so any leading or trailing wildcards 
11    ** must be explicitly part of it. As TSQL has no TEXT append 
12    ** function, the pattern matching is done row by row. Text 
13    ** crossing row boundaries that would match the pattern is thus 
14    ** missed. Hence, all multi-row APs are signaled for manual 
15    ** checking. Once we have a decent LOB in TSQL, this should be 
16    ** revisited.
17    */
18    
19    create or replace procedure sp_find_qplan
20        @pattern varchar(255),
21        @group varchar(30) = null
22    as
23        declare
24            @msg varchar(255)
25            , @uid int
26            , @gid int
27            , @dummy int
28            , @nullarg char(1)
29            , @gp_enabled int
30            , @dbname varchar(255)
31            , @status int
32    
33    
34        set nocount on
35    
36        /* Don't start a transaction for the user, he does not expect it. */
37        if @@trancount = 0
38        begin
39            set chained off
40        end
41    
42        /* Don't hold long READ locks, the user might not appreciate it. */
43        set transaction isolation level 1
44    
45    
46        select @dbname = db_name()
47    
48        /*
49        **  Only the Database Owner (DBO) or
50        **  Accounts with SA role can execute it.
51        **  If the user has SA role, it's uid will
52        **  be DBO uid (1). If granular permissions is enabled then
53        **  users with 'manage abstract plans' or 'monitor qp performance' 
54        **  permission can execute it.
55        */
56    
57        select @nullarg = NULL
58    
59        execute @status = sp_aux_checkroleperm "dbo",
60            "manage abstract plans", @dbname, @gp_enabled output
61    
62        if (@status != 0 and @gp_enabled = 1)
63        begin
64            execute @status = sp_aux_checkroleperm "dbo",
65                "monitor qp performance", @nullarg, @gp_enabled output
66        end
67        /* 
68        ** If @status = 0, user is dbo, has sa_role or if granular permissions
69        ** is enabled has either 'manage abstract plans' or
70        ** 'monitor qp performance' permission.  Therefore the
71        ** user will find all query plans in the group.
72        */
73        if (@status = 0)
74            select @uid = NULL
75        else
76            select @uid = user_id()
77    
78        if @group is not null
79            exec sp_aux_get_qpgroup @group, @gid out
80    
81        /* get the easy ones - where the pattern is immediately found */
82        select distinct id
83        into #found
84        from sysqueryplans
85        where
86            uid = isnull(@uid, uid)
87            and gid = isnull(@gid, gid)
88            and text like @pattern
89    
90        /* now get the ones that span over rows and didn't match yet */
91        select id
92        into #multi
93        from sysqueryplans
94        where
95            uid = isnull(@uid, uid)
96            and gid = isnull(@gid, gid)
97            and id not in (select id from #found)
98        group by gid, id
99        having count(*) > 2
100   
101       /* now look the pattern up on row boundaries */
102       insert #found
103       select distinct #multi.id
104       from
105           #multi,
106           sysqueryplans prev,
107           sysqueryplans next
108       where
109           #multi.id = prev.id
110           and #multi.id = next.id
111           and prev.type = next.type
112           and prev.sequence + 1 = next.sequence
113           and substring(prev.text, 128, 128)
114           + substring(next.text, 1, 128)
115           like @pattern
116   
117       select gid, id, text from sysqueryplans
118       where id in (select id from #found)
119       order by gid, id, type, sequence
120   
121       return 0
122   


exec sp_procxmode 'sp_find_qplan', 'AnyMode'
go

Grant Execute on sp_find_qplan to public
go
RESULT SETS
sp_find_qplan_rset_001

DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
109
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
110
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {sequence, type}
Uncovered: [uid, gid, hashkey, id]
111
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 112
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 82
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 91
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 97
 MAW1 3 Warning message on %name% tempdb..#found.id: Warning message on #found_crby_sybsystemprocs__sp_find_qplan 97
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 98
 MAW1 3 Warning message on %name% tempdb..#multi.id: Warning message on #multi_crby_sybsystemprocs__sp_find_qplan 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 109
 MAW1 3 Warning message on %name% tempdb..#multi.id: Warning message on #multi_crby_sybsystemprocs__sp_find_qplan 109
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 110
 MAW1 3 Warning message on %name% tempdb..#multi.id: Warning message on #multi_crby_sybsystemprocs__sp_find_qplan 110
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 117
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 118
 MAW1 3 Warning message on %name% tempdb..#found.id: Warning message on #found_crby_sybsystemprocs__sp_find_qplan 118
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 119
 MGTP 3 Grant to public sybsystemprocs..sp_find_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check @@error after select into 82
 MNER 3 No Error Check should check @@error after select into 91
 MNER 3 No Error Check should check @@error after insert 102
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 73
 QDIS 3 Check correct use of 'select distinct' 103
 QGWO 3 Group by/Distinct/Union without order by 82
 QGWO 3 Group by/Distinct/Union without order by 103
 QISO 3 Set isolation level 43
 QJWT 3 Join or Sarg Without Index on temp table 97
 QJWT 3 Join or Sarg Without Index on temp table 118
 QNAJ 3 Not using ANSI Inner Join 104
 QNUA 3 Should use Alias: Table #multi 105
 VNRD 3 Variable is not read @gp_enabled 65
 VUNU 3 Variable is not used @msg 24
 VUNU 3 Variable is not used @dummy 27
 MRST 2 Result Set Marker 117
 MTR1 2 Metrics: Comments Ratio Comments: 44% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 10dec - 1exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 50 19

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
writes table sybsystemprocs..sp_find_qplan_rset_001 
read_writes table tempdb..#found (1) 
read_writes table tempdb..#multi (1)