Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Use bind variables instead of literals with force match in SQL Profile generated from SQL Tuning advisory

Use bind variables instead of literals with force match in SQL Profile generated from SQL Tuning advisory

We can generate the SQL Profile with help of SQL Tuning advisory. But ours SQL are using the literals value then SQL Profile is worked only for that same literal value as Default.

Following are the default working example of SQL Profile with SQL Tuning for literal query
If you run SQL Tuning Advisor for the Query and it suggest to create SQL Profile then it only work for that literal Values.
1. We run the following query in SQL tuning advisory

select * from employees where employee_id = '1423' and employee_name = 'Ramesh';

2. In SQL tuning advisory report we get suggestion to create a profile for that query

execute dbms_sqltune.accept_sql_profile(task_name =>'sqltuningtask1', replace => TRUE);

3. We tested the execution plan for this query after running 2 step. It start using the SQL Profile.

Note
-----
- SQL profile "SYS_SQLPROF_127bd4a237793200" used for this statement

4. We change the literal value of the query but it doesnot show us using the sql profile in execution plan.

select * from employees where employee_id = '1675' and employee_name = 'Gaurav';
--No SqL Profile

Solution
Instead of running the Same accept query, we should add the force_match parameter for using bind variable instead of literal values for that query:

execute dbms_sqltune.accept_sql_profile(task_name =>'sqltuningtask1', replace => TRUE, force_match=>true););