Re: Significance of specific database parameter difference

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
>

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