Tuning de SQL

SQL Tuning Advisor

Ferramenta automática que analisa SQL problemático e fornece recomendações concretas: criar índices, atualizar estatísticas, reescrever SQL ou criar SQL Profile.

Usando SQL Tuning Advisor

-- Criar tuning task para SQL específico
DECLARE
    l_task_name VARCHAR2(100);
BEGIN
    l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => 'g2xb5k8f3zp9n',
        scope       => 'COMPREHENSIVE',  -- ou LIMITED
        time_limit  => 300,              -- segundos
        task_name   => 'TUNE_SQL_g2xb5k8',
        description => 'Tuning query lenta de relatório'
    );
    DBMS_OUTPUT.PUT_LINE('Task: ' || l_task_name);
END;
/

-- Executar a task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_SQL_g2xb5k8');

-- Ver o report
SET LONG 100000 LONGCHUNKSIZE 100000 PAGESIZE 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_SQL_g2xb5k8') FROM DUAL;

-- Aceitar recomendação de SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE('TUNE_SQL_g2xb5k8', 1);

-- Listar SQL Profiles criados
SELECT name, sql_text, status, created
FROM dba_sql_profiles
ORDER BY created DESC;

-- Desabilitar/Habilitar SQL Profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_01234567', 'STATUS', 'DISABLED');
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_01234567', 'STATUS', 'ENABLED');

-- Remover SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_01234567');

Gerenciando SQL Plan Baselines

-- Capturar baseline automaticamente (habilitar captura)
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

-- Capturar baseline para SQL específico
DECLARE
    l_plans_loaded PLS_INTEGER;
BEGIN
    l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id => 'g2xb5k8f3zp9n'
    );
    DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

-- Ver baselines existentes
SELECT
    sql_handle,
    plan_name,
    enabled,
    accepted,
    fixed,
    reproduced,
    origin,
    created
FROM dba_sql_plan_baselines
ORDER BY created DESC;

-- Ver SQL Text de uma baseline
SELECT sql_text
FROM dba_sql_plan_baselines
WHERE plan_name = 'SQL_PLAN_abcd1234';

-- Fixar um plano (impedir mudanças)
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_abc123def456',
        plan_name  => 'SQL_PLAN_abcd1234',
        attribute_name  => 'FIXED',
        attribute_value => 'YES'
    );
END;
/

-- Evoluir baselines (testar planos novos)
DECLARE
    l_report CLOB;
BEGIN
    l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_abc123def456'
    );
    DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

-- Remover baseline
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
        sql_handle => 'SQL_abc123def456',
        plan_name  => 'SQL_PLAN_abcd1234'
    );
END;
/

-- Exportar baselines (para outro banco)
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.CREATE_STGTAB_BASELINE(
        table_name => 'SPM_STAGING_TABLE'
    );

    l_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
        table_name => 'SPM_STAGING_TABLE',
        sql_handle => 'SQL_abc123def456'
    );
END;
/

-- Importar baselines
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
        table_name => 'SPM_STAGING_TABLE'
    );
END;
/