These 4 I'm familiar with. Comments in-line
nls_sort = 'BINARY' --- important for some application functionality. Could
very well be needed in new Database
optimizer_dynamic_sampling=0 --- if you were burning a lot of excess CPU,
this will help prevent that. Dynamic sampling can be expensive. If you're
not pushing your CPU capacity, then I'd probably leave this at the default
(2) instead of 0.
--- these two should be default, but you really need to test the
application to see if it misbehaves because these can definitely affect the
performance of the queries
--- I hate when I see these two params set as its a carryover from a long,
long time ago but can definitely affect application performance
optimizer_index_caching=100
optimizer_index_cost_adj=1
Chris
On Tue, Apr 20, 2021 at 7:05 AM Pap <oracle.developer35@xxxxxxxxx> 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
