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
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.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Ram Raman <veeeraman@xxxxxxxxx>
Sent: 29 April 2017 19:23
Subject: Query Performance with params
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.