DatabaseProcApplicationCreatedLinks
sybsystemprocssp_set_qplan  31 Aug 14Defects 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 procedure sp_set_qplan
24        @id int,
25        @plan varchar(255)
26    as
27        declare @uid int
28    
29        set nocount on
30    
31        /* Don't start a transaction for the user, he does not expect it. */
32        if @@trancount = 0
33        begin
34            set chained off
35        end
36    
37        /* Don't hold long READ locks, the user might not appreciate it. */
38        set transaction isolation level 1
39    
40    
41        select @uid = nullif (user_id(), 1)
42    
43        begin tran
44    
45        /* check the existence of the destination plan, level 3 shared lock */
46        /* 
47        ** Also force the index to use for the scan because the
48        ** index on id is always the better one here and needs
49        ** much less lcoks to hold. Optimizer does not always
50        ** pick the index automatically without more accurate
51        ** stats on sysqueryplans.
52        */
53        if not exists (select *
54                from sysqueryplans(index ncsysqueryplans) holdlock
55                where
56                    uid = isnull(@uid, uid)
57                    and id = @id)
58        begin
59            /* 18636, "There is no query plan with the ID %1! in this database."*/
60            raiserror 18636, @id
61    
62            rollback tran
63            return 1
64        end
65    
66        /*
67        ** Delete the old AP (i.e. the type == 100) rows of the plan. 
68        ** Keep the first one (i.e. sequence == 0), see below the 
69        ** update.
70        */
71        delete from sysqueryplans
72        where
73            uid = isnull(@uid, uid)
74            and id = @id
75            and type = 100
76            and sequence > 0
77    
78        if @@error != 0
79        begin
80            /* 18637, "Failed to drop the old query plan with ID %1!, rolling back and aborting." */
81            raiserror 18637, @id
82    
83            rollback tran
84            return 2
85        end
86    
87        /*
88        ** As the @plan parameter is varchar(255) (we don't really have 
89        ** the choice in TSQL, we're sure that it will fit on one row. 
90        ** So we'll reuse the first one, that we didn't delete above, 
91        ** rather than having deleted them all and inserting here a new 
92        ** row. It might be interesting later, when LOBs become first 
93        ** class types, to do better...
94        */
95        update sysqueryplans
96        set text = @plan
97        where
98            uid = isnull(@uid, uid)
99            and id = @id
100           and type = 100
101           and sequence = 0
102   
103       if @@error != 0
104       begin
105           /* 18638, "Failed to set the new query plan with ID %1!, rolling back and aborting." */
106           raiserror 18638, @id
107   
108           rollback tran
109           return 3
110       end
111   
112       /* success */
113       commit tran
114       return 0
115   


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}		 57
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 75
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 76
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 100
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 101
 MGTP 3 Grant to public sybsystemprocs..sp_set_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 QFID 3 Force index sybsystemprocs..sysqueryplans 54
 QISO 3 Set isolation level 38
 MSUB 2 Subquery Marker 53
 MTR1 2 Metrics: Comments Ratio Comments: 66% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 5dec - 4exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 41 23

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysqueryplans