Re: Significance of specific database parameter difference

The first point to question is whether you are running a 3rd party application that has dictated these non-default settings - and if so does the application owner have any specific comments relating to changing settings for Exadata.
If it's not a 3rd party application can you find the documentation that explains why an in-house application has these non-default settings, and determine from that whether the settings are now inappropriate.

You might note, in particular, that 20 years ago the manuals had the commnet "exists for backwards compatability only" for the optimizer_mode "first_rows". So that's a no-no. Then the optimizer_index_cost_adjust and optimizer_index_caching exist to encourage (badly) the optimizer to use indexes over tablescans when a primary feature of Exadata is that it will often perform better when allowed to do tablescans.

Mladen Gogala commented that setting system stats had the same effect setting the optimizer_index_xxx parameters. This is not strictly true - setting system stats generatlly increases the cost of tablescans, changing the optimizer_index_xxx parameters generally decreases the cost of indexed access paths, often to the level where all indexes are equally cheap. (Effectively your combination of parameters is almost saying to Oracle "pick an index, any index" - and that's not a good idea unless the indexing strategy has been very well defined.)

Many of your other parameters look as if they've been set on the basis that the application does a lot of optimisation (parsing) of unique SQL statements and the target was to disable various adaptive/feedback features of the optimizer that might have been using too much CPU - it's also possible that in later versions of Oracle the older bad parameter setting were the cause of the optimizer trying to hard to find that best path when ALL the paths were "low-cost".

There are 3 parameters I would worry about:

_buffered_publisher_flow_control_threshold _capture_publisher_flow_control_threshold optimizer_secure_view_merging
The first two because of the comment made by Lok P" about streams and desupport.
The last one because there's a very small chance that it has to be set to make some part of the application behave properly.

I am also faintly amused by the fact that you have query_rewrite_enabled=FALSE
_enable_query_rewrite_on_remote_objs = true

Wondering whether you could get "local" query rewrite working by query the local database through loopback links. (Again, though, the setting for local query rewrite may have been set to reduce the time spent in optimisation).

Bottom line - you probably ought to leave most of the parameters to default, but check up on argumants for the three I highlighted. You should remember, however that there is an "EXADATA" option for you system statistics (which basically says - "tablescans are very fast"); and you should check the effects of setting (globally, or for specific tables) the object statistics preference "table_cached_blocks" to correct the optimizer impression of how effects some indexes will be.

As far as your final question about nls_sort is concerned - I, too, am a little puzzled. "Binary" is the default nls_sort if not otherwise specified. I think I'd check the manuals and MOS to see if they say anything about the client O/S setting (e.g. region, language) being able to override the default nls_sort setting.

Regards
Jonathan Lewis

On Tue, 20 Apr 2021 at 12:05, 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