| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_copy_qplan | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_copy_qplan" 6 ** 7 ** 18639, "There is no query plans group named '%1!' in this database." 8 ** 18636, "There is no query plan with the ID %1! in this database." 9 ** 18652, "The new query plan would give an association key collision in group '%1!', with the existing query plan %1!." 10 ** 18653, "The new query plan already exists in group '%1!', under the ID %1!." 11 ** 18667, "The new query plan will give a hash collision with the existing query plan %1!, but the copy will be made." 12 ** 18654, "Obtained the unexpected plan comparison return code %1!, when comparing with the query plan %2!. Please contact Sybase Technical Support." 13 ** 18655, "Failed to copy the new query plan." 14 */ 15 16 /* 17 ** sp_copy_qplan copies a given AP in a given AP group. The new ID is 18 ** obtained by a dummy CREATE PLAN "" "". Then the rows are copied 19 ** as such (but with the new GID and ID). The old hashkey is reused, 20 ** as it goes with the old association (i.e. query text rows). 21 ** Note, no learning AP(s) or metrics are copied. 22 */ 23 24 create procedure sp_copy_qplan 25 @src_id int, 26 @dest_group varchar(30) 27 as 28 declare 29 @uid int, 30 @src_uid int, 31 @hashkey int, 32 @new_id int, 33 @dest_gid int, 34 @coll_id int, 35 @ret int, 36 @rows int 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 select @uid = nullif (user_id(), 1) 51 52 exec sp_aux_get_qpgroup @dest_group, @dest_gid out 53 54 if @dest_gid is null 55 begin 56 /* 18639, "There is no query plans group named '%1!' in this database." */ 57 raiserror 18639, @dest_group 58 59 rollback tran 60 return 1 61 end 62 63 /* 64 ** Search an AP with the same association 65 ** key as @src_id in group @dest_gid. 66 */ 67 68 select distinct 69 @src_uid = uid, 70 @hashkey = hashkey 71 from sysqueryplans 72 where 73 uid = isnull(@uid, uid) 74 and id = @src_id 75 and type = 100 76 77 if @hashkey is null 78 begin 79 /* 18636, "There is no query plan with the ID %1! in this database." */ 80 raiserror 18636, @src_id 81 82 return 2 83 end 84 85 /* get the collision candidates */ 86 select distinct id 87 into #t 88 from sysqueryplans 89 where 90 uid = @src_uid 91 and gid = @dest_gid 92 and hashkey = @hashkey 93 94 declare collide cursor 95 for 96 select id 97 from #t 98 99 open collide 100 fetch collide into @coll_id 101 102 while @@sqlstatus = 0 103 begin 104 /* compare with each collision candidate */ 105 exec @ret = sp_cmp_qplans @coll_id, @src_id 106 107 if @ret = 10 108 begin 109 /* 18652, "The new query plan would give an association key collision in group '%1!', with the existing query plan %2!." */ 110 raiserror 18652, @dest_group, @coll_id 111 112 return 3 113 end 114 115 if @ret = 0 116 begin 117 /* 18653, "The new query plan already exists in group '%1!', under the ID %2!." */ 118 raiserror 18653, @dest_group, @coll_id 119 120 return 4 121 end 122 123 if @ret = 2 or @ret = 12 124 /* 18667, "The new query plan will give a hash collision with the existing query plan %1!, but the copy will be made." */ 125 raiserror 18667, @coll_id 126 else 127 /* 18654, "Obtained the unexpected plan comparison return code %1!, when comparing with the query plan %2!. Please contact Sybase Technical Support." */ 128 raiserror 18654, @ret, @coll_id 129 130 fetch collide into @coll_id 131 end 132 133 create plan "select 1" "()" into @dest_group and set @new_id 134 delete sysqueryplans 135 where id = @new_id 136 137 insert sysqueryplans 138 select @src_uid, @dest_gid, 139 @hashkey, @new_id, 140 type, sequence, status, text, 141 dbid, qpdate, sprocid, hashkey2, 142 key1, key2, key3, key4 143 from sysqueryplans 144 where id = @src_id 145 and type <= 100 146 147 select @rows = @@rowcount 148 149 if @rows = 0 150 begin 151 /* 18655, "Failed to copy the new query plan." */ 152 raiserror 18655 153 154 return 5 155 end 156 157 return 0 158
exec sp_procxmode 'sp_copy_qplan', 'AnyMode' go Grant Execute on sp_copy_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: {type, id} | 74 |
| 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} | 135 |
| 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: {type, id} | 144 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 75 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 145 |
 MGTP 3 Grant to public sybsystemprocs..sp_copy_qplan | |
| MGTP 3 Grant to public sybsystemprocs..sysqueryplans | |
| MNER 3 No Error Check should check return value of exec | 52 |
| MNER 3 No Error Check should check @@error after select into | 86 |
| MNER 3 No Error Check should check @@error after delete | 134 |
| MNER 3 No Error Check should check @@error after insert | 137 |
| MUOT 3 Updates outside transaction | 137 |
| QAFM 3 Var Assignment from potentially many rows | 68 |
| QGWO 3 Group by/Distinct/Union without order by | 68 |
| QGWO 3 Group by/Distinct/Union without order by | 86 |
| QISO 3 Set isolation level | 47 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {uid, hashkey, gid} | 90 |
 CUPD 2 Updatable Cursor Marker (updatable by default) | 96 |
| MTR1 2 Metrics: Comments Ratio Comments: 49% | 24 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 14dec - 6exi + 2 | 24 |
| MTR3 2 Metrics: Query Complexity Complexity: 65 | 24 |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table sybsystemprocs..sysqueryplans calls proc sybsystemprocs..sp_cmp_qplans  calls proc sybsystemprocs..sp_getmessage calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1)  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysqueryplans read_writes table tempdb..#t (1) calls proc sybsystemprocs..sp_aux_get_qpgroup reads table sybsystemprocs..sysattributes calls proc sybsystemprocs..sp_aux_sargs_qpgroup CALLERS called by proc sybsystemprocs..sp_copy_all_qplans |