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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.