Re: Query Performance with params
Date: Sat, 29 Apr 2017 19:56:52 +0000
Message-ID: <VI1PR0901MB14370FCB0DD382CC29A4E7A9A1120_at_VI1PR0901MB1437.eurprd09.prod.outlook.com>
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