DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpconfig_process_estimate  14 déc. 14Defects Propagation Dependencies

1     create or replace procedure sp_helpconfig_process_estimate(
2         @estimate_clause varchar(256)
3         , @userconns_cfgval int
4         , @workerprocs_cfgval int
5         , @maxpardegree_cfgval int
6         , @nopenobjs_cfgval int
7         , @nopenindexes_cfgval int
8         , @nopenptns_cfgval int
9     
10        , @using_found tinyint output
11        , @maxconcusers int output
12        , @numtables float output
13        , @numcolumns int output
14        , @numcompobjs int output
15        , @numidxkeys int output
16        , @numcompidxs int output
17        , @debuglvl tinyint output
18    ) as
19        begin
20            declare @maxconcusers_dflt int -- concurrently requesting memory
21                , @numtables_dflt int
22                , @numcolumns_dflt int
23                , @numcolumns_dflt_srv int -- server-wide, ave # cols / table
24                , @numcolumns_max int
25                , @numcompobjs_dflt int
26                , @numidxkeys_dflt int
27                , @numcompidxs_dflt int
28                , @numidxkeys_max int
29                , @retval int
30                , @retval_sub int
31                , @usingIndex int
32                , @sprocname varchar(80)
33                , @using_clause varchar(256)
34                , @left_substr varchar(256)
35                , @right_substr varchar(256)
36                , @using_kwd varchar(30)
37                , @using_val varchar(30)
38                , @dbname varchar(30)
39                , @float_val float
40    
41            -- Set hard-coded defaults
42            set @numcolumns_dflt_srv = 50 -- per compressed table accessed
43            set @numidxkeys_dflt = 16 -- assume create index on half of
44            -- the max # of key columns(32)
45            set @numcolumns_max = 1024 -- the limit of numcolumns in ASE is
46            -- 1024(MAXCOLS)
47            set @numidxkeys_max = 32 -- the limit of numidxkeys in ASE is
48            -- 32(MAXKEY)
49    
50            set @sprocname = "sp_helpconfig_est_maxconcusers"
51            exec @maxconcusers_dflt = @sprocname @userconns_cfgval
52                , @workerprocs_cfgval
53                , @maxpardegree_cfgval
54    
55            set @numtables_dflt = 2 -- referenced per statement
56                , @numcolumns_dflt = @numcolumns_dflt_srv
57                , @numcompobjs_dflt = @nopenobjs_cfgval
58                -- Assume all configured 'open objects'
59                -- will be compressed, till we find
60                -- out otherwise below.
61                , @numcompidxs_dflt = @nopenindexes_cfgval
62                -- Assume all configured 'open indexes'
63                -- will be compressed.
64                , @retval = 1
65                , @using_found = 0
66    
67            -- Initially, set up output args to use defaults. Any sub-clauses
68            -- provided will override these defaults
69            --
70            set @maxconcusers = @maxconcusers_dflt
71                , @numtables = @numtables_dflt
72                , @debuglvl = 0
73    
74            -- As a convenience, if the user is running this sproc from the
75            -- db where the compressed objects live, use the avg # of 
76            -- columns found in compressed objects in that db as the
77            -- initial default value. Skip this work if the user's current
78            -- db is a system db, in which case go with the hard-coded
79            -- defaults for # of compressed objects and # of columns in a
80            -- compressed table.
81            --
82            set @dbname = db_name()
83            if (@dbname NOT IN ('master', 'model', 'tempdb', 'sybsystemprocs'
84                        , 'sybsystemdb'))
85            begin
86                -- Count the # of compressed tables in this db.
87                -- 0x1000 - Row compressed table
88                -- 0x2000 - Page compressed table
89                -- 0x4000 - Table contains compressed data
90                --
91                select @numcompobjs_dflt = count(*)
92                from sysobjects o
93                where o.id >= 256 -- start of user object ID range
94                    and o.type = 'U'
95                    and (o.sysstat3 & hextoint("0x7000") != 0)
96    
97                -- Count the ave. # of columns in compressed tables in this db.
98                select @numcolumns_dflt = isnull(avg(count(colid)), 0)
99                from syscolumns c, sysobjects o
100               where o.id = c.id
101                   and o.type = 'U'
102                   and (o.sysstat3 & hextoint("0x7000") != 0)
103               group by o.id
104   
105               -- Count the # of compressed indexes in this db.
106               -- 0x200 - Index is defined as compressed
107               -- 0x400 - Index contains compressed data
108               select @numcompidxs_dflt = count(*)
109               from sysindexes i
110               where i.id >= 256 -- start of user object ID range
111                   and i.indid > 1 -- only non-clustered index
112                   and i.indid < 255 -- could be compressed
113                   and (i.status3 & hextoint("0x600") != 0)
114   
115               -- Count the ave. # of columns in compressed indexes in this db
116               -- if we have compressed indexes.
117               if (@numcompidxs_dflt > 0)
118               begin
119                   select @numidxkeys_dflt =
120                       ((sum(keycnt - 1) - 1) / @numcompidxs_dflt + 1)
121                   from sysindexes i
122                   where i.id >= 256
123                       and i.indid > 1
124                       and i.indid < 255
125                       and (i.status3 & hextoint("0x600") != 0)
126               end
127           end
128   
129           -- If the currrent db has no compressed objects, inform the user that
130           -- we will be reverting back to server-wide defaults for the estimation.
131           --
132           if ((@numcompobjs_dflt = 0) or (@numcolumns_dflt = 0))
133           begin
134               print "No compressed objects found in database '%1!'. Use server-wide default value of %2! compressed objects, and %3! columns per compressed table, to estimate '%4!' memory"
135                   , @dbname
136                   , @nopenobjs_cfgval
137                   , @numcolumns_dflt_srv
138                   , "compression info pool size"
139   
140               set @numcompobjs_dflt = @nopenobjs_cfgval
141                   , @numcolumns_dflt = @numcolumns_dflt_srv
142           end
143   
144           -- If the currrent db has no compressed indexes, inform the user that
145           -- we will be reverting back to server-wide defaults for the estimation.
146           --
147           if (@numcompidxs_dflt = 0)
148           begin
149               print "No compressed indexes found in database '%1!'. Use server-wide default value of %2! compressed indexes, and %3! columns per compressed index, to estimate '%4!' memory"
150                   , @dbname
151                   , @nopenindexes_cfgval
152                   , @numidxkeys_dflt
153                   , "compression info pool size"
154   
155               set @numcompidxs_dflt = @nopenindexes_cfgval
156   
157           end
158   
159           -- Each object opened needs at least 1 index and 1 partition descriptor.
160           -- The # of objects that can be opened is, therefore, controlled by the
161           -- config options for open indexes and open partitions.
162           --
163           if (@nopenobjs_cfgval < @numcompobjs_dflt)
164               set @numcompobjs_dflt = @nopenobjs_cfgval
165   
166           if (@nopenindexes_cfgval < @numcompobjs_dflt)
167               set @numcompobjs_dflt = @nopenindexes_cfgval
168   
169           if (@nopenptns_cfgval < @numcompobjs_dflt)
170               set @numcompobjs_dflt = @nopenptns_cfgval
171   
172           if (@nopenindexes_cfgval < @numcompidxs_dflt)
173               set @numcompidxs_dflt = @nopenindexes_cfgval
174   
175           -- Reset the # of compressed objects, ave # of columns
176           -- # of compressed indexes and ave # of index key columns
177           -- in compressed objects to what we found out from the
178           -- catalogs for the db in-question.
179           --
180           set @numcolumns = @numcolumns_dflt
181               , @numcompobjs = @numcompobjs_dflt
182               , @numcompidxs = @numcompidxs_dflt
183               , @numidxkeys = @numidxkeys_dflt
184   
185           -- See if there is a USING clause. If not, return the defaults.
186           --
187           set @usingIndex = charindex("USING", upper(@estimate_clause))
188           if (@usingIndex = 0)
189               goto exit_proc
190   
191           exec sp_split_string @estimate_clause, "USING", 0
192               , @left_substr out
193               , @right_substr out
194   
195           -- Degenerate case of 'estimate USING', where the sub-clauses were
196           -- not provided by the user. This is really a user error but let it
197           -- go through and use defaults.
198           --
199           if (@right_substr IS NULL)
200               goto exit_proc
201   
202           select @using_clause = ltrim(rtrim(@right_substr))
203   
204           /*
205           print "estimate_clause: '%1!' left_substr: '%2!' right_substr: '%3!' using_clause: '%4!'"
206           , @estimate_clause, @left_substr, @right_substr
207           , @using_clause
208           */
209   
210           -- Else, USING clause has been found.
211           set @using_found = 1
212   
213           -- Repeatedly split the string into sub-clauses separated by a
214           -- comma. Process each sub-clause. Exit when no more commas are found.
215           --
216           while (1 = 1)
217           begin -- {
218               -- Keywords are case insensitive, so are the values.
219               exec @retval = sp_split_string @using_clause, ",", 0
220                   , @left_substr out
221                   , @right_substr out
222   
223               -- If no comma is found, this is the only, or last, sub-clause.
224               -- Process that looking for arg= syntax, and exit this
225               -- loop as we are now done.
226               --
227               if (@retval = 1)
228                   set @left_substr = @using_clause
229   
230               -- Extract the keyword and the value
231               --
232               exec @retval_sub = sp_split_string @left_substr, '=', 0
233                   , @using_kwd out
234                   , @using_val out
235   
236               /*
237               print "using_kwd: '%1!' using_val: '%2!' left_substr: '%3!'"
238               , @using_kwd, @using_val, @left_substr
239               */
240   
241               -- Check for proper form. Expect:  = 
242               if ((@using_kwd IS NULL) or (@using_val IS NULL))
243               begin
244                   raiserror 17993, @left_substr
245                   return 1
246               end
247   
248               set @using_kwd = lower(ltrim(rtrim(@using_kwd)))
249                   , @using_val = lower(ltrim(rtrim(@using_val)))
250   
251               set @float_val = convert(float, @using_val)
252   
253               /*
254               print "using_val: %1! float_val: %2!"
255               , @using_val, @float_val
256               */
257   
258               -- Flag illegal input
259               --
260               if ((@float_val <= 0)
261                       or ((@using_kwd in ('numtables', 'numcolumns', 'numindexkeys'))
262                           and (@float_val < 1))
263                   )
264               begin
265                   raiserror 19123, @using_kwd, @float_val
266                   return 3
267               end
268   
269               if (@using_kwd = 'maxconcusers')
270               begin
271                   set @maxconcusers = ceiling(@float_val)
272   
273                   -- Syntax permitted is:
274                   --  maxconcusers = 
275                   --  maxconcusers = <%age>
276                   --
277                   -- In the 2nd case, %age is of the total # of
278                   -- configured user connections, as an easy way 
279                   -- for the user to say how many concurrent logins
280                   -- will be active at one time.
281   
282                   set @maxconcusers
283                       = case
284                           when (@float_val < 1)
285                           then (@float_val * @userconns_cfgval)
286                           else ceiling(@float_val)
287                       end
288   
289                   -- Convert user-specified # of concurrent users
290                   -- to max # of concurrent requesters for memory,
291                   -- taking into account parallel configurations, too.
292                   --
293                   exec @maxconcusers = @sprocname @maxconcusers
294                       , @workerprocs_cfgval
295                       , @maxpardegree_cfgval
296   
297               end
298               else if (@using_kwd = 'numtables')
299               begin
300                   -- Allow float so user can choose an ave # of tables
301                   -- per statement, some thing like, 2.75. So, don't
302                   -- use ceiling() here.
303                   --
304                   set @numtables = @float_val
305               end
306               else if (@using_kwd = 'numcolumns')
307               begin
308                   set @numcolumns = ceiling(@float_val)
309               end
310               else if (@using_kwd = 'numcompobjs')
311               begin
312                   -- Syntax permitted is:
313                   --  numcompobjs = 
314                   --  numcompobjs = <%age>
315                   --
316                   -- In the 2nd case, %age is of the total # of
317                   -- configured open objects, as an easy way of saying
318                   -- how many compressed objects will be found in the
319                   -- work load
320   
321                   set @numcompobjs
322                       = case
323                           when (@float_val < 1)
324                           then (@float_val * @nopenobjs_cfgval)
325                           else ceiling(@float_val)
326                       end
327               end
328               else if (@using_kwd = 'numindexkeys')
329               begin
330                   set @numidxkeys = ceiling(@float_val)
331               end
332               else if (@using_kwd = 'numcompindexes')
333               begin
334                   -- Syntax permitted is:
335                   --  numcompindexes = 
336                   --  numcompindexes = <%age>
337                   --
338                   -- In the 2nd case, %age is of the total # of
339                   -- configured open indexes, as an easy way of saying
340                   -- how many compressed indexes will be found in the
341                   -- work load
342   
343                   set @numcompidxs
344                       = case
345                           when (@float_val < 1)
346                           then (@float_val * @nopenindexes_cfgval)
347                           else ceiling(@float_val)
348                       end
349               end
350               else if (@using_kwd = 'debug')
351               begin
352                   set @debuglvl = @float_val
353               end
354               else
355               begin
356                   raiserror 17993, @left_substr
357                   return 2
358               end
359   
360               -- Exit if we found the last arg=value pair.
361               if (@retval = 1)
362                   break
363               else
364                   -- Else, process the remaining sub-clauses
365                   set @using_clause = @right_substr
366   
367           end -- }
368   
369           -- Finally we will adjust numcolumns and numidxkeys, because they are
370           -- limited by our ASE server.
371           if (@numcolumns > @numcolumns_max)
372           begin
373               if (@debuglvl != 0)
374               begin
375                   print "sp_helpconfig_process_estimate: using @numcolumns = @numcolumns_max(%1!), because @numcolumns is larger than @numcolumns_max."
376                       , @numcolumns_max
377               end
378               set @numcolumns = @numcolumns_max
379           end
380   
381           if (@numidxkeys > @numidxkeys_max)
382           begin
383               if (@debuglvl != 0)
384               begin
385                   print "sp_helpconfig_process_estimate: using @numidxkeys = @numidxkeys_max(%1!), because @numidxkeys is larger than @numidxkeys_max."
386                       , @numidxkeys_max
387               end
388               set @numidxkeys = @numidxkeys_max
389           end
390   
391   exit_proc:
392   
393           if (@debuglvl != 0)
394           begin
395               print "sp_helpconfig_process_estimate: using_found = %1! maxconcusers = %2! numtables = %3! numcolumns = %4! numcompobjs = %5! numindexkeys = %6! numcompindexes = %7! in database '%8!'"
396                   , @using_found, @maxconcusers
397                   , @numtables, @numcolumns, @numcompobjs
398                   , @numidxkeys, @numcompidxs
399                   , @dbname
400           end
401   
402           return 0
403   
404       end -- }
405   


exec sp_procxmode 'sp_helpconfig_process_estimate', 'AnyMode'
go

Grant Execute on sp_helpconfig_process_estimate to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @debuglvl: tinyint = float 352
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 111
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 112
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 93
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 100
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 100
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 110
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 122
 MGTP 3 Grant to public sybsystemprocs..sp_helpconfig_process_estimate  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 191
 MNER 3 No Error Check should check return value of exec 232
 MUCO 3 Useless Code Useless Brackets in create proc 1
 MUCO 3 Useless Code Useless Begin-End Pair 19
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 361
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 381
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 393
 QNAJ 3 Not using ANSI Inner Join 99
 QNUA 3 Should use Alias: Column colid should use alias c 98
 MDYE 2 Dynamic Exec Marker exec @maxconcusers_dflt 51
 MDYE 2 Dynamic Exec Marker exec @maxconcusers 293
 MTR1 2 Metrics: Comments Ratio Comments: 36% 1
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 42dec - 4exi + 2 1
 MTR3 2 Metrics: Query Complexity Complexity: 139 1
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects} 0 98

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_helpconfig_est_maxconcusers  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_split_string  

CALLERS
called by proc sybsystemprocs..sp_helpconfig_est_compinfo_poolsize