Mostrando postagens com marcador sql tuning. Mostrar todas as postagens
Mostrando postagens com marcador sql tuning. Mostrar todas as postagens

quarta-feira, 23 de novembro de 2011

Tuning na Instruçao SQL LIKE no Oracle


Fala pessoal, vou postar aqui um dica simples, que é quando se precisa de um tunning rápido nas declarações "LIKE"  que o CBO naturalmente ignora os índices existentes nas colunas utilizadas pelo LIKE para uso de um FTS. Quando ocorre isso temos o recurso de utilizar um hint forçando a utilização do índice.

No Blog do Laurent Schneider tem um demo bem legal.
Abaixo a demonstração.

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 || ' '
 || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true);

SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5044 |   123K| 22123   (1)| 00:04:26 |
|*  1 |  TABLE ACCESS FULL| LSC_T |  5044 |   123K| 22123   (1)| 00:04:26 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
  379  recursive calls
    0  db block gets
  100090  consistent gets
  100016  physical reads
    0  redo size
    2066  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    5  sorts (memory)
    0  sorts (disk)
   35  rows processed

SWLQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  5044 |   123K|  3574   (1)| 00:00:43 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LSC_T |  5044 |   123K|  3574   (1)| 00:00:43 |
|*  2 |   INDEX FULL SCAN           | LSC_I |  5000 |       |   220   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
    1  recursive calls
    0  db block gets
  360  consistent gets
  322  physical reads
    0  redo size
  142849  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
   35  rows processed


segunda-feira, 1 de agosto de 2011

Recuperando Variável Bind com a V$SQL_BIND_CAPTURE

Segue abaixo uma forma bem simples de pegar o valor de uma variável bind utilizando a view V$SQL_BIND_CAPTURE . Precisei recentemente pegar os valores de uma consulta que uma sessão estava realizando e o trace não estava ajudando por "n" fatores e utilizando o velho dicionario de dados foi bem mais simples nesse caso.

1_ Buscando o valor o sql_id na V$SQL :

SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%all_tab_columns%';

SQL_TEXT SQL_ID
---------------------------------------- -------------
select col.*, com.Comments from sys.all_ 032n4avhdnaz3
tab_columns col, sys.all_col_commen
WHERE o.owner LIKE :1 ESCAPE '/'
AND o.object_name LIKE 2: ESCAPE '/'


Atenção a identificação das binds , essa mesma posição vai ser recuperada logo a frente.

2_ Olhando a V$SQL_BIND_CAPTURE

SQL>
 desc V$SQL_BIND_CAPTURE

Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
CHILD_ADDRESS RAW(8)
CHILD_NUMBER NUMBER
NAME VARCHAR2(30)
POSITION NUMBER
DUP_POSITION NUMBER
DATATYPE NUMBER
DATATYPE_STRING VARCHAR2(15)
CHARACTER_SID NUMBER
PRECISION NUMBER
SCALE NUMBER
MAX_LENGTH NUMBER
WAS_CAPTURED VARCHAR2(3)
LAST_CAPTURED DATE
VALUE_STRING VARCHAR2(4000)
VALUE_ANYDATA ANYDATA

3_Buscando o valor da bind desejada :

SQL_ SELECT NAME,TO_CHAR(LAST_CAPTURED,'DD/MM/YYYY HH24:MI:SS'),VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='0prhvnya3f97z' ;

NAME TO_CHAR(LAST_CAPTUR VALUE_STRING
------------------------------ ------------------- ------------------------------
:1 01/08/2011 16:24:39 REPORT_SERVER_AALTAMIRANO
:2 01/08/2011 16:24:39 ROLE
Pronto, de forma simples foi recuperado os valores passados no filtro da consulta SQL.

quinta-feira, 7 de abril de 2011

Métodos de Joins no Oracle Parte III


HASH JOIN
Os dois conjuntos de dados processados por um Hash Join são chamados de build input e probe input. A build input é a esquerda, e a probe input é a entrada à direita. Como ilustrado na Figura abaixo, usando cada linha da entrada da build input, uma tabela hash na memória (ou espaço temporário, se não há memória suficiente está disponível) é construída.
Observe que a chave de hash usada para esse fim é calculada com base nas colunas usadas como condição de join. Uma vez que a tabela hash contém todos os dados do build input, o processamento da probe input começa.
Cada linha é testada contra a tabela hash, a fim de descobrir se ele cumpre a condição de join. Obviamente, apenas registros coincidentes são retornados.


                * Visão do processo de um Hash Join

Hash Joins são caracterizados pelas seguintes proriedades :
·         Cada operação Filha só é executada uma vez
·         A tabela hash é construída sobre a entrada da esquerda. Por conseqüência , é geralmente construída na menor entrada.
·         Antes de retornar a primeira linha, somente a entrada deesquerda deve ser totalmente processado.
·         Cross joins, theta joins e partitiouned outer joins não são suportados.
Exemplo de Hash Join.
O exemplo a seguir é um simples plano de execução mostrando o processamento de um Hash Joins entre duas tabelas, O exemplo também mostra como forçar uma hash join por meio das dos hints leading  e use_hash.

SQL_IRAJA  > SELECT /*+ leading(t1) use_hash(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  1180 |    30   (4)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    10 |  1180 |    30   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    57 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   100 |  6100 |    24   (0)| 00:00:01 |
---------------------------------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=19)

 O processamento do plano de execução pode ser resumido da seguinte forma  :

·         Todas as linhas da tabela t1 são lidas através de um full table scan, a restrição n = 19 é aplicada, e uma tabela de hash é construído com as linhas resultantes.  Para construir a tabela de hash, uma função hash é aplicada para as colunas usadas na condição de join (id).
·         Todas as linhas da tabela T2 são ligas através de um full table scan, uma função hash é aplicada para as colunas usadas na condição de join (t2_id) e a tabela de hash entra no modo probed. Se for encontrado os dados correspondentes as linhas são retornadas

A limitação mais importante para a operação HASH JOIN (como para as outras operações combinadas não relacionadas) é a incapacidade de tirar proveito dos índices para aplicar nas condições de join. Isto significa que os índices podem ser utilizados como caminho de acesso somente se as restrições estão disponíveis. Por exemplo, se a restrição n = 19 fornece boa seletividade, pode ser útil para criar um índice para aplicá-la.

SQL_IRAJA > CREATE INDEX t1_n ON t1 (n)

In fact, with this index in place, the following execution plan might be used. Note that the
table t1 is no longer accessed through a full table scan.

Na verdade, com esse índice em vigor, o plano de execução a seguir pode ser usado. Observe que a tabela t1 já não é mas acessada através de um full table scan.

-------------------------------------------------------------------------------------                                                                                                                                                          
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                          
-------------------------------------------------------------------------------------                                                                                                                                                          
|   0 | SELECT STATEMENT             |      |    10 |  1180 |    27   (4)| 00:00:01 |                                                                                                                                                           
|*  1 |  HASH JOIN                   |      |    10 |  1180 |    27   (4)| 00:00:01 |                                                                                                                                                           
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    57 |     2   (0)| 00:00:01 |                                                                                                                                                           
|*  3 |    INDEX RANGE SCAN          | T1_N |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                           
|   4 |   TABLE ACCESS FULL          | T2   |   100 |  6100 |    24   (0)| 00:00:01 |                                                                                                                                                           
-------------------------------------------------------------------------------------                                                                                                                                                           
                                                                                                                                                
   1 - access("T1"."ID"="T2"."T1_ID")                                                                                                                                                                                                          
   3 - access("T1"."N"=19)