Performance issue for single query oracle database(handy during P1 call)

1. To identify the problematic sql_id which is having issue mentioned by the customer and currently running.


select distinct sql_id from gv$session where status='ACTIVE' and type <> 'BACKGROUND' and sql_id in (SELECT sql_id FROM   gv$sql WHERE  sql_text LIKE '%< the user executing the query>%' AND sql_text NOT LIKE '%gv$sql%');

2.Identify what type of wait event it is giving.

select sid,username,event,state,seconds_in_wait,wait_time from v$session where wait_class != 'Idle' and type <>'BACKGROUND' and sql_id='SQL_ID';

select

    event,

    time_waited "time_waited(s)",

    case when time_waited = 0 then 

        0

    else

        round(time_waited*100 / sum(time_waited) Over(), 2)

    end "percentage"

from

    (

        select event, sum(time_waited) time_waited

        from v$active_session_history

        where sql_id = ''

        group by event

    )

order by

    time_waited desc;

-To identify if the sql_id having multiple plans

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number 

and executions_delta > 0

order by 1, 2, 3

/

Based on the wait events, the necessary action can be taken.

3.Generate the explain plan to understand more about the query

set line 2000 pages 20000

set long 9999999

select plan_table_output from table (dbms_xplan.display_cursor('&sql_id',null,'ALL')); --current plan

SELECT PLAN_TABLE_OUTPUT FROM table(dbms_xplan.display_awr('&sql_id,null,null,'ADVANCE')); --All plans

select * from table(dbms_xplan.display_awr('&SQL_ID'));--AWR report

4. SQL tunning Advisor

 a)Create 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      => '<sql_id you received>',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '<Any task name you wish>',

                          description => 'Tuning task1 for statement 26axvt63chuu6');

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

END;

/

 b) Execute Tuning task:

==========================

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '26axvt63chuu6_tuning_task11'); 

c) Verify the task completed or not

=================================

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME LIKE '%26axvt63chuu6_tuning_task11%';

d) Get the Tuning advisor report.

=================================

set long 65536

set longchunksize 65536

set linesize 100

SELECT DBMS_SQLTUNE.report_tuning_task('<26axvt63chuu6_tuning_task11>') AS recommendations FROM dual;

e)Accept the new profile for future use

=======================================

EXECUTE DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(

          TASK_NAME => '26axvt63chuu6_tuning_task11',

          TASK_OWNER=> 'RAPDWH',

          REPLACE=> TRUE,

          PROFILE_TYPE => DBMS_SQLTUNE.PX_PROFILE);

f)Get list of tuning task present in database:

=========================================

 SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

 g)Drop a tuning task:

 ========================================

execute dbms_sqltune.drop_tuning_task('26axvt63chuu6_tuning_task11');