Tag Archives: optimizer_index_caching

Instance Parameter as hints for SQL Queries in Oracle

Instance Parameter used in hints for particular SQL

Suppose you want to change and test the SQL query by changing the instance level parameters with ALTER SESSION command.
You find that particular query is working fine when parameter is changed but you can not change the parameter at system level in production because you don’t know the impact to other queries.

So you can use the following undocumented parameter for that particular query for increase the performance by OPT_PARAM parameter as a HINT in SQL Query.

You can change the following session parameter as hints for particular query:

Alter session set optimizer_features_enable = '';
Alter session set optimizer_index_caching=20;
Alter session set optimizer_index_cost_adj=20;
alter session set _optimizer_cost_model = 'io';
alter session set optimizer_mode = first_rows_10;


select /*+ opt_param('optimizer_mode','first_rows_10') */
select /*+ opt_param('_optimizer_cost_model','io') */
select /*+ opt_param('optimizer_index_cost_adj',20) */
select /*+ opt_param('optimizer_index_caching',20) */
select /*+ opt_param('optimizer_features_enable','')*/

Example for using the parameter as Hints in SQL query as follows:
select /*+ opt_param('optimizer_mode','first_rows_10') */ from fin.tran where entity_no = '10044' and data_transaction=sysdate;

You can use it with another method as follows:

SELECT /*+ optimizer_features_enable('10.2') */ * FROM EMP JOIN DEPT USING(DEPTNO);