Re: Query Performance with params

  • From: Thomas Roach <troach@xxxxxxxxx>
  • To: gogala.mladen@xxxxxxxxx
  • Date: Sun, 30 Apr 2017 12:42:37 -0400

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 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:
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   

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

Other related posts: