Oracle 11gR2: Automatic SQL Tuning

O Patchset 11.2.0.2 trouxe o Automatic SQL Tuning. A primeira versão do 11gR2 (11.2.0.1) informa na documentação que esta feature já existe, mas ela só está totalmente implementada (com todas as Packages) na 11.2.0.2.

Quando eu vi esta opção, me perguntei: por que será que nós DBAs temos receio de ativar esta Feature? Será receio que ela funcione mal, ou que funcione bem?

Na minha opnião, toda automação é bem vinda, então vamos testa-la.

Em primeiro lugar, faço sua ativação automática, pois por padrão este recurso vem desabilitado.

Na emissão do primeiro relatório, veja que o Job de verificação dos SQLs estava agendado para as 22:00, e não encontrou problema algum.

Logo em seguida, eu apaguei as estatísticas do Schema De Benchmark, e limpei a Shared Pool, para depois executar uma carga de no Oracle. Então eu executei por cerca de 30 minutos um programa de carga no Oracle.
Logo em seguida, forcei a execução do Tuning Task, para não ter que esperar as 22:00. Aí sim tivemos um relatório de melhorias, que já foram implementadas.

[oracle@nerv00 ~]$ sqlplus / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 13 08:37:44 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE (CLIENT_NAME=>'SQL TUNING ADVISOR', OPERATION=>NULL, WINDOW_NAME=>NULL); END;/

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER (PARAMETER=>'ACCEPT_SQL_PROFILES', VALUE=>'TRUE'); END;/

PL/SQL procedure successfully completed.

SQL> SET LONG 9000
SQL> VARIABLE MY_RPT CLOB;
SQL> BEGIN:MY_RPT:=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(BEGIN_EXEC=>NULL, END_EXEC=>NULL, TYPE=>'TEXT', LEVEL=>'TYPICAL', SECTION=>'ALL',OBJECT_ID=>NULL, RESULT_LIMIT=>NULL);END;/

PL/SQL procedure successfully completed.

SQL> PRINT :MY_RPT

MY_RPT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 11
Current Execution                       : EXEC_1331
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200

MY_RPT
--------------------------------------------------------------------------------
Completion Status                       : COMPLETED
Started at                              : 10/12/2010 22:00:02
Completed at                            : 10/12/2010 22:00:08
Number of Candidate SQLs                : 45
Cumulative Elapsed Time of SQL (s)      : 396147

-------------------------------------------------------------------------------
There are no recommendations to improve the statements in the workload.

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

SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SOE');

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> BEGINDBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME=>'SYS_AUTO_SQL_TUNING_TASK');END;/

PL/SQL procedure successfully completed.

SQL> BEGIN:MY_RPT:=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(BEGIN_EXEC=>NULL, END_EXEC=>NULL, TYPE=>'TEXT', LEVEL=>'TYPICAL', SECTION=>'ALL',OBJECT_ID=>NULL, RESULT_LIMIT=>NULL);END;/

PL/SQL procedure successfully completed.

SQL> PRINT :MY_RPT

MY_RPT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 12
Current Execution                       : EXEC_1451
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200

MY_RPT
--------------------------------------------------------------------------------
Completion Status                       : COMPLETED
Started at                              : 10/13/2010 08:41:05
Completed at                            : 10/13/2010 08:41:15
Number of Candidate SQLs                : 44
Cumulative Elapsed Time of SQL (s)      : 396148

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
 Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------

MY_RPT
--------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 44
Number of SQLs in the Report                 : 1
Number of SQLs with Findings                 : 1
Number of SQLs with SQL profiles recommended : 1

-------------------------------------------------------------------------------
 SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
 423 1dhu936zcgb55                      19.84%

MY_RPT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID  : 423
Schema Name: SYSMAN
SQL ID     : 1dhu936zcgb55
SQL Text   : UPDATE MGMT_AVAILABILITY_MARKER SET MARKER_TIMESTAMP = :B1 WHERE
 MARKER_TIMESTAMP <= :B1 AND TARGET_GUID IN (SELECT /*+ INDEX(t

MY_RPT
--------------------------------------------------------------------------------
 mgmt_targets_idx_02) */ T.TARGET_GUID FROM MGMT_TARGETS T WHERE
 T.EMD_URL = :B2 AND T.REP_SIDE_AVAIL = 0 AND NOT EXISTS (SELECT
 1 FROM MGMT_BLACKOUT_WINDOWS BW WHERE BW.TARGET_GUID =
 T.TARGET_GUID AND BW.START_TIME <= :B1 AND (BW.STATUS NOT IN
 (:B4 , :B3 ) ) ) AND NOT EXISTS (SELECT 1 FROM
 MGMT_GENSVC_AVAIL_CONFIG BCNAV WHERE BCNAV.TARGET_GUID =
 T.TARGET_GUID) )

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

MY_RPT
--------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
 A potentially better execution plan was found for this statement.
 The SQL profile was not automatically created because the verified benefit
 was too low.

 Recommendation (estimated benefit: 19.84%)
 ------------------------------------------
 - Consider accepting the recommended SQL profile.
 execute dbms_sqltune.accept_sql_profile(task_name =>

MY_RPT
--------------------------------------------------------------------------------
 'SYS_AUTO_SQL_TUNING_TASK', object_id => 423, replace => TRUE);

 Validation results
 ------------------
 The SQL profile was tested by executing both its plan and the original plan
 and measuring their respective execution statistics. A plan may have been
 only partially executed if the other could be run to completion in less time.

 Original Plan  With SQL Profile  % Improved
 -------------  ----------------  ----------
 Completion Status:            COMPLETE          COMPLETE

MY_RPT
--------------------------------------------------------------------------------
 Elapsed Time (s):              .01839            .00031      98.31 %
 CPU Time (s):                 .000399           .000399          0 %
 User I/O Time (s):                  0                 0
 Buffer Gets:                        5                 4         20 %
 Physical Read Requests:             0                 0
 Physical Write Requests:            0                 0
 Physical Read Bytes:                0                 0
 Physical Write Bytes:               0                 0
 Rows Processed:                     0                 0
 Fetches:                            0                 0
 Executions:                         1                 1

MY_RPT
--------------------------------------------------------------------------------

 Notes
 -----
 1. Statistics for the original plan were averaged over 10 executions.
 2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost

MY_RPT
--------------------------------------------------------------------------------
------------------------------
Plan hash value: 431998515

--------------------------------------------------------------------------------
----------------------------
| Id  | Operation                       | Name                     | Rows  | Byt
es | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
----------------------------
|   0 | UPDATE STATEMENT                |                          |     1 |   1
09 |     3   (0)| 00:00:01 |

MY_RPT
--------------------------------------------------------------------------------
|   1 |  UPDATE                         | MGMT_AVAILABILITY_MARKER |       |
 |            |          |
|*  2 |   FILTER                        |                          |       |
 |            |          |
|   3 |    NESTED LOOPS                 |                          |     1 |   1
09 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS ANTI           |                          |     3 |   2
52 |     3   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| MGMT_TARGETS             |     3 |   1
56 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | MGMT_TARGETS_IDX_02      |     4 |

MY_RPT
--------------------------------------------------------------------------------
 |     1   (0)| 00:00:01 |
|*  7 |      INDEX FULL SCAN            | PK_MGMT_BWINDOWS         |     1 |
32 |     0   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN           | MGMT_AVAIL_MARKER_PK     |     1 |
25 |     0   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN            | MGMT_GENSVC_AV_CFG_PK    |     1 |
10 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------------

Predicate Information (identified by operation id):

MY_RPT
--------------------------------------------------------------------------------
---------------------------------------------------

 2 - filter( NOT EXISTS (SELECT /*+ INDEX ("BCNAV" "MGMT_GENSVC_AV_CFG_PK") */
0 FROM
 "MGMT_GENSVC_AVAIL_CONFIG" "BCNAV" WHERE "BCNAV"."TARGET_GUID"=:B1
))
 5 - filter("T"."REP_SIDE_AVAIL"=0)
 6 - access("T"."EMD_URL"=:B2)
 7 - access("BW"."TARGET_GUID"="T"."TARGET_GUID" AND "BW"."START_TIME"<=:B1)
 filter("BW"."START_TIME"<=:B1 AND "BW"."STATUS"<>:B4 AND "BW"."STATUS"<>:
B3 AND

MY_RPT
--------------------------------------------------------------------------------
 "BW"."TARGET_GUID"="T"."TARGET_GUID")
 8 - access("TARGET_GUID"="T"."TARGET_GUID")
 filter("MARKER_TIMESTAMP"<=:B1)
 9 - access("BCNAV"."TARGET_GUID"=:B1)

2- Using SQL Profile
--------------------
Plan hash value: 1060181646

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

MY_RPT
--------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Byt
es | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
----------------------------
|   0 | UPDATE STATEMENT                |                          |     1 |   1
09 |     3   (0)| 00:00:01 |
|   1 |  UPDATE                         | MGMT_AVAILABILITY_MARKER |       |
 |            |          |
|*  2 |   FILTER                        |                          |       |
 |            |          |
|   3 |    NESTED LOOPS ANTI            |                          |     1 |   1

MY_RPT
--------------------------------------------------------------------------------
09 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                          |     1 |
77 |     3   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| MGMT_TARGETS             |     3 |   1
56 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | MGMT_TARGETS_IDX_02      |     4 |
 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | MGMT_AVAIL_MARKER_PK     |     1 |
25 |     0   (0)| 00:00:01 |
|*  8 |     INDEX FULL SCAN             | PK_MGMT_BWINDOWS         |     1 |
32 |     0   (0)| 00:00:01 |

MY_RPT
--------------------------------------------------------------------------------
|*  9 |    INDEX UNIQUE SCAN            | MGMT_GENSVC_AV_CFG_PK    |     1 |
10 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------
-


SQL>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.