Nenhum resultado encontrado

Sua pesquisa não corresponde a nenhum resultado.

Sugerimos que você tente o seguinte para ajudar a encontrar o que procura:

  • Verifique a ortografia da sua pesquisa por palavra-chave.
  • Use sinônimos para a palavra-chave digitada; por exemplo, tente “aplicativo” em vez de “software.”
  • Tente uma das pesquisas populares mostradas abaixo.
  • Inicie uma nova pesquisa.
Perguntas Frequentes

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;  
 /     

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.