Category Archives: profiling

Manual Creation of SQL Profile

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,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,
TO_CHAR (INVOICE_DT, ‘YYYY-MM-DD’),
VENDOR_NAME_SHORT,
VENDOR_ID,
NAME1,
VOUCHER_STYLE,
VOUCHER_ID_RELATED,
ENTRY_STATUS_SRH,
VCHR_SRC
FROM PS_VOUCHER_SRCH_VW
WHERE BUSINESS_UNIT = ‘COMP1’ AND VOUCHER_ID LIKE ‘123456%’
ORDER BY BUSINESS_UNIT, VOUCHER_ID DESC ;

Lets Begin:

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,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,%”

Once, we have the sqlid we can create tuning task as follow:

declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘7u4t0k75bavcp’);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/

This will give a task ID, in my case it was “TASK_87241”

Next, We execute the task as follow:

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_87241’);
end;
/

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:

DECLARE
l_sql_tune_task_id  VARCHAR2(200);
BEGIN
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);
END;
/

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.

 

Thanks.

Advertisements