Ram,
Can you share a SQLMonitor report of one of the statements that's not
performing well?
Toon
On Sat, Apr 29, 2017 at 9:23 PM, Ram Raman <veeeraman@xxxxxxxxx> wrote:
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.
--