performance issues

STEPS OF HANDALING PERFORMACE ISSUES

1.       Check the current plan of execution  for the sql_id

Select inst_id,sql_id,parsing_schema_name, PLAN_HASH_VALUE, executions, elapsed_time,sql_profile,(elapsed_time/executions)/1000000 as avg_elapsed, sql_plan_baseline from gv$sqlarea where sql_id in ('sql_id');

If the current plan is best from all the plans available in the history baseline the plan so that the sql_id will pick the plan further.

2.       If the plan is having more average elapsed time check the history of the sql_id for any better plan.

SELECT snap_id,

       (select  begin_interval_time from dba_hist_snapshot where instance_number=a.instance_number and snap_id=a.snap_id) begin_time,

       (select  end_interval_time from dba_hist_snapshot where instance_number=a.instance_number and snap_id=a.snap_id) end_time,

instance_number,

parsing_Schema_name,

        sql_id,

plan_hash_value,

sql_profile,

executions_delta,

        (elapsed_time_delta / decode(executions_delta,0,1,executions_delta)) msecs,

        ROUND ( (elapsed_time_delta / decode(executions_delta,0,1,executions_delta)) / 1000000,

per_elapsed_sec,

          ROUND ( (rows_processed_delta / decode(executions_delta,0,1,executions_delta)) , 2)

rows_processed_per_execution,

        ROUND ( (buffer_gets_delta / decode(executions_delta,0,1,executions_delta)))

buffer_gets_per_executions ,

            ROUND ( (disk_Reads_delta / decode(executions_delta,0,1,executions_delta)))

disk_Reads_per_executions

   FROM dba_hist_sqlstat a

  WHERE sql_id in  ('sql_id')  and parsing_schema_name='schema name'

3.       To check the plans available in awr

select parsing_schema_name,sql_id, PLAN_HASH_VALUE, executions_delta, elapsed_time_delta, (decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000 as avg_elapsed

fromdba_hist_sqlstat  where EXECUTIONS_DELTA>0 and  sql_id='&SQL_ID' and parsing_schema_name='&schema_name' order by avg_elapsed desc;

4.       If found any better plan in Cursor Cache  baseline the plan with the best available plan in awr

set serveroutput on

declare

v_intpls_integer;

begin

v_int := dbms_spm.load_plans_from_cursor_cache (

sql_id => 'SQL_ID',

plan_hash_value =>'BEST PLAN HASHBVALUE',

fixed => 'YES',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(v_int);

end;

/

If found in AWR, baseline the plan with following commands:

Replace SQLID with actual sql_id

BEGIN

    DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'STS_SQLID',description => 'For Sql id xxxxxx');

  END;

 /

Find the snapshot from dba_hist_sqlstat with good PHV and put in the following command

DECLARE

l_cursorDBMS_SQLTUNE.sqlset_cursor;

   BEGIN

     OPEN l_cursor FOR

        SELECT VALUE (a)  FROM   TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (snapshotid_begin,snapshotid_end,'sql_id= ''SQLID''',   null,null,null,null,1,null,'ALL' )) a; 

DBMS_SQLTUNE.load_sqlset (sqlset_name  => 'STS_SQLID', populate_cursor =>l_cursor);

  END;

/

set serveroutput on

DECLARE sts_load PLS_INTEGER;

BEGIN

sts_load := DBMS_SPM.load_plans_from_sqlset

( sqlset_name => 'STS_SQLID',

basic_filter => 'sql_id=''SQLID''',                            

fixed  => 'YES',

    enabled => 'YES'

  );

DBMS_OUTPUT.put_line('Plans Loaded: ' || sts_load );

END;

/

5.       After base lining with the best plan, purge the old plan, profile available.

To drop SQL Profile:

execdbms_sqltune.drop_sql_profile('SYS_SQLPROF_015fe8de27e80007');

To drop SQL Plan Baseline:

Pass the baseline name in below query and run it to generate the command. Run the drop command to drop baseline.

      select 'declare '||chr(10)||' v_sql_plan_idpls_integer;'||chr(10)||' begin '||chr(10)||' v_sql_plan_id := dbms_spm.drop_sql_plan_baseline(sql_handle => '''||SQL_HANDLE||''',plan_name =>'''||PLAN_NAME||''');end;'||chr(10)||'/' from dba_sql_plan_baselines  where plan_name=’&sql_baseline_name’;  

or

Select sql_handle and plan_name from dba_sql_plan_baselines and drop the baseline accordingly based on the following commands:

selectsql_handle,plan_name,CREATED,enabled from dba_sql_plan_baselines order by created desc;

where plan_name='SQL_PLAN_gv3kg06wn2g2h73ab7ff8';

set serveroutput on

declare

v_intpls_integer;

BEGIN

v_int := DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle => 'SQL_fd8e4f01b9413c50',plan_name => 'SQL_PLAN_gv3kg06wn2g2h3b514d4e');

END;

/

Query to purge from shared pool: 

Run below statement on each node, pass the SQL_ID to get the command. Run that command to purge the SQL_ID.

  select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''',''C'');' from V$SQLAREA where SQL_ID='&SQL_ID';

  exec DBMS_SHARED_POOL.PURGE ('000000323D9B99F8, 1954855109','C');

or

selectinst_id,address,hash_value,PLAN_HASH_VALUE from gv$sqlarea where SQL_ID='SQL_ID';

from the output choose address and hash value and run the following command:

execdbms_shared_pool.purge('address from above query, hash value from above query ','C');

6.       Run the tuning adviser for any recommendations

@?/rdbms/admin/sqltrpt.sql

Plug the profile if found any recommendations with minimum benefit of 50 %

Or

if we know a particular sqlid, this will give a proper format:

run from sqlplus :

set lines 300

set trims on

set trim on

set linesize 200

set pages 2000

set long  10000000 longchunksize 100000;

variable task_name varchar2(30);

begin

:task_name := dbms_sqltune.create_tuning_task(sql_id=> '&SQL_ID');

dbms_sqltune.execute_tuning_task(task_name => :task_name);

end;

/

Spool sql_id_provided.log

selectdbms_sqltune.report_tuning_task(:task_name) as recommendations from dual;

spool off

NOTE: make sure you are NOT plugging the parallel profile (_PX)

Gather the stats check for the index recommendation from adviser.

If found any stats recommendation gather the stats on the tables

BEGIN

   DBMS_STATS.GATHER_TABLE_STATS (

       OWNNAME            => 'Schema name',

      TABNAME            => 'Table name'  ,

            ESTIMATE_PERCENT   =>dbms_stats.auto_sample_size,

   METHOD_OPT         =>‘FOR ALL COLUMNS SIZE AUTO’,

   DEGREE             => 24,

  GRANULARITY        => 'ALL',

    CASCADE            => TRUE,

  BLOCK_SAMPLE       => TRUE,

   NO_INVALIDATE      => FALSE

  );

END;

/

7.       To use Coe profile

cd /home/oracle/scripts/sqlt/utl

sqlplus / as sysdba

SQL>start coe_xfr_sql_profile.sql 0y9dt1vwmm7uq 688265959

then choose the best plan_hash_vlaue

It will create a .sql file using the sql_id in current directory

run that created .sql file to create the profile

EXIT

ls -lrt

sqlplus / as sysdba

SQL> @sql_script.sql

8.       Check the OEM

Find the top sql_id with high CPU usage and tune the sql_id having high CPU usage with available best plan

9.       Find the relevant or similar sql_id running on different clients and check

select * from TABLE(dbms_xplan.display_awr('SQL_ID'));

and for plan from cursor cache 

select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));

IF Sql_id exists in any other Client and see the plan used in that database, compare the plans, and see elapsed time and find any missing indexes in the current client and recommend based on that.