Re: Significance of specific database parameter difference

I can understand other explanations except for nls_sort = 'BINARY'". How this can impact application if we go with default one i.e. NULL?

On Tue, Apr 20, 2021 at 7:14 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> 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_at_gmail.com> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l