DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cmp_all_qplans  31 Aug 14Defects Dependencies

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    create procedure sp_cmp_all_qplans
57        @group1 varchar(30),
58        @group2 varchar(30),
59        @mode varchar(20) = "counts"
60    as
61        declare
62            @msg varchar(1024),
63            @uid int,
64            @gid1 int,
65            @gid2 int,
66            @id1 int,
67            @id2 int
68    
69        set flushmessage on
70        set nocount on
71    
72        /* Don't start a transaction for the user, he does not expect it. */
73        if @@trancount = 0
74        begin
75            set chained off
76        end
77    
78        /* Don't hold long READ locks, the user might not appreciate it. */
79        set transaction isolation level 1
80    
81    
82        select @uid = nullif (user_id(), 1)
83    
84        exec sp_aux_get_qpgroup @group1, @gid1 out
85        exec sp_aux_get_qpgroup @group2, @gid2 out
86    
87        if @gid1 is null
88            raiserror 18639, @group1
89    
90        if @gid2 is null
91            raiserror 18639, @group2
92    
93        if @gid1 is null or @gid2 is null
94            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       exec sp_getmessage 18661, @msg out
103       print @msg
104   
105   
106       select g1.id as id1, g2.id as id2, g1.type as type, count(*) as cnt
107       into #common
108       from sysqueryplans g1, sysqueryplans g2
109       where
110           g1.uid = g2.uid
111           and g1.uid = isnull(@uid, g1.uid)
112           and g2.uid = isnull(@uid, g2.uid)
113   
114           and g1.gid = @gid1
115           and g2.gid = @gid2
116   
117           and g1.hashkey = g2.hashkey
118           and g1.type = g2.type
119           and g1.type <= 100
120           and g1.sequence = g2.sequence
121           and isnull(rtrim(g1.text), " ") = isnull(rtrim(g2.text), " ")
122       group by g1.id, g2.id, g1.type
123       having count(*) > 0
124   
125       select id1, id2, sum(type) as type
126       into #similar
127       from #common
128       where
129           cnt = (select count(*)
130               from sysqueryplans
131               where
132                   id = #common.id1
133                   and type = #common.type)
134           and
135           cnt = (select count(*)
136               from sysqueryplans
137               where
138                   id = #common.id2
139                   and type = #common.type)
140       group by id1, id2
141       having sum(type) in (10, 110)
142   
143       /* for the per type searches */
144       create clustered index ci_type on #similar(type)
145   
146       /* for the NOT IN */
147       create index nci_id1 on #similar(id1)
148       create index 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       exec sp_getmessage 18662, @msg out
166       print @msg
167   
168       select count(*) as "count"
169       from #same
170   
171       /* Don't print the zillion of same APs, except if explicitly requested */
172   
173       if @mode in ("same", "full")
174       begin
175           /* print the same AP pairs of IDs */
176   
177           print ""
178           print "	%1!    %2!", @group1, @group2
179   
180           select id1, id2
181           from #same
182   
183           /* cursor to print each pair of same APs */
184   
185           print ""
186           declare same cursor
187           for
188           select id1, id2
189           from #same
190   
191           open same
192           fetch same into @id1, @id2
193   
194           while @@sqlstatus = 0
195           begin
196               exec sp_help_qplan @id1, full
197               exec sp_help_qplan @id2, full
198   
199               print "================================================"
200               fetch same into @id1, @id2
201           end
202   
203           if @@sqlstatus = 1
204           begin
205               /* 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           end
210   
211       end
212   
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       exec sp_getmessage 18663, @msg out
225       print @msg
226   
227       select count(*) as "count"
228       from #diff
229   
230       /* print the different AP pairs of IDs */
231       if @mode in ("brief", "diff", "offending", "full")
232       begin
233           print ""
234           print "	%1!    %2!", @group1, @group2
235   
236           select id1, id2
237           from #diff
238       end
239   
240       /* cursor to print the different APs */
241       if @mode in ("diff", "offending", "full")
242       begin
243           print ""
244           declare diff cursor
245           for
246           select id1, id2
247           from #diff
248   
249           open diff
250           fetch diff into @id1, @id2
251   
252           while @@sqlstatus = 0
253           begin
254               exec sp_help_qplan @id1, full
255               exec sp_help_qplan @id2, full
256   
257               print "================================================"
258               fetch diff into @id1, @id2
259           end
260   
261           if @@sqlstatus = 1
262           begin
263               /* 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           end
268       end
269   
270   
271       /*
272       ** APs that are only in the first group
273       */
274   
275       select distinct id
276       into #first
277       from sysqueryplans
278       where
279           id not in (select id1 from #similar)
280           and gid = @gid1
281           and uid = isnull(@uid, uid)
282           and type = 100
283   
284       /* 18664, "Query plans present only in group '%1!'" */
285       exec sp_getmessage 18664, @msg out
286       print @msg, @group1
287   
288       select count(*) as "count"
289       from #first
290   
291       if @mode in ("brief", "first", "offending", "full")
292       begin
293           print ""
294           select id
295           from #first
296       end
297   
298       if @mode in ("first", "offending", "full")
299       begin
300           print ""
301           declare first_c cursor
302           for
303           select id
304           from #first
305   
306           open first_c
307           fetch first_c into @id1
308   
309           while @@sqlstatus = 0
310           begin
311               exec sp_help_qplan @id1, full
312   
313               print "================================================"
314               fetch first_c into @id1
315           end
316   
317           if @@sqlstatus = 1
318           begin
319               /* 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           end
324       end
325   
326       /*
327       ** Aps that are only in the second group
328       */
329   
330       select distinct id
331       into #second
332       from sysqueryplans
333       where
334           id not in (select id2 from #similar)
335           and gid = @gid2
336           and uid = isnull(@uid, uid)
337           and type = 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, @group2
343   
344       select count(*) as "count"
345       from #second
346   
347       if @mode in ("brief", "second", "offending", "full")
348       begin
349           print ""
350           select id
351           from #second
352       end
353   
354       if @mode in ("second", "offending", "full")
355       begin
356           print ""
357           declare second cursor
358           for
359           select id
360           from #second
361   
362           open second
363           fetch second into @id2
364   
365           while @@sqlstatus = 0
366           begin
367               exec sp_help_qplan @id2, full
368   
369               print "================================================"
370               fetch second into @id2
371           end
372   
373           if @@sqlstatus = 1
374           begin
375               /* 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           end
380       end
381   
382       if @mode not in
383           ("counts", "brief", "same", "diff",
384               "first", "second", "offending", "full"
385           )
386       begin
387           /* 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       end
393   
394       return 0
395   


exec sp_procxmode 'sp_cmp_all_qplans', 'AnyMode'
go

Grant Execute on sp_cmp_all_qplans to public
go
RESULT SETS
sp_cmp_all_qplans_rset_007
sp_cmp_all_qplans_rset_006
sp_cmp_all_qplans_rset_005
sp_cmp_all_qplans_rset_004
sp_cmp_all_qplans_rset_003
sp_cmp_all_qplans_rset_002
sp_cmp_all_qplans_rset_001
sp_cmp_all_qplans_rset_008

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 177
 MEST 4 Empty String will be replaced by Single Space 185
 MEST 4 Empty String will be replaced by Single Space 233
 MEST 4 Empty String will be replaced by Single Space 243
 MEST 4 Empty String will be replaced by Single Space 293
 MEST 4 Empty String will be replaced by Single Space 300
 MEST 4 Empty String will be replaced by Single Space 349
 MEST 4 Empty String will be replaced by Single Space 356
 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: {gid, type}
280
 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: {gid, type}
335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 119
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 162
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 220
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 282
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 337
 MGTP 3 Grant to public sybsystemprocs..sp_cmp_all_qplans  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 84
 MNER 3 No Error Check should check return value of exec 85
 MNER 3 No Error Check should check return value of exec 102
 MNER 3 No Error Check should check @@error after select into 106
 MNER 3 No Error Check should check @@error after select into 125
 MNER 3 No Error Check should check @@error after select into 159
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check return value of exec 196
 MNER 3 No Error Check should check return value of exec 197
 MNER 3 No Error Check should check @@error after select into 217
 MNER 3 No Error Check should check return value of exec 224
 MNER 3 No Error Check should check return value of exec 254
 MNER 3 No Error Check should check return value of exec 255
 MNER 3 No Error Check should check @@error after select into 275
 MNER 3 No Error Check should check return value of exec 285
 MNER 3 No Error Check should check return value of exec 311
 MNER 3 No Error Check should check @@error after select into 330
 MNER 3 No Error Check should check return value of exec 367
 QCRS 3 Conditional Result Set 180
 QCRS 3 Conditional Result Set 236
 QCRS 3 Conditional Result Set 294
 QCRS 3 Conditional Result Set 350
 QGWO 3 Group by/Distinct/Union without order by 275
 QGWO 3 Group by/Distinct/Union without order by 330
 QISO 3 Set isolation level 79
 QJWT 3 Join or Sarg Without Index on temp table 132
 QJWT 3 Join or Sarg Without Index on temp table 138
 QJWT 3 Join or Sarg Without Index on temp table 279
 QJWT 3 Join or Sarg Without Index on temp table 334
 QNAJ 3 Not using ANSI Inner Join 108
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {uid, type, hashkey, sequence}
Uncovered: [id]
110
 CUPD 2 Updatable Cursor Marker (updatable by default) 188
 CUPD 2 Updatable Cursor Marker (updatable by default) 246
 CUPD 2 Updatable Cursor Marker (updatable by default) 303
 CUPD 2 Updatable Cursor Marker (updatable by default) 359
 MRST 2 Result Set Marker 168
 MRST 2 Result Set Marker 180
 MRST 2 Result Set Marker 227
 MRST 2 Result Set Marker 236
 MRST 2 Result Set Marker 288
 MRST 2 Result Set Marker 294
 MRST 2 Result Set Marker 344
 MRST 2 Result Set Marker 350
 MSUC 2 Correlated Subquery Marker 129
 MSUC 2 Correlated Subquery Marker 135
 MTR1 2 Metrics: Comments Ratio Comments: 44% 56
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 39dec - 6exi + 2 56
 MTR3 2 Metrics: Query Complexity Complexity: 183 56
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 106

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#diff (1) 
read_writes table tempdb..#common (1) 
read_writes table tempdb..#similar (1) 
read_writes table tempdb..#same (1) 
read_writes table tempdb..#second (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_help_qplan  
   reads table sybsystemprocs..sysqueryplans  
   calls proc sybsystemprocs..sp_autoformat  
      reads table master..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..syscolumns (1)  
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      reads table tempdb..systypes (1)  
   writes table tempdb..#plan_text (1) 
   writes table tempdb..#query_text (1) 
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysqueryplans  
read_writes table tempdb..#first (1)