Oracle Performance: How to Create a Sql Profile
Follow these Simple Instructions
I use a template to prepare new sql profiles for use. Here are the steps:
Define the Tuning Task
DECLARE
l_sql_tune_task_idVARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '8nv4vhya2dt60',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit=> 500,
task_name => 't60',
description => 't60');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
NOW RUN THE TUNING TASK
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 't60' );
END;
/
GET RECOMMENDATIONS
SET LONG 99999
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 't60')
FROM DUAL;
Apply the Profile
execute dbms_sqltune.accept_sql_profile(task_name => 't60', task_owner => 'CXLY', replace => TRUE);
Notes:
Of course, there is no guarantee that the Oracle Tuning Set analysis will suggest a profile. Oftentimes, the advisor will suggest an index, or better stats.
Also, be careful that you don't accidentally activate a PX (Parallel) profile. If you do, you might find hundreds of parallel processes suddently appearing!
