Re: Query Performance with params

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Sat, 29 Apr 2017 19:56:52 +0000
Message-ID: <VI1PR0901MB14370FCB0DD382CC29A4E7A9A1120_at_VI1PR0901MB1437.eurprd09.prod.outlook.com>



Fiddling with OIC/OICA was debunked as an acceptable tuning methodology about a decade ago possibly longer...

Apart from anything else, the scope of your "solution" does not match the scope of your problem.

Even if you mess with these parameters at a session level, you're still messing something which potentially changes the costings for all the tables within a SQL statement.

It would not be surprising, if you delve deep into the statement which have a problem, that you will find one or two specific issues which affect one or two specific tables/columns/access patterns and which have specific solutions.

Cheers,

Dominic



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ram Raman <veeeraman_at_gmail.com> Sent: 29 April 2017 19:23
To: ORACLE-L
Subject: Query Performance with params

List,

We have a 12c db that runs an ERP app. We face slowness with some processes - Upon analysis we created some indexes and re ran the processes. A few of the processes would not complete - the optimizer would not use the index. We adjusted the OIC/OICA (idxCachg/idxCostAdj) - with new values several of the reports completed. However some other processes do not complete even after an hour. But they complete ok with the older values of oic/oica. Many of the SQLs are generated dynamically, so using hints is not an option.

It looks like we can work with adjusting the values of oic/oica for different processes. However many of these users could run the processes at the same time. My limited research shows that oic/oica have an effect on the access paths and eventually the cost of the query; read wolfgang's paper on the subject. I am sure I am not the only person who faced this problem. Any ideas on how to approach the issue? Thanks a lot.

Ram.

--

--

http://www.freelists.org/webpage/oracle-l Received on Sat Apr 29 2017 - 21:56:52 CEST

Original text of this message