1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */3 4 /*
5 ** Messages for "sp_cmp_all_qplans"
6 **
7 ** 18639, "There is no query plans group named '%1!' in this database."
8 ** 18661, "If the two query plans groups are large, this might take some time."
9 ** 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support."
10 ** 18662, "Query plans that are the same"
11 ** 18663, "Different query plans that have the same association key"
12 ** 18664, "Query plans present only in group '%1!'"
13 ** 18640, "Unknown %1! option : '%2!'. Valid options are : %3!."
14 */15 16 /*
17 ** sp_cmp_all_qplans is a AP group comparison assistant.
18 **
19 ** Valid modes are :
20 ** - "counts" - print only the counts per category;
21 ** - "brief" - also print the IDs per category, except
22 ** for "same";
23 ** - "same" - "counts" + IDs + APs that are the same;
24 ** - "diff" - "counts" + IDs + APs that are different;
25 ** - "first" - "counts" + IDs + APs that are only in
26 ** the first group;
27 ** - "second" - "counts" + IDs + APs that are only in
28 ** the second group;
29 ** - "offending" - "diff" + "first" + "second";
30 ** - "full" - "same" + "offending".
31 **
32 ** The comparison is based on pairs of plans, one from each group,
33 ** that have the same association key (UID + query text). (Note
34 ** that the GID is also part of the association key, but it's set
35 ** by the first 2 parameters.) Plans that cannot be matched in
36 ** pairs on the association key will be considered as being part
37 ** of only one group. Plans that are matched, are further compared
38 ** on the AP texts, to check whether they are the same or different.
39 ** Note, no learning AP should be considered in comparisons.
40 **
41 ** Processing is done as follows :
42 ** - we accumulate in #common, the APs in the 2 groups that
43 ** have at least some corresponding rows of text in common;
44 ** - we accumulate in #similar pairs of APs from #common
45 ** that have, per type, the same number of rows as the common
46 ** rows and match at least on the query text;
47 ** - we put in #same the pairs from #similar that matched
48 ** both the query text and on the AP text;
49 ** - we put in #diff the pairs that have in #similar only
50 ** the query text, but not also the AP;
51 ** - #first and #second are basic set differences between
52 ** the relevant part of sysqueryplans and the part of
53 ** #similar that contains query text.
54 */55 56 createproceduresp_cmp_all_qplans57 @group1 varchar(30),58 @group2 varchar(30),59 @mode varchar(20)= "counts"
60 as61 declare62 @msg varchar(1024),63 @uid int,64 @gid1 int,65 @gid2 int,66 @id1 int,67 @id2 int
68 69 set flushmessage on70 set nocount on71 72 /* Don't start a transaction for the user, he does not expect it. */73 if @@trancount = 0
74 begin75 set chained off76 end77 78 /* Don't hold long READ locks, the user might not appreciate it. */79 settransactionisolationlevel 1
80 81 82 select@uid=nullif(user_id(), 1)83 84 execsp_aux_get_qpgroup@group1,@gid1out85 execsp_aux_get_qpgroup@group2,@gid2out86 87 if@gid1isnull88 raiserror 18639,@group189 90 if@gid2isnull91 raiserror 18639,@group292 93 if@gid1isnullor@gid2isnull94 return 3
95 96 /*
97 ** Work silently for a while, preparing intermediate results.
98 ** But warn the audience...
99 */100 101 /* 18661, "If the two query plans groups are large, this might take some time." */102 execsp_getmessage 18661,@msgout103 print@msg104 105 106 selectg1.idas id1,g2.idas id2,g1.typeas type,count(*)as cnt
107 into #common
108 fromsysqueryplans g1,sysqueryplans g2
109 where110 g1.uid=g2.uid111 andg1.uid= isnull(@uid,g1.uid)112 andg2.uid= isnull(@uid,g2.uid)113 114 andg1.gid=@gid1115 andg2.gid=@gid2116 117 andg1.hashkey=g2.hashkey118 andg1.type=g2.type119 andg1.type<= 100
120 andg1.sequence=g2.sequence121 and isnull(rtrim(g1.text), " ")= isnull(rtrim(g2.text), " ")122 groupbyg1.id,g2.id,g1.type123 havingcount(*)> 0
124 125 select id1, id2,sum(type)as type
126 into #similar
127 from #common
128 where129 cnt =(selectcount(*)130 fromsysqueryplans131 where132 id= #common.id1
133 andtype= #common.type)134 and135 cnt =(selectcount(*)136 fromsysqueryplans137 where138 id= #common.id2
139 andtype= #common.type)140 groupby id1, id2
141 havingsum(type)in(10, 110)142 143 /* for the per type searches */144 createclusteredindex ci_type on #similar(type)145 146 /* for the NOT IN */147 createindex nci_id1 on #similar(id1)148 createindex nci_id2 on #similar(id2)149 150 151 /*
152 ** Done with the computing, let's get out the conclusions.
153 */154 155 /*
156 ** Same APs
157 */158 159 select id1, id2
160 into #same
161 from #similar
162 where type = 110
163 164 /* 18662, "Query plans that are the same" */165 execsp_getmessage 18662,@msgout166 print@msg167 168 selectcount(*)as "count"
169 from #same
170 171 /* Don't print the zillion of same APs, except if explicitly requested */172 173 if@modein("same", "full")174 begin175 /* print the same AP pairs of IDs */176 177 print ""
178 print " %1! %2!",@group1,@group2179 180 select id1, id2
181 from #same
182 183 /* cursor to print each pair of same APs */184 185 print ""
186 declare same cursor187 for188 select id1, id2
189 from #same
190 191 open same
192 fetch same into@id1,@id2193 194 while @@sqlstatus = 0
195 begin196 execsp_help_qplan@id1, full
197 execsp_help_qplan@id2, full
198 199 print "================================================"
200 fetch same into@id1,@id2201 end202 203 if @@sqlstatus = 1
204 begin205 /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */206 raiserror 18666
207 208 return 1
209 end210 211 end212 213 /*
214 ** Different APs
215 */216 217 select id1, id2
218 into #diff
219 from #similar
220 where type = 10
221 222 223 /* 18663, "Different query plans that have the same association key" */224 execsp_getmessage 18663,@msgout225 print@msg226 227 selectcount(*)as "count"
228 from #diff
229 230 /* print the different AP pairs of IDs */231 if@modein("brief", "diff", "offending", "full")232 begin233 print ""
234 print " %1! %2!",@group1,@group2235 236 select id1, id2
237 from #diff
238 end239 240 /* cursor to print the different APs */241 if@modein("diff", "offending", "full")242 begin243 print ""
244 declare diff cursor245 for246 select id1, id2
247 from #diff
248 249 open diff
250 fetch diff into@id1,@id2251 252 while @@sqlstatus = 0
253 begin254 execsp_help_qplan@id1, full
255 execsp_help_qplan@id2, full
256 257 print "================================================"
258 fetch diff into@id1,@id2259 end260 261 if @@sqlstatus = 1
262 begin263 /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */264 raiserror 18666
265 266 return 1
267 end268 end269 270 271 /*
272 ** APs that are only in the first group
273 */274 275 selectdistinctid276 into #first
277 fromsysqueryplans278 where279 idnotin(select id1 from #similar)280 andgid=@gid1281 anduid= isnull(@uid,uid)282 andtype= 100
283 284 /* 18664, "Query plans present only in group '%1!'" */285 execsp_getmessage 18664,@msgout286 print@msg,@group1287 288 selectcount(*)as "count"
289 from #first
290 291 if@modein("brief", "first", "offending", "full")292 begin293 print ""
294 select id
295 from #first
296 end297 298 if@modein("first", "offending", "full")299 begin300 print ""
301 declare first_c cursor302 for303 select id
304 from #first
305 306 open first_c
307 fetch first_c into@id1308 309 while @@sqlstatus = 0
310 begin311 execsp_help_qplan@id1, full
312 313 print "================================================"
314 fetch first_c into@id1315 end316 317 if @@sqlstatus = 1
318 begin319 /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */320 raiserror 18666
321 322 return 1
323 end324 end325 326 /*
327 ** Aps that are only in the second group
328 */329 330 selectdistinctid331 into #second
332 fromsysqueryplans333 where334 idnotin(select id2 from #similar)335 andgid=@gid2336 anduid= isnull(@uid,uid)337 andtype= 100
338 339 /* 18664, "Query plans present only in group '%1!'" */340 /* @msg contains this message, no need to fetch it again */341 /* exec sp_getmessage 18664, @msg out */342 print@msg,@group2343 344 selectcount(*)as "count"
345 from #second
346 347 if@modein("brief", "second", "offending", "full")348 begin349 print ""
350 select id
351 from #second
352 end353 354 if@modein("second", "offending", "full")355 begin356 print ""
357 declare second cursor358 for359 select id
360 from #second
361 362 open second
363 fetch second into@id2364 365 while @@sqlstatus = 0
366 begin367 execsp_help_qplan@id2, full
368 369 print "================================================"
370 fetch second into@id2371 end372 373 if @@sqlstatus = 1
374 begin375 /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */376 raiserror 18666
377 378 return 1
379 end380 end381 382 if@modenotin383 ("counts", "brief", "same", "diff",384 "first", "second", "offending", "full"
385 )386 begin387 /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */388 raiserror 18640, "sp_cmp_all_qplans",@mode,389 "'counts', 'brief', 'same', 'diff', 'first', 'second', 'offending', 'full'"
390 391 return 2
392 end393 394 return 0
395
exec sp_procxmode 'sp_cmp_all_qplans', 'AnyMode'
go
Grant Execute on sp_cmp_all_qplans to public
go