If it's 12.1, then if you are going to set any parameter outside of defaults,
it would be optimizer_features_enable back to 11.2.0.4. That's usually when
people throw in the towel if they can't figure it out otherwise.
Good Luck.
Sent from my iPhone
On Apr 30, 2017, at 12:30 AM, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
On 04/29/2017 03:23 PM, Ram Raman 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.
Please DO NOT USE OPTIMIZER_INDEX_COST_ADJUST! You have system statistics
which defines multiple block read count and time, as well as single block
read time. The "OPTIMIZER_INDEX_COST_ADJUST" is a crude tool which should
have been obsoleted by Oracle a long time ago. Optimizer index caching is OK
as it provides information that is not normally available: how much of the
index is cached. The caching parameter sets the value across the board, which
can lead to wrong plans with extremely large indexes. I am not that eager to
set it on large databases. What optimizer index cost adjust parameter is set
the ratio between SREADTIM and MREADTIM in the following table:
SQL> set sqlformat ansiconsole
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 04-30-2017 00:14
SYSSTATS_INFO DSTOP 04-30-2017 00:16
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2923.27044025157
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 0.965
SYSSTATS_MAIN MREADTIM 2.687
SYSSTATS_MAIN CPUSPEED 3846
SYSSTATS_MAIN MBRC 8
SYSSTATS_MAIN MAXTHR 11572224
SYSSTATS_MAIN SLAVETHR
13 rows selected.
Elapsed: 00:00:00.280
However, this doesn't do anything about MBRC, CPUSPEED or IOTFRSPEED, which
all play roles in the CBO calculations. In my particular case, setting OICA
to 50 would give the same result as setting the SREADTIM to 2.687/2, which is
larger than the current value and would actually increase the cost of using
indexes, which is probably not what you want. A good way to cheat is to set
multiblock read count to something bigger than 8:
SQL> show parameter db_file_multi
NAME TYPE VALUE
----------------------------- ------- -----
db_file_multiblock_read_count integer 128
SQL>
The optimizer will still use MBRC 8 as an estimate for the cost of full table
scan, but the server process will actually read 1MB, if available. The "OICA"
parameter is harmful, amateurish cheating which will almost never give the
desired result.
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
