RE: Significance of specific database parameter difference

AND (not but). Those two particular things got folks a lot closer usability in response to a silly bad round hole not fitting into a square peg of some release of EBS having a database upgrade which I will not even pretend to remember.  

So yes, Tim is well past JL’s statute of limitations on advice, which I paraphrase: “Even if I didn’t mention the exact case considerations or whether it was OLTP or warehouse specific, and especially the release of Oracle in my notes, if it is older than the sell by date on your favorite beer, there is a good chance the advice is no longer valid.”  

Likewise for me. If it’s a note about some release of 4 or 5, there is only one that I think is still valid. Good luck guessing that one, except Graham Wood, who knows.  

More importantly, Tim’s advice to review all your non-standard settings is good strategic advice that will permanently survive.      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pot'Vin-Gorman Sent: Tuesday, April 20, 2021 3:08 PM
To: Tim Gorman
Cc: oracle.developer35_at_gmail.com; Oracle L Subject: Re: Significance of specific database parameter difference  

As soon as I saw this thread, I thought, "They are going to bring up optimizer_index_XXX and Tim is going to lose it.  

Release my husband from past transgressions people who are still foolishly messing with the parameters...he's more than paid his debt to Oracle society. LOL             

Kellyn Gorman

DBAKevlar Blog <http://dbakevlar.com>

about.me/dbakevlar      

On Tue, Apr 20, 2021 at 11:56 AM Tim Gorman <tim.evdbt_at_gmail.com> 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  

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