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;
2 /
PL/SQL procedure successfully completed.
SQL> BEGIN DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER (PARAMETER=>'ACCEPT_SQL_PROFILES', VALUE=>'TRUE'); END;
2 /
PL/SQL procedure successfully completed.
SQL> SET LONG 9000
SQL> VARIABLE MY_RPT CLOB;
SQL> BEGIN
2 :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);
3 END;
4 /
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> BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME=>'SYS_AUTO_SQL_TUNING_TASK');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 :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);
3 END;
4 /
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>