Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

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