DatabaseProcApplicationCreatedLinks
sybsystemprocssp_show_query_tuning  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Dec 19 13:43:49 2006 
4     */
5     /*
6     ** raiserror Messages for show_query_tuning [Total 0]
7     */
8     /*
9     ** sp_getmessage Messages for show_query_tuning [Total 0]
10    */
11    /*
12    ** End spgenmsgs.pl output.
13    */
14    
15    /*
16    ** sp_show_query_tuning
17    **
18    ** Display information in query_tuning groups
19    ** Requires Galaxy or later
20    **
21    */
22    
23    create or replace procedure sp_show_query_tuning
24        @showtype varchar(30) = NULL
25    as
26        declare @msg varchar(250)
27        declare @uid int
28        declare @nullarg char(1)
29        declare @dummy int
30        declare @status int
31        declare @gp_enabled int
32    
33        set nocount on
34    
35        /* 
36        ** Users can access their own rows, otherwise
37        ** if granular permissions is not enabled then user must be dbo or have sa_role.
38        ** If granular permissions is enabled then the user must be dbo or the 
39        ** permission 'manage abstract plans' is required. 
40        */
41    
42        select @nullarg = NULL
43        select @uid = NULL
44        execute @status = sp_aux_checkroleperm "sa_role", "manage abstract plans",
45            @nullarg, @gp_enabled output
46        if (@status != 0)
47        begin
48            select @uid = nullif (user_id(), 1)
49        end
50    
51        if (@showtype = 'query_tuning_objects')
52        begin
53            select gid 'Group ID', count(*) 'Query Tuning Objects'
54            from sysqueryplans
55            where ((type > 100 and type < 1000) or type > 1000)
56                and uid = isnull(@uid, uid)
57            group by gid
58            order by gid
59    
60            return 0
61        end
62    
63        if (@showtype = 'ase125_better_plans')
64        begin
65            select id, sequence, text
66            from sysqueryplans sq1
67            where type < 100
68                and uid = isnull(@uid, uid)
69                and id in
70                    (select id from sysqueryplans sq2
71                    where sq2.id = sq1.id
72                        and sq2.type > 1000
73                        and (sq2.status & 2) != 0
74                        and not exists
75                            (select id from sysqueryplans sq3
76                            where sq3.id = sq2.id
77                                and sq3.type > 1000
78                                and (sq3.status is NULL or (sq3.status & 2) = 0)
79                                and convert(int, substring(sq3.text, charindex('t3', sq3.text) + 3, charindex('l1', sq3.text) - charindex('t3', sq3.text) - 4)) < convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4)))
80                        and exists
81                            (select id from sysqueryplans sq4
82                            where sq4.id = sq2.id
83                                and sq4.type > 1000
84                                and (sq4.status is NULL or (sq4.status & 2) = 0)
85                                and convert(int, substring(sq4.text, charindex('t3', sq4.text) + 3, charindex('l1', sq4.text) - charindex('t3', sq4.text) - 4)) > convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4))))
86    
87            return 0
88        end
89    
90        if (@showtype = 'ase15_better_plans')
91        begin
92            select id, sequence, text
93            from sysqueryplans sq1
94            where type < 100
95                and uid = isnull(@uid, uid)
96                and id in
97                    (select id from sysqueryplans sq2
98                    where sq2.id = sq1.id
99                        and sq2.type > 1000
100                       and (sq2.status is NULL or (sq2.status & 2) = 0)
101                       and not exists
102                           (select id from sysqueryplans sq3
103                           where sq3.id = sq2.id
104                               and sq3.type > 1000
105                               and (sq3.status & 2) != 0
106                               and convert(int, substring(sq3.text, charindex('t3', sq3.text) + 3, charindex('l1', sq3.text) - charindex('t3', sq3.text) - 4)) < convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4)))
107                       and exists
108                           (select id from sysqueryplans sq4
109                           where sq4.id = sq2.id
110                               and sq4.type > 1000
111                               and (sq4.status & 2) != 0
112                               and convert(int, substring(sq4.text, charindex('t3', sq4.text) + 3, charindex('l1', sq4.text) - charindex('t3', sq4.text) - 4)) > convert(int, substring(sq2.text, charindex('t3', sq2.text) + 3, charindex('l1', sq2.text) - charindex('t3', sq2.text) - 4))))
113   
114           return 0
115       end
116   
117       return 1
118   


exec sp_procxmode 'sp_show_query_tuning', 'AnyMode'
go

Grant Execute on sp_show_query_tuning to public
go
RESULT SETS
sp_show_query_tuning_rset_001
sp_show_query_tuning_rset_003
sp_show_query_tuning_rset_002

DEFECTS
 QJWI 5 Join or Sarg Without Index 69
 QJWI 5 Join or Sarg Without Index 96
 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}
55
 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}
67
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
71
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
71
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
76
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
76
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
82
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
82
 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}
94
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
98
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
98
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
103
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
103
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, sequence]
109
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {id}
Uncovered: [uid, gid, hashkey, type, sequence]
109
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 55
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 72
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 77
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 83
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 94
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 65
 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 70
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 71
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 75
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 76
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 81
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 82
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 92
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 96
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 97
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 98
 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 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 108
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 109
 MGTP 3 Grant to public sybsystemprocs..sp_show_query_tuning  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 90
 QCRS 3 Conditional Result Set 53
 QCRS 3 Conditional Result Set 65
 QCRS 3 Conditional Result Set 92
 VNRD 3 Variable is not read @gp_enabled 45
 VUNU 3 Variable is not used @msg 26
 VUNU 3 Variable is not used @dummy 29
 MRST 2 Result Set Marker 53
 MRST 2 Result Set Marker 65
 MRST 2 Result Set Marker 92
 MSUC 2 Correlated Subquery Marker 70
 MSUC 2 Correlated Subquery Marker 75
 MSUC 2 Correlated Subquery Marker 81
 MSUC 2 Correlated Subquery Marker 97
 MSUC 2 Correlated Subquery Marker 102
 MSUC 2 Correlated Subquery Marker 108
 MTR1 2 Metrics: Comments Ratio Comments: 14% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 5dec - 4exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 75 23
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 70
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 75
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 81
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 97
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 102
 PRED_QUERY_COLLECTION 2 {q=sybsystemprocs..sysqueryplans, q2=sybsystemprocs..sysqueryplans} 0 108

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysqueryplans  
writes table sybsystemprocs..sp_show_query_tuning_rset_001 
writes table sybsystemprocs..sp_show_query_tuning_rset_002 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table sybsystemprocs..sp_show_query_tuning_rset_003 

CALLERS
called by proc sybsystemprocs..sp_query_tuning