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>

Deixe um comentário

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detectado !

Verificamos que está usando alguma extensão para bloquear os anúncios. O GPO (Grupo de Profissionais Oracle) obtém a sua renda através dos anúncios, para assim manter toda a estrutura dedicada a universalização do conhecimento.

Se você gosta de nosso trabalho, pedimos por gentileza que desabilite o ads blocker. Trabalhamos somente com o Google Adsense e tentamos ao máximo exibir apenas o necessário.

Agradecemos de antemão ! :)

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock