-
Notifications
You must be signed in to change notification settings - Fork 313
/
cofef_missing.sql
92 lines (82 loc) · 2.52 KB
/
cofef_missing.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- 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 Fix values - find fixes that are not affected by optimizer_features_enable setting
--
-- By Tanel Poder ( https://blog.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 sql_feature FOR a40
-- 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 Fix differences
prompt for values &1 and &2 (v$session_fix_control)
prompt
COL cofef_min NEW_VALUE cofef_min
COL cofef_max NEW_VALUE cofef_max
SET TERMOUT OFF
WITH sq AS (
SELECT ordinal, name, value FROM v$parameter_valid_values WHERE name = 'optimizer_features_enable'
)
SELECT
(SELECT value FROM sq WHERE ordinal = (SELECT MIN(ordinal) FROM sq)) cofef_min
, (SELECT value FROM sq WHERE ordinal = (SELECT MAX(ordinal) FROM sq)) cofef_max
FROM
dual
/
SET TERMOUT ON
WITH sq AS (
SELECT /*+ MATERIALIZE */ bugno
FROM
opt_fix_matrix
PIVOT(
MAX(SUBSTR(value,1,20))
FOR opt_features_enabled IN ('&cofef_min' , '&cofef_max')
)
WHERE
"'&cofef_min'" != "'&cofef_max'"
)
SELECT
*
FROM
v$session_fix_control
WHERE
session_id = SYS_CONTEXT('userenv', 'sid')
AND bugno NOT IN (
SELECT bugno FROM sq
)
/