Oracle Live

25/08/2016

DATABASE – Automatic SQL TUNING ADVISOR

Filed under: Database — mogukiller @ 11:24 am
Tags:

Con la version 11g, disponemos de una serie de procesos que se ejecutan de forma automática dentro de las ventanas de mantenimiento. Con este post os muestro como monitorizar y gestionar la ejecucion automatica del SQL Tuning Advisor.

-- Habilitamos o deshabilitamos la ejecución automática.

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

-- Configuramos

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

/* nota: Otros parametros seria:
	SQL_LIMIT
*/	

-- Autotask configuradas

COL CLIENT_NAME FOR A50
COL WINDOW_GROUP FOR A40
SELECT CLIENT_NAME, STATUS, WINDOW_GROUP FROM  DBA_AUTOTASK_CLIENT;

/*
CLIENT_NAME                                        STATUS   WINDOW_GROUP
-------------------------------------------------- -------- ----------------------------------------
auto optimizer stats collection                    ENABLED  ORA$AT_WGRP_OS
auto space advisor                                 ENABLED  ORA$AT_WGRP_SA
sql tuning advisor                                 ENABLED  ORA$AT_WGRP_SQ
*/

-- Monitorizamos las ejecuciones de los procesos

COL CLIENT_NAME FOR A35
COL START_TIME FOR A30
COL JOB_DURATION FOR A30
SELECT CLIENT_NAME, TO_CHAR(JOB_START_TIME,'YYYY/MM/DD HH24:MI:SS') START_TIME, JOB_DURATION, JOB_STATUS FROM DBA_AUTOTASK_JOB_HISTORY ORDER BY 1,2;
/*
CLIENT_NAME                         START_TIME                     JOB_DURATION                   JOB_STATUS
----------------------------------- ------------------------------ ------------------------------ ------------------------------
auto optimizer stats collection     2016/07/26 22:00:00            +000 00:03:35                  SUCCEEDED
auto optimizer stats collection     2016/07/27 22:00:02            +000 00:02:18                  SUCCEEDED
auto optimizer stats collection     2016/07/28 22:00:02            +000 00:02:18                  SUCCEEDED
...
auto space advisor                  2016/07/26 22:00:00            +000 00:00:50                  SUCCEEDED
auto space advisor                  2016/07/27 22:00:02            +000 00:00:43                  SUCCEEDED
auto space advisor                  2016/07/28 22:00:02            +000 00:00:58                  SUCCEEDED
...
sql tuning advisor                  2016/07/26 22:00:00            +000 00:07:11                  SUCCEEDED
sql tuning advisor                  2016/07/27 22:00:02            +000 00:02:56                  SUCCEEDED
sql tuning advisor                  2016/07/28 22:00:02            +000 00:05:25                  SUCCEEDED
*/

-- Imprimimos report SQL_Tuning

VARIABLE my_rept CLOB;
BEGIN
	:my_rept :=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;
/

PRINT :my_rept

/*
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 36
Current Execution                       : EXEC_99863
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200
Completion Status                       : INTERRUPTED
Started at                              : 08/25/2016 01:00:03
Completed at                            : 08/25/2016 02:00:30
Number of Candidate SQLs                : 168
Cumulative Elapsed Time of SQL (s)      : 188007

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

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 54
Number of SQLs in the Report                 : 6
Number of SQLs with Findings                 : 5
Number of SQLs with Alternative Plan Findings: 2
Number of SQLs with SQL profiles recommended : 5
Number of SQLs with Index Findings           : 2
Number of SQLs with SQL Restructure Findings : 1
Number of SQLs with Timeouts                 : 2
Number of SQLs with Errors                   : 1

-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
    227606 cx1wnu42gp6a3                      93.06%         99.96%
    227566 6ktn3k2sawmqb                      99.94%
    227587 3um68xwgd09vf                    <=10.00%         99.31%           1
    227588 gp1zhv84qhdmy                      99.12%
    227589 3rjav6c5gd2k9                      99.12%

-------------------------------------------------------------------------------
 Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name                 Table Name                  Index Name     Nb Time
--------------------------- --------------------------- -------------- --------
              CVPROCASTILLA TTRANSACTION                IDX$$_00010001        1
                            TTRANSACTION                IDX$$_00010008        1
                CVPROCOMMON TOPERATIONSTYPES            IDX$$_00010007        1
*/



Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: