DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cmp_all_qplans  14 déc. 14Defects Propagation 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 or replace 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            @dummy int,
69            @nullarg char(1),
70            @gp_enabled int,
71            @dbname varchar(255),
72            @status int
73    
74    
75        set flushmessage on
76        set nocount on
77    
78        /* Don't start a transaction for the user, he does not expect it. */
79        if @@trancount = 0
80        begin
81            set chained off
82        end
83    
84        /* Don't hold long READ locks, the user might not appreciate it. */
85        set transaction isolation level 1
86    
87        /*
88        **  If granular permissions is not enabled
89        **  only the Database Owner (DBO) or
90        **  Accounts with SA role can execute it.
91        **  If the user has SA role, it's uid will
92        **  be DBO uid (1). If granular permissions is enabled then
93        **  users with 'manage abstract plans' or 'monitor qp performance'
94        **  permission can execute it.
95        */
96    
97        select @dbname = db_name()
98        select @nullarg = NULL
99    
100       execute @status = sp_aux_checkroleperm "dbo",
101           "manage abstract plans", @dbname, @gp_enabled output
102   
103       if (@status != 0 and @gp_enabled = 1)
104       begin
105           execute @status = sp_aux_checkroleperm @nullarg,
106               "monitor qp performance", @nullarg, @gp_enabled output
107       end
108       /* 
109       ** If @status = 0, user is dbo, has sa_role or if granular permissions
110       ** is enabled has either 'manage abstract plans' or 
111       ** 'monitor qp performance' permission.  Therefore the
112       ** user can see all query plans in the group.
113       */
114       if (@status = 0)
115           select @uid = NULL
116       else
117           select @uid = user_id()
118   
119       exec sp_aux_get_qpgroup @group1, @gid1 out
120       exec sp_aux_get_qpgroup @group2, @gid2 out
121   
122       if @gid1 is null
123           raiserror 18639, @group1
124   
125       if @gid2 is null
126           raiserror 18639, @group2
127   
128       if @gid1 is null or @gid2 is null
129           return 3
130   
131       /*
132       ** Work silently for a while, preparing intermediate results.
133       ** But warn the audience...
134       */
135   
136       /* 18661, "If the two query plans groups are large, this might take some time." */
137       exec sp_getmessage 18661, @msg out
138       print @msg
139   
140   
141       select g1.id as id1, g2.id as id2, g1.type as type, count(*) as cnt
142       into #common
143       from sysqueryplans g1, sysqueryplans g2
144       where
145           g1.uid = g2.uid
146           and g1.uid = isnull(@uid, g1.uid)
147           and g2.uid = isnull(@uid, g2.uid)
148   
149           and g1.gid = @gid1
150           and g2.gid = @gid2
151   
152           and g1.hashkey = g2.hashkey
153           and g1.type = g2.type
154           and g1.type <= 100
155           and g1.sequence = g2.sequence
156           and isnull(rtrim(g1.text), " ") = isnull(rtrim(g2.text), " ")
157       group by g1.id, g2.id, g1.type
158       having count(*) > 0
159   
160       select id1, id2, sum(type) as type
161       into #similar
162       from #common
163       where
164           cnt = (select count(*)
165               from sysqueryplans
166               where
167                   id = #common.id1
168                   and type = #common.type)
169           and
170           cnt = (select count(*)
171               from sysqueryplans
172               where
173                   id = #common.id2
174                   and type = #common.type)
175       group by id1, id2
176       having sum(type) in (10, 110)
177   
178       /* for the per type searches */
179       create clustered index ci_type on #similar(type)
180   
181       /* for the NOT IN */
182       create index nci_id1 on #similar(id1)
183       create index nci_id2 on #similar(id2)
184   
185   
186       /*
187       ** Done with the computing, let's get out the conclusions.
188       */
189   
190       /*
191       ** Same APs
192       */
193   
194       select id1, id2
195       into #same
196       from #similar
197       where type = 110
198   
199       /* 18662, "Query plans that are the same" */
200       exec sp_getmessage 18662, @msg out
201       print @msg
202   
203       select count(*) as "count"
204       from #same
205   
206       /* Don't print the zillion of same APs, except if explicitly requested */
207   
208       if @mode in ("same", "full")
209       begin
210           /* print the same AP pairs of IDs */
211   
212           print ""
213           print "	%1!    %2!", @group1, @group2
214   
215           select id1, id2
216           from #same
217   
218           /* cursor to print each pair of same APs */
219   
220           print ""
221           declare same cursor
222           for
223           select id1, id2
224           from #same
225   
226           open same
227           fetch same into @id1, @id2
228   
229           while @@sqlstatus = 0
230           begin
231               exec sp_help_qplan @id1, full
232               exec sp_help_qplan @id2, full
233   
234               print "================================================"
235               fetch same into @id1, @id2
236           end
237   
238           if @@sqlstatus = 1
239           begin
240               /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */
241               raiserror 18666
242   
243               return 1
244           end
245   
246       end
247   
248       /*
249       ** Different APs
250       */
251   
252       select id1, id2
253       into #diff
254       from #similar
255       where type = 10
256   
257   
258       /* 18663, "Different query plans that have the same association key" */
259       exec sp_getmessage 18663, @msg out
260       print @msg
261   
262       select count(*) as "count"
263       from #diff
264   
265       /* print the different AP pairs of IDs */
266       if @mode in ("brief", "diff", "offending", "full")
267       begin
268           print ""
269           print "	%1!    %2!", @group1, @group2
270   
271           select id1, id2
272           from #diff
273       end
274   
275       /* cursor to print the different APs */
276       if @mode in ("diff", "offending", "full")
277       begin
278           print ""
279           declare diff cursor
280           for
281           select id1, id2
282           from #diff
283   
284           open diff
285           fetch diff into @id1, @id2
286   
287           while @@sqlstatus = 0
288           begin
289               exec sp_help_qplan @id1, full
290               exec sp_help_qplan @id2, full
291   
292               print "================================================"
293               fetch diff into @id1, @id2
294           end
295   
296           if @@sqlstatus = 1
297           begin
298               /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */
299               raiserror 18666
300   
301               return 1
302           end
303       end
304   
305   
306       /*
307       ** APs that are only in the first group
308       */
309   
310       select distinct id
311       into #first
312       from sysqueryplans
313       where
314           id not in (select id1 from #similar)
315           and gid = @gid1
316           and uid = isnull(@uid, uid)
317           and type = 100
318   
319       /* 18664, "Query plans present only in group '%1!'" */
320       exec sp_getmessage 18664, @msg out
321       print @msg, @group1
322   
323       select count(*) as "count"
324       from #first
325   
326       if @mode in ("brief", "first", "offending", "full")
327       begin
328           print ""
329           select id
330           from #first
331       end
332   
333       if @mode in ("first", "offending", "full")
334       begin
335           print ""
336           declare first_c cursor
337           for
338           select id
339           from #first
340   
341           open first_c
342           fetch first_c into @id1
343   
344           while @@sqlstatus = 0
345           begin
346               exec sp_help_qplan @id1, full
347   
348               print "================================================"
349               fetch first_c into @id1
350           end
351   
352           if @@sqlstatus = 1
353           begin
354               /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */
355               raiserror 18666
356   
357               return 1
358           end
359       end
360   
361       /*
362       ** Aps that are only in the second group
363       */
364   
365       select distinct id
366       into #second
367       from sysqueryplans
368       where
369           id not in (select id2 from #similar)
370           and gid = @gid2
371           and uid = isnull(@uid, uid)
372           and type = 100
373   
374       /* 18664, "Query plans present only in group '%1!'" */
375       /* @msg contains this message, no need to fetch it again */
376       /* exec sp_getmessage 18664, @msg out */
377       print @msg, @group2
378   
379       select count(*) as "count"
380       from #second
381   
382       if @mode in ("brief", "second", "offending", "full")
383       begin
384           print ""
385           select id
386           from #second
387       end
388   
389       if @mode in ("second", "offending", "full")
390       begin
391           print ""
392           declare second cursor
393           for
394           select id
395           from #second
396   
397           open second
398           fetch second into @id2
399   
400           while @@sqlstatus = 0
401           begin
402               exec sp_help_qplan @id2, full
403   
404               print "================================================"
405               fetch second into @id2
406           end
407   
408           if @@sqlstatus = 1
409           begin
410               /* 18666, "Temporary table fetch error. If there are no other error messages and this error persists, please contact Sybase Technical Support." */
411               raiserror 18666
412   
413               return 1
414           end
415       end
416   
417       if @mode not in
418           ("counts", "brief", "same", "diff",
419               "first", "second", "offending", "full"
420           )
421       begin
422           /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
423           raiserror 18640, "sp_cmp_all_qplans", @mode,
424               "'counts', 'brief', 'same', 'diff', 'first', 'second', 'offending', 'full'"
425   
426           return 2
427       end
428   
429       return 0
430   


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 212
 MEST 4 Empty String will be replaced by Single Space 220
 MEST 4 Empty String will be replaced by Single Space 268
 MEST 4 Empty String will be replaced by Single Space 278
 MEST 4 Empty String will be replaced by Single Space 328
 MEST 4 Empty String will be replaced by Single Space 335
 MEST 4 Empty String will be replaced by Single Space 384
 MEST 4 Empty String will be replaced by Single Space 391
 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}
315
 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}
370
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 197
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 255
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 317
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 372
 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 157
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 167
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 173
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 310
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 314
 MAW1 3 Warning message on %name% tempdb..#first.id: Warning message on #first_crby_sybsystemprocs__sp_cmp_all_qplans 329
 MAW1 3 Warning message on %name% tempdb..#first.id: Warning message on #first_crby_sybsystemprocs__sp_cmp_all_qplans 338
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 365
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 369
 MAW1 3 Warning message on %name% tempdb..#second.id: Warning message on #second_crby_sybsystemprocs__sp_cmp_all_qplans 385
 MAW1 3 Warning message on %name% tempdb..#second.id: Warning message on #second_crby_sybsystemprocs__sp_cmp_all_qplans 394
 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 119
 MNER 3 No Error Check should check return value of exec 120
 MNER 3 No Error Check should check return value of exec 137
 MNER 3 No Error Check should check @@error after select into 141
 MNER 3 No Error Check should check @@error after select into 160
 MNER 3 No Error Check should check @@error after select into 194
 MNER 3 No Error Check should check return value of exec 200
 MNER 3 No Error Check should check return value of exec 231
 MNER 3 No Error Check should check return value of exec 232
 MNER 3 No Error Check should check @@error after select into 252
 MNER 3 No Error Check should check return value of exec 259
 MNER 3 No Error Check should check return value of exec 289
 MNER 3 No Error Check should check return value of exec 290
 MNER 3 No Error Check should check @@error after select into 310
 MNER 3 No Error Check should check return value of exec 320
 MNER 3 No Error Check should check return value of exec 346
 MNER 3 No Error Check should check @@error after select into 365
 MNER 3 No Error Check should check return value of exec 402
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 114
 QCRS 3 Conditional Result Set 215
 QCRS 3 Conditional Result Set 271
 QCRS 3 Conditional Result Set 329
 QCRS 3 Conditional Result Set 385
 QGWO 3 Group by/Distinct/Union without order by 310
 QGWO 3 Group by/Distinct/Union without order by 365
 QISO 3 Set isolation level 85
 QJWT 3 Join or Sarg Without Index on temp table 314
 QJWT 3 Join or Sarg Without Index on temp table 369
 QNAJ 3 Not using ANSI Inner Join 143
 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]
145
 VNRD 3 Variable is not read @gp_enabled 106
 VUNU 3 Variable is not used @dummy 68
 CUPD 2 Updatable Cursor Marker (updatable by default) 223
 CUPD 2 Updatable Cursor Marker (updatable by default) 281
 CUPD 2 Updatable Cursor Marker (updatable by default) 338
 CUPD 2 Updatable Cursor Marker (updatable by default) 394
 MRST 2 Result Set Marker 203
 MRST 2 Result Set Marker 215
 MRST 2 Result Set Marker 262
 MRST 2 Result Set Marker 271
 MRST 2 Result Set Marker 323
 MRST 2 Result Set Marker 329
 MRST 2 Result Set Marker 379
 MRST 2 Result Set Marker 385
 MSUC 2 Correlated Subquery Marker 164
 MSUC 2 Correlated Subquery Marker 170
 MTR1 2 Metrics: Comments Ratio Comments: 45% 56
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 39 = 43dec - 6exi + 2 56
 MTR3 2 Metrics: Query Complexity Complexity: 193 56
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 141

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#second (1) 
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_help_qplan  
   calls proc sybsystemprocs..sp_autoformat  
      writes table sybsystemprocs..sp_autoformat_rset_003 
      reads table tempdb..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      writes table sybsystemprocs..sp_autoformat_rset_001 
      reads table tempdb..systypes (1)  
      reads table master..systypes (1)  
      writes table sybsystemprocs..sp_autoformat_rset_005 
      calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      writes table sybsystemprocs..sp_autoformat_rset_004 
      reads table master..syscolumns (1)  
      writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table sybsystemprocs..sysqueryplans  
   writes table tempdb..#plan_text (1) 
   writes table sybsystemprocs..sp_help_qplan_rset_002 
   writes table sybsystemprocs..sp_help_qplan_rset_005 
   writes table sybsystemprocs..sp_help_qplan_rset_003 
   writes table sybsystemprocs..sp_help_qplan_rset_001 
   writes table sybsystemprocs..sp_help_qplan_rset_004 
   calls proc sybsystemprocs..sp_aux_checkroleperm  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
   writes table tempdb..#query_text (1) 
read_writes table tempdb..#common (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
writes table sybsystemprocs..sp_cmp_all_qplans_rset_008 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_002 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_005 
read_writes table tempdb..#similar (1) 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_004 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_001 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_006 
read_writes table tempdb..#same (1) 
writes table sybsystemprocs..sp_cmp_all_qplans_rset_007 
reads table sybsystemprocs..sysqueryplans  
writes table sybsystemprocs..sp_cmp_all_qplans_rset_003 
read_writes table tempdb..#first (1) 
read_writes table tempdb..#diff (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm