Database | Proc | Application | Created | Links |
sybsystemprocs | sp_set_qplan | 31 Aug 14 | Defects 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 |