{"id":2213,"date":"2020-09-29T21:52:19","date_gmt":"2020-09-30T00:52:19","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2213"},"modified":"2020-09-29T21:52:19","modified_gmt":"2020-09-30T00:52:19","slug":"evite-surpresas-desagradaveis-no-desempenho-das-consultas","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/evite-surpresas-desagradaveis-no-desempenho-das-consultas\/","title":{"rendered":"Evite surpresas desagrad\u00e1veis no desempenho das consultas"},"content":{"rendered":"

Voc\u00ea j\u00e1 ouviu algu\u00e9m dizer: Minha aplica\u00e7\u00e3o ficou lenta e nada foi alterado. Este problema relativamente comum pode ser evitado se voc\u00ea implementar no banco Oracle o recurso SQL PLAN MANAGEMENT (SPM), este recurso esta dispon\u00edvel a partir vers\u00e3o 11G e n\u00e3o\u00a0requer a licen\u00e7a das Options Diagnostic & Tuning Pack<\/a><\/strong>. Basicamente\u00a0o SPM \u00e9 um mecanismo preventivo que permite ao Otimizador gerenciar automaticamente os planos de execu\u00e7\u00e3o, garantindo que o banco de dados use apenas planos que tenham desempenho igual ou melhor aos planos de execu\u00e7\u00e3o utilizados anteriormente, este mecanismo impede que uma consulta sofra uma regress\u00e3o no seu desempenho se n\u00e3o houver altera\u00e7\u00f5es nas estruturas dos objetos e par\u00e2metros do banco de dados.<\/span><\/p>\n

 
\n <\/p>\n

A seguir apresentamos uma pequena introdu\u00e7\u00e3o de como funciona a SQL PLAN BASELINE que \u00e9 o principal mecanismo do SPM e na sequ\u00eancia vamos fazer uma simula\u00e7\u00e3o pr\u00e1tica de como funciona o SPM.<\/span><\/p>\n

 <\/p>\n

Quando uma instru\u00e7\u00e3o SQL \u00e9 submetida ao processo de HARD PARSE, o Otimizador produz v\u00e1rios planos de execu\u00e7\u00e3o e seleciona aquele com o menor custo. Se esta instru\u00e7\u00e3o SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE for igual a um dos planos existente na BASELINE, o Otimizador vai verificar se este plano foi aceito, se SIM o plano ser\u00e1 utilizado na execu\u00e7\u00e3o, se no entanto o plano n\u00e3o foi aceito, o Otimizador vai verificar a exist\u00eancia de outro plano aceito com custo menor e utilizar este plano da BASELINE na execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL, se n\u00e3o existir outro plano aceito na BASELINE o plano gerado originalmente no HARD PARSE ser\u00e1 utilizado.<\/span><\/p>\n

O fluxograma abaixo representa a explica\u00e7\u00e3o acima<\/span><\/p>\n

\"Baseline<\/a><\/p>\n

Outro fluxo alternativo ao apresentado anteriormente seria ap\u00f3s o HARD PARSE, se esta instru\u00e7\u00e3o SQL estiver presente na SQL PLAN BASELINE e o plano gerado no HARD PARSE N\u00c3O for igual a um dos planos existente na BASELINE, neste caso o Otimizador vai comparar o custo desse novo plano com o custo dos planos aceitos, se o custo do novo plano for menor ser\u00e3o geradas BASELINE HINTS para ele, se no entanto estas BASELINES HINTS n\u00e3o conseguirem reproduzir o plano gerado no HARD PARSE, o Otimizador vai verificar a exist\u00eancia de outro plano aceito com o menor custo e utilizar este plano da BASELINE na execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL, se as BASELINES HINTS conseguirem reproduzir o plano gerado no HARD PARSE, o novo plano ser\u00e1 adicionado a BASELINE como n\u00e3o aceito e um plano da BASELINE com menor custo ser\u00e1 utilizado na execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL.<\/span><\/p>\n

\"Baseline<\/a><\/p>\n

Para ajudar no entendimento de como funcionam as BASELINES vamos realizar uma simula\u00e7\u00e3o do processo de inclus\u00e3o e evolu\u00e7\u00e3o dos planos de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL sob o controle do recurso SQL PLAN MANAGEMENT:<\/span><\/p>\n

01) Habilitar o uso do SQL PLAN MANAGEMENT <\/span><\/strong>
\n02) Executar uma consulta com SPM habilitado<\/span><\/strong>
\n03) Verificar o plano de execu\u00e7\u00e3o na SQL PLAN BASELINE<\/span><\/strong>
\n04) Executar a consulta novamente usando a BASELINE<\/span><\/strong>
\n05) Criar um \u00edndice para melhorar o desempenho da consulta<\/span><\/strong>
\n06) Verificar se a consulta vai usar o \u00edndice<\/span><\/strong>
\n07) Verificar exist\u00eancia de mais um plano na BASELINE<\/span><\/strong>
\n08) Verificar qual plano ser\u00e1 utilizado numa nova execu\u00e7\u00e3o<\/span><\/strong>
\n09) Executar o procedimento de evolu\u00e7\u00e3o do SPM<\/span><\/strong>
\n10) Executar a consulta novamente com o novo plano<\/span><\/strong>
\n11) Eliminar a BASELINE criada<\/span><\/strong><\/p>\n

 <\/p>\n

01) Habilitar o uso do SQL PLAN MANAGEMENT<\/span><\/h2>\n

Para utilizar o SQL PLAN MANAGEMENT precisamos habilitar o par\u00e2metro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<\/div><\/td>
SQL ><\/span> SELECT BANNER_FULL FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER_FULL
\n-------------------------------------------------------------------------------<\/span>
\nOracle Database 18c Express Edition Release 18.<\/span>0.<\/span>0.<\/span>0.<\/span>0 -<\/span> Production
\nVersion 18.<\/span>4.<\/span>0.<\/span>0.<\/span>0
\n
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
\n
\nNAME                                 TYPE        VALUE
\n------------------------------------<\/span> -----------<\/span> ------------------------------<\/span>
\noptimizer_capture_sql_plan_baselines boolean     FALSE<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SYSTEM<\/span><\/a> SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=<\/span>TRUE<\/span>;<\/span>
\n
\nSistema alterado.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

02) Executar uma consulta com SPM habilitado<\/span><\/h2>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<\/div><\/td>
SQL ><\/span> set tab off;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET current_schema=<\/span>SH;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-066 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n1xj5b63mpbz67               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1xj5b63mpbz67,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\nc.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/span>
\n
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    95<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador utilizou a opera\u00e7\u00e3o TABLE ACCESS FULL para acessar a tabela CUSTOMERS do esquema SH. <\/span><\/p>\n

03) Verificar o plano de execu\u00e7\u00e3o na SQL PLAN BASELINE<\/span><\/h2>\n

 <\/p>\n

Para obter informa\u00e7\u00f5es sobre as consultas que est\u00e3o sendo gerenciadas pelo SPM basta consultar a vis\u00e3o DBA_SQL_PLAN_BASELINES.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<\/div><\/td>
SQL ><\/span> SELECT sql_handle,<\/span> plan_name,<\/span> enabled,<\/span> accepted
\n  2<\/span>    FROM dba_sql_plan_baselines
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_HANDLE                     PLAN_NAME                        ENA ACC
\n------------------------------<\/span> --------------------------------<\/span> ---<\/span> ---<\/span>
\nSQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc764541f84   YES YES
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\n
\n==============================================================================================================<\/span>
\n
\nSET LONG 10000<\/span>
\n
\nSELECT *<\/span>
\nFROM   TABLE(<\/span>DBMS_XPLAN.<\/span>display_sql_plan_baseline(<\/span>plan_name=><\/span>'SQL_PLAN_1a11bn5sndsc764541f84'<\/span>)<\/span>)<\/span>;<\/span>
\n
\n==============================================================================================================<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------<\/span>
\nSQL handle:<\/span> SQL_15042ba17146e187
\nSQL text:<\/span> SELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\n          c.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\n          c.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_1a11bn5sndsc764541f84         Plan id:<\/span> 1683234692<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> YES     Origin:<\/span> AUTO-<\/span>CAPTURE
\nPlan rows:<\/span> From dictionary
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/span>
\n
\n-------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name      |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>     1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>     1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>
\n              AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>
\n
\n28<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No resultado da consulta podemos verificar que na BASELINE temos uma instru\u00e7\u00e3o SQL sendo gerenciada (SQL_15042ba17146e187) e esta instru\u00e7\u00e3o tem somente um plano de execu\u00e7\u00e3o (SQL_PLAN_1a11bn5sndsc764541f84). Este plano esta habilitado e aceito para ser utilizado na execu\u00e7\u00e3o desta instru\u00e7\u00e3o SQL. <\/span>
\nUtilizando o procedure DISPLAY_SQL_PLAN_BASELINE do pacote DBMS_XPLAN podemos conferir que o plano armazenado na BASELINE \u00e9 o mesmo plano que foi utilizado na execu\u00e7\u00e3o da consulta.<\/span><\/p>\n

 <\/p>\n

04) Executar a consulta novamente usando a BASELINE<\/strong><\/span><\/p>\n

Vamos executar a mesma consulta novamente e verificar se a BASELINE ser\u00e1 utilizada.<\/span>
\n <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<\/div><\/td>
SQL ><\/span> SELECT \/* dbtw-066 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n1xj5b63mpbz67               0<\/span>
\n1xj5b63mpbz67               1<\/span>
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1xj5b63mpbz67,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\nc.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/span>
\n
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    95<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for<\/span> this statement
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe na se\u00e7\u00e3o NOTE do plano de execu\u00e7\u00e3o que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execu\u00e7\u00e3o utilizado foi o que esta na BASELINE.<\/span><\/p>\n

 <\/p>\n

05) Criar um \u00edndice para melhorar o desempenho da consulta<\/span><\/h2>\n

Como a consulta que estamos executando possui o campo CUST_LAST_NAME no filtro da cl\u00e1usula WHERE e n\u00e3o existe um \u00edndice desta coluna para a tabela CUSTOMERS, vamos criar um \u00edndice neste campo para melhorar o desempenho desta consulta.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
<\/div><\/td>
SQL ><\/span> create index sh.<\/span>CUST_NAME_IDX on sh.<\/span>CUSTOMERS(<\/span>CUST_LAST_NAME)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

06) Verificar se a consulta vai usar o \u00edndice<\/span><\/h2>\n

Ap\u00f3s a cria\u00e7\u00e3o do \u00edndice vamos executar a consulta novamente para verificar se o \u00edndice ser\u00e1 utilizado no plano de execu\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<\/div><\/td>
SQL ><\/span> SELECT \/* dbtw-066 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n1xj5b63mpbz67               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1xj5b63mpbz67,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\nc.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/span>
\n
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    95<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observando o plano de execu\u00e7\u00e3o acima, constatamos que o \u00edndice n\u00e3o foi utilizado, o Otimizador continua utilizando o mesmo plano utilizado nas execu\u00e7\u00f5es anteriores. O novo plano com a utiliza\u00e7\u00e3o do \u00edndice n\u00e3o foi utilizado pois o SPM est\u00e1 atuando junto ao Otimizador, ele identificou que existe um novo plano com um CUSTO menor do que aquele que est\u00e1 ACEITO na BASELINE, porem ele n\u00e3o vai utilizar este novo plano at\u00e9 que o seu desempenho seja validado.<\/span><\/p>\n

 <\/p>\n

07) Verificar exist\u00eancia de mais um plano na BASELINE<\/span><\/h2>\n

Vamos consultar a vis\u00e3o DBA_SQL_PLAN_BASELINES novamente e verificar se realmente o SPM j\u00e1 registrou a exist\u00eancia de um novo plano com a utiliza\u00e7\u00e3o do \u00edndice que criamos anteriormente.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<\/div><\/td>
SQL ><\/span> SELECT sql_handle,<\/span> plan_name,<\/span> enabled,<\/span> accepted
\n  2<\/span>    FROM dba_sql_plan_baselines
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_HANDLE                     PLAN_NAME                        ENA ACC
\n------------------------------<\/span> --------------------------------<\/span> ---<\/span> ---<\/span>
\nSQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc71a248297   YES NO
\nSQL_15042ba17146e187           SQL_PLAN_1a11bn5sndsc764541f84   YES YES
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\n
\n==============================================================================================================<\/span>
\n
\nSET LONG 10000<\/span>
\n
\nSELECT *<\/span>
\nFROM   TABLE(<\/span>DBMS_XPLAN.<\/span>display_sql_plan_baseline(<\/span>plan_name=><\/span>'SQL_PLAN_1a11bn5sndsc71a248297'<\/span>)<\/span>)<\/span>;<\/span>
\n
\n==============================================================================================================<\/span>
\n
\nSQL ><\/span> SET LONG 10000<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>  FROM   TABLE(<\/span>DBMS_XPLAN.<\/span>display_sql_plan_baseline(<\/span>plan_name=><\/span>'SQL_PLAN_1a11bn5sndsc71a248297'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------<\/span>
\nSQL handle:<\/span> SQL_15042ba17146e187
\nSQL text:<\/span> SELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\n          c.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\n          c.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n--------------------------------------------------------------------------------<\/span>
\n
\n--------------------------------------------------------------------------------<\/span>
\nPlan name:<\/span> SQL_PLAN_1a11bn5sndsc71a248297         Plan id:<\/span> 438600343<\/span>
\nEnabled:<\/span> YES     Fixed:<\/span> NO      Accepted:<\/span> NO      Origin:<\/span> AUTO-<\/span>CAPTURE
\nPlan rows:<\/span> From dictionary
\n--------------------------------------------------------------------------------<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 203008431<\/span>
\n
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name          |<\/span> Rows  |<\/span> Bytes |<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> Time<\/span><\/a>     |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>               |<\/span>     1<\/span> |<\/span>    20<\/span> |<\/span>     6<\/span>  (<\/span>34<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> CUSTOMERS     |<\/span>     1<\/span> |<\/span>    20<\/span> |<\/span>     6<\/span>  (<\/span>34<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   BITMAP CONVERSION TO ROWIDS       |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>    BITMAP AND                       |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   4<\/span> |<\/span>     BITMAP CONVERSION FROM ROWIDS   |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   5<\/span> |<\/span>      SORT<\/span><\/a> ORDER BY                  |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|*<\/span>  6<\/span> |<\/span>       INDEX RANGE<\/span><\/a> SCAN              |<\/span> CUST_NAME_IDX |<\/span>    61<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>     BITMAP CONVERSION FROM ROWIDS   |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|<\/span>   8<\/span> |<\/span>      SORT<\/span><\/a> ORDER BY                  |<\/span>               |<\/span>       |<\/span>       |<\/span>            |<\/span>          |<\/span>
\n|*<\/span>  9<\/span> |<\/span>       INDEX RANGE<\/span><\/a> SCAN              |<\/span> CUSTOMERS_PK  |<\/span>    61<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   6<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>
\n       filter(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>
\n   9<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span>)<\/span>
\n
\n37<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o relat\u00f3rio acima podemos constatar que um novo plano (SQL_PLAN_1a11bn5sndsc71a248297) utilizando o \u00edndice foi inserido para a consulta (SQL_15042ba17146e187) e est\u00e1 com o campo ACCEPTED igual NO, por esta raz\u00e3o ele n\u00e3o ser\u00e1 utilizado at\u00e9 que o seu desempenho seja validado e esse campo passe a ser igual a YES.<\/span><\/p>\n

 <\/p>\n

08) Verificar qual plano ser\u00e1 utilizado numa nova execu\u00e7\u00e3o<\/span><\/h2>\n

Vamos executar a consulta novamente para confirmar que o novo plano de execu\u00e7\u00e3o utilizando o \u00edndice n\u00e3o ser\u00e1 utilizado.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<\/div><\/td>
SQL ><\/span> ALTER SYSTEM<\/span><\/a> FLUSH<\/span><\/a> SHARED_POOL;<\/span>
\n
\nSistema alterado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* dbtw-066 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n1xj5b63mpbz67               1<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1xj5b63mpbz67,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\nc.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/span>
\n
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name      |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>    95<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>    95<\/span>  (<\/span>24<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>    1520<\/span> |<\/span>
\n-------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for<\/span> this statement
\n
\n
\n24<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe na se\u00e7\u00e3o NOTE do plano de execu\u00e7\u00e3o que aparece a mensagem: “SQL plan baseline SQL_PLAN_1a11bn5sndsc764541f84 used for this statement”. Esta mensagem confirma que o plano de execu\u00e7\u00e3o utilizado foi aquele que acessa a tabela CUSTOMERS usando opera\u00e7\u00e3o TABLE ACCESS FULL.<\/span><\/p>\n

 <\/p>\n

09) Executar o procedimento de evolu\u00e7\u00e3o do SPM<\/span><\/h2>\n

Ao habilitar o gerenciamento dos planos de execu\u00e7\u00e3o utilizando o SPM garantimos que nenhum plano de execu\u00e7\u00e3o ser\u00e1 substitu\u00eddo por outro sem que antes o Oracle verifique se o desempenho deste novo plano \u00e9 melhor que aqueles j\u00e1 utilizados, este mecanismo impede a regress\u00e3o de desempenho na execu\u00e7\u00e3o das instru\u00e7\u00f5es SQL, para evoluir o plano da nossa consulta vamos executar a procedure EVOLVE_SQL_PLAN_BASELINE do pacote DBMS_SPM.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
<\/div><\/td>
SQL ><\/span> SET SERVEROUTPUT ON TAB OFF
\nSQL ><\/span> SET LONG 10000<\/span>
\nSQL ><\/span>
\nSQL ><\/span> DECLARE<\/span>
\n  2<\/span>     report clob;<\/span>
\n  3<\/span>  BEGIN
\n  4<\/span>     report :=<\/span> dbms_spm.<\/span>evolve_sql_plan_baseline(<\/span>'SQL_15042ba17146e187'<\/span>,<\/span>'SQL_PLAN_1a11bn5sndsc71a248297'<\/span>,<\/span>VERIFY=><\/span>'YES'<\/span>,<\/span>COMMIT=><\/span>'YES'<\/span>)<\/span>;<\/span>
\n  5<\/span>     DBMS_OUTPUT.<\/span>PUT_LINE(<\/span>report)<\/span>;<\/span>
\n  6<\/span>  END<\/span><\/a>;<\/span>
\n  7<\/span>  \/<\/span>
\nGENERAL INFORMATION SECTION
\n---------------------------------------------------------------------------------------------<\/span>
\n
\n Task Information:<\/span>
\n
\n---------------------------------------------<\/span>
\n Task Name            :<\/span> TAREFA_171
\n Task Owner           :<\/span> SYSTEM<\/span><\/a>
\n Execution Name       :<\/span> EXEC_951
\n Execution Type       :<\/span> SPM EVOLVE
\n Scope                :<\/span> COMPREHENSIVE
\n Status               :<\/span> COMPLETED
\n Started              :<\/span> 09<\/span>\/<\/span>14<\/span>\/<\/span>2020<\/span> 20<\/span>:<\/span>05<\/span>:<\/span>17<\/span>
\n Finished             :<\/span> 09<\/span>\/<\/span>14<\/span>\/<\/span>2020<\/span> 20<\/span>:<\/span>05<\/span>:<\/span>17<\/span>
\n Last Updated         :<\/span> 09<\/span>\/<\/span>14<\/span>\/<\/span>2020<\/span> 20<\/span>:<\/span>05<\/span>:<\/span>17<\/span>
\n Global<\/span> Time<\/span><\/a> Limit    :<\/span> 2147483646<\/span>
\n Per-<\/span>Plan Time<\/span><\/a> Limit  :<\/span> UNUSED
\n Number of Errors     :<\/span> 0<\/span>
\n
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nSUMMARY
\nSECTION
\n---------------------------------------------------------------------------------------------<\/span>
\n  Number of plans processed  :<\/span> 1<\/span>
\n  Number of findings         :<\/span> 2<\/span>
\n  Number of recommendations  :<\/span> 1<\/span>
\n  Number of errors           :<\/span> 0<\/span>
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nDETAILS
\nSECTION
\n---------------------------------------------------------------------------------------------<\/span>
\n Object ID          :<\/span> 2<\/span>
\n Test Plan Name     :<\/span> SQL_PLAN_1a11bn5sndsc71a248297
\n Base Plan Name     :<\/span> SQL_PLAN_1a11bn5sndsc764541f84
\n SQL Handle         :<\/span> SQL_15042ba17146e187
\n Parsing Schema     :<\/span> SH
\n Test Plan Creator  :<\/span> SYSTEM<\/span><\/a>
\n SQL Text           :<\/span> SELECT \/* dbtw-066 *\/<\/span> c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\n                    c.<\/span>cust_last_name FROM sh.<\/span>customers c WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n                    AND c.<\/span>cust_id <=<\/span>400<\/span> AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nExecution Statistics:<\/span>
\n-----------------------------<\/span>
\n                    Base Plan                     Test Plan
\n                    ----------------------------<\/span>  ----------------------------<\/span>
\n Elapsed Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>  .000192<\/span>                       .000019<\/span>
\n CPU Time<\/span><\/a> (<\/span>s)<\/span>:<\/span>      .000198<\/span>                       .000015<\/span>
\n Buffer Gets:<\/span>       151<\/span>                           0<\/span>
\n Optimizer Cost:<\/span>    95<\/span>                            6<\/span>
\n Disk Reads:<\/span>        0<\/span>         0<\/span>
\n Direct Writes:<\/span>     0<\/span>                             0<\/span>
\n Rows Processed:<\/span>    0<\/span>                             0<\/span>
\n Executions:<\/span>        10<\/span>                            10<\/span>
\n
\n
\nFINDINGS
\nSECTION
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nFindings (<\/span>2<\/span>)<\/span>:<\/span>
\n-----------------------------<\/span>
\n 1<\/span>.<\/span> O plano foi verificado em 0.03500<\/span> segundos.<\/span> Ele passou o crit\u00e9rio de
\n    benef\u00edcio porque seu desempenho verificado foi 252.55568<\/span> vezes melhor do<\/span> que a do<\/span> plano base.<\/span>
\n 2<\/span>.<\/span> O plano foi aceito automaticamente.<\/span>
\n
\nRecommendation:<\/span>
\n-----------------------------<\/span>
\n Consider accepting the plan.<\/span>
\n
\n
\n
\nEXPLAIN PLANS SECTION
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nBaseline Plan
\n-----------------------------<\/span>
\n Plan Id          :<\/span> 301<\/span>
\n
\nPlan Hash<\/span><\/a> Value  :<\/span> 1683234692<\/span>
\n
\n--------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation           |<\/span> Name      |<\/span> Rows |<\/span> Bytes |<\/span> Cost |<\/span> Time<\/span><\/a>     |<\/span>
\n--------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT    |<\/span>           |<\/span>    1<\/span> |<\/span>    20<\/span> |<\/span>   95<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span> *<\/span> 1<\/span> |<\/span>   TABLE ACCESS FULL |<\/span> CUSTOMERS |<\/span>    1<\/span> |<\/span>    20<\/span> |<\/span>   95<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n--------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n------------------------------------------<\/span>
\n*<\/span> 1<\/span> -<\/span> filter(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span>)<\/span>
\n
\n
\nTest Plan
\n-----------------------------<\/span>
\n Plan Id          :<\/span> 302<\/span>
\n Plan Hash<\/span><\/a> Value  :<\/span> 438600343<\/span>
\n
\n
\n------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                             |<\/span> Name          |<\/span> Rows |<\/span> Bytes |<\/span> Cost |<\/span> Time<\/span><\/a>     |<\/span>
\n------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                      |<\/span>               |<\/span>    1<\/span> |<\/span>    20<\/span> |<\/span>    6<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID BATCHED |<\/span> CUSTOMERS     |<\/span>    1<\/span> |<\/span>    20<\/span> |<\/span>    6<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>    BITMAP CONVERSION TO ROWIDS        |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span>   3<\/span> |<\/span>     BITMAP AND                        |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span>   4<\/span> |<\/span>      BITMAP CONVERSION FROM ROWIDS    |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span>   5<\/span> |<\/span>       SORT<\/span><\/a> ORDER BY                   |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span> *<\/span> 6<\/span> |<\/span>        INDEX RANGE<\/span><\/a> SCAN               |<\/span> CUST_NAME_IDX |<\/span>      |<\/span>       |<\/span>    2<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>      BITMAP CONVERSION FROM ROWIDS    |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span>   8<\/span> |<\/span>       SORT<\/span><\/a> ORDER BY                   |<\/span>               |<\/span>      |<\/span>       |<\/span>      |<\/span>          |<\/span>
\n|<\/span> *<\/span> 9<\/span> |<\/span>        INDEX RANGE<\/span><\/a> SCAN               |<\/span> CUSTOMERS_PK  |<\/span>      |<\/span>       |<\/span>    2<\/span> |<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>
\n------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information
\n(<\/span>identified by operation id)<\/span>:<\/span>
\n------------------------------------------<\/span>
\n*<\/span> 6<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>
\n*<\/span> 6<\/span> -<\/span> filter(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>
\n*<\/span> 9<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span>)<\/span>
\n
\n---------------------------------------------------------------------------------------------<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no relat\u00f3rio na se\u00e7\u00e3o EXECUTION STATISTICS que o SPM simula a execu\u00e7\u00e3o do plano n\u00e3o aceito e compara as estat\u00edsticas de desempenho dos dois planos, o que estava ACEITO na BASELINE e o novo plano. Na sequ\u00eancia temos a se\u00e7\u00e3o FINDINGS onde o SPM indica que o novo plano foi aceito.<\/span><\/p>\n

 <\/p>\n

10) Executar a consulta novamente com o novo plano<\/span><\/h2>\n

Vamos executar a consulta novamente e verficar se houve alguma evolu\u00e7\u00e3o no plano de execu\u00e7\u00e3o utilizado pelo Otimizador.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
<\/div><\/td>
SQL ><\/span> SELECT \/* dbtw-066 *\/<\/span>
\n  2<\/span>         c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span> c.<\/span>cust_last_name
\n  3<\/span>    FROM sh.<\/span>customers c
\n  4<\/span>   WHERE c.<\/span>cust_id >=<\/span>100<\/span>
\n  5<\/span>     AND c.<\/span>cust_id <=<\/span>400<\/span>
\n  6<\/span>     AND c.<\/span>cust_last_name like 'Her%'<\/span>;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n       167<\/span> Eve                  Herd
\n       161<\/span> Linda                Hermann
\n
\n2<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-066%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%sql_text%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n1xj5b63mpbz67               0<\/span>
\n
\n1<\/span> linha selecionada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1xj5b63mpbz67,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-066 *\/<\/span>        c.<\/span>cust_id,<\/span> c.<\/span>cust_first_name,<\/span>
\nc.<\/span>cust_last_name   FROM sh.<\/span>customers c  WHERE c.<\/span>cust_id >=<\/span>100<\/span>    AND
\nc.<\/span>cust_id <=<\/span>400<\/span>    AND c.<\/span>cust_last_name like 'Her%'<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 203008431<\/span>
\n
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name          |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>     6<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> CUSTOMERS     |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    20<\/span> |<\/span>     6<\/span>  (<\/span>34<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   BITMAP CONVERSION TO ROWIDS       |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      2<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   3<\/span> |<\/span>    BITMAP AND                       |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       4<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>     BITMAP CONVERSION FROM ROWIDS   |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|<\/span>   5<\/span> |<\/span>      SORT<\/span><\/a> ORDER BY                  |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>     82<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  6<\/span> |<\/span>       INDEX RANGE<\/span><\/a> SCAN              |<\/span> CUST_NAME_IDX |<\/span>      1<\/span> |<\/span>     61<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     82<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|<\/span>   7<\/span> |<\/span>     BITMAP CONVERSION FROM ROWIDS   |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|<\/span>   8<\/span> |<\/span>      SORT<\/span><\/a> ORDER BY                  |<\/span>               |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>    301<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n|*<\/span>  9<\/span> |<\/span>       INDEX RANGE<\/span><\/a> SCAN              |<\/span> CUSTOMERS_PK  |<\/span>      1<\/span> |<\/span>     61<\/span> |<\/span>       |<\/span>     2<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>    301<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   6<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>
\n       filter(<\/span>(<\/span>"C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span> AND "C"<\/span>.<\/span>"CUST_LAST_NAME"<\/span> LIKE 'Her%'<\/span>)<\/span>)<\/span>
\n   9<\/span> -<\/span> access(<\/span>"C"<\/span>.<\/span>"CUST_ID"<\/span>>=<\/span>100<\/span> AND "C"<\/span>.<\/span>"CUST_ID"<\/span><=<\/span>400<\/span>)<\/span>
\n
\nNote
\n-----<\/span>
\n   -<\/span> SQL plan baseline SQL_PLAN_1a11bn5sndsc71a248297 used for<\/span> this statement
\n
\n
\n34<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o constatamos que o \u00edndice CUST_NAME_IDX criado na etapa 05 foi utilizado, este plano de execu\u00e7\u00e3o utilizando \u00edndice \u00e9 muito mais eficiente, o plano que fazia FULL SCAN na tabela CUSTOMERS estava lendo 1.520 Buffers, o novo plano leu somente 6 Buffers na Memoria do banco.<\/span><\/p>\n

 <\/p>\n

11) Eliminar a BASELINE criada<\/span><\/h2>\n

Vamos eliminar a BASELINE e o \u00cdNDICE para deixar o esquema SH como estava antes da simula\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<\/div><\/td>
SQL ><\/span> SET SERVEROUTPUT ON
\nSQL ><\/span> DECLARE<\/span>
\n  2<\/span>    l_plans_dropped  PLS_INTEGER;<\/span>
\n  3<\/span>  BEGIN
\n  4<\/span>    l_plans_dropped :=<\/span> DBMS_SPM.<\/span>drop_sql_plan_baseline (<\/span>
\n  5<\/span>      sql_handle =><\/span> 'SQL_15042ba17146e187'<\/span>)<\/span>;<\/span>
\n  6<\/span>
\n  7<\/span>    DBMS_OUTPUT.<\/span>put_line(<\/span>l_plans_dropped)<\/span>;<\/span>
\n  8<\/span>  END<\/span><\/a>;<\/span>
\n  9<\/span>  \/<\/span>
\n2<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> drop index sh.<\/span>CUST_NAME_IDX;<\/span>
\n
\n\u00cdndice eliminado.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Refer\u00eancias<\/span><\/h2>\n

 <\/p>\n

https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/tgsql\/overview-of-sql-plan-management.html<\/a><\/span><\/strong><\/p>\n

 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Voc\u00ea j\u00e1 ouviu algu\u00e9m dizer: Minha aplica\u00e7\u00e3o ficou lenta e nada foi alterado. Este problema relativamente comum pode ser evitado se voc\u00ea implementar no banco Oracle o recurso SQL PLAN MANAGEMENT (SPM), este recurso esta dispon\u00edvel a partir vers\u00e3o 11G e n\u00e3o\u00a0requer a licen\u00e7a das Options Diagnostic & Tuning Pack. Basicamente\u00a0o SPM \u00e9 um mecanismo preventivo que permite ao Otimizador<\/p>\n","protected":false},"author":2,"featured_media":2214,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[156],"tags":[241,240,82,10,242,136,157],"class_list":["post-2213","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-plan-management","tag-display_sql_plan_baseline","tag-evolve_sql_plan_baseline","tag-optimizer","tag-otimizador","tag-plan-stability","tag-sql-plan-baseline","tag-sql-plan-management"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2213","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=2213"}],"version-history":[{"count":15,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2213\/revisions"}],"predecessor-version":[{"id":2237,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2213\/revisions\/2237"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2214"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2213"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}