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
https://aprakash.wordpress.com/2012/07/05/loading-sql-plan-into-spm-using-awr/
0 comentários:
Postar um comentário