Performance Tuning

Performance Tuning Guide-

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;

 

How to Tune Segment if ADDM report reported that segment is responsible for significant “User I/O”

Example:

Run "Segment Advisor" on TABLE "AR.AR_PAYMENT_SCHEDULES_ALL" with object ID 28886.

     

Step 1: Creating the tuning task and Execute.

variable id number;

begin

  declare

  name varchar2(100);

  descr varchar2(500);

  obj_id number;

  begin

  name:='';

  descr:='Segment Advisor Example';

  dbms_advisor.create_task (

    advisor_name     => 'Segment Advisor',

    task_id          => :id,

    task_name        => name,

    task_desc        => descr);

  dbms_advisor.create_object (

    task_name        => name,

    object_type      => 'TABLE',

    attr1            => '',

    attr2            => '',

    attr3            => NULL,

    attr4            => NULL,

    attr5            => NULL,

    object_id        => obj_id);

  dbms_advisor.set_task_parameter(

    task_name        => name,

    parameter        => 'recommend_all',

    value            => 'TRUE');

  dbms_advisor.execute_task(name);

  end;

end;

/

Step 2: Check Task status.

select task_name, status from dba_advisor_tasks where owner = 'SYS' and advisor_name = 'Segment Advisor' and task_name='';

Step 3: Displaying the recommendations

select af.more_info, af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

from dba_advisor_findings af, dba_advisor_objects ao

where ao.task_id = af.task_id

and ao.object_id = af.object_id

and ao.owner = 'SYS' and af.task_name='';

Step 4: If advisory show message:

The free space in the object is less than the size of the last extent.

Then follow bellow Action Plan:

A)    Take the backup of objects

B)     Backup the scripts by using TOAD

C)    Check Function based index on objects if exist the copy scripts.

D)    After copy scripts, drop the function based index on objects

E)     Enable row movement on objects

SQL> alter table . enable row movement;

F)     Shrink the space

SQL> alter table .

shrink space;

G)    Create the function based index on table by executing copied create index scripts.