Re: Query Performance with params

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


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