-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathxplan_optim_env_body.sql
192 lines (177 loc) · 8.79 KB
/
xplan_optim_env_body.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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
--------------------------------------------------------------------------------
-- Author: Alberto Dell'Era
-- Copyright: (c) 2008-2021 Alberto Dell'Era http://www.adellera.it
--------------------------------------------------------------------------------
procedure optim_env_init_print_sys_pars
is
l_line varchar2(500 char);
begin
if :OPT_PLAN_ENV = 'N' then
return;
end if;
-- put sys optimizer parameters in global hash table
mcf_reset (p_default_execs => to_number(null), -- ignored
p_stat_default_decimals => 0, -- ignored
p_stex_default_decimals => to_number(null) -- suppress disaply if stat/exec
);
mcf_add_line_char ('optimizer param name', 'value', null);
for r in ( with pars as (
select /*+ xplan_exec_marker */ name, value, isdefault, 'O' as typ
&COMM_IF_GT_9I. from sys.gv_$system_parameter
&COMM_IF_GT_9I. where inst_id = :OPT_INST_ID
&COMM_IF_GT_9I. and name in ('active_instance_count', 'bitmap_merge_area_size', 'cpu_count', 'cursor_sharing', 'hash_area_size',
&COMM_IF_GT_9I. 'is_recur_flags', 'optimizer_capture_sql_plan_baselines', 'optimizer_dynamic_sampling',
&COMM_IF_GT_9I. 'optimizer_features_enable', 'optimizer_index_caching', 'optimizer_index_cost_adj', 'optimizer_mode',
&COMM_IF_GT_9I. 'optimizer_secure_view_merging', 'optimizer_use_invisible_indexes', 'optimizer_use_pending_statistics',
&COMM_IF_GT_9I. 'optimizer_use_sql_plan_baselines', 'parallel_ddl_mode', 'parallel_degree', 'parallel_dml_mode',
&COMM_IF_GT_9I. 'parallel_execution_enabled', 'parallel_query_default_dop', 'parallel_query_mode', 'parallel_threads_per_cpu',
&COMM_IF_GT_9I. 'pga_aggregate_target', 'query_rewrite_enabled', 'query_rewrite_integrity', 'result_cache_mode',
&COMM_IF_GT_9I. 'skip_unusable_indexes', 'sort_area_retained_size', 'sort_area_size', 'star_transformation_enabled',
&COMM_IF_GT_9I. 'statistics_level', 'transaction_isolation_level', 'workarea_size_policy')
&COMM_IF_LT_10G from sys.gv_$sys_optimizer_env
&COMM_IF_LT_10G. where inst_id = :OPT_INST_ID
union all
select /*+ xplan_exec_marker */ name, value, isdefault, ' ' as typ
from sys.gv_$system_parameter
where inst_id = :OPT_INST_ID
and (name in ('disk_asynch_io', 'filesystemio_options', 'db_block_size', 'db_writer_processes', 'dbwr_io_slaves',
'event', 'log_buffer', 'memory_target', 'processes', 'sessions', 'session_cached_cursors', 'sga_target',
'shared_servers', 'undo_management', 'undo_retention', 'use_large_pages')
or name like 'parallel%'
or name like 'db\_%cache\_size' escape '\'
or name like '%pool\_size%' escape '\'
or name like 'query\_rewrite\_%' escape '\'
or name like 'result\_cache\_%' escape '\'
or name like 'shared\_pool\_%' escape '\'
)
), pars_adapted as (
select name,
lower( nvl(value,'*null*') ) as value,
case when lower(isdefault) in ('true','yes') then 'yes' else 'false' end as isdefault,
typ
from pars
)
select name, value, isdefault, typ
from pars_adapted
-- repeat General only if value is different from Optimizer
where (typ = 'O' or (typ = ' ' and (name, value, isdefault) not in (select name, value, isdefault from pars_adapted where typ='O')))
order by name, typ)
loop
if r.typ = 'O' then
m_optim_env_sys_params(r.name) := r.value;
end if;
mcf_add_line_char (case when r.isdefault in ('yes','true') then r.name else upper(r.name) end, r.value||' '||r.typ, null);
end loop;
-- display sys optimizer parameters
print ('parameters instance(sys) settings (O=optimizer parameter, name in bold=non-default):');
mcf_prepare_output (p_num_columns => 3);
loop
l_line := mcf_next_output_line;
exit when l_line is null;
print (l_line);
end loop;
end optim_env_init_print_sys_pars;
procedure optim_env_print_sql_pars (
p_address raw,
p_hash_value number,
p_child_number number
)
is
&COMM_IF_LT_10G. l_line varchar2(500 char);
&COMM_IF_LT_10G. l_num_params_found int := 0;
begin
if :OPT_PLAN_ENV = 'N' then
return;
end if;
&COMM_IF_GT_9I. print ('gv$sql_optimizer_env does not exist before 10g.');
-- display sql optimizer parameters
&COMM_IF_LT_10G. mcf_reset (p_default_execs => to_number(null), -- ignored
&COMM_IF_LT_10G. p_stat_default_decimals => 0, -- ignored
&COMM_IF_LT_10G. p_stex_default_decimals => to_number(null) -- suppress disaply if stat/exec
&COMM_IF_LT_10G. );
&COMM_IF_LT_10G. mcf_add_line_char ('optimizer param name', 'value', null);
&COMM_IF_LT_10G. for r in (select /*+ xplan_exec_marker */ name, lower( nvl(value,'*null*') ) as value
&COMM_IF_LT_10G. from sys.gv_$sql_optimizer_env
&COMM_IF_LT_10G. where inst_id = :OPT_INST_ID
&COMM_IF_LT_10G. and address = p_address
&COMM_IF_LT_10G. and hash_value = p_hash_value
&COMM_IF_LT_10G. and child_number = p_child_number
&COMM_IF_LT_10G. order by name)
&COMM_IF_LT_10G. loop
&COMM_IF_LT_10G. if not m_optim_env_sys_params.exists(r.name) or m_optim_env_sys_params(r.name) != r.value then
&COMM_IF_LT_10G. mcf_add_line_char (r.name, r.value, null);
&COMM_IF_LT_10G. l_num_params_found := l_num_params_found + 1;
&COMM_IF_LT_10G. end if;
&COMM_IF_LT_10G. end loop;
&COMM_IF_LT_10G. if l_num_params_found > 0 then
&COMM_IF_LT_10G. print ('WARNING: '||l_num_params_found || ' params in gv$sql_optimizer_env are not the same as instance ones:');
&COMM_IF_LT_10G. mcf_prepare_output (p_num_columns => least (l_num_params_found, 3));
&COMM_IF_LT_10G. loop
&COMM_IF_LT_10G. l_line := mcf_next_output_line;
&COMM_IF_LT_10G. exit when l_line is null;
&COMM_IF_LT_10G. print (l_line);
&COMM_IF_LT_10G. end loop;
&COMM_IF_LT_10G. else
&COMM_IF_LT_10G. print ('all params in gv$sql_optimizer_env are the same as instance ones.');
&COMM_IF_LT_10G. end if;
end optim_env_print_sql_pars;
procedure optim_env_add_sys_stats_to_mcf (p_name varchar2, p_insert_general boolean default false)
is
l_status VARCHAR2(100 char);
l_dstart date;
l_dstop date;
l_value number;
SYS_STAT_UNABLE_GET exception;
pragma exception_init (SYS_STAT_UNABLE_GET, -20003);
SYS_STAT_NOT_EXISTS exception;
pragma exception_init (SYS_STAT_NOT_EXISTS, -20004);
begin
dbms_stats.get_system_stats (
status => l_status,
dstart => l_dstart,
dstop => l_dstop,
pname => p_name,
pvalue => l_value
);
if p_insert_general then
mcf_add_line_char ('status', lower(l_status), null);
mcf_add_line_char ('gathering start', to_char (l_dstart, 'yyyy-mm-dd/hh24:mi:ss'), null);
mcf_add_line_char ('gathering stop', to_char (l_dstop, 'yyyy-mm-dd/hh24:mi:ss'), null);
end if;
if l_value is not null then
mcf_add_line (lower(p_name), l_value);
else
mcf_add_line_char (lower(p_name), 'null', null);
end if;
exception
when SYS_STAT_UNABLE_GET then
mcf_add_line_char (lower(p_name), 'no value found', null);
when SYS_STAT_NOT_EXISTS then
mcf_add_line_char (lower(p_name), 'not existent', null);
end optim_env_add_sys_stats_to_mcf;
procedure optim_env_print_sys_stats
is
l_line varchar2(500 char);
begin
mcf_reset (p_default_execs => to_number(null), -- ignored
p_stat_default_decimals => 0, -- ignored
p_stex_default_decimals => to_number(null) -- suppress disaply if stat/exec
);
mcf_add_line_char ('system statistic', 'value', null);
optim_env_add_sys_stats_to_mcf ('CPUSPEED', true);
&COMM_IF_LT_10G. optim_env_add_sys_stats_to_mcf ('CPUSPEEDNW');
optim_env_add_sys_stats_to_mcf ('SREADTIM');
optim_env_add_sys_stats_to_mcf ('MREADTIM');
optim_env_add_sys_stats_to_mcf ('MBRC');
&COMM_IF_LT_10G. optim_env_add_sys_stats_to_mcf ('IOSEEKTIM');
&COMM_IF_LT_10G. optim_env_add_sys_stats_to_mcf ('IOTFRSPEED');
optim_env_add_sys_stats_to_mcf ('MAXTHR');
optim_env_add_sys_stats_to_mcf ('SLAVETHR');
print ('optimizer system statistics:');
mcf_prepare_output (p_num_columns => 3);
loop
l_line := mcf_next_output_line;
exit when l_line is null;
print (l_line);
end loop;
end optim_env_print_sys_stats;