Oracle SQL Patch.

Por Rogerio Eguchi
Publicado em Março 2018

Revisado por Eluizio Barretto


Em grande parte dos sistemas o código da aplicação é estático e portanto não pode ser alterado pelos usuários.

Caso ocorra um problema de performance em uma consulta SQL, uma das possíveis soluções seria modificar a consulta e incluir um hint para forçar um plano de acesso desejado.

Em situações críticas em que uma solução rápida deva ser empregada, uma alternativa ao SQL Plan Management ou SQL Profiles é a utilização do Oracle SQL Patch.

Esse recurso permite ao DBA anexar um hint à consulta problemática em tempo de execução, sem a necessidade de alteração no código da aplicação.

Na versão Oracle 12.2 podemos usar a API DBMS_SQLDIAG. Já nas versões Oracle 12.1 e 11g utilize a API dbms_sqldiag_internal.



Demonstração de utilização:

Considere a tabela t com as colunas X number e DATA varchar2(20), um índice T_IDX na coluna X e com a seguinte distribuição de dados:


SQL> select count(*), x from t group by x;

  COUNT(*)          X
---------- ----------
  44411210          1
        11          2


A consulta abaixo deveria realizar um index range scan:


SQL> select count(data) from t where x = 2;
         11

SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  0g8grhjf9dch9, child number 0
-------------------------------------
select count(data) from t where x = 2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 54281 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    22M|   508M| 54281   (4)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=2)



Porém como não existe histograma para a coluna X, a consulta realiza um Full Table Scan (FTS).


Podemos confirmar na dba_tab_columns que a coluna histogram não está definida.


SQL> SELECT owner,
           table_name,
       column_name,
           column_id,
       histogram
FROM   dba_tab_columns
WHERE  owner = 'HR' and table_name = 'T'
ORDER BY 1,2,3,4;

OWNER           TABLE_NAME           COLUMN_NAME     COLUMN_ID  HISTOGRAM
--------------- -------------------- --------------- ---------- ---------------
HR              T                    DATA                     2 NONE
HR              T                    X                        1 NONE



A correção para essa situação seria coletar estatística para a tabela T e gerar o histograma. Isso será feito através do comando abaixo:


SQL> EXEC DBMS_STATS.gather_table_stats('HR','T');
PL/SQL procedure successfully completed.


Podemos agora observar a mudança no plano de acesso para INDEX RANGE SCAN:


SQL> select count(data) from t where x = 2;
         11

SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  0g8grhjf9dch9, child number 0
-------------------------------------
select count(data) from t where x = 2

Plan hash value: 1339972470

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |     4 (100)|          |
|   1 | SORT AGGREGATE                       |       |     1 |    24 |            |          |
|   2 | TABLE ACCESS BY INDEX ROWID BATCHED  | T     |    11 |   264 |     4   (0)| 00:00:01 |
|*  3 | INDEX RANGE SCAN                     | T_IDX |    11 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"=2)



E confirmar na que o histograma de frequência para a coluna X foi criado:


SQL> SELECT owner,
           table_name,
       column_name,
           column_id,
       histogram
FROM   dba_tab_columns
WHERE  owner = 'HR' and table_name = 'T'
ORDER BY 1,2,3,4;
  2    3    4    5    6    7    8

OWNER           TABLE_NAME           COLUMN_NAME      COLUMN_ID HISTOGRAM
--------------- -------------------- --------------- ---------- ---------------
HR              T                    DATA                     2 NONE
HR              T                    X                        1 FREQUENCY



Mas vamos resolver de uma forma diferente, ou seja, ao invés de coletar estatística criaremos um SQL Patch !

Primeiro passo será remover o histograma da tabela:


SQL> exec dbms_stats.delete_column_stats( ownname=>'HR', tabname=>'T', colname=>'X', 
col_stat_type=>'HISTOGRAM');
PL/SQL procedure successfully completed.



E então criaremos um SQL Patch para corrigir o plano de acesso:


SQL> DECLARE
   patch_name  VARCHAR2(32767);
BEGIN
  -- SQL ID
    patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
    sql_id    => '0g8grhjf9dch9',
    hint_text => 'INDEX(@"SEL$1" "T"@"SEL$1")',
    name      => 'sql_patch_1');
END;
/



Na view dba_sql_patches podemos confirmar a criação do sql patch:


SQL> select name, status, created, sql_text from dba_sql_patches;

NAME            STATUS     CREATED                        SQL_TEXT
--------------- ---------- ------------------------------ -------------------------------------
sql_patch_1     ENABLED    18-DEC-17 06.09.36.000000 PM   select count(data) from t where x = 2



Executando novamente a consulta, podemos confirmar a utilização do SQL Patch:


SQL> select count(data) from t where x = 2;
         11

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST OUTLINE BASIC NOTE'));
EXPLAINED SQL STATEMENT:
------------------------
select count(data) from t where x = 2

Plan hash value: 1339972470

------------------------------------------------------
| Id  | Operation                            | Name  |
------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |
|   1 |  SORT AGGREGATE                      |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |
|   3 |    INDEX RANGE SCAN                  | T_IDX |
------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Note
-----
   - SQL patch "sql_patch_1" used for this statement



Caso precise ou queira remover o SQL Patch criado, execute:


BEGIN
  DBMS_SQLDIAG.drop_sql_patch(name => 'sql_patch_1');
END;
/



O SQL Patch faz parte do Oracle SQL Repair Advisor, não tem custo de licenciamento e pode ser utilizado nas versões do Oracle Standard e Enterprise.
O SQL Plan Management está disponível somente na versão Oracle Enterprise.
O Oracle SQL Profiles está disponível somente na versão Oracle Enterprise e tem o custo extra de licenciamento do Oracle Tuning Pack.


Referências:

DBMS_SQLDIAG - https://docs.oracle.com/database/122/ARPLS/DBMS_SQLDIAG.htm#ARPLS203
Adding and Disabling Hints Using SQL Patch - https://blogs.oracle.com/optimizer/adding-and-disabling-hints-using-sql-patch
Using SQL Patch to add hints to a packaged application - https://blogs.oracle.com/optimizer/using-sql-patch-to-add-hints-to-a-packaged-application
Additional Information on SQL Patches - https://blogs.oracle.com/optimizer/additional-information-on-sql-patches
Oracle Licensing Information Manual - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dblic/Licensing-Information.html



Rogerio Bacchi Eguchi é um DBA Oracle Sênior com extensa experiência em ambientes OLTP de missão crítica e que empregam as tecnologias da "Oracle Maximum Availability Architecture". Atuou como DBA em empresas como Oracle, UOL, UOLDiveo, PagSeguro e atualmente atua como DBA/DMA na TOTVS. Possui as certificações Oracle OCP 8i, 9i, 10g, 11g, 12c e OCE Exadata. Atua também como SysAdmin/DEVOPS onde é certificado Linux RHCE. Entusiata de novas tecnologias como big data, automações e cloud computing. Compartilha conhecimento no blog reguchi.wordpress.com e twitter @reguchi_br.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.