Digging SQL performance issue by using SQL Advisor

Step 1: Find CPU Load average history

cd /var/log/sa
sar -q -f sa10 | less

Step 2: Generate ADDM report.

@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Step 3: Find SQL ID which is using high CPU

Step 4: creating the tuning task

set serveroutput on

declare

  l_sql_tune_task_id  varchar2(100);

begin

  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

                          sql_id      => '',

                          scope       => dbms_sqltune.scope_comprehensive,

                          time_limit  => 7200,

                          task_name   => '',

                          description => 'tuning task for statement your_sql_id.');

  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/

Step 5: Executing the tuning task

exec dbms_sqltune.execute_tuning_task(task_name => '

Step 6: Displaying the recommendations

set long 100000;

set longchunksize 1000

set pagesize 10000

set linesize 100

select dbms_sqltune.report_tuning_task('') as recommendations from dual;