Re: Query Performance with params

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 following.

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

1. what part (SQL statement usually ) of the process was taking the time and
2. 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 http://www.oracle.com/technetwork/database/manageability/owp-sql-monitoring-128746.pdf 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_at_gmail.com> 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.
>
> --
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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