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