Re: Query Performance with params

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: Vee Aar <veeeraman@xxxxxxxxx>
  • Date: Mon, 1 May 2017 14:46:37 +0100

Hi Ram

Is this a COTS ERP app? If so then it would be helpful to state which -
there are people on this list that know for example SAP, EBiz and
Peoplesoft really well. More to the point most ERP apps have specific
interfaces to enable you to implement performance tuning enhancements, even
ones as simple as adding an index.

I see there's been lots of feedback about your approach. I'd highlight the

1. Your problems are process scoped - but your solutions are system wide.
That means you risk destabilising currently good parts of the system with
your approach. I think that is in fact what you describe with the comment
"but they complete OK with the older values of OIC/OICA"

Your best bet, prior to 11, would be to enable tracing for the problematic
process and then run the resulting trace files through a profiler. This
would tell you

a) what part (SQL statement usually ) of the process was taking the time and
b) what it was spending its time doing.

You could then attack that particular SQL statement via a number of
approaches depending on what the issue was. Common approaches would be

   - Ensuring statistics were representative so that the optimizer produced
   good estimates of the number of rows to be returned/operated on at each
   stage of the plan execution
   - Ensuring the plan reduced data volumes early in the execution pipeline
   where possible - maybe by index creation and/or predicate data type
   changes; maybe by fixing join conditions etc.
   - Baselining a known good plan if you had one.
   - Applying judicious hints to the SQL as a workaround.

As of 11gR2, and importantly if you have the diagnostics pack and tuning
packs licensed, then the SQL tuning report
allows much of this data to be presented in real time (or after sql
execution) and/or saved and distributed for further analysis - you might
get some help from this list and you might also be able to get help from
either the ERP vendor or Oracle with a saved sql monitoring report.

On Sat, Apr 29, 2017 at 8:23 PM, Ram Raman <veeeraman@xxxxxxxxx> wrote:


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.



Niall Litchfield
Oracle DBA

Other related posts: