DatabaseProcApplicationCreatedLinks
sybsystemprocssp_set_qplan  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_set_qplan"
6     **
7     ** 18636, "There is no query plan with the ID %1! in this database."
8     ** 18637, "Failed to drop the old query plan with ID %1!, rolling back and aborting."
9     ** 18638, "Failed to set the new query plan with ID %1!, rolling back and aborting."
10    */
11    
12    /*
13    ** sp_set_qplan sets a new AP for a given ID, i.e. existing 
14    **  pair, where the association key is 
15    ** the query SQL text and is unchanged. Note that it's important 
16    ** not to touch the SQL text, as it's hashed to obtain the 
17    ** HASHKEY column in SYSQUERYPLANS. Hence, the association part 
18    ** of a plan can only be installed through the SQL command 
19    ** CREATE PLAN, that also handles hashing. Failing to observe 
20    ** these rules would result in plans that are not found at lookup.
21    */
22    
23    create or replace procedure sp_set_qplan
24        @id int,
25        @plan varchar(255)
26    as
27        declare @uid int
28        declare @dummy int
29            , @nullarg char(1)
30            , @gp_enabled int
31            , @status int
32            , @dbname varchar(255)
33            , @status1 int
34    
35        select @status = 1
36        select @status1 = 1
37    
38        set nocount on
39    
40        /* Don't start a transaction for the user, he does not expect it. */
41        if @@trancount = 0
42        begin
43            set chained off
44        end
45    
46        /* Don't hold long READ locks, the user might not appreciate it. */
47        set transaction isolation level 1
48    
49        /*
50        ** If granular permissions is not enabled only the Database 
51        ** Owner (DBO) or accounts with SA role can execute it.
52        ** If the user has SA role, it's uid will be DBO uid (1). If granular 
53        ** permissions is enabled then users with 'manage abstract plans' 
54        ** permission can execute it.
55        */
56    
57        select @dbname = db_name()
58    
59        select @uid = NULL
60        select @nullarg = NULL
61    
62        execute @status = sp_aux_checkroleperm "dbo",
63            "manage abstract plans", @dbname, @gp_enabled output
64    
65        if (@status != 0)
66        begin
67            select @uid = user_id()
68        end
69    
70        begin tran
71    
72        /* check the existence of the destination plan, level 3 shared lock */
73        /* 
74        ** Also force the index to use for the scan because the
75        ** index on id is always the better one here and needs
76        ** much less lcoks to hold. Optimizer does not always
77        ** pick the index automatically without more accurate
78        ** stats on sysqueryplans.
79        */
80        if not exists (select *
81                from sysqueryplans(index ncsysqueryplans) holdlock
82                where
83                    uid = isnull(@uid, uid)
84                    and id = @id)
85        begin
86            /* 18636, "There is no query plan with the ID %1! in this database."*/
87            raiserror 18636, @id
88    
89            rollback tran
90            return 1
91        end
92    
93        /*
94        ** Delete the old AP (i.e. the type == 100) rows of the plan. 
95        ** Keep the first one (i.e. sequence == 0), see below the 
96        ** update.
97        */
98        delete from sysqueryplans
99        where
100           uid = isnull(@uid, uid)
101           and id = @id
102           and type = 100
103           and sequence > 0
104   
105       if @@error != 0
106       begin
107           /* 18637, "Failed to drop the old query plan with ID %1!, rolling back and aborting." */
108           raiserror 18637, @id
109   
110           rollback tran
111           return 2
112       end
113   
114       /*
115       ** As the @plan parameter is varchar(255) (we don't really have 
116       ** the choice in TSQL, we're sure that it will fit on one row. 
117       ** So we'll reuse the first one, that we didn't delete above, 
118       ** rather than having deleted them all and inserting here a new 
119       ** row. It might be interesting later, when LOBs become first 
120       ** class types, to do better...
121       */
122       update sysqueryplans
123       set text = @plan
124       where
125           uid = isnull(@uid, uid)
126           and id = @id
127           and type = 100
128           and sequence = 0
129   
130       if @@error != 0
131       begin
132           /* 18638, "Failed to set the new query plan with ID %1!, rolling back and aborting." */
133           raiserror 18638, @id
134   
135           rollback tran
136           return 3
137       end
138   
139       /* success */
140       commit tran
141       return 0
142   


exec sp_procxmode 'sp_set_qplan', 'AnyMode'
go

Grant Execute on sp_set_qplan 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}
84
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 102
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 127
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 128
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 84
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 101
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 126
 MGTP 3 Grant to public sybsystemprocs..sp_set_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MUCO 3 Useless Code Useless Brackets 65
 QFID 3 Force index sybsystemprocs..sysqueryplans 81
 QISO 3 Set isolation level 47
 VNRD 3 Variable is not read @status1 36
 VNRD 3 Variable is not read @nullarg 60
 VNRD 3 Variable is not read @gp_enabled 63
 VUNU 3 Variable is not used @dummy 28
 MSUB 2 Subquery Marker 80
 MTR1 2 Metrics: Comments Ratio Comments: 60% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 7dec - 4exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 50 23

DATA PROPAGATION detailed
ColumnWritten To
@plansysqueryplans.text   sp_find_qplan_rset_001.text sp_help_qpgroup_rset_001.Total QueryPlans sp_help_qplan_rset_002.gid sp_help_qplan_rset_003.query sp_help_qplan_rset_005.id sp_show_query_tuning_rset_002.text sp_show_query_tuning_rset_003.text

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