-
Notifications
You must be signed in to change notification settings - Fork 312
/
cofep.sql
72 lines (64 loc) · 2.02 KB
/
cofep.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
-- Compare Optimizer Features Enable Parameter values
-- By Tanel Poder ( http://www.tanelpoder.com )
-- Requires opt_param_matrix table to be created (using tools/optimizer/optimizer_features_matrix.sql)
-- Requires Oracle 11g+ due to PIVOT clause (but you can rewrite this SQL in earlier versions)`
col pd_name head NAME for a50
col pd_value head VALUE for a30
column pd_descr heading DESCRIPTION format a70 word_wrap
-- funky pivot formatting for sqlplus
COL "'18.1.0'" FOR A30 WRAP
COL "'8.0.0'" FOR A30 WRAP
COL "'8.0.3'" FOR A30 WRAP
COL "'8.0.4'" FOR A30 WRAP
COL "'8.0.5'" FOR A30 WRAP
COL "'8.0.6'" FOR A30 WRAP
COL "'8.0.7'" FOR A30 WRAP
COL "'8.1.0'" FOR A30 WRAP
COL "'8.1.3'" FOR A30 WRAP
COL "'8.1.4'" FOR A30 WRAP
COL "'8.1.5'" FOR A30 WRAP
COL "'8.1.6'" FOR A30 WRAP
COL "'8.1.7'" FOR A30 WRAP
COL "'9.0.0'" FOR A30 WRAP
COL "'9.0.1'" FOR A30 WRAP
COL "'9.2.0'" FOR A30 WRAP
COL "'9.2.0.8'" FOR A30 WRAP
COL "'10.1.0'" FOR A30 WRAP
COL "'10.1.0.3'" FOR A30 WRAP
COL "'10.1.0.4'" FOR A30 WRAP
COL "'10.1.0.5'" FOR A30 WRAP
COL "'10.2.0.1'" FOR A30 WRAP
COL "'10.2.0.2'" FOR A30 WRAP
COL "'10.2.0.3'" FOR A30 WRAP
COL "'10.2.0.4'" FOR A30 WRAP
COL "'10.2.0.5'" FOR A30 WRAP
COL "'11.1.0.6'" FOR A30 WRAP
COL "'11.1.0.7'" FOR A30 WRAP
COL "'11.2.0.1'" FOR A30 WRAP
COL "'11.2.0.2'" FOR A30 WRAP
COL "'11.2.0.3'" FOR A30 WRAP
COL "'11.2.0.4'" FOR A30 WRAP
COL "'12.1.0.1'" FOR A30 WRAP
COL "'12.1.0.2'" FOR A30 WRAP
COL "'12.2.0.1'" FOR A30 WRAP
COL "'18.1.0.1'" FOR A30 WRAP
prompt Compare Optimizer_Features_Enable Parameter differences
prompt for values &1 and &2
select m.*, n.ksppdesc pd_descr
from (
select *
from opt_param_matrix
pivot(
max(substr(value,1,20))
for opt_features_enabled in ('&1','&2')
)
where "'&1'" != "'&2'"
) m
, sys.x$ksppi n
, sys.x$ksppcv c
where
n.indx=c.indx
and n.ksppinm = m.parameter
/