Re: Query Performance with params

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sun, 30 Apr 2017 00:30:48 -0400

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

Other related posts: