SQL Profiling is required when there are multiple execution plan available in the database and there is a need to use or better performance in one of the execution plan. Now, it is not guaranteed that CBO will always use the same plan hence there is a need of SQL Profiling. In SQL Profiling we lock the execution plan which we and CBO feels the optimal performance can be achieved.
For Test, I will be taking following query from PeopleSoft Application.
SELECT DISTINCT BUSINESS_UNIT,
TO_CHAR (INVOICE_DT, ‘YYYY-MM-DD’),
WHERE BUSINESS_UNIT = ‘COMP1’ AND VOUCHER_ID LIKE ‘123456%’
ORDER BY BUSINESS_UNIT, VOUCHER_ID DESC ;
First, We have to create tuning task.
For Tuning task we need to have the sql id for the sql and then create tuning task, to get sqlid we can use following:
select * from v$sql where sql_text like “SELECT DISTINCT BUSINESS_UNIT,
Once, we have the sqlid we can create tuning task as follow:
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘7u4t0k75bavcp’);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
This will give a task ID, in my case it was “TASK_87241”
Next, We execute the task as follow:
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_87241’);
Once task is executed, we have the recommendation from CBO. Which can be queried as follow:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => ‘TASK_87241’) from dual;
If you are satisfied with the sql profile recommended by the CBO then you can accept it as follow:
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => ‘TASK_87204’,
name => ‘TEST_PROFILE’,
force_match => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
If you are not satisfied with the SQL profile then try changing the session parameter and try running the above steps again, optimizer should provide different results.
Leave a comment if you found anything which can be changed or not up to the mark. There are multiple ways to do this, this is just one way. In other blog I will be taking through the process of locking down the SQL profile in one database and then copy the profile to other database.