Carregando SQL Plans na SPM utilizando o AWR

fevereiro 23, 2017 - Alta disponibilidade, Oracle Database, Performance - Tagged: awr, baseline, load_plans_from_sqlset, plan, plano de execução, spm, table_exists_action, xplan - no comments

A alteração do plano de execução de qualquer instrução sql pode ser desencadeada por uma variedade de ações e mudanças, desde a coleta de estatísticas (tabela, índice, schema, etc), mudanças de parâmetros, definição do objeto, criação ou remoção de índices, entre outros.

Uma solução muito útil para este problema de desempenho resultante da mudança de plano de execução tem sido o SPM (SQL Plan Management).

O SPM foi introduzido na versão 11g cujo proposito é “impedir regressões de desempenho resultantes de mudanças súbitas no plano de execução de uma instrução SQL, fornecendo componentes para capturar, selecionar e desenvolver informações de plano SQL.”

Este mecanismo cria uma SQL Plan Baseline, que é um conjunto de planos aceitos para uma instrução SQL.

Os SQL Plan Baselines podem ser carregados de varias formas:

A partir do cursor cache (shared sql area);
Utilizando SQL Tuning Set (STS);
Export e Import usando “Staging table”;
Automaticamente.

Veremos mais abaixo como carregar um plano “bom” que não esta mais na shared sql area (cursor cache) mas ainda está presente no AWR.

Neste caso tenho a instrução sql (sql_id = 91jcabsgk4ydx) com problemas de performance pois um plano ruim está sendo selecionado.

Buscando pelos planos atuais deste sql_id temos apenas o plano de hash 1491429885, que no caso é o plano de execução ruim.

SQL> select distinct plan_hash_value from gv$sql where sql_id='91jcabsgk4ydx';
 
PLAN_HASH_VALUE
---------------
     1491429885

Pesquisando no AWR encontrei outros planos para esta mesma instrução sql, neste caso o plano de hash 1748113394 é o plano “bom”.

SQL> select distinct plan_hash_value from dba_hist_sqlstat where sql_id='91jcabsgk4ydx';
 
PLAN_HASH_VALUE
---------------
     1748113394
     1491429885

Mais a frente irei especificar um conjunto de snapshots onde será buscado e carregado o plano de execução para dentro da STS, desta forma, abaixo já coletei o intervalo de snapshots onde o plano desejado está presente.

SQL> select min(snap_id), max(snap_id), plan_hash_value from dba_hist_sqlstat where sql_id='91jcabsgk4ydx' group by plan_hash_value ;
 
MIN(SNAP_ID) MAX(SNAP_ID) PLAN_HASH_VALUE
------------ ------------ ---------------
       14912        15006      1748113394
       14895        15008      1491429885

Criando um STS (SQL Tuning Set):

exec dbms_sqltune.create_sqlset(sqlset_name => '91jcabsgk4ydx_load_plan',description => 'load plan');
 
PL/SQL procedure successfully completed.

Populando STS com os planos do AWR:

declare
cur sys_refcursor;
begin
open cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap=> 14912, end_snap => 15006, basic_filter =>'sql_id = ''91jcabsgk4ydx''', attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => '91jcabsgk4ydx_load_plan', populate_cursor=>cur);
close cur;
end;
/
 
PL/SQL procedure successfully completed.

Nos parâmetros begin_snap e end_snap insira os dados retornados no comando anterior de min/max snap_id. Na coluna basic_filter é a condição de “pesquisa”/filtro, ou seja, foi inserido sql_id = 91jcabsgk4ydx para capturar todos os planos relacionados a este sql_id.

A coluna attribute_list representa os atributos de retorno os valores possíveis são:

BASIC: todos os atributos (como estatísticas de execução e binds) são retornados, exceto os planos. O contexto de execução é sempre parte do resultado.

TYPICAL: BASIC + SQL plan (sem estatísticas de origem de linha) e sem lista de referência de objeto (padrão).

ALL: todos os atributos, sem excessão.

Lista de nomes de atributos separados por vírgulas, isto permite retornar apenas um subconjunto de atributos SQL: EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN_STATISTICS

OBS: Caso queria carregar todos os sqls capturados no AWR basta inserir NULL em basic_filter.

Detalhes do STS:

SQL> SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='91jcabsgk4ydx_load_plan';
 
NAME                           OWNER                          CREATED   STATEMENT_COUNT
------------------------------ ------------------------------ --------- ---------------
91jcabsgk4ydx_load_plan        SYS                            17-FEB-17               1

Para verificar os planos do SQL_ID carregados na STS:

SQL> select * from table(dbms_xplan.display_sqlset('91jcabsgk4ydx_load_plan','91jcabsgk4ydx'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: 91jcabsgk4ydx_load_plan
SQL Tuning Set Owner: SYS
SQL_ID: 91jcabsgk4ydx
SQL Text: 
   [...]
--------------------------------------------------------------------------------
 
Plan hash value: 203381466
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |       |       |     5 (100)|          |
|   1 |  SORT GROUP BY                    |                      |     1 |   231 |     5  (20)| 00:00:01 |
|   2 |   FILTER                          |                      |       |       |            |          |
|   3 |    NESTED LOOPS                   |                      |     1 |   231 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                      |     2 |   231 |     4   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                      |     1 |   156 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                      |     1 |   105 |     2   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| TAB1                 |     2 |   118 |     1   (0)| 00:00:01 |
|   8 |         INDEX RANGE SCAN          | TAB1_IDX1            |   158 |       |     1   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| TAB2                 |     1 |    46 |     1   (0)| 00:00:01 |
|  10 |         INDEX UNIQUE SCAN         | TAB2_IDX1            |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | TAB3                 |     1 |    51 |     1   (0)| 00:00:01 |
|  12 |        INDEX UNIQUE SCAN          | TAB3_PK              |     1 |       |     1   (0)| 00:00:01 |
|  13 |      INDEX RANGE SCAN             | TAB4                 |     2 |       |     1   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | TAB4_IDX1            |     1 |    75 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Plan hash value: 1748113394
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |       |       |     6 (100)|          |
|   1 |  SORT GROUP BY                    |                      |     1 |   232 |     6  (34)| 00:00:01 |
|   2 |   FILTER                          |                      |       |       |            |          |
|   3 |    NESTED LOOPS                   |                      |     1 |   232 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                      |     2 |   232 |     4   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                      |     1 |   157 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                      |     1 |   105 |     2   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| TAB2                 |     1 |    46 |     1   (0)| 00:00:01 |
|   8 |         INDEX RANGE SCAN          | TAB2_IDX2            |     1 |       |     1   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| TAB1                 |     1 |    59 |     1   (0)| 00:00:01 |
|  10 |         INDEX RANGE SCAN          | TAB1_IDX2            |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | TAB3                 |     1 |    52 |     1   (0)| 00:00:01 |
|  12 |        INDEX UNIQUE SCAN          | TAB3_PK              |     1 |       |     1   (0)| 00:00:01 |
|  13 |      INDEX RANGE SCAN             | TAB4                 |     2 |       |     1   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | TAB4_IDX1            |     1 |    75 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
 
108 rows selected.
 
SQL>

A partir dos planos acima, confirmei que a considerada “boa” é a do plano hash = 1748113394

Criando o SQL Baseline a partir do STS deixando o novo plano (1748113394) habilitado e fixado:

set serveroutput on
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => '91jcabsgk4ydx_load_plan',
basic_filter => 'plan_hash_value=''1748113394''',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/
 
PL/SQL procedure successfully completed.

A partir de agora quando a instrução SQL for executada o SPB criado será utilizado para forçar o novo plano.

Verificando a instrução em cursor cache podemos observar que já consta na Note: – SQL plan baseline SQL_PLAN_2qz5a7x8r3ppzbd69646e used for this statement

SQL> select * from table (dbms_xplan.display_cursor('91jcabsgk4ydx', 0));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  91jcabsgk4ydx, child number 0
-------------------------------------
 
[...]
 
Plan hash value: 1748113394
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |       |       |     6 (100)|          |
|   1 |  SORT GROUP BY                    |                      |     1 |   232 |     6  (34)| 00:00:01 |
|   2 |   FILTER                          |                      |       |       |            |          |
|   3 |    NESTED LOOPS                   |                      |     1 |   232 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                      |     2 |   232 |     4   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                      |     1 |   157 |     3   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                      |     1 |   105 |     2   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| TAB2                 |     1 |    46 |     1   (0)| 00:00:01 |
|   8 |         INDEX RANGE SCAN          | TAB2_IDX2            |     1 |       |     1   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| TAB1                 |     1 |    59 |     1   (0)| 00:00:01 |
|  10 |         INDEX RANGE SCAN          | TAB1_IDX2            |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | TAB3                 |     1 |    52 |     1   (0)| 00:00:01 |
|  12 |        INDEX UNIQUE SCAN          | TAB3_PK              |     1 |       |     1   (0)| 00:00:01 |
|  13 |      INDEX RANGE SCAN             | TAB4                 |     2 |       |     1   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | TAB4_IDX1            |     1 |    75 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
[...]
 
Note
-----
   - SQL plan baseline SQL_PLAN_2qz5a7x8r3ppzbd69646e used for this statement
 
 
70 rows selected.

Referências:

http://docs.oracle.com/cd/E25178_01/server.1111/e16638/optplanmgmt.htm
Loading SQL Plans into SPM using AWR

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br

Carregando SQL Plans na SPM utilizando o AWR

Autor: Anderson Graf

Deixe um comentário Cancelar resposta

:: Entrar

:: Categorias

:: Social

:: Tweets

:: Entrar

:: Blogroll