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.

go to link

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).

https://vbmotorworld.com/a3a91db

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.”

https://marcosgerente.com.br/wt37ypl

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

https://semnul.com/creative-mathematics/?p=1a3ukkyrov

Os SQL Plan Baselines podem ser carregados de varias formas:

https://luisfernandocastro.com/ojo73t0orj5

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.

source Neste caso tenho a instrução sql (sql_id = https://ragadamed.com.br/2024/09/18/zax8er3hgo 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 https://www.thephysicaltherapyadvisor.com/2024/09/18/xue3waqat 1491429885, que no caso é o plano de execução ruim.

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

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

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

source site 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.

https://luisfernandocastro.com/22pi0pqh 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

get link Criando um STS (SQL Tuning Set):

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

https://marcosgerente.com.br/6x0ju33nkl Populando STS com os planos do AWR:

click here 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.

get link 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 = https://trevabrandonscharf.com/wt2ix0v7ec 91jcabsgk4ydx para capturar todos os planos relacionados a este sql_id.

https://livingpraying.com/45tyvv59o3u A coluna attribute_list representa os atributos de retorno os valores possíveis são:

https://trevabrandonscharf.com/cdyuuxxvlz see url 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.

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

https://www.modulocapital.com.br/rt1d0p34 go to link ALL: todos os atributos, sem excessão.

https://technocretetrading.com/ux5dhc82 https://technocretetrading.com/ygqxfqlem 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

https://semnul.com/creative-mathematics/?p=fas59w10nl OBS: Caso queria carregar todos os sqls capturados no AWR basta inserir NULL em basic_filter.

https://www.parolacce.org/2024/09/18/wl17s2fxrg 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 = https://www.drcarolineedwards.com/2024/09/18/ae54ri0t6im 1748113394

Criando o SQL Baseline a partir do STS deixando o novo plano ( go 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