Automatic SQL Tuning task overview

 

Preamble

I think the below sentence from official Oracle documentation sum up very well what is Automatic SQL Tuning (task):

The DBMS_AUTO_SQLTUNE package is the interface to SQL Tuning Advisor (DBMS_SQLTUNE) when run within the Autotask framework. The database creates the automated system task SYS_AUTO_SQL_TUNING_TASK as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs the SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.

In other words no magic or new things behind the scene. The task is simply taking the most consuming SQL statements, criteria to choose them remains Oracle internals, and execute a SQL Tuning task using SQL Tuning Advisor (STA) that we have already seen in another post.

starting with Oracle 11.2.0.2 you have access to DBMS_AUTO_SQLTUNE package to manage automatic SQL tuning task. In 11.2.0.1 the automatic SQL tuning task is there but you had to use DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK to display its recommendations.

Post has been written using Red Hat Enterprise Linux Server release 5.5 (Tikanga), Oracle 11.2.0.3 and Cloud Control 12.1.0.1.

Automated tasks

Starting with Oracle 11gR2 automated task are displayed using dedicated views and are no more lost in standard Oracle scheduler views. To get the list, and status, of automated maintenance tasks use:

SQL> SET lines 200 pages 100
SQL> SELECT client_name, status, max_duration_last_30_days
     FROM DBA_AUTOTASK_CLIENT;
 
CLIENT_NAME                                                      STATUS   MAX_DURATION_LAST_30_DAYS
---------------------------------------------------------------- -------- ---------------------------------------------------------------------------
auto optimizer stats collection                                  ENABLED  +000 00:51:06
auto SPACE advisor                                               DISABLED
SQL tuning advisor                                               ENABLED  +000 01:00:10

Remark:
This view has also interesting columns of CPU used and duration of the automated tasks over the last 7 or 30 days.

How behaved Automatic SQL Tuning over last week:

SQL> SET lines 200 pages 100
SQL> SELECT job_start_time, job_status, job_duration
     FROM DBA_AUTOTASK_JOB_HISTORY
     WHERE client_name='sql tuning advisor'
     AND job_start_time >= SYSDATE -7
     ORDER BY job_start_time DESC;
 
JOB_START_TIME                                                              JOB_STATUS                     JOB_DURATION
--------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------
23-OCT-12 10.00.02.294336 PM EUROPE/VIENNA                                  SUCCEEDED                      +000 01:00:07
22-OCT-12 10.00.02.374938 PM EUROPE/VIENNA                                  SUCCEEDED                      +000 01:00:10
21-OCT-12 06.00.02.451373 AM EUROPE/VIENNA                                  SUCCEEDED                      +000 00:05:42
20-OCT-12 06.00.06.518359 AM EUROPE/VIENNA                                  SUCCEEDED                      +000 00:22:19
19-OCT-12 10.00.02.660040 PM EUROPE/VIENNA                                  SUCCEEDED                      +000 00:17:52
18-OCT-12 10.00.02.544862 PM EUROPE/VIENNA                                  SUCCEEDED                      +000 00:25:29
17-OCT-12 10.00.02.525450 PM EUROPE/VIENNA                                  SUCCEEDED                      +000 00:24:41
 
7 ROWS selected.

The parameters of the automatic SQL Tuning task can be changed using DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure, the way to get current (default) parameters is not piece of cake (all is supposed to be in DBA_AUTOTASK_xx views when it’s not):

SQL> SET lines 200 pages 100
SQL> col DESCRIPTION FOR a100
SQL> col parameter_value FOR a15
SQL> SELECT parameter_name,parameter_value,is_default,description
     FROM DBA_ADVISOR_PARAMETERS
     WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
     ORDER BY parameter_name;
 
PARAMETER_NAME                 PARAMETER_VALUE I DESCRIPTION
------------------------------ --------------- - ----------------------------------------------------------------------------------------------------
ACCEPT_SQL_PROFILES            FALSE           Y TRUE IF SQL Profiles should be created BY the task, FALSE otherwise
APPLY_CAPTURED_COMPILENV       UNUSED          Y Indicated whether the advisor could USE the compilation environment captured WITH the SQL statements
BASIC_FILTER                   UNUSED          Y SQL predicate TO filter the SQL FROM the SQL Tuning SET
DAYS_TO_EXPIRE                 UNLIMITED       N The expiration TIME IN days FOR the CURRENT SQL ACCESS Advisor task
DEFAULT_EXECUTION_TYPE         TUNE SQL        N Tune the performance OF SQL statements
END_SNAPSHOT                   UNUSED          Y The LAST snapshot id IN the analysis period
END_TIME                       UNUSED          Y The END TIME filter FOR selecting data
EXECUTION_DAYS_TO_EXPIRE       30              N Specifies the expiration TIME IN days FOR individual executions OF the CURRENT task
INSTANCE                       UNUSED          Y The instance NUMBER FOR analysis
JOURNALING                     INFORMATION     Y Specifies logging OF messages TO the advisor journal
LOCAL_TIME_LIMIT               1200            N TIME limit per statement IN a SQL Tuning SET
MAX_AUTO_SQL_PROFILES          10000           Y Maximum NUMBER OF SYSTEM SQL Profiles that are allowable AT ANY one TIME
MAX_SQL_PROFILES_PER_EXEC      20              Y Maximum NUMBER OF SQL Profiles that can be created BY the SYSTEM IN one run
MODE                           COMPREHENSIVE   Y Specifies either a LIMITED OR comprehensive analysis operation, WHERE LIMITED runs IN less TIME but
                                                 may produce slightly LOWER quality results
 
ORA_EM_PARAM1                  UNUSED          Y
ORA_EM_PARAM10                 UNUSED          Y
ORA_EM_PARAM2                  UNUSED          Y
ORA_EM_PARAM3                  UNUSED          Y
ORA_EM_PARAM4                  UNUSED          Y
ORA_EM_PARAM5                  UNUSED          Y
ORA_EM_PARAM6                  UNUSED          Y
ORA_EM_PARAM7                  UNUSED          Y
ORA_EM_PARAM8                  UNUSED          Y
ORA_EM_PARAM9                  UNUSED          Y
PLAN_FILTER                    UNUSED          Y PLAN filter applicable IN CASE there are multiple plans (plan_hash_value) associated WITH the same s
                                                 tatement
 
RANK_MEASURE1                  UNUSED          Y A FIRST ORDER BY clause ON the selected SQL
RANK_MEASURE2                  UNUSED          Y A SECOND ORDER BY clause ON the selected SQL
RANK_MEASURE3                  UNUSED          Y A third ORDER BY clause ON the selected SQL
RESUME_FILTER                  UNUSED          Y Specify the DEFAULT action OF a task execution.
SQL_LIMIT                      -1              Y Limit the NUMBER OF SQL FROM the filtered AND ranked SQL tuning SET
SQL_PERCENTAGE                 1               Y A percentage ON the SUM OF a ranking measure
START_SNAPSHOT                 UNUSED          Y The FIRST snapshot id IN the analysis period
START_TIME                     UNUSED          Y The START TIME filter FOR selecting data
TARGET_OBJECTS                 1               N Deprecated Parameter
TEST_EXECUTE                   FULL            N FULL IF SQL statements should be test-executed FOR the full local TIME limit TO measure benefit, AUT
                                                 O FOR some automatically-chosen smaller TIME, AND NO FOR no test execution whatsoever
 
TIME_LIMIT                     3600            N The maximum TIME that an analysis can EXECUTE
USERNAME                       UNUSED          Y The username FOR whom the SQL statement will be tuned
 
37 ROWS selected.

Remark:
One important parameter is ACCEPT_SQL_PROFILES which can make SQL Profiles acceptation automatic… Quite dangerous…

With command lines

First we may need to get begin_exec and end_exec first two parameters of DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK procedure. Identify the task name using:

SQL> col EXECUTION_DESCRIPTION FOR a50
SQL> SELECT * FROM DBA_ADVISOR_execution_types;
 
ADVISOR_NAME                   EXECUTION_TYPE                 EXECUTION_DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
SQL Tuning Advisor             TUNE SQL                       Tune the performance OF SQL statements
SQL Performance Analyzer       CONVERT SQLSET
SQL Performance Analyzer       TEST EXECUTE                   Test EXECUTE OF SQL statements
SQL Performance Analyzer       EXPLAIN PLAN                   Generate EXPLAIN PLAN OF SQL statements
SQL Performance Analyzer       COMPARE PERFORMANCE            ANALYZE performance OF SQL statements
SQL Repair Advisor             SQL DIAGNOSIS
 
6 ROWS selected.
 
SQL> col DESCRIPTION FOR a50
SQL> SELECT task_name,description,execution_type FROM DBA_Advisor_TASKs WHERE advisor_name='SQL Tuning Advisor';
 
TASK_NAME                      DESCRIPTION                                        EXECUTION_TYPE
------------------------------ -------------------------------------------------- ------------------------------
SYS_AUTO_SQL_TUNING_TASK       Automatic SQL Tuning Task                          TUNE SQL

Then display execution names using:

SQL> col error_message FOR a70
SQL> SET lines 200 pages 1000
SQL> SELECT execution_name,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     status,error_message
     FROM DBA_ADVISOR_EXECUTIONS
     WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
     ORDER BY execution_id DESC;
 
EXECUTION_NAME                 EXECUTION_START               STATUS      ERROR_MESSAGE
------------------------------ ----------------------------- ----------- ----------------------------------------------------------------------
EXEC_19911                     23-oct-2012 22:00:03          INTERRUPTED ORA-13639: The CURRENT operation was interrupted because it timed OUT.
EXEC_19882                     22-oct-2012 22:00:03          INTERRUPTED ORA-13639: The CURRENT operation was interrupted because it timed OUT.
EXEC_19785                     21-oct-2012 06:00:03          COMPLETED
EXEC_19759                     20-oct-2012 06:00:08          COMPLETED
EXEC_19750                     19-oct-2012 22:00:03          COMPLETED
EXEC_19721                     18-oct-2012 22:00:03          COMPLETED
.
.

Remark:
Please note that in this view we can see that task has been timeout the last two days while in DBA_AUTOTASK_JOB_HISTORY we have feeling that all is in success…

Then to generate a text report (level and section parameters may take other values):

SQL> SET lines 200 pages 1000
SQL> SET LONG 999999999
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('EXEC_19911','EXEC_19911','TEXT','ALL','SUMMARY') FROM dual;
 
DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('EXEC_19911','EXEC_19911','TEXT','ALL'
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Tuning Task ID                          : 1
Workload TYPE                           : Automatic High-Load SQL Workload
Execution COUNT                         : 32
CURRENT Execution                       : EXEC_19911
Execution TYPE                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global TIME Limit(seconds)              : 3600
Per-SQL TIME Limit(seconds)             : 1200
Completion Status                       : INTERRUPTED
Started AT                              : 10/23/2012 22:00:03
Completed AT                            : 10/23/2012 23:00:09
NUMBER OF Candidate SQLs                : 237
Cumulative Elapsed TIME OF SQL (s)      : 200578
 
-------------------------------------------------------------------------------
Error: ORA-13639: The CURRENT operation was interrupted because it timed OUT.
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result STATISTICS
-------------------------------------------------------------------------------
NUMBER OF SQLs Analyzed                      : 137
NUMBER OF SQLs IN the Report                 : 137
NUMBER OF SQLs WITH Findings                 : 22
NUMBER OF SQLs WITH Alternative PLAN Findings: 12
NUMBER OF SQLs WITH SQL profiles recommended : 13
NUMBER OF SQLs WITH INDEX Findings           : 9
NUMBER OF SQLs WITH SQL Restructure Findings : 5
NUMBER OF SQLs WITH Timeouts                 : 1
 
-------------------------------------------------------------------------------
    SQLs WITH Findings Ordered BY Maximum (PROFILE/INDEX) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        STATISTICS PROFILE(benefit) INDEX(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
     62464 asrjufk4rqp9w                      98.26%         99.99%
     62455 0pf7p39v3837f                      99.98%         99.96%
     62465 cjsw4bgt5rkt6                      99.98%         99.97%
     62466 cbh423x0ckcuh                      99.98%         99.97%
     62467 2zrxuz81p05v9                      99.98%         99.97%
     62468 02kvj08z7vbs1                      99.98%         99.97%
     62469 8shsznktrr50m                      99.98%         99.97%
     62344 gg6ahka0dhnkg                      99.42%                          4
     62458 5taf298vqzh4j                                     98.03%
     62437 f1d19p5kd70bb                      94.64%                          1
     62435 cnvcujdjxm78y                      94.36%         50.84%           1
     62473 aqbjxy8zj2hjd                      91.01%
     62446 9m5ha7jvc615y                      86.11%                          1
     62479 g40ubdmj8kz4v                      37.75%                          1
     62456 cnfxrms8zwkxr
     62471 719unza2x3nrs
     62472 5uns34fj8xmg3
     62474 101ksavbfw2nq
     62475 1tcfp9zm7gsz3
     62476 9t5dmqb6untmx
     62477 c6m7bjmmw6n3c
 
-------------------------------------------------------------------------------
 Tables WITH NEW Potential Indices (ordered BY SCHEMA, NUMBER OF times, TABLE)
-------------------------------------------------------------------------------
SCHEMA Name                 TABLE Name                  INDEX Name     Nb TIME
--------------------------- --------------------------- -------------- --------
                   DATALOAD CUSTOMER                    IDX$$_00010009        6
                            MN_HIST_TASK                IDX$$_0001000D        1
                            ORG                         IDX$$_00010005        1
                            PRODUCT_FAMILY              IDX$$_00010006        1
                            REPORT_TYPE_COLUMN          IDX$$_0001000B        1
 
-------------------------------------------------------------------------------

you may also use something like:

VARIABLE report CLOB;
BEGIN
  :report:=DBMS_SQLTUNE.report_auto_tuning_task(begin_exec=>'EXEC_19911', end_exec=>'EXEC_19911', LEVEL=>'ALL', section=>'SUMMARY');
END;
/
SET LONG 999999999
PRINT :report

Remark:
What’s nasty is that reports can be generated only in text format and you can’t have something well organized with hyper links in html. Which make use of Cloud Control almost mandatory…

We can anyway dig a bit in sql_id using something like (example on first sql_id and so first object_id which has SQL Profile and index findings):

SQL> SET lines 200 pages 1000
SQL> SET LONG 999999999
SQL> SET longchunksize 200
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('EXEC_19911','EXEC_19911','TEXT','ALL','ALL',62464) FROM dual
 
DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK('EXEC_19911','EXEC_19911','TEXT','ALL','ALL',62464)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Tuning Task ID                          : 1
Workload TYPE                           : Automatic High-Load SQL Workload
Execution COUNT                         : 32
CURRENT Execution                       : EXEC_19911
Execution TYPE                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global TIME Limit(seconds)              : 3600
Per-SQL TIME Limit(seconds)             : 1200
Completion Status                       : INTERRUPTED
Started AT                              : 10/23/2012 22:00:03
Completed AT                            : 10/23/2012 23:00:09
NUMBER OF Candidate SQLs                : 237
Cumulative Elapsed TIME OF SQL (s)      : 200578
 
-------------------------------------------------------------------------------
Error: ORA-13639: The CURRENT operation was interrupted because it timed OUT.
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
Object ID  : 62464
SCHEMA Name: SYS
SQL ID     : asrjufk4rqp9w
SQL Text   :  SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"
             WHERE "MEMBER_ID_CREATED" = :1
 
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
 
1- SQL PROFILE Finding (see EXPLAIN plans section below)
--------------------------------------------------------
  A potentially better execution PLAN was found FOR this statement.
 
  Recommendation (estimated benefit: 98.26%)
  ------------------------------------------
  - Consider accepting the recommended SQL PROFILE TO USE parallel execution
    FOR this statement.
    EXECUTE dbms_sqltune.accept_sql_profile(task_name =>
            'SYS_AUTO_SQL_TUNING_TASK', object_id => 62464, REPLACE => TRUE,
            profile_type => DBMS_SQLTUNE.PX_PROFILE);
 
  Executing this query parallel WITH DOP 64 will improve its response TIME
  98.27% over the original PLAN. However, there IS some COST IN enabling
  parallel execution. It will increase the statement's resource consumption by
  an estimated 11.00% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.
 
  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.
 
                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0
 
                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0
 
2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DATALOAD.IDX$$_0001000D on
    DATALOAD.MN_HIST_TASK("MEMBER_ID_CREATED");
 
  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 1159508356
 
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    13 | 16464   (1)| 00:03:18 |
|   1 |  SORT AGGREGATE    |              |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| MN_HIST_TASK |     1 |    13 | 16464   (1)| 00:03:18 |
-----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / MN_HIST_TASK@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("MEMBER_ID_CREATED"=:1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
 
2- Using New Indices
--------------------
Plan hash value: 3895781544
 
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX$$_0001000D |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / MN_HIST_TASK@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("MEMBER_ID_CREATED"=:1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
 
3- Using Parallel Execution
---------------------------
Plan hash value: 2132902461
 
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    13 |   286   (1)| 00:00:04 |        |      |            |
|   1 |  SORT AGGREGATE        |              |     1 |    13 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |              |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000     |     1 |    13 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |              |     1 |    13 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |              |     1 |    13 |   286   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| MN_HIST_TASK |     1 |    13 |   286   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   6 - SEL$1 / MN_HIST_TASK@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("MEMBER_ID_CREATED"=:1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
 
Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
 
-------------------------------------------------------------------------------

Even if the display is readable we are at few years light from an html output…

With Cloud Control

Even if Cloud Control installation and configuration requires a bit of work the added value is then clearly shown in below screen shots, first the task overview with graphics:

automatic sql tuning 1
automatic sql tuning 1

if you click on SQL examined with findings hyper link:

automatic sql tuning 2
automatic sql tuning 2

If you display recommendations for same sql_id we have seen with command lines:

automatic sql tuning 3
automatic sql tuning 3

References

  • Automatic SQL Tune Job Fails With ORA-13639 [ID 1363111.1]

5 thoughts on “Automatic SQL Tuning task overview

  1. Hi Yannick,

    Please tell me if there is any provision to make this ‘SYS_AUTO_SQL_TUNING_TASK’ to run on weekly basis.

    Right now this task is running daily in my Prod database for 60 mins which is causing performance issues.

    Thanks,
    Sneha Jadhav

    • Hi Sneha,

      Thanks for stopping by…

      You may ask confirmation to Oracle but in my opinion there is no harm to set this auto task weekly or even deactivate it if you never check the result. Personally I always deactivate by default for segment advisor that is an heavy resources consumer…

      Either you do it with graphical interface in Cloud Control or using something like (Monday window example), you may as well create new running windows:

      dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW');

      Thanks, Yannick.

  2. sneha jadhav says:

    Hello Yannick,

    Thanks for the reply . I would like to know if we should blindly follow the recommendations given by Oracle through the below command:
    SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
    ‘SYS_AUTO_SQL_TUNING_TASK’)
    FROM DUAL;

    Like I got recommendations to accept profile and create indices .I want to know if I create indices , will that not have side effects .Thanks in advance .
    Sneha Jadhav

    • Hello Sneha,

      No you should never follow blindly any of the advisors’ recommendations.

      The example you give on indices is a good one. Creating indices is obviously impacting all the DML on master table…

      Thanks, Yannick.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>