Running SQL tuning advisory

Running SQL tuning advisor
========================================================================
set long 65536
set longchunksize 65536
set linesize 100
set pages 255
set lines 1000

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'ftt3txfr7qxwu',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 600,
                          task_name   => 'ftt3txfr7qxwu_TASK_NAME_1',
                          description => 'Tuning task for statement ftt3txfr7qxwu');
END;

---------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'ftt3txfr7qxwu_TASK_NAME_1');
----------------------------------------------------------------------------

SELECT DBMS_SQLTUNE.report_tuning_task('ftt3txfr7qxwu_TASK_NAME_1') AS recommendations FROM dual;

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

select table_name, stale_stats,last_analyzed from dba_tab_statistics where table_name='&1';
select owner, index_name, last_analyzed, global_stats from dba_indexes where index_name='&1';

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

select * from table(dbms_xplan.display_awr('d6xu9cxq57ant'));

select plan_table_output from table(DBMS_XPLAN.display_cursor('7s78wpgr5wadp'));

EXPLAIN PLAN FOR

SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename  = 'SMITH';

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY);

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

select INST_ID,SID from gv$session s,apps.fnd_concurrent_requests r where s.audsid=r.oracle_session_id and r.request_id=61825419;

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=1305;

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=&1;

V$SESSION_EVENT can show that many waits, but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

select module,sql_hash_value,event,action,last_call_et from v$session where sid ='&1';

select sid,SERIAL#,inst_id,status,SQL_ID,LAST_CALL_ET  from gv$session where SID in ('15654');

select SQL_TEXT from gv$sql where sql_id='&1';

select plan_table_output from table(DBMS_XPLAN.display_cursor('d6xu9cxq57ant'));

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=15654;

select SID,serial#,SQL_ID from v$session where sql_id in ('d6xu9cxq57ant');

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

execute dbms_stats.gather_table_stats(ownname => 'XX_DISCO', tabname => 'EXB_FA_DETAILS_MV', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE  AUTO', cascade => TRUE); 

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

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

Child request:

set lines 200

col USER_CONCURRENT_PROGRAM_NAME for a40

col PHASE_CODE for a10

col STATUS_CODE for a10

col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE

(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',

'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',

'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,

sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,

apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =

'&parent_concurrent_request_id';

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

alter session set current_schema=APPS;

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

Related Documents:

Doc ID 215187.1 SQLT Diagnostic Tool

Document 1614107.1 SQLT Usage Instructions

Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions

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

Killing in RAC ::::::::::

SELECT  'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where osuser = 'xxwwexp' and machine in ('ccixapp-prod1-128','ccixapp-prod2-155');

SELECT  'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where sql_id in ('az41pugxbqdha','cmndhqbj6h5dj','d6xu9cxq57ant');

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

Manager name:

select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a, 

apps.fnd_concurrent_queues_vl b, apps.fnd_concurrent_requests c

where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and    a.CONCURRENT_PROCESS_ID = c.controlling_manager

and    c.request_id ='&request_id';

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

select s.snap_id,to_char(s.begin_interval_time,'DD-MON-YYYY DY HH24:MI') sample_end

, q.sql_id

, q.plan_hash_value

, q.EXECUTIONS_DELTA "executions"

, q.VERSION_COUNT "VersionCount"

, q.INVALIDATIONS_DELTA  "Invalidation"

, round(disk_reads_delta/executions_delta) "Phy IO/Exec"

, round(buffer_gets_delta/executions_delta) "LogIO/Exec"

, round(elapsed_time_delta*0.0000001/executions_delta,2) "Elapsed_Time_In_Sec/Exec"

, round(cpu_time_delta*0.0000001/executions_delta,2) "CPU_Time_In_Sec/Exec"

, round(rows_processed_delta/executions_delta) "Rows/Exec"

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.SQL_ID=trim('&sql_id')

and s.snap_id = q.snap_id

and s.dbid = q.dbid

and s.instance_number = q.instance_number

and q.executions_delta > 0

and begin_interval_time BETWEEN (SYSDATE - nvl(to_number('&num_days'),1)) AND SYSDATE

order by q.sql_id,s.snap_id desc;

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

Accept the profile generated by the tuning task using:

EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'ftt3txfr7qxwu_TASK_NAME_1', description => 'Tuning test for sql_id ftt3txfr7qxwu');


Drop the tuning task with:

EXECUTE dbms_sqltune.drop_tuning_task('ftt3txfr7qxwu_TASK_NAME_1');

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

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
col description FOR a40
SELECT task_name, description, advisor_name, execution_start, execution_end, status FROM dba_advisor_tasks WHERE owner='TEST' ORDER BY task_id DESC;

SELECT name, created, description, status FROM dba_sql_profiles;