Performance and Tuning

Performance and Tuning 

If  a query started working slow in database. One can take below major steps to get that fixed .

1.) History of base plan  : 

  check the time query took last time for completion in database.

History of SQL_ID

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','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3

/

Operational Cost of SQL 

select SQL_ID,PLAN_HASH_VALUE, OPERATION, COST, TIMESTAMP from DBA_HIST_SQL_PLAN where SQL_ID='dup309y4j68mk'

2.)Elapsed time of SQL_ID

select t.sql_id,

    t.sql_text,

    s.executions_total,

    s.elapsed_time_total

from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t

Where t.sql_id='dup309y4j68mk'

and rownum < 50;

  --

ELAPSED_TIME_TOTAL

NUMBER

Cumulative value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching

EXECUTIONS_TOTAL

NUMBER

Cumulative number of executions that took place on this object since it was brought into the library cache

3.)Query to check if the execution plan is changed for a SQL_ID

SELECT DISTINCT plan_hash_value, TIMESTAMP, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI') 

FROM dba_hist_sql_plan WHERE sql_id=&s ORDER BY TIMESTAMP;

SELECT  q.plan_hash_value,q.sql_id

FROM dba_hist_sqlstat q,

    (

    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap

    FROM dba_hist_snapshot ss

    WHERE ss.begin_interval_time BETWEEN (SYSDATE - 3) AND SYSDATE

    ) s

WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap

  AND q.sql_id IN ('532htu0231uv6')

  AND q.plan_hash_value  in

('1819099645')

/

4.)  To get execution plan of a sql_id/Current running SQL

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dup309y4j68mk'));

9i
 1.) Install the explain table.
     $ORACLE_HOME/rdbms/admin/utlxplan.sql

 2.) explain plan for 

      2 <select query >

 3.) select * from table(dbms_xplan.display)

Session tracing

SQL> set autotrace traceonly explain
SQL> select ename from emp where empno = 12;

5.) Enable session level tracing for user and get tkprof.

SQL> exec sys.dbms_system.set_sql_trace_in_session( sid, serial#, TRUE );

SQL> exec sys.dbms_system.set_sql_trace_in_session( 25, 8932, TRUE );

SQL> show parameter dump  -- To get the trace location ( udump)

SQL> exec sys.dbms_system.set_sql_trace_in_session( 30, 7157,FALSE);

tkprof cart_ora_8441978.trc  cart_ora_8441978.out  explain=system/CH33TAH

  Set awr retion to 32 days

select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                            RETENTION                                TOPNSQL

---------- ---------------------------------------- ---------------------------------------- ----------

   4718128 +00000 01:00:00.0                        +00032 00:00:00.0                        DEFAULT

32 days retention set

exec dbms_workload_repository.modify_snapshot_settings(retention=>46080);  mins