DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showplan  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
4     
5     /* Messages for "sp_showplan"		18359
6     ** 
7     ** 18359, "The spid value must be a positive integer. To look up plans for cached statements, use sp_showplan -1, ."
8     ** 18360, "If the batch id, context id, or statement number is specified, \
9     **         all three must be specified."
10    ** 18361, "Batch id must be non-negative."
11    ** 18362, "Context id must be non-negative."
12    ** 18363, "Statement number must be positive."
13    ** 19611, "The statement id value cannot be NULL or negative."
14    ** 19612, "Could not find a plan for the statement id '%1!'."
15    */
16    
17    create or replace procedure sp_showplan
18    
19        @spid smallint,
20    
21        @batch_id int = NULL output,
22        @context_id int = NULL output,
23        @stmt_num int = NULL output
24    as
25    
26        declare @dbname varchar(255)
27        declare @error int
28        declare @return_value int
29        declare @dummy int
30        declare @nullarg char(1)
31        declare @gp_enabled int
32        declare @status int
33    
34    
35        if @@trancount = 0
36        begin
37            set chained off
38        end
39    
40        set transaction isolation level 1
41    
42        if (@dbname is null)
43            select @dbname = db_name()
44        /*
45        **  If granular permissions is not enabled, 
46        **  only accounts with sa_role can execute it.
47        **  If granular permissions is enabled then users with 
48        ** 'monitor qp performance' permission can execute it.
49        */
50        select @nullarg = NULL
51        execute @status = sp_aux_checkroleperm "sa_role",
52            "monitor qp performance", @nullarg, @gp_enabled output
53    
54        /* For Auditing */
55        if (@gp_enabled = 0)
56        begin
57    
58            select @dummy = proc_role("sa_role")
59        end
60        else
61        begin
62            select @dummy = proc_auditperm("monitor qp performance",
63                    @status)
64        end
65        if (@status != 0)
66        begin
67            return (1)
68        end
69    
70        /*
71        ** If @spid is -1, assume that @batch_id is the id for a 
72        ** cached statement.
73        */
74        if (@spid = - 1)
75        begin
76            if (@batch_id is NULL or @batch_id < 0)
77            begin
78                /* 
79                ** 19611, "The statement id value cannot be 
80                ** NULL or negative." 
81                */
82                raiserror 19611
83                return (1)
84            end
85            else
86            begin
87                select @return_value = show_plan(@spid, @batch_id, - 1, - 1)
88                if (@return_value < 0)
89                begin
90                    /*
91                    ** 19612, "Could not find a plan for the
92                    ** statement id '%1!'." 
93                    */
94                    raiserror 19612, @batch_id
95                    return (1)
96                end
97                else
98                    return (0)
99            end
100       end
101   
102       /* 
103       ** sp_showplan is not being used for cached statements,
104       ** the spid value must be a positive integer.
105       */
106       select @error = 0
107       if (@spid is NULL)
108           select @error = 1
109       else if (@spid < 1)
110           select @error = 1
111       if (@error = 1)
112       begin
113           /* 
114           ** 18359, "The spid value must be a positive integer. 
115           ** To look up plans for cached statements, use 
116           ** sp_showplan -1, .
117           */
118           raiserror 18359
119           return (1)
120       end
121   
122   
123       /* 
124       ** Batch id, context id, and statement number must be NULL or 
125       ** none may be NULL.
126       */
127       select @error = 0
128       if (@batch_id * @context_id * @stmt_num is NULL)
129       begin
130           if (@batch_id is not NULL)
131               select @error = 1
132           else if (@context_id is not NULL)
133               select @error = 1
134           else if (@stmt_num is not NULL)
135               select @error = 1
136       end
137       if (@error = 1)
138       begin
139           /* 18360, "If the batch id, context id, or statement number is specified, all three must be specified." */
140           raiserror 18360
141           return (1)
142       end
143   
144   
145       /* 
146       ** Batch id, context id, and statement number are all NULL or none are
147       ** NULL.  If all are NULL, call the builtin function 3 times:
148       ** 	1st to return the batch id, 
149       **	2nd to return the context id, and
150       **	3rd to display the query plan and return the current statement number.
151       */
152       if (@batch_id is NULL)
153       begin
154           /* Pass -1 for unknown values. */
155           select @return_value = show_plan(@spid, - 1, - 1, - 1)
156           if (@return_value < 0)
157               return (1)
158           else
159               select @batch_id = @return_value
160   
161           select @return_value = show_plan(@spid, @batch_id, - 1, - 1)
162           if (@return_value < 0)
163               return (1)
164           else
165               select @context_id = @return_value
166   
167           select @return_value = show_plan(@spid, @batch_id, @context_id, - 1)
168           if (@return_value < 0)
169               return (1)
170           else
171           begin
172               select @stmt_num = @return_value
173               return (0)
174           end
175       end
176   
177   
178       /* 
179       ** Non-NULL parameter values must be non-negative.
180       */
181       if (@batch_id < 0)
182       begin
183           /* 18361, "Batch id must be non-negative." */
184           raiserror 18361
185           return (1)
186       end
187       if (@context_id < 0)
188       begin
189           /* 18362, "Context id must be non-negative." */
190           raiserror 18362
191           return (1)
192       end
193       if (@stmt_num < 1)
194       begin
195           /* 18363, "Statement number must be positive." */
196           raiserror 18363
197           return (1)
198       end
199   
200   
201       /* 
202       ** Display the query plan for the specified statement number.
203       */
204       select @return_value = show_plan(@spid, @batch_id, @context_id, @stmt_num)
205       if (@return_value < 0)
206           return (1)
207       else
208           return (0)
209   
210   


exec sp_procxmode 'sp_showplan', 'AnyMode'
go

Grant Execute on sp_showplan to public
go
DEFECTS
 VRUN 4 Variable is read and not initialized @dbname 42
 MGTP 3 Grant to public sybsystemprocs..sp_showplan  
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 208
 QISO 3 Set isolation level 40
 VNRD 3 Variable is not read @dbname 43
 VNRD 3 Variable is not read @dummy 62
 MTR1 2 Metrics: Comments Ratio Comments: 43% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 25dec - 14exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 99 17

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_monitor_procstack