DatabaseProcApplicationCreatedLinks
sybsystemprocssp_help_qplan  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_help_qplan"	
6     **
7     ** 18640, "Unknown %1! option : '%2!'. Valid options are : %3!."
8     */
9     
10    /*
11    ** sp_help_qplan displays a plan. The display modes can be "brief", 
12    ** "list" and "full". The default display mode is "brief".
13    */
14    
15    create or replace procedure sp_help_qplan
16        @id int,
17        @mode varchar(20) = "brief"
18    as
19        declare @uid int
20        declare @retval int
21    
22        declare @dummy int
23            , @nullarg char(1)
24            , @dbname varchar(255)
25            , @gp_enabled int
26            , @status int
27    
28    
29        set nocount on
30    
31        select @dbname = db_name()
32    
33        /* Don't start a transaction for the user, he does not expect it. */
34        if @@trancount = 0
35        begin
36            set chained off
37        end
38    
39        /* Don't hold long READ locks, the user might not appreciate it. */
40        set transaction isolation level 1
41    
42    
43        /*
44        **  If granular permissions is not enabled
45        **  only the Database Owner (DBO) or
46        **  Accounts with SA role can execute it.
47        **  If the user has SA role, it's uid will
48        **  be DBO uid (1). If granular permissions is enabled then
49        **  users with 'manage abstract plans' permission can execute it.
50        */
51    
52        select @nullarg = NULL
53    
54        execute @status = sp_aux_checkroleperm "dbo",
55            "manage abstract plans", @dbname, @gp_enabled output
56    
57        /* 
58        ** If @status = 0, user is dbo, has sa_role or if granular permissions
59        ** is enabled has 'manage abstract plans' permission.  Therefore the
60        ** user can delete all query plans in the group.
61        */
62        if (@status = 0)
63            select @uid = NULL
64        else
65            select @uid = user_id()
66    
67        if @mode = "list"
68        begin
69            select hashkey, id,
70                substring(text, 1, 20) + "..." as "query",
71                    (select substring(text, 1, 20) + "..."
72                    from sysqueryplans
73                    where uid = isnull(@uid, uid)
74                        and id = @id
75                        and type = 100
76                        and sequence = 0) as "query_plan"
77            from sysqueryplans
78            where uid = isnull(@uid, uid)
79                and id = @id
80                and type = 10
81                and sequence = 0
82    
83            return 0
84        end
85    
86        if @mode = "brief"
87        begin
88            select gid, hashkey, id
89            from sysqueryplans
90            where uid = isnull(@uid, uid)
91                and id = @id
92                and type = 10
93                and sequence = 0
94    
95            select case
96                    when char_length(text) <= 78
97                    then substring(text, 1, 78)
98                    else substring(text, 1, 75) + "..."
99                end as "query"
100           from sysqueryplans
101           where uid = isnull(@uid, uid)
102               and id = @id
103               and type = 10
104               and sequence = 0
105   
106           select case
107                   when char_length(text) <= 78
108                   then substring(text, 1, 78)
109                   else substring(text, 1, 75) + "..."
110               end as "query_plan"
111           from sysqueryplans
112           where uid = isnull(@uid, uid)
113               and id = @id
114               and type = 100
115               and sequence = 0
116   
117           return 0
118       end
119   
120       if @mode = "full"
121       begin
122           select gid, hashkey, id
123           from sysqueryplans
124           where uid = isnull(@uid, uid)
125               and id = @id
126               and type = 10
127               and sequence = 0
128   
129           select text as "query" into #query_text
130           from sysqueryplans
131           where uid = isnull(@uid, uid)
132               and id = @id
133               and type = 10
134           order by sequence
135   
136           exec @retval = sp_autoformat #query_text
137           drop table #query_text
138           if (@retval != 0)
139               return 1
140   
141           select text as "query_plan" into #plan_text
142           from sysqueryplans
143           where uid = isnull(@uid, uid)
144               and id = @id
145               and type = 100
146           order by sequence
147   
148           exec @retval = sp_autoformat #plan_text
149           drop table #plan_text
150           if (@retval != 0)
151               return 1
152   
153           return 0
154       end
155   
156       /* 18640, "Unknown %1! option : '%2!'. Valid options are : %3!." */
157       raiserror 18640, "sp_help_qplan", @mode, "'brief', 'list', 'full'"
158   
159       return 1
160   


exec sp_procxmode 'sp_help_qplan', 'AnyMode'
go

Grant Execute on sp_help_qplan to public
go
RESULT SETS
sp_help_qplan_rset_005
sp_help_qplan_rset_004
sp_help_qplan_rset_003
sp_help_qplan_rset_002
sp_help_qplan_rset_001

DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 136
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 148
 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}
132
 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 76
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 81
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 127
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 145
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 69
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 74
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 79
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 88
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 91
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 102
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 113
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 122
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 125
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 132
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 144
 MGTP 3 Grant to public sybsystemprocs..sp_help_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after select into 129
 MNER 3 No Error Check should check return value of exec 136
 MNER 3 No Error Check should check @@error after select into 141
 MNER 3 No Error Check should check return value of exec 148
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 150
 QCRS 3 Conditional Result Set 69
 QCRS 3 Conditional Result Set 88
 QCRS 3 Conditional Result Set 95
 QCRS 3 Conditional Result Set 106
 QCRS 3 Conditional Result Set 122
 QCTC 3 Conditional Table Creation 129
 QCTC 3 Conditional Table Creation 141
 QISO 3 Set isolation level 40
 VNRD 3 Variable is not read @nullarg 52
 VNRD 3 Variable is not read @gp_enabled 55
 VUNU 3 Variable is not used @dummy 22
 MRST 2 Result Set Marker 69
 MRST 2 Result Set Marker 88
 MRST 2 Result Set Marker 95
 MRST 2 Result Set Marker 106
 MRST 2 Result Set Marker 122
 MSUB 2 Subquery Marker 71
 MTR1 2 Metrics: Comments Ratio Comments: 27% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 12dec - 6exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 86 15

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_help_qplan_rset_003 
writes table tempdb..#query_text (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table tempdb..#plan_text (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
writes table sybsystemprocs..sp_help_qplan_rset_002 
writes table sybsystemprocs..sp_help_qplan_rset_001 
writes table sybsystemprocs..sp_help_qplan_rset_004 
writes table sybsystemprocs..sp_help_qplan_rset_005 
reads table sybsystemprocs..sysqueryplans  

CALLERS
called by proc sybsystemprocs..sp_cmp_all_qplans