DatabaseProcApplicationCreatedLinks
sybsystemprocssp_copy_qplan  14 déc. 14Defects Propagation 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 or replace 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            @dummy int,
38            @dbname varchar(255),
39            @nullarg char(1),
40            @gp_enabled int,
41            @status int
42    
43    
44        select @dbname = db_name()
45    
46        set nocount on
47    
48        /* Don't start a transaction for the user, he does not expect it. */
49        if @@trancount = 0
50        begin
51            set chained off
52        end
53    
54        /* Don't hold long READ locks, the user might not appreciate it. */
55        set transaction isolation level 1
56    
57    
58        /*
59        **  If granular permissions is not enabled
60        **  only the Database Owner (DBO) or
61        **  Accounts with SA role can execute it.
62        **  If the user has SA role, it's uid will
63        **  be DBO uid (1). If granular permissions is enabled then
64        **  users with 'manage abstract plans' permission can execute it.
65        */
66    
67        select @nullarg = NULL
68    
69        execute @status = sp_aux_checkroleperm "dbo",
70            "manage abstract plans", @dbname, @gp_enabled output
71    
72        /* 
73        ** If @status = 0, user is dbo, has sa_role or if granular permissions
74        ** is enabled has 'manage abstract plans' permission.  Therefore the
75        ** user can copy all query plans in the group.
76        */
77        if (@status = 0)
78            select @uid = NULL
79        else
80            select @uid = user_id()
81    
82        exec sp_aux_get_qpgroup @dest_group, @dest_gid out
83    
84        if @dest_gid is null
85        begin
86            /* 18639, "There is no query plans group named '%1!' in this database." */
87            raiserror 18639, @dest_group
88    
89            rollback tran
90            return 1
91        end
92    
93        /*
94        ** Search an AP with the same association 
95        ** key as @src_id in group @dest_gid. 
96        */
97    
98        select distinct
99            @src_uid = uid,
100           @hashkey = hashkey
101       from sysqueryplans
102       where
103           uid = isnull(@uid, uid)
104           and id = @src_id
105           and type = 100
106   
107       if @hashkey is null
108       begin
109           /* 18636, "There is no query plan with the ID %1! in this database." */
110           raiserror 18636, @src_id
111   
112           return 2
113       end
114   
115       /* get the collision candidates */
116       select distinct id
117       into #t
118       from sysqueryplans
119       where
120           uid = @src_uid
121           and gid = @dest_gid
122           and hashkey = @hashkey
123   
124       declare collide cursor
125       for
126       select id
127       from #t
128   
129       open collide
130       fetch collide into @coll_id
131   
132       while @@sqlstatus = 0
133       begin
134           /* compare with each collision candidate */
135           exec @ret = sp_cmp_qplans @coll_id, @src_id
136   
137           if @ret = 10
138           begin
139               /* 18652, "The new query plan would give an association key collision in group '%1!', with the existing query plan %2!." */
140               raiserror 18652, @dest_group, @coll_id
141   
142               return 3
143           end
144   
145           if @ret = 0
146           begin
147               /* 18653, "The new query plan already exists in group '%1!', under the ID %2!." */
148               raiserror 18653, @dest_group, @coll_id
149   
150               return 4
151           end
152   
153           if @ret = 2 or @ret = 12
154               /* 18667, "The new query plan will give a hash collision with the existing query plan %1!, but the copy will be made." */
155               raiserror 18667, @coll_id
156           else
157               /* 18654, "Obtained the unexpected plan comparison return code %1!, when comparing with the query plan %2!. Please contact Sybase Technical Support." */
158               raiserror 18654, @ret, @coll_id
159   
160           fetch collide into @coll_id
161       end
162   
163       create plan "select 1" "()" into @dest_group and set @new_id
164       delete sysqueryplans
165       where id = @new_id
166   
167       insert sysqueryplans
168       select @src_uid, @dest_gid,
169           @hashkey, @new_id,
170           type, sequence, status, text,
171           dbid, qpdate, sprocid, hashkey2,
172           key1, key2, key3, key4
173       from sysqueryplans
174       where id = @src_id
175           and type <= 100
176   
177       select @rows = @@rowcount
178   
179       if @rows = 0
180       begin
181           /* 18655, "Failed to copy the new query plan." */
182           raiserror 18655
183   
184           return 5
185       end
186   
187       return 0
188   


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}
104
 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}
165
 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}
174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 175
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 104
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 116
 MAW1 3 Warning message on %name% tempdb..#t.id: Warning message on #t_crby_sybsystemprocs__sp_copy_qplan 126
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 165
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 174
 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 82
 MNER 3 No Error Check should check @@error after select into 116
 MNER 3 No Error Check should check @@error after delete 164
 MNER 3 No Error Check should check @@error after insert 167
 MUCO 3 Useless Code Useless Brackets 77
 MUOT 3 Updates outside transaction 167
 QAFM 3 Var Assignment from potentially many rows 98
 QGWO 3 Group by/Distinct/Union without order by 98
 QGWO 3 Group by/Distinct/Union without order by 116
 QISO 3 Set isolation level 55
 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}
120
 VNRD 3 Variable is not read @nullarg 67
 VNRD 3 Variable is not read @gp_enabled 70
 VUNU 3 Variable is not used @dummy 37
 CUPD 2 Updatable Cursor Marker (updatable by default) 126
 MTR1 2 Metrics: Comments Ratio Comments: 51% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 16dec - 6exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 71 24

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table tempdb..#t (1) 
read_writes table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_cmp_qplans  
   reads table sybsystemprocs..sysqueryplans  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   calls proc sybsystemprocs..sp_getmessage  
      reads table sybsystemprocs..sysusermessages  
      reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (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