DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_enable  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_monitor_enable(@montype varchar(30) = NULL
3     ) as
4         begin
5     
6             /* Values of the configuration parameters */
7             declare @stmt_pipe_maxmsg int
8     
9             /* Names of the configuration parameters */
10            declare @stmt_pipe_maxmsg_cfg char(100)
11    
12            /* Configuration option numbers for the configuration parameters */
13            declare @stmt_pipe_maxmsg_cfg_num int
14    
15            /* Misc declarations */
16            declare @rtnstatus int
17            declare @val int
18                , @turn_all_on tinyint
19    
20            -- Generated fragments of SQL that will be executed to turn ON options.
21            declare @inlist_str varchar(400)
22                , @inlist_initial varchar(30)
23                , @sqlstmt varchar(1024)
24                , @notexists_sql varchar(128)
25                , @insert_stmt varchar(128)
26                , @rest_of_select varchar(80)
27                , @value_zero varchar(20)
28                , @updstmt varchar(400)
29                , @lcl_montype varchar(30)
30                , @found_montype tinyint -- validate monitoring type arg.
31                , @char_index int
32                , @retval int
33                , @nrowsaffected int
34    
35                -- Names for individual configuration options.
36                , @enable_monitoring varchar(30)
37                , @sql_batch_capture varchar(30)
38                , @stmt_stats_active varchar(30)
39                , @stmt_pipe_active varchar(30)
40                , @perobj_stats_active varchar(30)
41                , @wait_event_timing varchar(30)
42                , @process_wait_events varchar(30)
43                , @deadlock_pipe_active varchar(30)
44                , @whoami varchar(30)
45    
46            select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
47    
48            exec @rtnstatus = sp_monitor_verify_setup
49            if (@rtnstatus != 0)
50                return (@rtnstatus)
51    
52            -- Initialize names for individual config options that we might use.
53            select @enable_monitoring = "'enable monitoring'"
54                , @sql_batch_capture = "'SQL batch capture'"
55                , @stmt_stats_active = "'statement statistics active'"
56                , @stmt_pipe_active = "'statement pipe active'"
57                , @perobj_stats_active = "'per object statistics active'"
58                , @wait_event_timing = "'wait event timing'"
59                , @process_wait_events = "'process wait events'"
60                , @deadlock_pipe_active = "'deadlock pipe active'"
61    
62                , @turn_all_on = 0
63                , @found_montype = 0
64    
65            -- User might have entered: sp_monitor enable, 'procstack monitoring'
66            -- If so, strip out the term 'monitoring', and get the base monitoring type
67            -- which would be one of terms like 'all', 'connection', 'procstack' etc.
68            --
69            if (@montype LIKE "% monitoring")
70            begin
71                -- Locate start of ' monitoring' phrase.
72                select @char_index = charindex(' ', @montype)
73    
74                -- We could raise an error in the else case, but it's of no use.
75                -- If the monitoring type is wrong, you get an error later on.
76                if (@char_index > 0)
77                begin
78                    select @montype = ltrim(rtrim(substring(@montype, 1,
79                                    (@char_index - 1))))
80                end
81            end
82    
83            -- Re-enabling 'all' means user is starting a new monitoring window. Drop and
84            -- re-create the control table, to re-capture the state of the config options.
85            --
86            if (@montype IS NULL) or (@montype = "all")
87            begin
88                if exists (select 1 from tempdb.dbo.sysobjects
89                        where name = "mon_config" and uid = 1)
90                begin
91                    print "Drop and recreate control table '%1!'"
92                        , "tempdb.dbo.mon_config"
93                    drop table tempdb.dbo.mon_config
94                end
95                select @turn_all_on = 1
96                    , @found_montype = 1
97            end
98    
99            -- Create the table if it does not exist (dropped above), or if the user
100           -- is runnning 'enable' the 1st time on a monitor type (in which case the
101           -- table would not have been created earlier).
102           --
103           if (object_id('tempdb.dbo.mon_config') IS NULL)
104           begin
105               exec @retval = sp_monitor_crt_mon_config
106               if (@retval != 0)
107                   return @retval
108           end
109   
110           select @insert_stmt = " INSERT INTO tempdb.dbo.mon_config"
111               + "(monitor, confignum, configval, configname, enabled)"
112               , @value_zero = " AND value = 0"
113   
114               -- Insert a value of -1 for 'configval' as it will be updated later.
115               -- See below.
116               --
117               , @rest_of_select = ", config, -1, name, getdate()"
118               + " FROM master.dbo.sysconfigures WHERE name IN "
119   
120               -- NOT EXISTS clause sql substring, to which we will append later on the
121               -- IN-list that we are looking for before we insert any new rows.
122               --
123               , @notexists_sql = "IF NOT EXISTS (SELECT 1 FROM tempdb.dbo.mon_config"
124               + " WHERE configname IN "
125   
126           /*
127           ** This is an UPDATE statement to register in the control table the value
128           ** of the configuration option *BEFORE* the sp_monitor enable was ever
129           ** started. We are trying to note down what the original config setting was
130           ** prior to a config option being turned ON for some monitoring type. 
131           **
132           ** o If this is the 1st row being inserted for a configuration option, save
133           **   the current run value from syscurconfigs for this config option.
134           **
135           ** o If this is a row being inserted for a config option, and another row
136           **   already exists, then save off the min(configval) value, which will give
137           **   us the value of the config option *before* the first row was inserted.
138           **
139           ** o ONLY do the update if the row is (are) newly inserted; i.e. configval =
140           **    -1. This way we avoid doing needless updates of the same rows if the
141           **   'enable' was run on the same monitoring type repeatedly.
142           **
143           ** We do the UPDATE via execute immediate as it is the same exact SQL
144           ** fragment that we want to run for each monitoring type, *except* that the
145           ** WHERE clause will change for each monitoring type. Hence, build a common
146           ** SQL frament and then attach the WHERE clause.
147           */
148           select @updstmt =
149               "UPDATE tempdb.dbo.mon_config "
150               + " SET configval = (SELECT ISNULL(MIN(configval)"
151               + ", (SELECT cu.value"
152               + "   FROM master.dbo.syscurconfigs cu"
153   
154               + "   WHERE cu.config = o.confignum) )"
155   
156               + " FROM tempdb.dbo.mon_config i"
157               + " WHERE i.configval != -1"
158               + "  AND i.confignum = o.confignum)"
159               + " FROM tempdb.dbo.mon_config o"
160               + " WHERE o.configval = -1"
161   
162           select @inlist_initial = "(" + @enable_monitoring
163   
164           /*
165           ** Here we continually build an IN-list string, for various configuration
166           ** options that would be needed if a particular type of monitoring is being
167           ** enabled. In the 'all' case, we might end up generating duplicate config
168           ** options in the IN-list, but that's not a problem as the WHERE clause
169           ** will only essentially consider one instance.
170           */
171           if (@montype = 'connection') or (@turn_all_on = 1)
172           begin
173               select @inlist_str = @inlist_initial
174                   + "," + @sql_batch_capture
175                   + "," + @wait_event_timing
176                   + "," + @perobj_stats_active
177                   + ")"
178                   , @lcl_montype = "connection"
179                   , @found_montype = 1
180   
181               -- Do not include the @value_zero clause here as we want to INSERT
182               -- all the config options that are required for this monitoring type
183               -- into the control table. If we insert only the ones that are
184               -- currently zero, it might be that some of the config options that
185               -- are shared between different monitoring types are already ON, and
186               -- so don't get tracked under this monitoring type in the control
187               -- table. This means if this monitoring type is subsequently disabled,
188               -- we don't have all the data needed to turn OFF all the required
189               -- config options; some will be left ON (which is inconsistent with
190               -- the defined semantic of disable turning OFF all related items).
191               --
192               select @sqlstmt = @notexists_sql
193                   + @inlist_str
194                   + " AND monitor = '" + @lcl_montype + "'"
195                   + ")"
196                   + @insert_stmt
197                   + " SELECT " + "'" + @lcl_montype + "'"
198                   + @rest_of_select
199                   + @inlist_str
200               -- + @value_zero
201   
202               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
203               if (@rtnstatus != 0)
204                   return @rtnstatus
205   
206               select @sqlstmt = @updstmt
207                   + " AND monitor = '" + @lcl_montype + "'"
208               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
209                   , @nrowsaffected output
210               if (@rtnstatus != 0)
211                   return @rtnstatus
212           end
213   
214           if (@montype = 'statement') or (@turn_all_on = 1)
215           begin
216               select @inlist_str = @inlist_initial
217                   + "," + @sql_batch_capture
218                   + "," + @stmt_stats_active
219                   + "," + @perobj_stats_active
220                   + "," + @wait_event_timing
221                   + ")"
222                   , @lcl_montype = "statement"
223                   , @found_montype = 1
224   
225               select @sqlstmt = @notexists_sql
226                   + @inlist_str
227                   + " AND monitor = '" + @lcl_montype + "'"
228                   + ")"
229                   + @insert_stmt
230                   + " SELECT " + "'" + @lcl_montype + "'"
231                   + @rest_of_select
232                   + @inlist_str
233   
234               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
235               if (@rtnstatus != 0)
236                   return @rtnstatus
237   
238               select @sqlstmt = @updstmt
239                   + " AND monitor = '" + @lcl_montype + "'"
240               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
241               if (@rtnstatus != 0)
242                   return @rtnstatus
243           end
244   
245           if (@montype = 'event') or (@turn_all_on = 1)
246           begin
247               select @inlist_str = @inlist_initial
248                   + "," + @wait_event_timing
249                   + "," + @process_wait_events
250                   + ")"
251                   , @lcl_montype = "event"
252                   , @found_montype = 1
253   
254               select @sqlstmt = @notexists_sql
255                   + @inlist_str
256                   + " AND monitor = '" + @lcl_montype + "'"
257                   + ")"
258                   + @insert_stmt
259                   + " SELECT " + "'" + @lcl_montype + "'"
260                   + @rest_of_select
261                   + @inlist_str
262   
263               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
264               if (@rtnstatus != 0)
265                   return @rtnstatus
266   
267               select @sqlstmt = @updstmt
268                   + " AND monitor = '" + @lcl_montype + "'"
269               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
270               if (@rtnstatus != 0)
271                   return @rtnstatus
272           end
273   
274           if (@montype = 'procedure') or (@turn_all_on = 1)
275           begin
276               select @inlist_str = @inlist_initial
277                   + "," + @stmt_stats_active
278                   + "," + @perobj_stats_active
279                   + "," + @stmt_pipe_active
280                   + ")"
281                   , @lcl_montype = "procedure"
282                   , @found_montype = 1
283   
284               select @sqlstmt = @notexists_sql
285                   + @inlist_str
286                   + " AND monitor = '" + @lcl_montype + "'"
287                   + ")"
288                   + @insert_stmt
289                   + " SELECT " + "'" + @lcl_montype + "'"
290                   + @rest_of_select
291                   + @inlist_str
292   
293               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
294               if (@rtnstatus != 0)
295                   return @rtnstatus
296   
297               select @sqlstmt = @updstmt
298                   + " AND monitor = '" + @lcl_montype + "'"
299               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
300               if (@rtnstatus != 0)
301                   return @rtnstatus
302           end
303   
304           if (@montype = 'deadlock') or (@turn_all_on = 1)
305           begin
306               select @inlist_str = @inlist_initial
307                   + "," + @deadlock_pipe_active
308                   + ")"
309                   , @lcl_montype = "deadlock"
310                   , @found_montype = 1
311   
312               select @sqlstmt = @notexists_sql
313                   + @inlist_str
314                   + " AND monitor = '" + @lcl_montype + "'"
315                   + ")"
316                   + @insert_stmt
317                   + " SELECT " + "'" + @lcl_montype + "'"
318                   + @rest_of_select
319                   + @inlist_str
320   
321               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
322               if (@rtnstatus != 0)
323                   return @rtnstatus
324   
325               select @sqlstmt = @updstmt
326                   + " AND monitor = '" + @lcl_montype + "'"
327               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
328               if (@rtnstatus != 0)
329                   return @rtnstatus
330           end
331   
332           if (@montype = 'procstack') or (@turn_all_on = 1)
333           begin
334               -- Procedure stack monitoring does not need any additional
335               -- config options to be turned ON. It selects from a table that is
336               -- always around.
337   
338               select @inlist_str = @inlist_initial
339                   + ")"
340                   , @lcl_montype = "procstack"
341                   , @found_montype = 1
342   
343               select @sqlstmt = @notexists_sql
344                   + @inlist_str
345                   + " AND monitor = '" + @lcl_montype + "'"
346                   + ")"
347                   + @insert_stmt
348                   + " SELECT " + "'" + @lcl_montype + "'"
349                   + @rest_of_select
350                   + @inlist_str
351   
352               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
353               if (@rtnstatus != 0)
354                   return @rtnstatus
355   
356               select @sqlstmt = @updstmt
357                   + " AND monitor = '" + @lcl_montype + "'"
358               exec @rtnstatus = sp_exec_SQL @sqlstmt, @lcl_montype
359               if (@rtnstatus != 0)
360                   return @rtnstatus
361           end
362   
363           -- This means user asked to enable a monitoring type that is invalid.
364           -- Raise an error and bail.
365           if (@found_montype = 0)
366           begin
367               raiserror 19260, "sp_monitor 'help', 'enable'"
368               return 1
369           end
370   
371           --
372           -- Enable the configuration parameters that have not already been turned
373           -- ON, from the ones that are relevant to the monitoring type being enabled.
374           -- If the user is running in 'all' mode, then turn ON all the config options
375           -- that are currently 0. If user is running enable for a particular monitor
376           -- type, turn ON only those config options that are needed for that type of
377           -- monitoring. In the latter case, the CASE statement below is the one that
378           -- selects based on an IN-list only those config options that need to be turned
379           -- ON.
380   
381           select @sqlstmt = "declare @val int "
382               + "SELECT @val = config_admin(23, confignum, 1, 0, NULL, NULL)"
383               + " FROM tempdb.dbo.mon_config"
384               + " WHERE 1 = 1"
385               + case @turn_all_on
386                   when 1 then ""
387                   else " AND configname IN " + @inlist_str
388               end
389               + " AND configval = 0"
390               + " AND confignum > 0" -- eliminate non-config item rows.
391   
392           exec @rtnstatus = sp_exec_SQL @sqlstmt, "sp_monitor_enable-config_admin"
393   
394           -- If the config_admin() built-in were to fail due to some memory configuration
395           -- issue, rollback the entire 'enable' command's work, by calling the disable
396           -- feature on the monitoring type being enabled.
397           --
398           if (@rtnstatus != 0)
399           begin
400               -- Reuse variable to define name of sp_monitor sproc.
401               select @enable_monitoring = "sybsystemprocs.dbo.sp_monitor"
402               exec @enable_monitoring 'disable', @montype
403               return @rtnstatus
404           end
405   
406           /* 
407           ** Certain configuration parameters such as 'statement pipe max messages'
408           ** need to be tuned to some optimal values. Get the config value for 
409           ** these options and tune them to some predefined values. Also insert them
410           ** into the mon_config table so that as part of sp_monitor "disable", these
411           ** values can be reset back to their original values.
412           */
413           select @stmt_pipe_maxmsg_cfg = "statement pipe max messages"
414   
415           exec sp_monitor_getcfgval @stmt_pipe_maxmsg_cfg, @stmt_pipe_maxmsg output
416   
417           exec sp_monitor_getcfgnum @stmt_pipe_maxmsg_cfg, @stmt_pipe_maxmsg_cfg_num output
418   
419   
420           -- Tune the 'statement pipe max messages' option if it is not already on.
421           -- It's only needed for procedure monitoring, so turn it ON to some
422           -- pre-selected default value only in case we are enabling proceduring 
423           -- monitoring, or 'all' monitoring.
424           --
425           if ((@stmt_pipe_maxmsg = 0)
426                   and ((@montype = 'procedure') or (@turn_all_on = 1)))
427           begin
428               select @val = config_admin(23, @stmt_pipe_maxmsg_cfg_num, 100000, 0, NULL, NULL)
429               insert into tempdb.dbo.mon_config(
430                   monitor, confignum, configval, configname, enabled)
431               values ('procedure',
432                   @stmt_pipe_maxmsg_cfg_num, 100000, @stmt_pipe_maxmsg_cfg,
433                   getdate())
434           end
435   
436           select @val = mdaconfig('disable_lwp', 'begin')
437   
438           return (0)
439       end -- }
440   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 429
 MEST 4 Empty String will be replaced by Single Space 386
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 88
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 429
 MGTP 3 Grant to public tempdb..sysobjects  
 MLCH 3 Char type with length>30 char(100) 10
 MNER 3 No Error Check should check return value of exec 415
 MNER 3 No Error Check should check return value of exec 417
 MNER 3 No Error Check should check @@error after insert 429
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 4
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 425
 MUCO 3 Useless Code Useless Brackets 438
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..'enable monitoring' 0
 MZMB 3 Zombie: use of non-existent object sybsystemprocs.dbo.sp_monitor 0
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 429
 VNRD 3 Variable is not read @whoami 46
 VNRD 3 Variable is not read @value_zero 112
 VNRD 3 Variable is not read @nrowsaffected 209
 VNRD 3 Variable is not read @val 436
 MDYE 2 Dynamic Exec Marker exec @enable_monitoring 402
 MSUB 2 Subquery Marker 88
 MTR1 2 Metrics: Comments Ratio Comments: 36% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 39dec - 16exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 146 2

DATA PROPAGATION detailed
ColumnWritten To
@montypesp_exec_SQL_rset_001.sqlbNoName57

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_monitor_getcfgnum  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
calls proc sybsystemprocs..sp_monitor_getcfgval  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_monitor_verify_setup  
   reads table master..sysobjects (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
calls proc sybsystemprocs..sp_monitor_crt_mon_config  
   reads table tempdb..sysobjects (1)  
   reads table master..sysconfigures (1)