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 donot know the impact to other queries.

So you can use the following undocumented parameter for that particular query for increase the perfomrance 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 = '11.2.0.4';
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;

as:

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','11.2.0.4')*/

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);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s