DatabaseProcApplicationCreatedLinks
sybsystemprocssp_cmp_qplans  31 Aug 14Defects 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 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    
45        set nocount on
46    
47        /* Don't start a transaction for the user, he does not expect it. */
48        if @@trancount = 0
49        begin
50            set chained off
51        end
52    
53        /* Don't hold long READ locks, the user might not appreciate it. */
54        set transaction isolation level 1
55    
56    
57        select @uid = nullif (user_id(), 1)
58    
59        /* check the hash keys first */
60    
61        select @h1 = (select distinct hashkey
62                from sysqueryplans
63                where
64                    uid = isnull(@uid, uid)
65                    and id = @id1)
66    
67        select @h2 = (select distinct hashkey
68                from sysqueryplans
69                where
70                    uid = isnull(@uid, uid)
71                    and id = @id2)
72    
73        if @h1 is null
74            /* 18636, "There is no query plan with the ID %1! in this database." */
75            raiserror 18636, @id1
76    
77        if @h2 is null
78            /* 18636, "There is no query plan with the ID %1! in this database." */
79            raiserror 18636, @id2
80    
81        /* fail if either ID is absent */
82        if @h1 is null or @h2 is null
83            return 100
84    
85        if @h1 != @h2
86        begin
87            select @query = 1
88            goto plans
89        end
90    
91        /* hash collision at least */
92        select @query = 2
93    
94        if
95                (select count(*)
96                from sysqueryplans
97                where
98                    id = @id1
99                    and type = 10)
100           !=
101               (select count(*)
102               from sysqueryplans
103               where
104                   id = @id2
105                   and type = 10)
106           goto plans
107   
108       if exists (select *
109               from sysqueryplans p1, sysqueryplans p2
110               where
111                   p1.id = @id1
112                   and p2.id = @id2
113                   and p1.type = 10
114                   and p2.type = 10
115                   and p1.sequence = p2.sequence
116                   and isnull(rtrim(p1.text), " ") != isnull(rtrim(p2.text), " "))
117           goto plans
118   
119       /* same query */
120       select @query = 0
121   
122   plans:
123   
124       /* assume different plans */
125       select @plan = 10
126   
127       if
128               (select count(*)
129               from sysqueryplans
130               where
131                   id = @id1
132                   and type = 100)
133           !=
134               (select count(*)
135               from sysqueryplans
136               where
137                   id = @id2
138                   and type = 100)
139           goto ret
140   
141       if exists (select *
142               from sysqueryplans p1, sysqueryplans p2
143               where
144                   p1.id = @id1
145                   and p2.id = @id2
146                   and p1.type = 100
147                   and p2.type = 100
148                   and p1.sequence = p2.sequence
149                   and isnull(rtrim(p1.text), " ") != isnull(rtrim(p2.text), " "))
150           goto ret
151   
152       /* same plan */
153       select @plan = 0
154   
155   ret:
156   
157       if @mode = "brief"
158       begin
159           /* query related messages */
160           if @query = 0
161           begin
162               /* 18692, "The queries are the same." */
163               exec sp_getmessage 18692, @msg out
164           end
165           else if @query = 1
166           begin
167               /* 18693, "The queries are different." */
168               exec sp_getmessage 18693, @msg out
169           end
170           else if @query = 2
171           begin
172               /* 18694, "The queries are different but have the same hash key." */
173               exec sp_getmessage 18694, @msg out
174           end
175           /* else wrong @query - do nothing */
176   
177           print @msg
178   
179           /* plan related messages */
180           if @plan = 0
181           begin
182               /* 18695, "The query plans are the same." */
183               exec sp_getmessage 18695, @msg out
184           end
185           else if @plan = 10
186           begin
187               /* 18696, "The query plans are different." */
188               exec sp_getmessage 18696, @msg out
189           end
190           /* else wrong @plan - do nothing */
191   
192           print @msg
193   
194       end
195       else if @mode != "quiet"
196       begin
197           /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
198           raiserror 18640, "sp_cmp_qplans", @mode, "'quiet', 'brief'"
199       end
200   
201       /*
202       ** 0 - same query/plan
203       ** +1 - different queries
204       ** +2 - different queries and hash collision
205       ** +10 - different plans
206       ** 100 - plan ID does not exist
207       */
208       return @query + @plan
209   
210   


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}		 65
 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}		 71
 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}		 98
 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}		 104
 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]		 115
 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}		 131
 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}		 137
 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]		 148
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 113
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 147
 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 163
 MNER 3 No Error Check should check return value of exec 168
 MNER 3 No Error Check should check return value of exec 173
 MNER 3 No Error Check should check return value of exec 183
 MNER 3 No Error Check should check return value of exec 188
 QGWO 3 Group by/Distinct/Union without order by 61
 QGWO 3 Group by/Distinct/Union without order by 67
 QISO 3 Set isolation level 54
 QNAJ 3 Not using ANSI Inner Join 109
 QNAJ 3 Not using ANSI Inner Join 142
 MSUB 2 Subquery Marker 61
 MSUB 2 Subquery Marker 67
 MSUB 2 Subquery Marker 95
 MSUB 2 Subquery Marker 101
 MSUB 2 Subquery Marker 108
 MSUB 2 Subquery Marker 128
 MSUB 2 Subquery Marker 134
 MSUB 2 Subquery Marker 141
 MTR1 2 Metrics: Comments Ratio Comments: 44% 32
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 34 = 33dec - 1exi + 2 32
 MTR3 2 Metrics: Query Complexity Complexity: 99 32
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 108
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 141

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

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