TroubleShooting

SQL Tuning Advisor

Prerequisite
Process
Post Changes Session
sql.current.LongRunning.sql
Error: ORA-13639

Overview

The SQL Tuning Advisor analyzes SQL statements and offers tuning recommendations.
SQL_ID used in these examples: c53k9q5pxkdam.

Prerequisite

If need be, get the SQL statement for the badly running SQL.

Process

Perform these steps on your test system first.

 1. From SQL Developer, connect to your database.

 2. Paste your SQL in a Query Builder window.

 3. Press, Ctrl-F12 to invoke the SQL Tuning Advisor.
    Process runs that analyzes your SQL using the Automatic Tuning Optimizer.

    This can take a while if your SQL statement is complex and\or
    you have a lot of data.

 4. When completed go to the detail tab and search on "Recommendation".
    It may be easier to copy-and-paste the Detail findings into an editor to search on Recommendation.

Implement Recommendations

 Recommendation (estimated benefit>=90%)
   ---------------------------------------
   - Consider accepting the recommended SQL profile.
     execute dbms_sqltune.accept_sql_profile(task_name => 'staName68515',
             task_owner => 'SYS', replace => TRUE);

 -- Perform Recommended Change(s)
 SQL> exec dbms_sqltune.accept_sql_profile(task_name => 'staName68515',task_owner => 'SYS',replace => TRUE);

Post Changes Session

All commands placed on one line when actually run.

--- SQL Profile Exist?

 select name from dba_sql_profiles;
 SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL 
 WHERE sql_id='c53k9q5pxkdam';
 It may not be visible yet.

--- Show All SQL Profiles

 SET LINESIZE 250
 SET PAGESIZE 9999
 COL name     FORMAT a30
 COL category FORMAT a15
 COL status   FORMAT a15
 COL sql_text FORMAT a35
 COL SQL      FORMAT a50
 SELECT NAME, CATEGORY, STATUS, substr(SQL_TEXT,1,35) "SQL" 
 FROM DBA_SQL_PROFILES;

--- Run Your SQL and Check

 Run your previously bad performing SQL at this point then check status as shown below:

 -- Matching Cursor Running?
 SELECT * from table(dbms_xplan.display_cursor('c53k9q5pxkdam',null,
                    'ADVANCED ALLSTATS LAST'));
 PLAN_TABLE_OUTPUT displayed...

 SELECT SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE FROM V$SQL 
 WHERE SQL_ID='c53k9q5pxkdam';
  SQL_ID        SQL_PROFILE                                 PLAN_HASH_VALUE
  ------------- ------------------------------------------- ---------------
  c53k9q5pxkdam coe_c53k9q5pxkdam_1833286388                     2793605347

 -- Check the Profile Status
 COL PLAN_HASH_VALUE FORMAT 9999999999
 COL SQL_ID          FORMAT a16
 COL SQL_PROFILE     FORMAT a30
 SELECT SQL_ID, PLAN_HASH_VALUE,SQL_PROFILE FROM V$SQL 
 WHERE SQL_ID='c53k9q5pxkdam';

 SELECT ADDRESS, HASH_VALUE from V$SQLAREA 
 WHERE SQL_ID like 'c53k9q5pxkdam';

sql.current.LongRunning.sql

Use v$sql_monitor to show current long running SQL.

-- sql.current.LongRunning.sql
-- Purpose: Show currently long running SQL.
-- -----------------------------------------------------------------------------
set pagesize 9999
set verify off

COL Elapsed         FORMAT 999999
COL sql_id          FORMAT a20
COL sql_text        FORMAT a25
COL parsing_schema_name FORMAT a15 HEADING 'Schema'
COL module          FORMAT a15
COL Program         FORMAT a20
COL FIRST_LOAD_TIME FORMAT a20
COL OptMode         FORMAT a10
COL OptCost         FORMAT 99999999999999999999

SELECT round(round(elapsed_time/1000000,2)/60) AS Minutes, 
       FIRST_LOAD_TIME, 
       parsing_schema_name, 
       substr(module,1,20) AS Program, 
       sql_id, optimizer_mode AS OptMode, 
       optimizer_cost AS OptCost, 
       substr(sql_text,1,35) AS SQL 
FROM v$sql 
WHERE elapsed_time/1000000 > 300
AND   parsing_schema_name != 'SYS'
AND   substr(FIRST_LOAD_TIME,1,10) = to_char(SYSDATE, 'YYYY-MM-DD')
ORDER BY Minutes DESC;

Error: ORA-13639

-- Full Error

 ORA-13639: The current operation was interrupted because it timed out.

-- Associated Default Values

 LOCAL_TIME_LIMIT 1200 Time limit per statement in a SQL Tuning Set (in minutes).
 TIME_LIMIT       3600 The maximum time that an analysis can execute (in minutes).
 • Common value: 7200 (1.5 hours) 
 • Max value:   14400 (4 hours)

-- Fix 1: Ensure Sql Tuning Advisor Enabled

 -- View Status
 SELECT CLIENT_NAME, STATUS
 FROM   DBA_AUTOTASK_CLIENT
 WHERE  CLIENT_NAME = 'sql tuning advisor';

 -- Change
 EXEC DBMS_AUTO_TASK_ADMIN.ENABLE (client_name => 'sql tuning advisor',
    operation => NULL, window_name => NULL);
 EXEC DBMS_AUTO_TASK_ADMIN.DISABLE (client_name => 'sql tuning advisor',
    operation => NULL, window_name => NULL);

Place above commands on one line when executing.

-- Fix 2: Increase the Corresponding Parameters (Doc ID 1363111.1).

 EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK', 
    parameter => 'LOCAL_TIME_LIMIT', value => 7200);

 EXEC DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
   parameter => 'TIME_LIMIT', 
   value => 7200);

Place above commands on one line when executing.

-- View Values

 SELECT parameter_name, parameter_value, description
 FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
 AND parameter_name IN ('TIME_LIMIT','LOCAL_TIME_LIMIT');