DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cmp_qplans  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_cmp_qplans"
6     **
7     ** 18636, "There is no query plan with the ID %1! in this database."
8     ** 18692, "The queries are the same."
9     ** 18693, "The queries are different."
10    ** 18694, "The queries are different but have the same hash key."
11    ** 18695, "The query plans are the same."
12    ** 18696, "The query plans are different."
13    ** 18640, "Unknown %1! option : '%2!'. Valid options are : %3!."
14    */
15    
16    /*
17    ** sp_cmp_qplans compares 2 plans, and returns : 
18    **
19    ** 0 - same query/plan
20    ** +1 - different queries
21    ** +2 - different queries and hash collision (does not accumulate with +1)
22    ** +10 - different plans
23    ** 100 - plan ID does not exist
24    **
25    ** For instance, 2 different APs that belong to 2 different queries
26    ** that collide on the hash key will return 2+10 = 12.
27    **
28    ** Accepts an @mode parameter, when "quiet" only returns status, 
29    ** when "brief" also gives messages, no "verbose" mode.
30    */
31    
32    create or replace procedure sp_cmp_qplans
33        @id1 int,
34        @id2 int,
35        @mode varchar(20) = "brief"
36    as
37        declare
38            @uid int,
39            @h1 int,
40            @h2 int,
41            @query int,
42            @plan int,
43            @msg varchar(1024),
44            @dbname varchar(255),
45            @dummy int,
46            @nullarg char(1),
47            @gp_enabled int,
48            @status int
49    
50    
51        set nocount on
52    
53        /* Don't start a transaction for the user, he does not expect it. */
54        if @@trancount = 0
55        begin
56            set chained off
57        end
58    
59        /* Don't hold long READ locks, the user might not appreciate it. */
60        set transaction isolation level 1
61    
62    
63        /*
64        **  If granular permissions is not enabled
65        **  only the Database Owner (DBO) or
66        **  Accounts with SA role can execute it.
67        **  If the user has SA role, it's uid will
68        **  be DBO uid (1). If granular permissions is enabled then
69        **  users with 'manage abstract plans' or 'monitor qp performance' 
70        **  permission can execute it.
71        */
72    
73        select @dbname = db_name()
74    
75        select @nullarg = NULL
76    
77        execute @status = sp_aux_checkroleperm "dbo",
78            "manage abstract plans", @dbname, @gp_enabled output
79    
80        if (@status != 0 and @gp_enabled = 1)
81        begin
82            execute @status = sp_aux_checkroleperm @nullarg,
83                "monitor qp performance", @nullarg, @gp_enabled output
84        end
85    
86        /* 
87        ** If @status = 0, user is dbo, has sa_role or if granular permissions
88        ** is enabled has 'manage abstract plans' permission.  Therefore the
89        ** user can see all query plans in the group.
90        */
91        if (@status = 0)
92            select @uid = NULL
93        else
94            select @uid = user_id()
95    
96        /* check the hash keys first */
97    
98        select @h1 = (select distinct hashkey
99                from sysqueryplans
100               where
101                   uid = isnull(@uid, uid)
102                   and id = @id1)
103   
104       select @h2 = (select distinct hashkey
105               from sysqueryplans
106               where
107                   uid = isnull(@uid, uid)
108                   and id = @id2)
109   
110       if @h1 is null
111           /* 18636, "There is no query plan with the ID %1! in this database." */
112           raiserror 18636, @id1
113   
114       if @h2 is null
115           /* 18636, "There is no query plan with the ID %1! in this database." */
116           raiserror 18636, @id2
117   
118       /* fail if either ID is absent */
119       if @h1 is null or @h2 is null
120           return 100
121   
122       if @h1 != @h2
123       begin
124           select @query = 1
125           goto plans
126       end
127   
128       /* hash collision at least */
129       select @query = 2
130   
131       if
132               (select count(*)
133               from sysqueryplans
134               where
135                   id = @id1
136                   and type = 10)
137           !=
138               (select count(*)
139               from sysqueryplans
140               where
141                   id = @id2
142                   and type = 10)
143           goto plans
144   
145       if exists (select *
146               from sysqueryplans p1, sysqueryplans p2
147               where
148                   p1.id = @id1
149                   and p2.id = @id2
150                   and p1.type = 10
151                   and p2.type = 10
152                   and p1.sequence = p2.sequence
153                   and isnull(rtrim(p1.text), " ") != isnull(rtrim(p2.text), " "))
154           goto plans
155   
156       /* same query */
157       select @query = 0
158   
159   plans:
160   
161       /* assume different plans */
162       select @plan = 10
163   
164       if
165               (select count(*)
166               from sysqueryplans
167               where
168                   id = @id1
169                   and type = 100)
170           !=
171               (select count(*)
172               from sysqueryplans
173               where
174                   id = @id2
175                   and type = 100)
176           goto ret
177   
178       if exists (select *
179               from sysqueryplans p1, sysqueryplans p2
180               where
181                   p1.id = @id1
182                   and p2.id = @id2
183                   and p1.type = 100
184                   and p2.type = 100
185                   and p1.sequence = p2.sequence
186                   and isnull(rtrim(p1.text), " ") != isnull(rtrim(p2.text), " "))
187           goto ret
188   
189       /* same plan */
190       select @plan = 0
191   
192   ret:
193   
194       if @mode = "brief"
195       begin
196           /* query related messages */
197           if @query = 0
198           begin
199               /* 18692, "The queries are the same." */
200               exec sp_getmessage 18692, @msg out
201           end
202           else if @query = 1
203           begin
204               /* 18693, "The queries are different." */
205               exec sp_getmessage 18693, @msg out
206           end
207           else if @query = 2
208           begin
209               /* 18694, "The queries are different but have the same hash key." */
210               exec sp_getmessage 18694, @msg out
211           end
212           /* else wrong @query - do nothing */
213   
214           print @msg
215   
216           /* plan related messages */
217           if @plan = 0
218           begin
219               /* 18695, "The query plans are the same." */
220               exec sp_getmessage 18695, @msg out
221           end
222           else if @plan = 10
223           begin
224               /* 18696, "The query plans are different." */
225               exec sp_getmessage 18696, @msg out
226           end
227           /* else wrong @plan - do nothing */
228   
229           print @msg
230   
231       end
232       else if @mode != "quiet"
233       begin
234           /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
235           raiserror 18640, "sp_cmp_qplans", @mode, "'quiet', 'brief'"
236       end
237   
238       /*
239       ** 0 - same query/plan
240       ** +1 - different queries
241       ** +2 - different queries and hash collision
242       ** +10 - different plans
243       ** 100 - plan ID does not exist
244       */
245       return @query + @plan
246   
247   


exec sp_procxmode 'sp_cmp_qplans', 'AnyMode'
go

Grant Execute on sp_cmp_qplans to public
go
DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
102
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
108
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {type, id}
135
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {type, id}
141
 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}
Uncovered: [uid, gid, hashkey]
152
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {type, id}
168
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {type, id}
174
 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}
Uncovered: [uid, gid, hashkey]
185
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 142
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 175
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 183
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 184
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 108
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 135
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 141
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 148
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 149
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 168
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 174
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 181
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 182
 MGTP 3 Grant to public sybsystemprocs..sp_cmp_qplans  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 200
 MNER 3 No Error Check should check return value of exec 205
 MNER 3 No Error Check should check return value of exec 210
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check return value of exec 225
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 91
 QGWO 3 Group by/Distinct/Union without order by 98
 QGWO 3 Group by/Distinct/Union without order by 104
 QISO 3 Set isolation level 60
 QNAJ 3 Not using ANSI Inner Join 146
 QNAJ 3 Not using ANSI Inner Join 179
 VNRD 3 Variable is not read @gp_enabled 83
 VUNU 3 Variable is not used @dummy 45
 MSUB 2 Subquery Marker 98
 MSUB 2 Subquery Marker 104
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 145
 MSUB 2 Subquery Marker 165
 MSUB 2 Subquery Marker 171
 MSUB 2 Subquery Marker 178
 MTR1 2 Metrics: Comments Ratio Comments: 45% 32
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 37dec - 1exi + 2 32
 MTR3 2 Metrics: Query Complexity Complexity: 109 32
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 145
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 178

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_copy_qplan  
   called by proc sybsystemprocs..sp_copy_all_qplans