In
general application team come to DBA’s and complaint on one application query,
last time or previous times it was done in few seconds now it is taking 1
minute or more time. For this issue no concreate answer. Every DBA has their
own approaches to solve the issue.
Step:1
Find the long running query:
Long
running session are currently running, then we can use v$session find the long
running query sql_id, using sql_id get the sql using v$sqltext view.
Sql>select
sql_id from v$session where sid=1234;
Sql_id
btdzd9ktsa55n
sql>
select sql_text from v$sqltext where sql_id=’ btdzd9ktsa55n’ order by piece;
Sql_text
select
name, accno, htown, amt, transactions from abd.transact where mobile_no=’9933445599’;
If
in-case query already completed, then we can find the sql_id from AWR, ASH and
dba_hist_active_session_history views on specific time interval.
Step2:
1. Run the Sql_Tuning_advisor
for the sql_id:
SET
SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql_tune_task_id :=
DBMS_SQLTUNE.create_tuning_task (
sql_id => 'btdzd9ktsa55n',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'btdzd9ktsa55n_tuning',
description =>
'Tuning for btdzd9ktsa55n');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' ||
l_sql_tune_task_id);
END;
/
2. Execute the DBMS_SQLTUNE
using task_name
EXEC
DBMS_SQLTUNE.execute_tuning_task(task_name => 'btdzd9ktsa55n_tuning');
3. Once tuning task done,
execute report tuning task.
SELECT
DBMS_SQLTUNE.report_tuning_task('btdzd9ktsa55n_tuning') FROM dual;
It gives us few recommendations have to take the actions
accordingly.
Recommendations
could be gather statistics, creating indexes, drop indexes……..
Step 3:
Few
time we didn’t get any recommendations from the Sql_tune, then that time we
have to verify is there any plan changed for the query using dba_sqlstat, dba_hist_snapshot
and DBA_HIST_SQL_PLAN.
select
distinct b.BEGIN_INTERVAL_TIME as Snap, a.PLAN_HASH_VALUE as plan,
a.EXECUTIONS_DELTA as EXECUTIONS, a.ELAPSED_TIME_DELTA/1000000 as ELAPSED_SEC,
ROWS_PROCESSED_DELTA as "ROWS" , a.ROWS_PROCESSED_DELTA/CASE WHEN
a.EXECUTION_DELTA = 0 THEN -1 ELSE a.EXECUTIONS_DELTA END "Avg Rows",
a.ELAPSED_TIME_DELTA/1000000/CASE WHEN a. EXECUTION_DELTA = 0 THEN -1 ELSE
a.EXECUTION_DELTA END "Avg Elapsed", a.optimizer_cost, a.SQL_PROFILE
from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPHOT b where a.SQL_ID = '&sqlid' and
a.snap_id = b.snap_id order by b.BEGIN_INTERVAL_TIME;
or
SELECT
DISTINCT sql_id, plan_hash_value
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 - &No_Days) AND SYSDATE
) s
WHERE
q.snap_id BETWEEN s.min_snap AND s.max_snap
AND q.sql_id IN ( '&SQLID')
/
Above
queries gives us sql paln from number of days, from the list we can choose the
best plan and pin the best plan.
How
to pin the sql plan: