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.