Well, I found that through oracle 10.2.0.4 you could in a very few situations
get some improvement by adjusting those settings. After that, adjusting them
usually resulted in negative performance.
Sent from my iPad
On Apr 20, 2021, at 13:56, Tim Gorman <tim.evdbt@xxxxxxxxx> wrote:
As the idiot who wrote a white paper 20 years ago to make people aware of
the optimizer_index_caching and optimizer_index_cost_adj parameters
introduced initially in Oracle8 and documented in Oracle8i, I always wince
when I see people crippling themselves with those parameters today. Please,
for heaven's sake, stop! Using an instance-level parameter to issue a broad
directive to every SQL statement about a condition which cannot be true in
all cases is inappropriate micromanagement. Let the CBO do what it is
designed to do, which is to use specific data conditions portrayed by
system-, table-, index-, and column-statistics to make decisions on a
case-by-case basis for each individual SQL statement.
Also, big kudos on getting rid of the "underscore" parameters! Using
undocumented "underscore" parameters not justified by directives from Oracle
Support cases or documented configuration requirements from a vendor (i.e.
SAP for R3, Oracle for EBS, etc) is a stunningly bad idea. Undocumented
parameters must be justified, else removed, especially when upgrading.
Everything else looks sensible to me.
On 4/20/2021 4:05 AM, Pap wrote:
Hello All, we are moving from HP to Exadata - X8. Along with this we are
also moving from current Oracle version 11.2.0.4 to 19C. And below are a few
of the differences in parameters we see between old and new databases. New
database(which is not yet live) holds mostly default setup only. But the
existing database has below non default setup(Around ~12 parameters), And I
don't know if those non default setups in the old/existing database is
really helping us anyway. So wanted to understand if it's safe to go by all
the defaults(as it's mentioned below) for the new database? Or any of these
non default parameters from existing databases is really critical to be
double checked/verified thoroughly, so that we may need to move those as it
is to the new database?
Existing environment (11.2, HP):-
_buffered_publisher_flow_control_threshold = 500000
_capture_publisher_flow_control_threshold =500000
_enable_query_rewrite_on_remote_objs = true
_optimizer_cost_based_transformation = linear
nls_sort = 'BINARY'
optimizer_dynamic_sampling=0
optimizer_index_caching=100
optimizer_index_cost_adj=1
optimizer_secure_view_merging=FALSE
query_rewrite_enabled=FALSE
optimizer_mode=FIRST_ROWS
session_cached_cursors=0
New environment- (19C, Exadata - X8):-
_buffered_publisher_flow_control_threshold = null
_capture_publisher_flow_control_threshold =null
_enable_query_rewrite_on_remote_objs = NULL
_optimizer_cost_based_transformation = null
nls_sort =NULL
optimizer_dynamic_sampling=2
optimizer_index_caching=0
optimizer_index_cost_adj=100
optimizer_secure_view_merging=TRUE
query_rewrite_enabled=TRUE
optimizer_mode=ALL_ROWS
session_cached_cursors=200
