Create an Oracle SQL TUNING TASK manually

 Create an Oracle SQL TUNING TASK manually with the sql_id 

You can create an SQL TUNING TASK manually ad hoc with the following simple steps. ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.

 In case this is a current sql running use the v$session.

 select sql_id from v$session where sid = :x

 1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task: ===========================================================================

SET SERVEROUTPUT ON
declare 
stmt_task VARCHAR2(40); 
begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); 
end; / =========================================================================== task_id: TASK_69287 

2. Run the SQL TUNING TASK =========================================================================== begin 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287'); 
end; 
/ =========================================================================== == 3. You can monitor the processing of the tuning task with the statement

 SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_ 69287';

 4. When the task has a status=COMPLETED, then run: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual; 

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile. begin DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE); end; / 

6. You can check the database sql profiles with the statement:

 select * from dba_sql_profiles; 

In case you want to disable an sql profile use the statement:

 begin
 DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED'); 
end; 
/

Query is running slow for long time for SQL_ID 
Query is running slow for long time for SQL_ID captured 

Run SQL Tuning Adviosor for the sql_id
------------------------------------- 

Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID

 So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID. 

Recommendations from sqltrpt.sql will be providing the best Explain Plan. 

We can implement these profiles/index rebuild/... suggested after checking with the SME of the database. 

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql 

$sqlplus "/as sysdba" 

Query to see current running sqls 

set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v 
$sqlarea c
 where b.sql_id=c.sql_id 
/ 

Run SQL Tuning Advisor for the SQL_ID

SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:

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

SQL> execute dbms_sqltune.accept_sql_profile(task_name => '',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE); 

If successful, you should see the following:

PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:

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

SQL> execute dbms_sqltune.drop_sql_profile(''); 

If successful, you should see the following: PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; 

Command to Alter SQL Profile in Oracle for the SQL_ID:-

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

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ('','STATUS','DISABLED');

 If successful, you should see the following: PL/SQL procedure successfully completed. 

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; If you don't know the name of the SQL Profile then use the below query 

SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT% TABLE%NAME%';

 Query
---- 

The SQL_ID is not stored with the profiles. You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%' and sql_id like nvl('&sql_id',sql_id) and sql_profile like nvl('&sql_profile_name',sql_profile) and sql_profile is not null order by 1, 2, 3 /