How to run SQL Tuning Advisor in Oracle

Steps to Run SQL Tuning Advisor Against Slow Running Queries in Oracle

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

STEP 1: Create tuning task for the specific SQL_ID:

declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
end;
/

NOTE: Replace sql_id in above statement


STEP 2: Check the status of newly created task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

STEP 3: Execute the newly created task:

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Note: Please replace the task name as mentioned in step 1

STEP 4: Check the status after executing the task:

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

*Task should be completed, it will take some time to complete.

STEP 5: Execute the Below Query to get the Advisory Report:


SET LONG 99999;
SET PAGESIZE 1000
SET LONGCHUNKSIZE 65536
SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;


NOTE: Replace task name in above query

STEP 6: To Drop the Tuning Task:

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');


To execute tuning advisory using AWR snap ID if sql_id is not present in cursor:


STEP 7: Find the snap ID using below query:

SQL>
SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&SQL_ID';

SQL>
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID' and plan_hash_value='&plan_hash_value' order by snap_id desc;



STEP 8: Create Tuning Task:

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 841,
end_snap => 891,
sql_id => '663zvxqjuxtqn',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '663zvxqjuxtqn_AWR_tuning_task',
description => 'Tuning task for statement 663zvxqjuxtqn in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

--NOTE: Replace the above highlighted values:



STEP 9: After creation of tuning task you can follow the above steps 

Monitor job progress :-

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS;

How to Identify SID Based on OS PID in Oracle

How to Identify SID Based on OS PID in Oracle

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

Query to check SID from PID (get from top command )

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

set lines 200 pages 500

col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'

order by p.spid;

Purge AWR Snapshots in oracle

Purge AWR Snapshots

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

• Get the snapshot details  : 

SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

• purge snapshot by given range :

First Method:

exec dbms_workload_repository.drop_snapshot_range(881,991);



Second Method:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id  => 881,
high_snap_id => 991);
END;
/  

Get the client IP address from a session in oracle

Get the client IP address from a session in oracle .  There are two way to get IP

•  SYS_CONTEXT('USERENV','IP_ADDRESS')
•  UTL_INADDR.GET_HOST_ADDRESS

Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE,
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP,
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;